Skip site navigation (1) Skip section navigation (2)

Re: Scalability in postgres

From: Fabrix <fabrixio1(at)gmail(dot)com>
To: Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>
Cc: Greg Smith <gsmith(at)gregsmith(dot)com>, Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>, Flavio Henrique Araque Gurgel <flavio(at)4linux(dot)com(dot)br>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Scalability in postgres
Date: 2009-05-29 19:45:48
Message-ID: dedbc5820905291245r6df1f85ck99ba65abbf3def3a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>
>

In response to

Responses

pgsql-performance by date

Next:From: Scott MeadDate: 2009-05-29 19:50:48
Subject: Re: Scalability in postgres
Previous:From: Scott MeadDate: 2009-05-29 19:08:43
Subject: Re: Unexpected query plan results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group