Gevent with Database connection pool for Stream Server

Question

I am using Python Gevent's stream servers for communicating with another machine (remote) which sends concurrent TCP/IP requests (on an avg 60 req/sec). Nature of this communication is mostly IO bound (short text and then audio streams). I intend to use Postgresql to store the result of each communication (eg: filename received from the remote server).

I think its bad idea to invoke a new db connection for every greenlet spawned in Streamserver (pool size 90 so 90 req/sec max, that is the max I expect and avg 60 req/sec). Is it possible to have db connection pool which can be queued and every greenlet when starts running the handler function get a db connection from pool? Is there any tutorial that has worked for 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.

See: http://stackoverflow.com/questions/12650048/how-can-i-pool-connections-using-psycopg-and-gevent/ — Code Painters, Nov 04 '12 at 10:15

Denis · Answer 1 · 2015-05-18T19:14:51.597

2

Gevent includes a postgres database pool in the examples:

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

edited May 18 '15 at 19:14

answered Nov 02 '12 at 18:41

Denis

3,760
25
22

As per my reading and use of Gevent, the version 0.13.8 doesn't have this example. For me, it doesn't seem to work when 0.13.8 is installed (gives error on `gevent.wait()` ). It is part of latest beta release. I tried importing `Event` class and then `wait()` instead of `gevent.wait()`. It doesn't give any error but seems to hang/block somewhere. Doesn't give any output. Anything production ready/tested suggestion? – user1794913 Nov 03 '12 at 20:03
replace gevent.wait() with gevent.sleep(30) or gevent.joinall. The pool itself does not seem to depend on any feature in 1.0 (although I've not used it with 0.13) – Denis Nov 04 '12 at 11:18

score 2 · Answer 2 · answered Nov 03 '12 at 01:47

An alternative to in-application pooling is to pool using PgBouncer. You still have the overhead of a TCP connection and a little bit of setup, but massively less then making a full new PostgreSQL session.

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

score 0 · Answer 3 · answered Mar 23 '18 at 18:35

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

Basically I am setting up an async connection pool with gevent, starting up a Threadpool connection via postgres, and adding the "green" connection state to postgres.

So basically this creates a preset pool of connections that you specify (like 100) and then queues queries using this pool, as one query comes back a new query is handled. I like this approach better than a normal postgres pool because of the async nature of the requests, and the ease to implement inside a web framework.

Gevent with Database connection pool for Stream Server

3 Answers3