You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Mark Stosberg <ma...@summersault.com> on 2006/08/05 16:02:59 UTC
issue with DBD::Pg, server side prepares, and persistent connections?
Hello,
I'm wondering if some other people here have some wisdom about this case
a few people have run into, apparently with no resolution.
Newer versions of DBD::Pg and PostgreSQL support a feature called
"server side prepares", which is supposed to give a significant
performance boost in some cases.
However, when deploying it on mod_perl on a busy website, I quickly saw
a lot of this kind of error:
prepared statement "dbdpg_7" already exists
Other people have run into this as well:
http://gborg.postgresql.org/pipermail/dbdpg-general/2005-December/001926.html
Any suggestions? I looked at the DBD::Pg code myself, but my C skills
aren't up to digging into the XS code there.
I found a related mention of this issue here:
http://fudforum.org/forum/index.php?t=msg&th=4598&start=0&
There the fix involved putting using DEALLOCATE when persistent
connections are detected. The actual patch they used in their
application was here:
http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=10611
Thanks!
Mark
Re: issue with DBD::Pg, server side prepares, and persistent connections?
Posted by Mark Stosberg <ma...@summersault.com>.
>> I found a related mention of this issue here:
>> http://fudforum.org/forum/index.php?t=msg&th=4598&start=0&
>>
>> There the fix involved putting using DEALLOCATE when persistent
>
> first, the patch with DEALLOCATE makes prepared statements entirely
> useless, as it means you're caching a prepared statement, running it,
> then deallocating it immediately afterwards. thats some brilliant php
> right there. why didn't they just do a conditional to never prepare on
> persistent connections ?
Or as they say at http://www.thedailywtf.com/, "brillant".
> before looking into how dbd::pg is internally handling the prepared
> statements, are you using some sort of db abstraction layer like rose or
> class::dbi. or are you executing directly? if executing directly,
> are you creating statement handles on the outset, or are you just
> letting dbdpg handle all of that internally?
We have no abstraction layer, just using SQL::Interpolate to help with
SQL generation. (recommended!). We create statement handles if there is
a need, but usually not.
> the prepared statement 'handle/name' is private to the session. so two
> different connections will each have their own statement handle.
>
> my guess on things to look into:
> how is dpd::pg caching the statement handle? are they even doing it?
> where is the statment handle being cached, accessed, checked? in
> the memory space of the child, or in the shared area?
If "shared" means "what happens in startup.pl", then there's no SQL
being executed there.
Mark
--
http://mark.stosberg.com/
Re: issue with DBD::Pg, server side prepares, and persistent connections?
Posted by Jonathan <mo...@2xlp.com>.
this is more of a discussion post than a response:
On Aug 5, 2006, at 10:02 AM, Mark Stosberg wrote:
do you have links of that in regards to dbd::pg? i didn't realize
they supported it.
> However, when deploying it on mod_perl on a busy website, I quickly
> saw a lot of this kind of error:
>
> prepared statement "dbdpg_7" already exists
from my experience with psycopg ( python pg interface ) it is the
persistant connection issue. obviously , you're trying to prepare a
statement that has already been allocated. but i'm wondering how/why
that's happening.
> I found a related mention of this issue here:
> http://fudforum.org/forum/index.php?t=msg&th=4598&start=0&
>
> There the fix involved putting using DEALLOCATE when persistent
first, the patch with DEALLOCATE makes prepared statements entirely
useless, as it means you're caching a prepared statement, running it,
then deallocating it immediately afterwards. thats some brilliant
php right there. why didn't they just do a conditional to never
prepare on persistant connections ?
before looking into how dbd::pg is internally handling the prepared
statements, are you using some sort of db abstraction layer like rose
or class::dbi. or are you executing directly? if executing
directly, are you creating statement handles on the outset, or are
you just letting dbdpg handle all of that internally?
the prepared statement 'handle/name' is private to the session. so
two different connections will each have their own statement handle.
my guess on things to look into:
how is dpd::pg caching the statement handle? are they even doing it?
where is the statment handle being cached, accessed, checked? in
the memory space of the child, or in the shared area?
// Jonathan Vanasco
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
Re: issue with DBD::Pg, server side prepares, and persistent
connections?
Posted by Perrin Harkins <pe...@elem.com>.
On Sat, 2006-08-05 at 18:46 -0500, Mark Stosberg wrote:
> So are you suggesting I should use:
>
> $dbh->prepare_cached($sql, { pg_server_prepare => 1 }, 3)
>
> ...throughout the web-app?
If you use prepare_cached, adding the 3 is the safest way to go. It
avoids possible problems with accidentally reusing an active statement
handle. This is mostly an issue for people using SQL generation
frameworks though.
- Perrin
Re: issue with DBD::Pg, server side prepares, and persistent connections?
Posted by Mark Stosberg <ma...@summersault.com>.
Perrin Harkins wrote:
> On Sat, 2006-08-05 at 09:02 -0500, Mark Stosberg wrote:
>> However, when deploying it on mod_perl on a busy website, I quickly saw
>> a lot of this kind of error:
>>
>> prepared statement "dbdpg_7" already exists
>
> Do you use prepare_cached? You might want to try that, with a 3 for the
> $if_active parameter.
I do, but not everywhere. However, I have now set
"pg_server_prepare => 0" immediately after connecting to prevent the
errors I was seeing. So are you suggesting I should use:
$dbh->prepare_cached($sql, { pg_server_prepare => 1 }, 3)
...throughout the web-app?
Mark
--
http://mark.stosberg.com/
Re: issue with DBD::Pg, server side prepares, and persistent
connections?
Posted by Perrin Harkins <pe...@elem.com>.
On Sat, 2006-08-05 at 09:02 -0500, Mark Stosberg wrote:
> However, when deploying it on mod_perl on a busy website, I quickly saw
> a lot of this kind of error:
>
> prepared statement "dbdpg_7" already exists
Do you use prepare_cached? You might want to try that, with a 3 for the
$if_active parameter.
- Perrin
Re: issue with DBD::Pg, server side prepares, and persistent connections?
Posted by David Dick <da...@iprimus.com.au>.
David Dick wrote:
>
>
> Mark Stosberg wrote:
>
>> Hello,
>>
>> I'm wondering if some other people here have some wisdom about this
>> case a few people have run into, apparently with no resolution.
>>
>> Newer versions of DBD::Pg and PostgreSQL support a feature called
>> "server side prepares", which is supposed to give a significant
>> performance boost in some cases.
>>
>> However, when deploying it on mod_perl on a busy website, I quickly
>> saw a lot of this kind of error:
>>
>> prepared statement "dbdpg_7" already exists
>
>
> that error could be caused by forking in the server code. To make it go
> away, you should upgrade DBD::Pg to be at least 1.44 and, in the child
> process after the fork, issue a $dbh->{InactiveDestroy} = 1; call.
Dammit, i meant version 1.45. Sorry.
Re: issue with DBD::Pg, server side prepares, and persistent connections?
Posted by David Dick <da...@iprimus.com.au>.
Mark Stosberg wrote:
> Hello,
>
> I'm wondering if some other people here have some wisdom about this case
> a few people have run into, apparently with no resolution.
>
> Newer versions of DBD::Pg and PostgreSQL support a feature called
> "server side prepares", which is supposed to give a significant
> performance boost in some cases.
>
> However, when deploying it on mod_perl on a busy website, I quickly saw
> a lot of this kind of error:
>
> prepared statement "dbdpg_7" already exists
that error could be caused by forking in the server code. To make it go
away, you should upgrade DBD::Pg to be at least 1.44 and, in the child
process after the fork, issue a $dbh->{InactiveDestroy} = 1; call.
Uru
-Dave
Re: issue with DBD::Pg, server side prepares, and persistent connections?
Posted by Greg Sabino Mullane <gr...@turnstep.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Newer versions of DBD::Pg and PostgreSQL support a feature called
> "server side prepares", which is supposed to give a significant
> performance boost in some cases.
>
> However, when deploying it on mod_perl on a busy website, I quickly saw
> a lot of this kind of error:
>
> prepared statement "dbdpg_7" already exists
Server-side prepare statements exists on a per-connection basis, and
have been causing some problems for applications that do funky things
with connections, such as pgpool. However, when mod_perl is involved,
the problem is usually caused by connecting to a database *before*
forking children, which is almost always a Bad Thing. In short, the
children have no way of coordinating with each other about the names
being used, as their parent dbh handles have been cloned.
I put a workaround for this problem into the next version of DBD::Pg -
it will be available in the upcoming version 1.50. If you want to try it
out before then, you can try patching up dbdimp.c yourself like so:
- - /* Name is simply "dbdpg_#" */
- - sprintf(imp_sth->prepare_name,"dbdpg_%d", imp_dbh->prepare_number);
+ /* Name is simply "dbdpg_PID_#" */
+ sprintf(imp_sth->prepare_name,"dbdpg_%d_%d", getpid(), imp_dbh->prepare_number);
I've still no idea how to cure the pgpool problem, but this will probably
help your mod_perl one. The other option is to simply turn off the
server-side prepares by adding pg_server_prepare => 0 to the connection
attributes, or via the database handle.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com
PGP Key: 0x14964AC8 200608051821
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFE1RrDvJuQZxSWSsgRAmOoAJ4oPavBM79VbPUk3sGX9Y+cVgoRUwCdFg9p
VXomfEWLrUvIcPGAqcKDOfk=
=gLol
-----END PGP SIGNATURE-----