Mar 2, 2005

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$/;
 }
 
 };

About Me

My Photo

つくばで働く研究者

Total Pageviews

Amazon

Copyright 2012 Ogawa::Buzz | Powered by Blogger
Design by Web2feel | Blogger Template by NewBloggerThemes.com