In my copious free time ;-), I've been poking at MySQL's "sql-bench" benchmark and trying to understand why it makes us look so bad. There are a number of things involved, but one item that I just realized tonight is that a pretty substantial part of the problem is on the client side. The test driver is written in Perl and depends on DBI/DBD to connect to the database. Here is a Perl Devel::DProf trace for a run of 300000 simple INSERT commands (plus a few CREATE TABLE and other ilk):

PG:

Total Elapsed Time = 231.6619 Seconds
  User+System Time = 132.5019 Seconds
Inclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 104.   4.140 138.81 300007   0.0000 0.0005  DBI::db::do
 101.   27.09 134.67 300007   0.0001 0.0004  DBD::Pg::db::do
 44.2   3.050 58.638 300008   0.0000 0.0002  DBI::db::prepare
 41.9   9.660 55.589 300008   0.0000 0.0002  DBD::Pg::db::prepare
 29.9   8.270 39.629 300008   0.0000 0.0001  DBI::_new_sth
 26.5   35.16 35.160 300008   0.0001 0.0001  DBI::st::execute
 23.6   14.17 31.359 300013   0.0000 0.0001  DBI::_new_handle
 11.0   14.66 14.660 300013   0.0000 0.0000  DBI::_setup_handle
 4.75   6.300  6.300 300008   0.0000 0.0000  DBD::Pg::st::_prepare
 4.75   6.300  6.300 300004   0.0000 0.0000  DBI::st::rows
 4.04   5.350  5.350 600016   0.0000 0.0000  DBI::st::DESTROY
 1.91   2.530  2.530 300013   0.0000 0.0000  DBI::st::TIEHASH
 1.61   2.130  2.130 300011   0.0000 0.0000  DBD::_mem::common::DESTRO
+Y
 0.14   0.109  0.188      7   0.0156 0.0268  main::BEGIN
 0.02   0.020  0.030      1   0.0199 0.0297  DBI::install_driver
[download]

MySQL:

Total Elapsed Time = 115.0148 Seconds
  User+System Time = 31.19480 Seconds
Inclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 69.1   21.57 21.570 300006   0.0001 0.0001  DBI::db::do
 0.57   0.099  0.177      7   0.0142 0.0254  main::BEGIN
 0.06   0.020  0.020      6   0.0033 0.0033  DynaLoader::dl_load_file
 0.06       -  0.020      5        - 0.0040  Cwd::BEGIN
 0.06       -  0.020      3        - 0.0066  DynaLoader::bootstrap
 0.06   0.010  0.020      1   0.0100 0.0197  DBI::install_driver
 0.06   0.010  0.020     12   0.0008 0.0016  DBI::BEGIN
 0.06       -  0.020      2        - 0.0098  DBI::connect
 0.06       -  0.019      2        - 0.0097  db_MySQL::connect
 0.06       -  0.019      1        - 0.0194  db_MySQL::version
 0.03   0.010  0.010      2   0.0050 0.0050  AutoLoader::import
 0.03       -  0.010      2        - 0.0050  POSIX::BEGIN
 0.03       -  0.010      1        - 0.0100  POSIX::import
 0.03   0.010  0.010     10   0.0010 0.0010  vars::import
 0.03       -  0.010      1        - 0.0100  Benchmark::import
[download]

The server-side time isn't all that much different: about 100 sec for us, 85 for them. But there is something way wrong on the Perl side.

I'm not any kind of Perl module guru, but it looks to me like the explanation for the discrepancy is that much of DBD::mysql is written in C while the equivalent code in DBD::Pg is written in Perl. This is killing us for any app written in Perl :-( --- the above trace only shows a factor of 2 total penalty, but I've seen up to a factor of 4 in other cases. There is no way that a client-side driver should be taking several times longer than the backend to process a SQL command :-(

Can anyone who knows more about Perl confirm or deny? Anyone want to step up to fix it?

regards, tom lane