Hi Michael, My first problem is that I have to deal with Oracle instead of PostgreSQL, so using PGBouncer is not an option :-( Moreover, what I need is not to be able to create a new connection each time it's accessed from the pool, but to be able to close a connection if it's unused for a given time. Because I have several processes, each process having several threads with a connection pool maintaining connections to several database schemas; in the end that means a lot of connections, most being unfrequently used! As you say, that probably means creating a new thread to monitor unused connection. Do you think that using pool events to monitor a pool's connections could be a good starting point?
Best regards, Thierry 2014-09-25 18:04 GMT+02:00 Michael Bayer <[email protected]>: > > On Sep 25, 2014, at 4:33 AM, Thierry Florac <[email protected]> wrote: > > > Hi, > > > > I have a multi-threaded web application using SQLAlchemy connections > pool. > > As soon as a connection is opened, it's maintained opened in the pool > even if it's not used anymore. On a long run, this can consume too much > unnecessary connections and database server resources. > > > > So what I'm actually looking for is a way to close a given connection > which is returned to the pool if it wasn't used for a given amount of > time... > > > OK well first we assume that you do want pooling in the first place. If > you just don't want any, you'd use NullPool. So assuming you do want > pooling, the next thing that resembles what you describe, but I'm guessing > still is not what you want, is the pool_recycle setting, which will prevent > a connection that is older than N seconds from being used. This recycle > occurs when the connection is to be fetched; if it is past the expiration > time, it is closed and replaced with a new one. However the connection > stays in the pool until the pool is accessed. > > So the final option is, you want the connection returned to the pool while > the pool is idle. The challenge there is that nothing is happening in the > app to make this happen, which implies a background thread or other > asynchronous task system, so you'd have to roll that yourself. > > Overall if you have more detailed pooling needs the suggestion to use > PGBouncer is probably a good one. If it were me, I'd just use a low pool > size, just have 5 connections hanging around with a higher overflow. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- http://www.imagesdusport.com -- http://www.ztfy.org -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
