pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerl/DBI vs Native
10+ messages / 7 participants
[nested] [flat]
* Perl/DBI vs Native
@ 2008-07-21 11:19 Valentin Bogdanov <valiouk@yahoo.co.uk>
0 siblings, 3 replies; 10+ messages in thread
From: Valentin Bogdanov @ 2008-07-21 11:19 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have ran quite a few tests comparing how long a query takes to execute from Perl/DBI as compared to psql/pqlib. No matter how many times I run the test the results were always the same.
I run a SELECT all on a fairly big table and enabled the log_min_duration_statement option. With psql postgres consistently logs half a second while the exact same query executed with Perl/DBI takes again consistently 2 seconds.
If I were timing the applications I would have been too much surprised by these results, obviously, processing with Perl would be slower than a native application. But it's the postmaster that gives these results. Could it be because the DBI module is slower at assimilating the data?
Any light on the subject would be greatly appreciated.
Regards,
Val
__________________________________________________________
Not happy with your email address?.
Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-21 13:49 Craig Ringer <craig@postnewspapers.com.au>
parent: Valentin Bogdanov <valiouk@yahoo.co.uk>
2 siblings, 0 replies; 10+ messages in thread
From: Craig Ringer @ 2008-07-21 13:49 UTC (permalink / raw)
To: valiouk@yahoo.co.uk; +Cc: pgsql-performance
Valentin Bogdanov wrote:
> Hi,
>
> I have ran quite a few tests comparing how long a query takes to execute from Perl/DBI as compared to psql/pqlib. No matter how many times I run the test the results were always the same.
>
> I run a SELECT all on a fairly big table and enabled the log_min_duration_statement option. With psql postgres consistently logs half a second while the exact same query executed with Perl/DBI takes again consistently 2 seconds.
>
> If I were timing the applications I would have been too much surprised by these results, obviously, processing with Perl would be slower than a native application. But it's the postmaster that gives these results. Could it be because the DBI module is slower at assimilating the data?
>
> Any light on the subject would be greatly appreciated.
Random guess: Perl's DBI is using parameterized prepared statements,
preventing the optimizer from using its knowledge about common values in
the table to decide whether or not index use is appropriate. When you're
writing the query in psql, you're not using prepared statements so the
optimizer can be cleverer.
Try comparing:
SELECT statement
to
PREPARE test(params) AS statement;
EXECUTE test(params);
eg:
SELECT x + 44 FROM t;
vs:
PREPARE test(int) AS x + $1 FROM t;
EXECUTE test(44);
Use EXPLAIN ANALYZE to better understand the changes in the query plan.
--
Craig Ringer
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-21 13:57 Rusty Conover <rconover@infogears.com>
parent: Valentin Bogdanov <valiouk@yahoo.co.uk>
2 siblings, 0 replies; 10+ messages in thread
From: Rusty Conover @ 2008-07-21 13:57 UTC (permalink / raw)
To: valiouk@yahoo.co.uk; +Cc: pgsql-performance
On Jul 21, 2008, at 5:19 AM, Valentin Bogdanov wrote:
> Hi,
>
> I have ran quite a few tests comparing how long a query takes to
> execute from Perl/DBI as compared to psql/pqlib. No matter how many
> times I run the test the results were always the same.
>
> I run a SELECT all on a fairly big table and enabled the
> log_min_duration_statement option. With psql postgres consistently
> logs half a second while the exact same query executed with Perl/DBI
> takes again consistently 2 seconds.
>
> If I were timing the applications I would have been too much
> surprised by these results, obviously, processing with Perl would be
> slower than a native application. But it's the postmaster that gives
> these results. Could it be because the DBI module is slower at
> assimilating the data?
Hi Val,
Yes, DBI can be slower then the native C interface. The speed depends
on how the data is being returned inside of Perl. Returning hashes is
a slower method then returning arrays from what I've found due to the
overhead in the creation of the objects in Perl.
So:
my $r = $dbh->selectall_arrayref("select * from table", { Columns =>
{}});
Is slower then:
my $r = $dbh->selectall_arrayref("select * from table", undef);
Secondarily, if you're returning a lot of rows you may want to look
into using a cursor, so that you can fetch the rows a 1000 at a time
in a tight loop then discard them once you are done with them. This
will hopefully prevent the system from having continually allocate
memory for all of your rows. For each field in each row Perl
allocates memory to store the value from Postgres, so if you have many
fields on your table this can be a large number of allocations
depending on the number of rows. Any userland profile tool should
help you debug what's going on here.
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com - http://www.gearbuyer.com - http://www.footwearbuyer.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-21 17:24 Craig James <craig_james@emolecules.com>
parent: Valentin Bogdanov <valiouk@yahoo.co.uk>
2 siblings, 1 reply; 10+ messages in thread
From: Craig James @ 2008-07-21 17:24 UTC (permalink / raw)
To: valiouk@yahoo.co.uk; +Cc: pgsql-performance
Valentin Bogdanov wrote:
> I have ran quite a few tests comparing how long a query takes to
> execute from Perl/DBI as compared to psql/pqlib. No matter how many
> times I run the test the results were always the same.
>
> I run a SELECT all on a fairly big table and enabled the
> log_min_duration_statement option. With psql postgres consistently
> logs half a second while the exact same query executed with Perl/DBI
> takes again consistently 2 seconds.
The problem may be that your two tests are not equivalent. When Perl executes a statement, it copies the *entire* result set back to the client before it returns the first row. The following program might appear to just be fetching the first row:
$sth = $dbh->prepare("select item from mytable");
$sth->execute();
$item = $sth->fetchrow_array();
But in fact, before Perl returns from the $sth->execute() statement, it has already run the query and copied all of the rows into a hidden, client-side cache. Each $sth->fetchrow_array() merely copies the data from the hidden cache into your local variable.
By contrast, psql executes the query, and starts returning the data a page at a time. So it may appear to be much faster.
This also means that Perl has trouble with very large tables. If the "mytable" in the above example is very large, say a hundred billion rows, you simply can't execute this statement in Perl. It will try to copy 100 billion rows into memory before returning the first answer.
The reason for Perl's behind-the-scenes caching is because it allows multiple connections to a single database, and multiple statements on each database handle. By executing each statement completely, it gives the appearance that multiple concurrent queries are supported. The downside is that it can be a huge memory hog.
Craig
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-21 18:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2008-07-21 18:44 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> Valentin Bogdanov wrote:
>> I have ran quite a few tests comparing how long a query takes to
>> execute from Perl/DBI as compared to psql/pqlib. No matter how many
>> times I run the test the results were always the same.
>>
>> I run a SELECT all on a fairly big table and enabled the
>> log_min_duration_statement option. With psql postgres consistently
>> logs half a second while the exact same query executed with Perl/DBI
>> takes again consistently 2 seconds.
> The problem may be that your two tests are not equivalent. When Perl
> executes a statement, it copies the *entire* result set back to the
> client before it returns the first row.
Sure, but so does psql (unless you've turned on the magic FETCH_COUNT
setting). I think the theories about prepared versus literal statements
were more promising; but I don't know DBI well enough to know exactly
what it was sending to the server.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-21 20:37 Greg Sabino Mullane <greg@turnstep.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Greg Sabino Mullane @ 2008-07-21 20:37 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Tom Lane wrote:
> Sure, but so does psql (unless you've turned on the magic FETCH_COUNT
> setting). I think the theories about prepared versus literal statements
> were more promising; but I don't know DBI well enough to know exactly
> what it was sending to the server.
Almost certainly a prepared_statement unless no placeholders were being
used at all. Another way to test (from the DBI side) is to set
$sth->{pg_server_prepare} = 0, which will send the SQL directly to the
backend, just as if you've typed it in at a command prompt. You can also
use the tracing mechanism of DBI to see what's going on behind the scenes.
For example:
$dbh->trace('SQL');
$dbh->do("SELECT 1234 FROM pg_class WHERE relname = 'bob'");
$dbh->do("SELECT 1234 FROM pg_class WHERE relname = ?", undef, 'mallory');
$sth = $dbh->prepare("SELECT 4567 FROM pg_class WHERE relname = ?");
$sth->execute('alice');
$sth->{pg_server_prepare} = 0;
$sth->execute('eve1');
$sth->{pg_server_prepare} = 1;
$sth->execute('eve2');
$dbh->commit;
Outputs:
===
begin;
SELECT 1234 FROM pg_class WHERE relname = 'bob';
EXECUTE SELECT 1234 FROM pg_class WHERE relname = $1 (
$1: mallory
);
PREPARE dbdpg_p22988_1 AS SELECT 4567 FROM pg_class WHERE relname = $1;
EXECUTE dbdpg_p22988_1 (
$1: alice
);
SELECT 4567 FROM pg_class WHERE relname = 'eve1';
EXECUTE dbdpg_p22988_1 (
$1: eve2
);
commit;
DEALLOCATE dbdpg_p22988_1;
===
You can even view exactly which libpq calls are being used at each point with:
$dbh->trace('SQL,libpq');
To get back to the original poster's complaint, you may want to figure out why
the difference is so great for a prepared plan. It may be that you need to
cast the placeholder(s) to a specific type, for example.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200807211637
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkiE83wACgkQvJuQZxSWSsiGrwCdGMLgauGwR2UzfoMPrTH/mrRg
nxsAnjx14goMV23a9yRjtSw+ixJWQkuI
=gjVE
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-22 11:32 Valentin Bogdanov <valiouk@yahoo.co.uk>
parent: Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 1 reply; 10+ messages in thread
From: Valentin Bogdanov @ 2008-07-22 11:32 UTC (permalink / raw)
To: pgsql-performance
Thanks to everyone who replied. There were some really good points.
However, I found what is causing the difference. The perl program was connecting to the database via a TCP socket while the C version was using Unix socket. I changed the connect in my perl script, so that it now uses Unix sockets as well. Run the tests again and got identical results for both programs.
In case someone is wondering, the way to force DBI to use unix sockets is by not specifying a host and port in the connect call.
Cheers,
Val
__________________________________________________________
Not happy with your email address?.
Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-22 16:48 Greg Sabino Mullane <greg@turnstep.com>
parent: Valentin Bogdanov <valiouk@yahoo.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Greg Sabino Mullane @ 2008-07-22 16:48 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> In case someone is wondering, the way to force DBI to use unix
> sockets is by not specifying a host and port in the connect call.
Actually, the host defaults to the local socket. Using the port
may still be needed: if you leave it out, it simply uses the default
value (5432) if left out. Thus, for most purposes, just leaving
the host out is enough to cause a socket connection on the default
port.
For completeness in the archives, you can also specify the complete
path to the unix socket directory in the host line, for those cases in
which the socket is not where you expect it to be:
$dbh = DBI->connect('dbi:Pg:dbname=test;host=/var/local/sockets',
$user, $pass, {AutoCommit=>0, RaiseError=>1});
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200807221248
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkiGD1UACgkQvJuQZxSWSsjHTQCfYbGnh3dvs9ggZX0FCSwMro81
sJsAoOUcDyu6vQM43EJOGAay/vXyKWES
=hYLf
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-22 20:35 Jeffrey Baker <jwbaker@gmail.com>
parent: Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeffrey Baker @ 2008-07-22 20:35 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-performance
On Tue, Jul 22, 2008 at 9:48 AM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>> In case someone is wondering, the way to force DBI to use unix
>> sockets is by not specifying a host and port in the connect call.
>
> Actually, the host defaults to the local socket. Using the port
> may still be needed: if you leave it out, it simply uses the default
> value (5432) if left out. Thus, for most purposes, just leaving
> the host out is enough to cause a socket connection on the default
> port.
For the further illumination of the historical record, the best
practice here is probably to use the pg_service.conf file, which may
or may not live in /etc depending on your operating system. Then you
can connect in DBI using dbi:Pg:service=whatever, and change the
definition of "whatever" in pg_service.conf. This has the same
semantics as PGSERVICE=whatever when using psql. It's a good idea to
keep these connection details out of your program code.
-jwb
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Perl/DBI vs Native
@ 2008-07-23 10:46 Valentin Bogdanov <valiouk@yahoo.co.uk>
parent: Jeffrey Baker <jwbaker@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Valentin Bogdanov @ 2008-07-23 10:46 UTC (permalink / raw)
To: pgsql-performance
Thanks Guys, this is really useful, especially the pg_service.conf. I have got an app where the connection parameters have to be set in 3 different places I was thinking of writing something myself but now that I know of pg_service.conf, problem solved.
Regards,
Val
--- On Tue, 22/7/08, Jeffrey Baker <jwbaker@gmail.com> wrote:
> From: Jeffrey Baker <jwbaker@gmail.com>
> Subject: Re: [PERFORM] Perl/DBI vs Native
> To: "Greg Sabino Mullane" <greg@turnstep.com>
> Cc: pgsql-performance@postgresql.org
> Date: Tuesday, 22 July, 2008, 9:35 PM
> On Tue, Jul 22, 2008 at 9:48 AM, Greg Sabino Mullane
> <greg@turnstep.com> wrote:
> >> In case someone is wondering, the way to force DBI
> to use unix
> >> sockets is by not specifying a host and port in
> the connect call.
> >
> > Actually, the host defaults to the local socket. Using
> the port
> > may still be needed: if you leave it out, it simply
> uses the default
> > value (5432) if left out. Thus, for most purposes,
> just leaving
> > the host out is enough to cause a socket connection on
> the default
> > port.
>
> For the further illumination of the historical record, the
> best
> practice here is probably to use the pg_service.conf file,
> which may
> or may not live in /etc depending on your operating system.
> Then you
> can connect in DBI using dbi:Pg:service=whatever, and
> change the
> definition of "whatever" in pg_service.conf.
> This has the same
> semantics as PGSERVICE=whatever when using psql. It's
> a good idea to
> keep these connection details out of your program code.
>
> -jwb
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
__________________________________________________________
Not happy with your email address?.
Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-07-23 10:46 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-21 11:19 Perl/DBI vs Native Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-21 13:49 ` Craig Ringer <craig@postnewspapers.com.au>
2008-07-21 13:57 ` Rusty Conover <rconover@infogears.com>
2008-07-21 17:24 ` Craig James <craig_james@emolecules.com>
2008-07-21 18:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-21 20:37 ` Greg Sabino Mullane <greg@turnstep.com>
2008-07-22 11:32 ` Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-22 16:48 ` Greg Sabino Mullane <greg@turnstep.com>
2008-07-22 20:35 ` Jeffrey Baker <jwbaker@gmail.com>
2008-07-23 10:46 ` Valentin Bogdanov <valiouk@yahoo.co.uk>
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