SQLAlchemy: db pool 관리

DB connection pool

 

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


engine = create_engine(
	self._url,
    poolclass=QueuePool,
    pool_size=20,
    max_overflow=40,
    pool_timeout=70,
    pool_recycle=60,
)

 

poolclass - 어떠한 connection pool을 사용할지에 대한 옵션

일반적으로 QueuePool을 사용하며, 옵션에 별도 명시하지 않을 경우 default 값이기도 하다.

 

QueuePool

  - 재사용 가능한 pool 사용

  - 다수의 동시적인 connection 허용 (multiple concurrent databasae connections)

    -> 멀티 스레딩 환경에서 유용

 

SingletonTreadPool

  - 하나의 connection 유지

 

StaticPool, NullPoll, AssertionPool 등도 있다.

 

 

pool_size는 허용하려는 connection pool의 사이즈,

 

max_overflow는 pool_size가 모두 사용될 때, 추가적으로 허용하는 pool의 설정 (유연성을 제공)

 

pool_timeout은 s(초) 단윌로, 해당 시간동안 db에서 응답이 없으면 connection을 끊는다.

 

pool_recycle은 해당 시간(초)이 지난 후에 idle 상태의 connection들을 끊어서 새로운 connection 요청이 왔을 때 connection을 만들 수  있도록 pool을 채운다.

 

pool_recycle의 시간을 mysql등의 DB에서 pool을 끊는 시간보다 적게 설정해주어야, 안정적인 DB connection을 유지할 수 있다.

MySQL의 경우 wait_timeout의 값을 보려면, mysql에 접속해서 다음 명령어를 실행하면 된다.

SHOW VARIABLES LIKE 'wait_timeout';

connection pool을 확인하는 것은 'max_connections'로 쓰면 된다. SHOW VARIABLES LIKE '원하는 값'; 과 같은 식으로!

 

sqlalchemy에서 MySQL의 wait_timeout을 동적으로 할당하는 코드를 짜보면 다음과 같다.

from sqlalchemy import create_engine, text
from sqlalchemy.pool import QueuePool

temp_engine = create_engine(url)
with temp_engine.connect() as conn:
    result = conn.execute(text("SHOW GLOBAL VARIABLES LIKE 'wait_timeout';"))
    db_wait_timeout = result.fetchone()
    adjusted_pool_timeout = int(db_wait_timeout[1]) - 10
engine = create_engine(
    url,
    poolclass=QueuePool,
    pool_size=20,
    max_overflow=20,
    pool_timeout=adjusted_pool_timeout,
)

 

raw sql만 database에 맞게 변경해주면 될 듯 하다.

 

 

** 부하테스트 중에, DB 트랜잭션이 포함된 request가 과도하게 들어오면, Too many connections의 error가 발생한다.

-> 일정 수준 이상의 connection이 생겼을 때, connection 요청을 차단하는 로직을 짜야 할 듯 하다.

 

** pool_recycle을 database (MySQL의 경우) 의 wait_timeout보다 작게 설정했음에도, sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') 오류가 발생하는 경우가 있다고 한다... 재현이 잘 안되는데, 이 부분 어떤 경우에 발생하는지 구체적인 테스트가 필요하다.

----이유를 찾은 듯 하다.----

메모리를 낮게 설정하고, CPU도 한개만 쓰면서 과부하를 걸어도 해당 오류가 발생하지 않았는데,

어제 트러블슈팅을 시도하다, 오늘 아침에 출근해서 서버를 키니, 바로 해당 오류가 발생.

현재 내 작업환경은 클라우드 서버에서 작업을 하고 있고, 내가 FastAPI 서버를 끄더라도, MySQL은 항상 켜져있다.

다음날 와서 MySQL에 연결된 FastAPI 서버를 실행하니, 해당 오류가 바로 발생했는데, 이는 FastAPI가 꺼졌음에도, FastAPI에서 잡고있던 idle connection이 DB 상에서 끊어지지 않은 상태로 남아 있는 것 같다..... 이게 말이 되나?근데 이게 말이 안되면 뭘로 설명이 되나?

 

pool_recycle 옵션을 해주면, 서버가 시작될 때 MySQL의 connection을 recycle 하도록 하는 것인가? 그렇다면 말이 되는 듯 하다

 

 

 

 -- MySQL에서 timeout과 관련하여 wait_timeout 외에 net_read_timeout, net_write_timeout, innodb_lock_wait_timeout 등도 있으며, 다른 설정값들도 참고해보자

또한, MySQL과의 연결이 끊기는 것은 connection이 wait_timeout 이상으로 idle한 상태로 유지되는 것 외에도, 네트워크 이슈, 지나치게 오래 실행되는 쿼리, 서버의 과부하 상태 등의 이슈가 있을 수 있으므로 다각도로 살펴봐야 할 듯 싶다.