pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedpdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log.
3+ messages / 3 participants
[nested] [flat]
* pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log.
@ 2011-08-26 03:30 mark <dvlhntr@gmail.com>
2011-08-26 10:42 ` Re: pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log. itb348@googlemail.com
0 siblings, 1 reply; 3+ messages in thread
From: mark @ 2011-08-26 03:30 UTC (permalink / raw)
To: pgsql-php
Hi all,
I am trying to convince some of our php guys to not use prepared statements.
The reason for this is, first reasons is we are seeing no benefit IMO.
performance wise nearly all of the statements are being prepared, executed
and then deallocated in nearly all cases. The other times it's probably only
run twice so it's still not a win.
The second reason is I want to be able to use transaction pooling with
pgbouncer. This is the primary driving force.
So someone mashed up a quick bit of php to start testing a few thing. (more
about checking what error cases look like than anything else)
The problem we found is that even with "PDO::ATTR_EMULATE_PREPARES => true"
I still see it preparing statements when I set log_min_duration to 0.
I am not a php person but I googled around and I can't seem to figure out
why the below isn't working as expected. Any tips on what are we doing
wrong? What should this look like to disable/emulate prepared statements. I
am expecting that if this were working we wouldn't see
parse,bind,execute,deallocate in the Postgres log.
Apologies if this is the wrong list for this sort of thing.
-Mark
<?php
try {
// Can run it in a loop to get timed results over a large number of
executions.
$tStart = microtime(true);
for ($i=0;$i<1;$i++) {
$dbh = new PDO('pgsql:host=localhost;dbname=my_mainline',
'postgres', 'postgres', array(PDO::ATTR_EMULATE_PREPARES => true));
// replace this with a valid int to test the expected case.
$rname = 'P1\'Partner';
// Purposely trying to bind a string to an integer column to
see what happens
// when using client-side prepare emulation vs not using it.
$stmt = $dbh->prepare("SELECT * FROM my_reseller WHERE
reseller_id=:rname");
$stmt->bindParam(':rname', $rname);
$stmt->execute();
/* Print out results if you want to see them.
foreach($stmt->fetch() as $row) {
print_r($row); print("\n");
}
*/
$dbh = null;
}
printf("%17s: %.4f secs for %d iterations, avg: %.6f secs\n"
, 'date'
, microtime(true) - $tStart
, $i
, (microtime(true) - $tStart)/$i
);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log.
2011-08-26 03:30 pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log. mark <dvlhntr@gmail.com>
@ 2011-08-26 10:42 ` itb348@googlemail.com
0 siblings, 0 replies; 3+ messages in thread
From: itb348@googlemail.com @ 2011-08-26 10:42 UTC (permalink / raw)
To: pgsql-php
Am 26.08.2011 05:30, schrieb mark:
>
>
> Hi all,
>
>
> I am trying to convince some of our php guys to not use prepared statements.
>
>
> The reason for this is, first reasons is we are seeing no benefit IMO.
> performance wise nearly all of the statements are being prepared, executed
> and then deallocated in nearly all cases. The other times it's probably only
> run twice so it's still not a win.
>
> The second reason is I want to be able to use transaction pooling with
> pgbouncer. This is the primary driving force.
--snip--
Well, in my systems I make the best of the forced prepare and use
my own cache of prepared statements in PHP. Statistics show that
some statements are repeated a lot, which might give a benefit in
speed.
And I am especially against connection pooling because it can
give you untraceable errors and just makes your systems more complex.
But if you want it: my first test had success. Just add
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
after creating the connection, do not touch the connection string.
/Str
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log
@ 2011-09-13 23:39 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Kirkwood @ 2011-09-13 23:39 UTC (permalink / raw)
To: pgsql-php
You may want to check your version of Php - your example works fine for
me (i.e setting ATTR_EMULATE_PREPARES => true *stops* the parse, bind,
exec etc) using Php 5.3.6. I cannot recall exactly when I fixed this,
but the 5.2.12 changelog says the fix is there
(https://bugs.php.net/bug.php?id=48764), so later 5.2 and 5.3 versions
should work properly.
Cheers
Mark
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-09-13 23:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-26 03:30 pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log. mark <dvlhntr@gmail.com>
2011-08-26 10:42 ` itb348@googlemail.com
2011-09-13 23:39 Re: pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
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