银河星尘

性能测试, 数据库
http://obmem.com/?p=317

TNND，今天浪费了一天的时间在Mysql上面，先是改代码，然后是转换sqlite3数据库到mysql，然后发现原来好好的网站跑不起来了。 @。@ 然后就这么折腾了半天，基本上确定了，在select语句上，mysql的性能平均落后sqlite十倍左右，内存消耗超过sqlite则是三倍左右。
-
实际上mysql更灵活点，我的意思是：给mysql三倍的内存，那么他的表现只比sqlite慢十倍而已，如果你给他很抠门的内存？那么超时是唯一的结果。就像我一开始网站挂掉那样。
-
阅读全文…

http://stackoverflow.com/questions/1303514/why-does-dbh-dovacuum-fail-with-perls-dbdsqlite/1303537
这网站不错……

Why does $dbh->do(’VACUUM’) fail with Perl’s DBD::SQLite?

I want to do VACUUM at a certain time on a SQLite database under Perl, but it always says

    DBD::SQLite::db do failed: cannot VACUUM from within a transaction

So how do I do this?

my %attr = ( RaiseError => 0, PrintError => 1, AutoCommit => 0 );
my $dbh = DBI->connect('dbi:SQLite:dbname='.$file'','',\%attr) 
    or die $DBI::errstr;

I am using AutoCommit => 0. And the error happens while:

$dbh->do('DELETE FROM soap');
$dbh->do('DELETE FROM result');
$dbh->commit; 
$dbh->do('VACUUM');

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
 
my $dbh = DBI->connect( 'dbi:SQLite:test.db', undef, undef,
    { RaiseError => 1, AutoCommit => 1} );
$dbh->do('VACUUM');
$dbh->disconnect;

You don’t have to give up on transactions to be able to VACUUM: You can use the following so that AutoCommit is turned on for VACUUM and after the VACUUM the AutoCommit state is reverted back to whatever it was. Add error checking to taste if you do not set RaiseError.

sub do_vacuum {
    my ($dbh) = @_;
    local $dbh->{AutoCommit} = 1;
    $dbh->do('VACUUM');
    return;
}

Call it:

do_vacuum($dbh);

阅读全文…