Thanksl Mike and Jonathan! I wasn't expecting such good advice and such detailed responses so quickly :-)
> 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. Interesting, I hadn't thought of it in those terms. I guess this setting makes more sense to me as a form of garbage collection. > it is actually not trivial to track "idle" time on the connection Of course, the client can only approximate the idle time. To be honest, I was expecting to put a value much lower than that of the timeout on the server (the default for MySQL is 8 hours) such that the approximation's quality doesn't really matter. I assume that the person who set the pool_recycle to 5 minutes was thinking along the same lines. > You can make an idle timeout right now with any version of SQLAlchemy using pool events. See below. Thanks for the detailed example, I'll most definitely try this out! > I suggest implementing the timeout as happening at a random time This also sounds quite interesting, especially for the maximum connection age. Again, many thanks for sharing. I'll see how I can put all this information to good use :-) André On Thu, Oct 26, 2017 at 8:14 PM, Jonathan Vanasco <jonat...@findmeon.com> wrote: > On a related note, I suggest implementing the timeout as happening at a > random time within a 2-3 minute window (e.g. at 2 minutes there is an > increasing 25% chance of a reconnect, at 3 minutes it jumps up to 75%). > This should scatter reconnects during periods of high load. > > A property I managed once had an issue where a link wasn't fronted through > the CDN as intended, and publicly hit the origin servers [so 1-3 million > requests within 30 minutes to a cluster of 4 nodes sharing a mysql > master]. There was a perfect storm of timing where many simultaneous > timeouts + reconnects ended up ddos'ing the database and crashing mysql; > the barrage of reconnects kept mysql from restarting correctly too. > Variants of this scenario are amazingly common in publishing, and a dozen > major sites get blackouts from it each year. Two sites I know of had links > to their bare origin in server/cdn error pages too - so causing an error > brought more damage. > > -- > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/R2EdJzwtRso/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
