Benchmark: Importing Multiple Entries
This benchmark is intended to make clear the performance characteristics of the underlying DB engines which can be used by Movable Type.
I recognize this is not a typical transaction for Movable Type. My intention of performing this benchmark was to test the hardest transactions for any DBs, because importing has leastly-read but mostly-write operations. Rebuilding takes a long time but it is a mostly-read case and doesn't push DBs so hardly. I believe that the rebuilding time is dominated by Disk I/O rather than DB accesses. Also thinking about other cases such as coutinueously commenting or tbpinging, CGI setup time is supposed to be dominant.
Method
Importing multiple entries (40, 200, 1000) and couting the cpu times and elapsed times by using Benchmark and Time::HiRes modules. In each cases, I ran the benchmark four times and picked up the best case.
Server Specification
- Intel Pentium 4 2.40Ghz 384MB Memory
- Fedora Core 3
- Apache httpd 2.0.52
- MySQL 3.23.58
- Movable Type 3.15
Movable Type and MySQL are running on a server.
Importing Data
All entry have "GNU Public License" as their entry body, and they have diffrent titles and same one category.
DB Engines
- DBD
- Berkeley DB 4.2.52 + DB_File 1.809
- MySQL
- MySQL 3.23.58 + DBD-mysql 2.903
- SQLite2
- DBD-SQLite2 0.33
- SQLite3
- DBD-SQLite 1.0.8
- MySQL*
- MySQL 3.23.58 + DBD-mysql 2.903 + w/o AutoCommit
- SQLite2*
- DBD-SQLite2 0.33 + w/o AutoCommit
- SQLite3*
- DBD-SQLite 1.0.8 + w/o AutoCommit
MySQL*, SQLite2*, and SQLite3* disable AutoCommit option and do "begin_work" and "commit" at the start and end of the importing process.
Results
Importing 40 Entries
Importing 200 Entries
Importing 1000 Entries
Quick Observations
- BDB is slowest in any cases.
- SQLite3 is 5%-30% faster than SQLite2. Upgrading SQLite version is meaningful.
- If "AutoCommit" turns on, SQLite2 and SQLite3 are as slow as BDB.
- If "AutoCommit" turns off, SQLite3 can be faster than MySQL.
- For MySQL, the effect of turning "AutoCommit" off is quite small or at least not a positive option.
No AutoCommit Patch
Here is a patch for disabling AutoCommit option, which is used in this benchmark.
diff -ru MT-3.15-full-en_US/lib/MT/ObjectDriver/DBI/sqlite.pm MT-3.15-full-en_US-noautocommit/lib/MT/ObjectDriver/DBI/sqlite.pm
--- MT-3.15-full-en_US/lib/MT/ObjectDriver/DBI/sqlite.pm 2004-08-17 09:40:01.000000000 +0900
+++ MT-3.15-full-en_US-noautocommit/lib/MT/ObjectDriver/DBI/sqlite.pm 2005-03-02 21:17:00.403018954 +0900
@@ -34,9 +34,18 @@
{ RaiseError => 0, PrintError => 1 })
or return $driver->error("Connection error: " . $DBI::errstr);
$driver->{dbh}->{sqlite_handle_binary_nulls} = 1;
+ $driver->{dbh}->begin_work;
$driver;
}
+sub DESTROY {
+ my $dbh = $_[0]->{dbh};
+ if ($dbh) {
+ $dbh->commit;
+ $dbh->disconnect;
+ }
+}
+
sub _prepare_from_where {
my $driver = shift;
my($class, $terms, $args) = @_;
diff -ru MT-3.15-full-en_US/mt-load.cgi MT-3.15-full-en_US-noautocommit/mt-load.cgi
--- MT-3.15-full-en_US/mt-load.cgi 2005-01-25 12:37:45.000000000 +0900
+++ MT-3.15-full-en_US-noautocommit/mt-load.cgi 2005-03-02 21:13:54.835168910 +0900
@@ -298,6 +298,8 @@
$map->save
or die "Save failed: ", $map->errstr;
}
+ MT::Object->driver->{dbh}->commit
+ if $mt->{cfg}->ObjectDriver =~ /^DBI::sqlite$/;
}
};