Re: Prepared Statements vs. pgbouncer - Mailing list pgsql-jdbc

On Sat, Sep 29, 2007 at 08:43:33PM +1200, Oliver Jowett wrote:
> Paul Lindner wrote:
>
> > * 100s of application servers using Torque and DBCP
> > * Dozens of databases.
> > * All app servers can connect to all databases.
> > * Each application server may need many connections to an individual
> >   database.
> > * App code as written will spawn multiple concurrent SELECTs to
> >   a single database to speed up queries on partitioned tables.
>
> I'm assuming there is some reason why you can't run the same apps on
> fewer appservers and share the pools. The basic problem here is that you
> can't get a good global view of which connections are idle because you
> have a lot of separate appservers each doing their own thing.

Partially, yes.  DBCP and Torque are supposed to take care of this,
however the idle connection reaper is buggy/doesn't work correctly for
us.  This results in app servers quickly going to their max pool size.

What other client-side connection pools are people using?  Is Sequoia
an option?  I have not tried it as of yet.

> >  3. Use pgbouncer to allow 6k connections while actually
> >     lowering the number of DB backends, plus giving us some very cool
> >     maintenance features like redirecting connections to other hosts
> >     and more.
> >
> >So we we're trying to implement #3.
>
> That is a reasonable approach to take, that's essentially giving you a
> global connection pool. However..
>
> >The only thing holding us back from deploying the pgbouncer solution
> >is this issue with the server-side prepared statements.
>
> This is a really an issue with pgbouncer's incomplete implementation of
> the protocol.

Okay, can we get the JDBC people talking with the pgbouncer people
(and the pgpool, pgcluster people too...)  I've Cc'd Marko and hope to
get him into this conversation, especially since the suggested fix of
using DEALLOCATE ALL, DISCARD ALL will probably not work with the JDBC
driver as is.

Note that each of those products has the exact same issues:

http://pgfoundry.org/pipermail/pgbouncer-general/2007-June/000004.html
http://archives.postgresql.org/pgsql-jdbc/2007-02/msg00132.php
http://pgfoundry.org/pipermail/pgcluster-general/2006-October/001070.html
http://pgfoundry.org/pipermail/pgpool-general/2006-January/000275.html

We really need to have some way of maintaining server/client
coherency...

Also, it would be in all of our best interests to solve this problem.
As it stands we could never use a load balancer of any sort to
maintain a highly reliable pool of read-only replicas.

> If you were going to change the driver the best place to do it is in the
> V3 protocol code itself -- tell it not to use named statements at all.
> The higher layers in the driver are working to a different API that
> doesn't know anything about named statements at all, it just provides
> hints about whether a query is likely to be re-used or not. So trying to
> repair particular instances of that so they don't used named statements
> is going to be fairly error-prone.
>
> I don't see that sort of change going into the official driver though ..
> as Tom says, why would we deliberately cripple use of the protocol
> because a 3rd party piece of software can't handle the full protocol? So
> I think you are going to be stuck with either deploying a customized
> JDBC driver for your particular environment, or fixing pgbouncer so that
> it properly implements the protocol.

I never said I wanted the driver crippled.  I'm just looking for a way
to make this scenario work for me.  You already provide
prepareThreshold=X for people that don't want automatic server-side
prepared statements.

How about I code up a patch that adds a new parameter
preparePrefix=XXX to DSN, and add a global utility method to set the
same?  Would you accept such a patch?

--
Paul Lindner        ||||| | | | |  |  |  |   |   |
lindner@inuus.com