Hello all, I've inherited a fairly large / complex internal web-based data portal (with a distinct lack of documentation), which is causing some fairly major headaches.
The application was originally written with a SQLite backend, but I was asked to convert this to Oracle, which I've done. However, we seem to get large numbers of connections being used, even though a pool is being used, and I've now been asked to try and reduce the number of connections to a sensible level (however, not being a DBA, I'm even struggling to get my head round what a sensible number would be...) This is what I understand from my limited knowledge: - the web portal has several search filters - using each one fires a jQuery ajax request to get data (and so runs a query each time a filter is used) - originally, with no changes to the default create_engine options, we were regularly seeing >100 connections (as recorded in the v$session table) - why does this happen, even though the defaults for pool_size and max_overflow are 5 and 10 respectively? - I wondered if this was caused by connections not being closed properly, so I tried adding close() statements after statements had been executed, but this caused the portal to complain with 500 errors (`exc.ResourceClosedError("This Connection is closed")`) - I have tried to limit the values of pool_size, max_overflow, pool_recycle, and pool_timeout (e.g. setting them to 1, 5, 60, and 60 respectively). The number of concurrent connections does seem to have decreased, but it's still much larger than I'd expect given the values of the arguments - and our Oracle DBA is still complaining about it... - I even tried setting the value of max_overflow to 0, but got an error of `TimeoutError: QueuePool limit of size 1 overflow 0 reached, connection timed out, timeout 30` - The number of connections seems to vary fairly randomly (e.g. I've been looking at the number of connections at minute intervals this afternoon while doing some testing, and it's jumped from up and down fairly erratically, the sudden drop in the number of connections doesn't seem to correspond with timeouts: 18 2015 dec 21 15 49 00 18 2015 dec 21 15 48 00 3 2015 dec 21 15 47 00 5 2015 dec 21 15 46 00 5 2015 dec 21 15 45 00 5 2015 dec 21 15 44 00 5 2015 dec 21 15 43 00 1 2015 dec 21 15 42 00 4 2015 dec 21 15 41 00 4 2015 dec 21 15 40 00 4 2015 dec 21 15 39 00 - I've outputting data via the sqlalchemy.pool logger, but I'm struggling to understand what it's showing. I see that connections are created, checked out, and returned to the pool, but I can't make these match with connections in the code. I'm now at a total loss of what to try next. I clearly have failed to understand some major aspects of connections / pooling - any help or pointers to help me sort out the number of connections would be majorly appreciated! Thanks, Chris -- 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.