Gevent with a database connection pool for Stream Server

Question

I am using Python Gevent streaming servers to communicate with another computer (remote) that sends concurrent TCP / IP requests (on avg 60 req / sec). The nature of this message is mainly related to IO (short text and then audio streams). I intend to use Postgresql to store the results of each message (for example: the name of the file received from the remote server).

I think its a bad idea to call a new db connection for each pedigree spawned in Streamserver (pool size 90, so 90 req / sec max, i.e. the maximum I expect and avg 60 req / sec). Is it possible to have a db connection pool that can be queued, and every green thing gets a db connection from the pool when the handler function starts? Is there a tutorial that worked on production systems? How would you suggest? I am using gevent 0.13.8 and postgres 9.1 with Python 2.7.3 on Ubuntu 10.04 64bit.

Answer 1

In the examples, Gevent includes the postgres database pool:

https://github.com/gevent/gevent/blob/master/examples/psycopg2_pool.py

Answer 2

An alternative to the pool in the application is pooling using PgBouncer . You still have the overhead of a TCP connection and a little tweaking, but much less than when creating a complete new PostgreSQL session.

Unlike an application pool, PgBouncer can be transparently injected into existing systems as an intermediary between PostgreSQL and the pool. Just move PostgreSQl to port 5433 and listen to PgBouncer on port 5432.

Answer 3

I actually answered a similar answer:
Python Postgres psycopg2 ThreadedConnectionPool exhausted

Basically, I configure the pool of asynchronous connections with gevent, starting the Threadpool connection through postgres and adding the state of the green connection to postgres.

Thus, basically it creates a predefined pool of connections that you specify (for example, 100), and then request queues using this pool, since one request is returned, a new request is processed. I like this approach better than the regular postgres pool due to the asynchronous nature of the requests and the ease of implementation inside the web structure.