sqlalchemy默认数据库连接池限制导致的服务异常排查

现象和log

批量添加100个耗时任务，发现有成功执行仅有15个，找到报错log

  return db.query(models.Config).first()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/query.py", line 2748, in first
    return self.limit(1)._iter().first()  # type: ignore
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/query.py", line 2847, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/session.py", line 2308, in execute
    return self._execute_internal(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/session.py", line 2180, in _execute_internal
    conn = self._connection_for_bind(bind)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/session.py", line 2047, in _connection_for_bind
    return trans._connection_for_bind(engine, execution_options)
  File "<string>", line 2, in _connection_for_bind
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/session.py", line 1143, in _connection_for_bind
    conn = bind.connect()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3268, in connect
    return self._connection_cls(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3292, in raw_connection
    return self.pool.connect()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 1269, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/pool/impl.py", line 158, in _do_get
    raise exc.TimeoutError(
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/20/3o7r)
WARNING: 2024-03-01 Friday 06:21:39 utils.py --------runing-----[<function start at 0x7f3aebce5f30>, (<models.scan.Target object at 0x7f3aeb24bb20>,), {}]
ERROR: 2024-03-01 06:21:39,827 utils.py eeeeeeeeee-----QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/20/3o7r)

原因

sqlalchemy的连接池默认设置pool_size=5， max_overflow=10。

方案

考虑内存和cpu，合理设置以上2个参数。