Thread: Connection pooling and with statement
Hi,
I want to use psycopg2 in a WSGI environment. There you can have a
(configurable) number of processes and threads created that would be
accessing the database. Now I wonder what might be the best practice to
implement the pool. I don't think it is practicable to create the pool
in the WSGI application since this would create a number of pools for
each process and/or threads.
Or is the pool class simply not suitable for such a task?
Another question that occured to me is how I use the with statement with
the pool class:
I can do:
with pool.getconn() as conn:
with conn.cursor() as cursor:
# do stuff
Where do I need to call pool.putconn(conn)? How does the with statement
affect the connection? Is the with statement suitable for connection
pooling at all?
Many thanks,
Frank
--
Frank BRONIEWSKI
METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN
tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu
Hey Frank,
My advice for the pool, is to use pg_bouncer. Thus, your WSGI application can focus on working on your business, and not manage technical optimization.
The only difference you need to pay attention in your code is to release the PG connection properly for the next client (make a rollback()).
In the code to process an HTTP request, you should have something to release the allocated resources (like PG connection). Let's say that you have a Controller class, at the end of the HTTP request processing, you could have something like this:
if self.conn.get_transaction_status() > 0:
self.conn.rollback()
self.conn.close()
Cheers,
Sam
On Fri, Aug 23, 2013 at 12:03 PM, Frank Broniewski <brfr@metrico.lu> wrote:
Hi,
I want to use psycopg2 in a WSGI environment. There you can have a (configurable) number of processes and threads created that would be accessing the database. Now I wonder what might be the best practice to implement the pool. I don't think it is practicable to create the pool in the WSGI application since this would create a number of pools for each process and/or threads.
Or is the pool class simply not suitable for such a task?
Another question that occured to me is how I use the with statement with the pool class:
I can do:
with pool.getconn() as conn:
with conn.cursor() as cursor:
# do stuff
Where do I need to call pool.putconn(conn)? How does the with statement affect the connection? Is the with statement suitable for connection pooling at all?
Many thanks,
Frank
--
Frank BRONIEWSKI
METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN
tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu
--
Sent via psycopg mailing list (psycopg@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/psycopg
Hi Frank, Application level connection pooling isn't a bad thing and can bring some performance improvements in combination with a general connection pool (pgpool). Depending on what framework you are using, and how it is setup, it is possible to share the connection pool between threads, what wsgi framework are you using? (if any). regards, Jules. On 23/08/13 20:03, Frank Broniewski wrote: > Hi, > > I want to use psycopg2 in a WSGI environment. There you can have a > (configurable) number of processes and threads created that would be > accessing the database. Now I wonder what might be the best practice > to implement the pool. I don't think it is practicable to create the > pool in the WSGI application since this would create a number of pools > for each process and/or threads. > > Or is the pool class simply not suitable for such a task? > > Another question that occured to me is how I use the with statement > with the pool class: > > I can do: > with pool.getconn() as conn: > with conn.cursor() as cursor: > # do stuff > > Where do I need to call pool.putconn(conn)? How does the with > statement affect the connection? Is the with statement suitable for > connection pooling at all? > > Many thanks, > > Frank > >
Jules, Sam, thanks for your answers so far. I'm running Apache with mod_wsgi and I am using Bottle [1] as the Web Framework. I guess I could attach the pool to something global within Bottle, but I will have to check the docs for that. Concerning the more general connection pools like pgpool and pg_bouncer; I will definitely check them out. This is already for quite some time on my todo-list ... Frank [1] bottlepy.org Am 2013-08-23 16:19, schrieb Julian: > Hi Frank, > Application level connection pooling isn't a bad thing and can bring > some performance improvements in combination with a general connection > pool (pgpool). > Depending on what framework you are using, and how it is setup, it is > possible to share the connection pool between threads, what wsgi > framework are you using? (if any). > > regards, Jules. > On 23/08/13 20:03, Frank Broniewski wrote: >> Hi, >> >> I want to use psycopg2 in a WSGI environment. There you can have a >> (configurable) number of processes and threads created that would be >> accessing the database. Now I wonder what might be the best practice >> to implement the pool. I don't think it is practicable to create the >> pool in the WSGI application since this would create a number of pools >> for each process and/or threads. >> >> Or is the pool class simply not suitable for such a task? >> >> Another question that occured to me is how I use the with statement >> with the pool class: >> >> I can do: >> with pool.getconn() as conn: >> with conn.cursor() as cursor: >> # do stuff >> >> Where do I need to call pool.putconn(conn)? How does the with >> statement affect the connection? Is the with statement suitable for >> connection pooling at all? >> >> Many thanks, >> >> Frank >> >> > > > -- Frank BRONIEWSKI METRICO s.à r.l. géomètres technologies d'information géographique rue des Romains 36 L-5433 NIEDERDONVEN tél.: +352 26 74 94 - 28 fax.: +352 26 74 94 99 http://www.metrico.lu
Am 2013-08-23 12:03, schrieb Frank Broniewski: > Another question that occured to me is how I use the with statement with > the pool class: > > I can do: > with pool.getconn() as conn: > with conn.cursor() as cursor: > # do stuff > > Where do I need to call pool.putconn(conn)? How does the with statement > affect the connection? Is the with statement suitable for connection > pooling at all? Hey everybody, I want to come back to my question above. Any ideas on this? > > Many thanks, > > Frank > > -- Frank BRONIEWSKI METRICO s.à r.l. géomètres technologies d'information géographique rue des Romains 36 L-5433 NIEDERDONVEN tél.: +352 26 74 94 - 28 fax.: +352 26 74 94 99 http://www.metrico.lu
On Tue, Aug 27, 2013 at 8:13 AM, Frank Broniewski <brfr@metrico.lu> wrote: > Am 2013-08-23 12:03, schrieb Frank Broniewski: > >> Another question that occured to me is how I use the with statement with >> the pool class: >> >> I can do: >> with pool.getconn() as conn: >> with conn.cursor() as cursor: >> # do stuff >> >> Where do I need to call pool.putconn(conn)? How does the with statement >> affect the connection? Is the with statement suitable for connection >> pooling at all? > > > Hey everybody, > > I want to come back to my question above. Any ideas on this? The pools are not transaction managers. You you want to use a pool in a 'with' statement you can write your subclass adding __enter__ and __exit__ method. -- Daniele