2009/5/29 Greg Smith <gsm...@gregsmith.com>
> On Fri, 29 May 2009, Grzegorz Ja?kiewicz wrote:
>
> if it is implemented somewhere else better, shouldn't that make it
>> obvious that postgresql should solve it internally ?
>>
>
> Opening a database connection has some overhead to it that can't go away
> without losing *something* in the process that you want the database to
> handle. That something usually impacts either security or crash-safety.
> This is why every serious database product in the world suggests using
> connection pooling; examples:
>
> http://blogs.oracle.com/opal/2006/10/oracle_announces_new_connectio.html
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm
> http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
>
> http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html
>
Exactly, here's the thing, if you have an open transaction somewhere to
the system, there may be a REALLY good reason for it. If you're app or dev
team is keeping those open, it's very possible that 'reaping' them is going
to cause some kind of data integrity issue in your database. I would
investigate the application and make sure that everything is actually
rolling back or commiting. If you're using an ORM, make sure that it's
using autocommit, this usually makes the issue go away.
As to the context switching point -- A connection pooler is what you need.
Why make your database server dedicate cycles to having to figure out who
gets on the CPU next? Why not lower the number of connections, and let a
connection pool decide what to use. That usually helps with your open
transactions too (if they indeed are just abandoned by the application).
>
> The only difference here is that some of the commercial products bundle the
> connection pooler into the main program. In most cases, you're still stuck
> with configuring a second piece of software, the only difference is that
> said software might already be installed for you by the big DB installer.
> Since this project isn't in the business of bundling every piece of
> additional software that might be useful with the database, it's never going
> to make it into the core code when it works quite happily outside of it.
> The best you could hope for is that people who bundle large chunks of other
> stuff along with their PostgreSQL installer, like Enterprise DB does, might
> include one of the popular poolers one day.
This sounds like a dirty plug (sorry sorry sorry, it's for informative
purposes only)...
Open Source:
One-Click installers : No connection pool bundled (will be
included in 8.4 one-click installers)
PostgresPlus Standard Edition : pgBouncer is bundled
Proprietary:
PostgresPlus Advanced Server: pgBouncer is bundled
That being said, the well known connection pools for postgres are pretty
small and easy to download / build / configure and get up and running.
https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
http://pgfoundry.org/projects/pgpool/
--Scott
