From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-interfaces@postgreSQL.org
Subject: DBD::Pg performance bites compared to DBD::mysql?
Date: Fri, 03 Jun 2005 01:17:16 -0400
Message-ID: <1015.1117775836@sss.pgh.pa.us> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: DBD::Pg performance bites compared to DBD::mysql?
In-Reply-To: <1015.1117775836@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox