Re: connection pooling in web apps

Hello,

The cost of opening a connection to PostgreSQL over TCP/IP and using SSL is
high. The first thing you can do to decrease this cost is to switch to a
local socket and disable SSL. To do this, you can remove the parameter host
from your call to psycopg2.connect (without this parameter, a socket is
used by default). You can also try to add sslmode='disable'. You will still
create a connection for each request, but it will be quicker (about 10 ms
on my laptop).

To improve on this, you have to use some kind of connection pooling. At my
company, we used to pool connections directly in our WSGI applications, but
it was unnecessarily complex and we moved to using an external connection
pool one year ago. Basically, we removed all our connection pooling code,
and simply lazily create one connection per WSGI request that needs the
database, but instead of connecting directly to PostgreSQL, we connect to a
pgbouncer instance, which is very efficient piece of software. It's simple,
very efficient and scalable.

More information here:
http://brainacle.com/pgbouncer-makes-a-difference.html
http://enterprisedb.com/resources-community/tutorials-quickstarts/all-platforms/how-setup-pgbouncer-connection-pooling-postg
http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/
http://pgbouncer.projects.postgresql.org/doc/usage.html
http://pgbouncer.projects.postgresql.org/doc/config.html
http://pgbouncer.projects.postgresql.org/doc/faq.html
http://raghavt.blogspot.fr/2011/08/connection-pooling-with-pgbouncer-on.html

Cheers,

Nicolas

On Tue, Jun 19, 2012 at 9:51 AM, Ottavio Campana <ottavio(at)campana(dot)vi(dot)it>wrote:

> I am developing some apps with pyramid and I am having performance
> problems.
>
> I really suspect that the issue is the fact that I am not using a
> persistent connection to the database, thus for each query I have to
> reconnect to the db.
>
> In the paste, I used to develop on zope, which handled automatically the
> connections to the db. Now I am using my custom methods on pyramid (not
> sqlalchemy).
>
> Do you have a link to some documentation, that explain how to use
> connection pooling in wsgi apps? I was thinking about using pgpool-II
> but this does not solve the problem that I reconnect every time to the db.
>

--
Nicolas Grilly
Garden / Vocation City
+33 1 45 72 48 78 - office
+33 6 03 00 25 34 - mobile
www.gardentechno.com - Développement web & reporting / *Web development &
data analytics*
www.vocationcity.com - Plateforme de recrutement sur le web / *Web
recruitment platform*