[python] SQLAlchemy, MySQL 연결 에러 해결 방법(pool_recycle 수정?)

안녕하세요. 비스카이비전입니다. 오늘은 SQLAlchemy를 사용해서 MySQL 서버와 연결해서 데이터를 주고 받는 응용 프로그램에서 발생했던 데이터베이스 연결 에러와 그 에러를 해결하는 과정을 공유해보려고 합니다. 

 

에러 발생

이 프로그램은 5~30초에 한번씩 수집한 데이터를 DB에 적재하는 프로그램인데, 10번에 한 번 정도 데이터 적재에 실패하는 현상이 있었습니다. 아래와 같은 에러 메시지가 뜨면서 말이죠.

 

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'ip주소' (timed out)") (Background on this error at: https://sqlahche.me/e/14/e3q8) 

 

그런데 이 에러가 발생하는데 있어서 어떤 규칙성이 보이지 않아서 답답한 상황이었습니다. 물론 예외처리를 해놔서 에러가 발생해 데이터가 적재되지 않아도 넘어가긴 했습니다. 하지만, 공들여 수집한 데이터가 모두 DB에 쌓이지 않으면 안 되기 때문에 어떻게든 문제를 해결해야 했습니다.

 

해결 방법

일단 구글링과 삽질의 반복을 통해 문제가 해결되긴 했습니다. 그런데 솔직히 왜 해결되었는지 정확한 이유는 잘 모르겠습니다. 제가 이 문제를 해결한 방법은 engine을 생성할 때 pool_recycle을 종전에 세팅했던 값인 30에서 3600으로 늘린 것입니다. 예전에 pool_recycle을 왜 30초로 세팅했었는지는 기억이 잘 안나네요. 아마도 어떤 문제를 해결하기 위해 이런 저런 시도를 하다가 30을 넣었던 것 같습니다. 

 

engine = sqlalchemy.create_engine("mysql://연결정보", encoding="utf-8", pool_recycle=3600)

 

MySQL 서버(또는 MariaDB 서버)는 서버에 쿼리 요청이 있는 후 wait_timeout 시간 동안 재요청이 없으면 연결을 끊어버린다고 합니다. 제가 사용한 DB 서버의 wait_timeout은 8시간(28800초)이었습니다. 

 

 

8시간 동안 쿼리 재요청이 없다면 원래는 연결이 끊어지는데, pool_recycle을 설정해주면 설정한 시간 주기로 연결을 갱신해준다고 합니다. 

 

의문점 & 질문

그런데 의문점은 pool_recycle을 30으로 설정하면 30초마다 연결을 갱신해주는 것이고, 3600으로 하면 1시간 마다 갱신해주는 것인데 무슨 차이가 있길래 더 이상 해당 에러가 발생하지 않게 된 것일까요?

 

먼저 연결 풀(connection pool)에 대한 이해가 필요한 것 같습니다. 위키백과에[2]에 의하면 연결 풀은 데이터베이스 연결의 캐시(cache) 기법이라고 합니다. pool_recycle을 작게 설정하면 자주 캐시를 지웠다가 다시 쌓는 것과 같은 결과가 생기는 것인지 궁금하네요. 그렇다면 결과적으로 그만큼 리소스를 많이 사용하게 되는 것이니, 뭔가 안정적인 프로그램 운영에 무리를 줄 수도 있을 것 같네요. 

 

아무튼 pool_recycle을 30에서 3600으로 높이기 전과 후의 연결의 개수를 비교해보면 확실히 많이 줄어들었습니다.

 

show processlist;

 

pool_recycle을 높여서 그런 것인지는 확실치는 않습니다. 연결을 새로 생성하는 것이 아니라 재활용을 하니까 줄어든 것일까요? 재활용의 주기를 길게 가져가니, 연결을 새로 생성하는 횟수가 적어진 것일까요? 

 

 

이 이슈를 경험해보셨거나 아시는 분은 댓글로 피드백 주시면 감사하겠습니다! 제가 이해한 게 틀렸으면, 제가 아예 갈피를 못 잡고 있다면 댓글로 가르쳐주세요! 감사합니다. 

 

참고자료

[1] https://docs.sqlalchemy.org/en/14/core/engines.html  

[2] https://en.wikipedia.org/wiki/Connection_pool

[3] https://yongho1037.tistory.com/569 

[4] https://velog.io/@n0wkim/sqlalchemy-connection-pool

 

관련 글

- [javascript] 예외 처리 방법, try, catch, finally