Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Why is DBD::Pg slower than DBD::mysql?

by danb (Friar)
on Jun 04, 2005 at 06:39 UTC ( [id://463484]=perlquestion: print w/replies, xml ) Need Help??

danb has asked for the wisdom of the Perl Monks concerning the following question:

Tom Lane benchmarked DBD::Pg and DBD::mysql today and found that mysql was much faster at executing the client-side code, even ignoring the server-side portion. He supposes that it's because more of the mysql driver is written in C.

I would like to pose his question to the Perl Monks: Can anyone who knows more about Perl confirm or deny the findings? Anyone want to step up to fix it?

Quoting Tom Lane:

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

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

Replies are listed 'Best First'.
Re: Why is DBD::Pg slower than DBD::mysql?
by demerphq (Chancellor) on Jun 04, 2005 at 13:14 UTC

    I bet a bunch of the Pg code could be improved. I looked through it and spotted a couple of examples of less than efficient coding that make me think that the Pg code could be improved somewhat without resorting to C. OTOH, MySQL appears to have a better structure to its client code and XS. For instance the MySQL do() statement does everythign in C, where the Pg code does an awful lot of method calls. Assuming each subroutine call has a measurable overhead to it, its clear that the ~300100 calls that occur under MySQL are going to be more efficient that the ~4.2 million calls that DBD::Pg does for the same thing. And in the latter case the calls are primarily method lookups which are even slower than subroutine calls.

    Pruning the call tree graph involved in a do() statement would be the first place id look. Getting rid of the dependency on DBI::st would also seem prudent.

    ---
    $world=~s/war/peace/g

Re: Why is DBD::Pg slower than DBD::mysql?
by calin (Deacon) on Jun 05, 2005 at 12:07 UTC

    DBD::Pg has never been state of the art. In fact, it has suffered from lack of dedicated maintainership for years. However, in the last year or so development seems to be picking up. Monk Greg Sabino Mullane aka turnstep is very active in development right now.

    Apart from lack of performance, there are issues with lack of complete backend functionality coverage and stability problems (bugs / crashes / corner cases etc.). Said this, the driver mostly "works" (i.e. it is not in a "broken" state right now).

    The issues with DBD::Pg are a bottleneck which prevents the use of what I think is a superior database IMO in heavy-duty environments. I use the space here to ask people who are involved with real DB application development to try to subscribe to the mailing list and report bugs / fixes, share experiences, post suggestions, ask for features etc. Don't let the trail run cold.

    DBD::Pg needs you!

    Another interesting PostgreSQL / Perl project is PLperl NG. It makes possible to write server-side procedures in Perl, very cool indeed. However, development goes at a very slow pace, this project also desperately needs more people involved.

Re: Why is DBD::Pg slower than DBD::mysql?
by Zaxo (Archbishop) on Jun 04, 2005 at 06:55 UTC

    This doesn't mean much to us without the code.

    After Compline,
    Zaxo

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://463484]
Approved by K_M_McMahon
Front-paged by bart
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (7)
As of 2024-03-28 09:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found