Posted in云计算 系统

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached

2019年3月22日

Python3 + Flask + mysql5.7搭建的web环境下，使用了sqlalchemy配置数据库连接池，近期发现有大量的错误日志报出，如下所示：

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。

原连接方式：

SQLALCHEMY_DATABASE_URI = os.environ.get(
    'DATABASE_URL') or "mysql+pymysql://dbuser:pwd@127.0.0.1:3306/sudops?
charset=utf8&autocommit=true"

修改后的连接方式：

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服务即可。

Tags: