On 01/06/2016 08:09 AM, Chris Wood wrote: > The project is actually using mod_wsgi (presumably using MPM) - this > seems to explain the rapid increase in the number of connections. It's > been suggested that implementing SQLRelay could be a solution. Is this > sensible? Are there any (better?) alternatives that I could look at?
SQLRelay is only a proxy solution and won't solve the problem of the application itself requesting too many connections. This is unfortunately not a problem that can be solved by installing more libraries or tools, the code itself is using the API in some non-optimal way and should be fixed. > > Cheers, > Chris > > > On Tuesday, 22 December 2015 12:02:40 UTC, Chris Wood wrote: > > > > On Tuesday, 22 December 2015 01:53:59 UTC, Michael Bayer wrote: > > > > On 12/21/2015 07:44 PM, Chris Wood wrote: > > Ah, ok - thanks for the explanation - this is different to how > I'd been > > led to believe it worked! However, I know that even when I'm > the only > > person testing the application, I'm still getting a large > number of > > connections. Is there a likely explanation why? > > > there are three categories of why an application would have lots > more > connections than what one has set for a given Engine. > > The most common is that the application is making use of child > processes, meaning it uses either Python multiprocessing, > os.fork(), or > is running in a multi-process container such as mod_wsgi under > Apache > using the prefork MPM. When Python forks a child process, an > existing > Engine in the parent process is essentially copied to a new one > in the > child that now refers to an independent pool of connections. > > The second, also pretty common reason is that it is a common > beginner > mistake to confuse the create_engine() call for one that is used to > procure a database connection. In this situation, the code will > have > routines that clearly wish to connect to the database once, then > leave, > but you'll see the create_engine() call being used each time a new > connection is desired, and often you'll see the block ending > with an > engine.dispose() call (but not always). As the Engine object is > the > home for a connection pool, you are essentially creating a whole > new > connection pool for each actual database request. > > The third, and far less likely scenario, is that there's only > one Engine > in play, but either the connection.detach() or the > engine.dispose() API > is being abused, such that connections are de-associated with > the Engine > but are not being closed. This is unlikely because those detached > connections are implicitly closed one they are garbage > collected, though > in the case of cx_Oracle this might not work very quickly or > reliably. > > For the first two scenarios, pool logging won't indicate much of > anything; inspection and understanding of the code and its > process model > would be needed. For the third, again code inspection looking > for any > unusual patterns in use with engines or connections, especially > calls to > engine.dispose() which should never be used in an ordinary > application > as well as calls to connection.detach(). > > > This information is really helpful, thanks. At the moment, I think > that the second explanation is probably most likely, but I'll go and > see if I can work out what's going on properly, and if the code is > using that technique then it gives me somewhere to start debugging... > > > > > > > > On Monday, 21 December 2015 18:51:25 UTC, Jonathan Vanasco wrote: > > > > The sizes for the connection pool are for each instance of > your > > application. If you have a 10connection pool and you are > running 10 > > instances of your application on the server, you'll easily > have 100 > > connections. If you're running 1 instance that forks, > each fork > > will have it's own pool (if correctly set up). Search the > docs and > > FAQ for "fork" for more info. > > > > I don't have time to respond to the logging stuff now. > Hopefully > > someone else will. > > > > -- > > 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] > > <mailto:[email protected]>. > > To post to this group, send email to [email protected] > > <mailto:[email protected]>. > > Visit this group at https://groups.google.com/group/sqlalchemy > <https://groups.google.com/group/sqlalchemy>. > > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. > > -- > 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] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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.
