[sqlalchemy] Closing pool connections
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... Any idea ? Best regards, Thierry -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Closing pool connections
On Thu, Sep 25, 2014 at 5:33 AM, Thierry Florac tflo...@gmail.com 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... Any idea ? A while back when I wanted this, I ended up subclassing QueuePool and using that subclass with the engine. Sadly, I don't have the code at hand. But I have managed without it since then, so I'm wondering if this is really an issue. Currently, I'm using postgres with pgbouncer, and this last one can handle separating the idle from the active very well. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Closing pool connections
On Sep 25, 2014, at 4:33 AM, Thierry Florac tflo...@gmail.com 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Closing pool connections
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 mike...@zzzcomputing.com: On Sep 25, 2014, at 4:33 AM, Thierry Florac tflo...@gmail.com 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
