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?
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 sqlalchemy+...@googlegroups.com >> > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> > To post to this group, send email to sqlal...@googlegroups.com >> > <mailto:sqlal...@googlegroups.com>. >> > 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 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.
