Skip site navigation (1) Skip section navigation (2)

DBD::Pg performance bites compared to DBD::mysql?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-interfaces(at)postgreSQL(dot)org
Subject: DBD::Pg performance bites compared to DBD::mysql?
Date: 2005-06-03 05:17:16
Message-ID: 1015.1117775836@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
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::DESTROY
 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

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

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

Responses

pgsql-interfaces by date

Next:From: Volkan YAZICIDate: 2005-06-03 13:55:51
Subject: Re: libpq, blocking/nonblocking mechanism
Previous:From: setyawankimDate: 2005-06-03 03:10:44
Subject: unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group