Re: Scalability in postgres

2009/5/29 Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>

> 2009/5/29 Greg Smith <gsmith(at)gregsmith(dot)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/
>

Which is better and more complete, which have more features?
What you recommend? pgbouncer or pgpool?

>
> --Scott
>
>