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:
Cheers,
Nicolas
On Tue, Jun 19, 2012 at 9:51 AM, Ottavio Campana
<ottavio@campana.vi.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 analyticswww.vocationcity.com - Plateforme de recrutement sur le web /
Web recruitment platform