On Thu, Oct 26, 2017 at 5:36 AM, André Caron <[email protected]> wrote: > Hi there! > > It's my first time on this list. Don't be shy to tell me if this isn't the > right channel to discuss this :-) > > When looking at MySQL metrics on our production deployment of MySQL, I > noticed a hammer effect in the number of new connections to MySQL. In > short, when there is a sudden burst of new connections, this burst repeats > itself every 8 minutes for quite some time until randomized access patterns > smooth it out. I could correlate impacts on latency of MySQL queries in our > application to these bursts in reconnections. > > I tracked this down to our use of the pool_recycle setting, which was set to > 8 minutes. I built a small executable example and posted it on GitHub to > showcase the behaviour: > https://github.com/AndreLouisCaron/sqlalchemy-pool-recycle
so yes, 8 minutes is lower than what was the original idea for this setting. Also, depending on what you have set for max_overflow vs. pool size, usually the "newness" of connections gets flattened out pretty quickly due to some of them being created as "overflow" connections, combined with the fact that its random which checked-out connections become persistently pooled vs. not. These aren't necessarily excuses but this is probably why what you are observing is not too common. > > I'm really curious to know why the QueuePool chooses to track time elapsed > since the connection was opened rather than the time elapsed since the > connection was last used since the latter seems more natural to me. Well, the pool was written about 11 years ago and decisions like these filtered out of things I saw in projects like the c3p0 connection pool as well as from working a lot with Apache, and the concept of "limiting the life of a resource" is more common there, as even today c3p0 has this setting maxConnectionAge: http://www.mchange.com/projects/c3p0/#maxConnectionAge . The rationale they give is sort of along the lines of what my thinking was as well, that of the "misbehaving database connection" that leaks memory either on the client or server and needs to have its lifespan limited (they mention this here: http://www.mchange.com/projects/c3p0/#configuring_connection_testing). That concept was more common back then and was probably part of the rationale. After that, c3p0 has an enormous number of flags and switches and is extremely complicated, and I was looking to build an very simple pool, especially one that didn't have exotic requirements like running a background thread to test connections and such. The "pool_recycle" setting presented itself as a simple approach to the problem of connections timed out by the server or middleware that would work in all cases with virtually no overhead. While c3p0 also has a maxIdleTime setting I could have imitated, it is actually not trivial to track "idle" time on the connection, since you have to instrument every API call on the connection that emits on the connection such as cursor.execute(), and additionally you can't even know in the case of DBAPI if a call like connection.rollback() actually emits SQL; the earliest version of the pool did not even emit rollback() upon checkin. Even as I think about it now, while it seems like an unusual case that "connection is checked out" would not be equivalent to "non-idle", maybe it's not. Maybe a web application is set up to build a Session on every request that will pull up a connection, but the vast majority of web requests just read from a cache, or don't write to a particular database that nonetheless has a connection checked out. These situations could produce long-idle connections that are nevertheless being checked out. It would certainly be very easy to add an "idle" timeout, but it would need to be accompanied by the above caveat that this is not as much an "idle" timeout as it is a "not checked out" timeout, which may or may not track "idleness". However, when combined with pre-ping, this would work quite well. Pre-ping, something that I would have considered to be unheard of when I first wrote the pool (since I anticipated a vastly higher checkin/checkout frequency back then when transactions were not used by default), is now a built in feature at http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#pessimistic-disconnection-detection-added-to-the-connection-pool. You can make an idle timeout right now with any version of SQLAlchemy using pool events. See below. from sqlalchemy import create_engine from sqlalchemy import event from sqlalchemy import exc import time e = create_engine("mysql://scott:tiger@localhost/test", echo_pool='debug') idle_time = 30 @event.listens_for(e, "checkout") def co(dbapi_connection, connection_record, connection_proxy): now = time.time() checkout_time = connection_record.info.get('checkout_time') if checkout_time and now - checkout_time > idle_time: raise exc.DisconnectionError( "recycling connection idle past %s seconds" % idle_time) connection_record.info['checkout_time'] = now c = e.connect() time.sleep(1) c.close() time.sleep(35) c = e.connect() time.sleep(1) c.close() time.sleep(5) c = e.connect() time.sleep(1) c.close() 2017-10-26 12:24:46,825 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to 'localhost' at 55c60f8d8920> 2017-10-26 12:24:46,831 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d8920> checked out from pool 2017-10-26 12:24:47,834 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d8920> being returned to pool 2017-10-26 12:24:47,834 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d8920> rollback-on-return 2017-10-26 12:25:22,870 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d8920> checked out from pool 2017-10-26 12:25:22,870 INFO sqlalchemy.pool.QueuePool Disconnection detected on checkout, invalidating individual connection: DisconnectionError('recycling connection idle past 30 seconds',) 2017-10-26 12:25:22,871 INFO sqlalchemy.pool.QueuePool Invalidate connection <_mysql.connection open to 'localhost' at 55c60f8d8920> (reason: DisconnectionError:recycling connection idle past 30 seconds) 2017-10-26 12:25:22,871 DEBUG sqlalchemy.pool.QueuePool Closing connection <_mysql.connection open to 'localhost' at 55c60f8d8920> 2017-10-26 12:25:22,873 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to 'localhost' at 55c60f8d9b40> 2017-10-26 12:25:23,874 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d9b40> being returned to pool 2017-10-26 12:25:23,875 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d9b40> rollback-on-return 2017-10-26 12:25:28,880 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d9b40> checked out from pool 2017-10-26 12:25:29,881 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d9b40> being returned to pool 2017-10-26 12:25:29,881 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to 'localhost' at 55c60f8d9b40> rollback-on-return > > Was this design of the pool_recycle deliberate? If so, what was the > rationale? > > Thanks, > > André > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
