sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached
Mar222019
Python3 + Flask + mysql5.7搭建的web环境下,使用了sqlalchemy配置数据库连接池,近期发现有大量的错误日志报出,如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
2019-03-22 16:22:30,602 - sudops - compute - 63 - ERROR - QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r) Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2888, in first ret = list(self[0:1]) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2680, in __getitem__ return list(res) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__ return self._execute_and_instances(context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3009, in _execute_and_instances close_with_result=True) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in _get_bind_args **kw File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3000, in _connection_from_session conn = self.session.connection(**kw) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1035, in connection execution_options=execution_options) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1040, in _connection_for_bind engine, execution_options) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 409, in _connection_for_bind conn = bind.contextual_connect() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect self._wrap_pool_connect(self.pool.connect, None), File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect return fn() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 403, in connect return _ConnectionFairy._checkout(self) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 791, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout rec = pool._do_get() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1189, in _do_get (self.size(), self.overflow(), self._timeout), code="3o7r") sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r) |
看上去是默认的连接池为5个,已经超过了,导致数据库连接超时。
查看下数据库的超时时间:

解决办法:SQLALCHEMY的连接串中增加pool_size。
原连接方式:
1 2 3 |
SQLALCHEMY_DATABASE_URI = os.environ.get( 'DATABASE_URL') or "mysql+pymysql://dbuser:pwd@127.0.0.1:3306/sudops? charset=utf8&autocommit=true" |
修改后的连接方式:
1 2 3 |
SQLALCHEMY_DATABASE_URI = os.environ.get( 'DATABASE_URL') or "mysql+pymysql://dbuser:pwd@127.0.0.1:3306/sudops? charset=utf8&autocommit=true, encoding='utf-8', pool_size=100, pool_recycle=3600, echo=False" |
重启web服务即可。