Bug #6
closed
Datasync error: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
0%
Description
Semi-recent refactoring of datasync has caused an increase in number of simultaneous connections to the local DB. (Probably still need to look closer at that, seems like it uses "too many" somehow.)
Now the default config for SQLAlchemy pooling is: pool_size=5, max_overflow=10 (see docs).
Note that this means the pool will contain at most 5 connections normally, but if more are needed an additional 10 may be created, so ultimately 15 max connections. See also docs on Connection Pooling.
If datasync has multiple consumers for the local/default DB watcher, it's possible now that it needs more connections than pool allows for, in which case you'll get an error like:
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/13/3o7r)
For now, the only practical way to fix this is to increase pool size via config. This need only be done for datasync, e.g. the web app does not need an increased pool. So for example in /srv/envs/poser/app/datasync.conf add:
[rattail.db]
# nb. datasync may need several simultaneous connections to local (default) db
default.pool_size = 15
default.max_overflow = 30
The above would use up to 15 connections in the pool normally, with additional 30 possible for overflow, i.e. ultimately 45 max connections.
Updated by Lance Edgar over 2 years ago
- Related to Feature #8: Add some sort of caching for config from DB settings added
Updated by Lance Edgar over 2 years ago
- Status changed from New to In Progress
Well, I'd hoped/assumed that #8 was the real fix here, but it turned out not to be the case. Definitely the cache mechanism was a good thing to add, but it didn't help this problem.
The question became, how to get a "clear" picture of session usage within a running app, and datasync in particular. Thanks to this Stack Overflow post it seems SQLAlchemy has a basic way to give connection pool status. So now if configured, any Rattail DB engine can opt to have its pool status logged when sessions are created, e.g.:
[rattail.db]
default.url = postgresql:// ...
default.log_pool_status = true
I then enabled that for a multi-node datasync app, and lo and behold after some sleuthing I realized some sessions were never being closed! In particular the Rattail -> Rattail "export" datasync consumer, was creating the session for reading from source DB, but never closing it.
That's been fixed now so hopefully this problem stops...
Updated by Lance Edgar about 2 years ago
- Status changed from In Progress to Closed
The major issue here was indeed some sessions not being closed; primarily fixed in dc1ac248.
I've removed the pool size increases in all server configs, and yet no more occurrences of the problem. So calling this one good.