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 [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.
