[SQLAlchemy] Connection 설정에 관한 탐구

목차

 

개요 

SQLAlchemy를 사용하다 MySQL 쪽에서 자꾸 Aborted Connection이 발생하고 있는 걸 확인했다. 왜 이런 경고가 뜨는지 의문이 들었고 이왕 조사하는 김에 Connection 설정에 관련된 부분들을 더 살펴봤다.

 

관찰한 결과를 기록하기 위한 용도로 정리했기에 SQLAlchemy의 동작원리와 다소 상이할 수 있는 것에 유의하자.

 

또한, 사견도 기록하기에 SQLAlchemy을 미리 사용하시는 분이시라면 많이 혼란이 생길 수 있음을 미리 알려드린다.

 

1.  Connection은 실제로 query를 날릴 때 생성된다.

Aborted Connection이 일어난 상황은 SQLAlchemy에서 Connection Pool을 잡은 상태였기에 Connection Pool에 관련된 설정부터 알아봤다.

 

SQLAlchemy에서 Connection Pool을 사용하기 위해 다음과 같은 코드를 사용한다.

from sqlalchemy import pool
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

class SQLAlchemyEngineFactory:

    @classmethod
    def get_engine(cls, url: URL):
        return create_engine(url,
            pool_size=3,
            pool_timeout=3,
            max_overflow=0,
            poolclass=pool.QueuePool
        )

Connection Pool이 제대로 동작하는지 테스트하기 위해서는 다음과 같은 코드를 사용할 수 있다.

preset_url = URL.create(
    drivername="mysql+pymysql",
    username="root",
    password="1234",
    database="db",
    host="127.0.0.1",
    port=9902
)
engine = SQLAlchemyEngineFactory.get_engine(preset_url)

connections = [engine.connect() for x in range(4)]
for connection in connections:
    connection.execute("select 1;")

pool_size가 3개로 설정되어 있지만 생성하는 connection은 4개이다. 즉 설정한 Connection Pool의 크기보다 더 많은 Connection을 잡으려 하는 경우이다. 이에 관련한 Exception은 다음과 같다.

sqlalchemy.exc.TimeoutError: QueuePool limit of size 3 overflow 0 reached, connection timed out, timeout 3.00
(Background on this error at: https://sqlalche.me/e/14/3o7r)

관찰한 결과 중 한 가지 흥미로웠던 점은 engine.connect()를 하는 시점에 실제로 DB에서 Connection이 생성되는 것이 아니라 connection.execute()를 할 때라는 점이다. 

 

만약 Connection을 미리 생성해두고자 한다면 생성한 connection 객체를 가지고 쿼리를 날려야 한다는 점이다.

 

 

2.  Lost Connection이 일어날 때는 pool_pre_ping을 살펴보자.

Aborted Connection을 해결하기 위한 방법을 조사했을 때 가장 먼저 접한 해결 방법이다.

class SQLAlchemyEngineFactory:

    @classmethod
    def get_engine(cls, url: URL):
        return create_engine(url,
            pool_pre_ping=True,
            pool_size=3,
            pool_timeout=3,
            pool_recycle=10,
            max_overflow=0,
            poolclass=pool.QueuePool
        )

engine을 생성하는 시점에 "pool_pre_ping"이라는 옵션을 사용하는 것이다. SQLAlchemy 문서에서는 이에 관련하여 다음과 같은 내용으로 설명되어 있다.

Reference: https://docs.sqlalchemy.org/en/20/core/pooling.html

The pessimistic approach refers to emitting a test statement on the SQL connection at the start of each connection pool checkout, to test that the database connection is still viable. The implementation is dialect-specific, and makes use of either a DBAPI-specific ping method, or by using a simple SQL statement like “SELECT 1”, in order to test the connection for liveness.

비관적 접근 방식은 각 연결 풀 체크 아웃이 시작될 때마다 SQL 연결에 대한 테스트 문을 실행하여 데이터베이스 연결이 여전히 실행 가능한지 테스트하는 것을 말합니다. 이 구현은 방언에 따라 다르며, DBAPI 전용 핑 방법을 사용하거나 "SELECT 1"과 같은 간단한 SQL 문을 사용하여 연결이 살아있는지 테스트합니다.

 

"Connection Pool"이라는 개념은 미리 Connection을 생성하고 이렇게 생성된 Connection을 미리 사용한다는 것인데 만약 DataBase에서 Connection이 삭제된 경우 "SELECT 1"과 같은 요청을 보내 연결이 살아있는지 테스트하고 없으면 생성을 사용한다. 

 

이를 테스트하기 위해서는 DataBase에서 다음의 설정을 날려 미리 준비를 해두자.

set global  wait_timeout=3;

 

이제 pool_pre_ping 옵션을 테스트하기 위한 Code를 작성하자.

from sqlalchemy import pool
from sqlalchemy.engine import URL, create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.orm import Session
    
...

class SQLALchemySessionFactory:

    @classmethod
    def get_session(cls, engine):
        _maked_session = sessionmaker(bind=engine, autocommit=False, autoflush=False, expire_on_commit=True)
        _Session = scoped_session(_maked_session)
        _Session.configure()

        return _Session()

if __name__ == '__main__':
    engine = SQLAlchemyEngineFactory.get_engine(preset_url)
    session: Session = SQLALchemySessionFactory.get_session(engine)

    # connection create
    session.execute("select 1;")
    session.commit()
    session.close()
    import time

    # connection disappear
    time.sleep(4)

    # (pool_pre_ping) connection recreate
    session.execute("select 1;")
    session.commit()
    session.close()

connection create 부분에서 select 1을 날려 미리 connection 하나를 잡아둔다. 그리고 4초 이후에 select 1을 한번 더 날린다. 앞서 wait_timeout을 3으로 잡아두었기에 중간에 4초가 지나면 connection이 삭제되며 이후 pool_pre_ping이 제대로 동작할 것이라 기대한다면 Lost Connection과 같은 Error는 일어나지 않는다. 확인한 결과는 다음과 같다. 

pool_pre_ping이 설정되었기에 중간에 관련 경고를 보여주고 있다. pool_pre_ping이 설정되어있지 않으면 결과는 다음과 같다.

이 결과로써 알 수 있다시피 만약 "Lost Connection"이 Log에 잡힌다면 pool_pre_ping을 도입해 보는 걸 고려해 볼 수 있다는 점이다.

 

 

3. Aborted Connection Error

SQLAlchemy의 Session을 Close() 시키는데 자꾸 Aborted Connection이 일어난다. 확인한 로그는 다음과 같은 형식이다.

명확한 정답은 찾지 못했으나 관찰을 통해 알아낸 사실은 session을 close() 처리하고도 DataBase에서는 Connection이 살아있다는 점이었다. 이렇게 살아있는 Connection이 DataBase에서 설정한 timeout을 넘어갈 때 자동적으로 종료가 되어 Aborted Connect이 일어남을 확인했다. 

 

몇 가지 구글링 끝에 session을 close 시키고 나면 engine.dispose를 이용해 connection을 완전히 끝내야하나는 사실을 알게 되었다. 다음과 같은 형식이다.

engine = SQLAlchemyEngineFactory.get_engine(url=preset_url)
session: Session = SQLALchemySessionFactory.get_session(engine=engine)

session.execute("select 1;")
session.commit()
session.close()

engine.dispose()

 

그런데 이 방법을 fastapi나 flask에서 사용하려고 본다면 실용적이지 못한 방법이라 생각한다. application이 종료되는 시점에 dispose를 걸어서 application 종료와 함께 connection을 정리할 수는 있겠으나 application 자체는 24시간 돌아가는 걸 가정하기 때문이다.

 

뭔가 다른 방법은 없을까 했는데 pool_recycle을 통해 connection을 주기적으로 정리해 주는 방법을 활용해보고자 했다.

 

DataBase(MySQL)의 wait_timeout은 기본적으로 28800초이기에 이 시간을 넘어서지 않는 선에서 pool을 재활용해주면 aborted_connection이 발생하는 경우를 감소시킬 수 있지 않을까 싶었다. 그러나 실제로 pool_recycle을 설정하면 DataBase에서 Connection이 어떤 식으로 동작하는지를 관찰하지 못했다.

 

 

4. Session을 사용한다면 pool_size를 하나만 사용해도 충분한가?

지금까지 예제 코드로 pool_size를 3으로 잡았다. session을 통해 query를 질의하고 connection이 잡히는 과정을 mysql 모니터링 도구로 확인했을 때는 어째선지 실제로 connection은 1개만 생성되고 이 connection이 대기되어 재사용되는 것을 확인할 수 있었다.

 

pool_size를 3개로 잡아놓은 것이 그다지 의미가 없었다. 만약 비동기처리를 위해 여러 connection을 사용해야 하는 경우라면 제일 처음 예제로 connection pool의 동작을 테스트한 코드를 사용하면 되지만 실제로는 session을 통해 쿼리를 날리는 형태를 많이 작성하고 있어서 그런지 pool_size를 설정하는 것이 그다지 의미가 없는 건 아닌지 싶다.

 

 

 

5. 마치며

SQLAlchemy를 사용하면서 Session이 종료될 때 왜 Aborted Connection이 일어나는지가 궁금헀는데 이제야 그 이유를 알게 되었다. 그러나 이 사실을 가지고 실제 Production에서 발생하는 문제들을 처리할 수 있는지를 생각해 본다면 그건 어려울 것 같다.

 

web application을 통해 명확하게 해결할 수 있는 방법을 찾아낼 때까지 자꾸 탐구해 봐야겠다.

 

---