Database(MYSQL)의 Connection Pool 및 튜닝

Connection Pool이란?

사용자의 요청에 따라 Connection을 생성하다 보면 많은 수의 연결이 발생했을 때 서버에 과부하가 걸리게 된다.

이러한 상황을 방지하기 위해 미리 일정수의 Connection을 만들어 Pool 에 담아뒀다가 사용자의 요청이 발생하면 연결을 해주고 연결 종료 시 Pool 에 다시 반환하여 보관하는 것이다.

Connection Pool을 사용하는 이유?

DB Connection Pool 매니저가 일정의 Connection을 연결하고 있다가 요청이 들어오면 Connection 을 할당해주고 없으면 기다리게 한다.

클라이언트는 Connection을 다 쓰면 다시 반납하는 구조로 이루어진다 . 따라서 통신 속도 성능이 향상 된다.

보통의 경우 DB 에 연결을 하고 결과를 가져온 후에 close 시켜버린다.

DB에 연결하는 과정은 시간이 많이 소요되는 Cost가 비싼 연산이 며 performance도 많이 떨어진다.

이러한 문제점을 해결하기 위해 DB Connection pool 을 사용한다. 한번 맺은 DB Connection을 바로 close 시키지 않고 pool에 저장한 뒤에 다음 번에 동일한 Connection 을 요청하면 바로 pool에서 꺼내 제공을 함으로써 빠른 DB Connection Time 을 보장해 준다.

 

개발하며 겪었던 문제

발견된 문제

API서버를 배포 후 퇴근하고 다음날 아침에 출근을 하면, DB액세스가 필요한 API들에 다음과 같은 에러가 존재했다.

• a. (MySQLdb._exceptions.OperationalError) (1040, 'Too many connections')
• b. ('Lost connection to MySQL server during query')
• c. (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')"

원인 분석

a: [Too many connection] 

Connection수가 db에 설정된 max값을 초과해서 발생. Connection을 쓰면 사용한 커넥션 인스턴스 자원을 반납을 해야하는데 하지 않았기 때문에 계속 커넥션 인스턴스가 유지되면서 db관련 api호출시마다 새로운 연결이 계속해서 추가되서 발생.

=> 커넥션 사용 후 자원 반납하도록 해서 해결

 

b: [Lost connection to MySQL server during query]

문제를 해결하기전에 알아야 하는 기본 지식

• Database(Mysql)은 기본적으로 어플리케이션에서 커넥션 요청이 올때마다 1개의 process가 생성됩니다.
• 생성된 프로세스는 설정된 시간(wait_timeout)이 지날때까지 유지됩니다.
• 사용이 끝난 프로세스는 Sleep 상태에 돌입하며, 마지막 사용 시간 이 후(아래 사진의 Time) 설정된 프로세스 생존주기까지(wait_timeout) 살아있다가 시간이 초과됨과 동시에 프로세스가 제거됩니다.

Connection Pool 위에서 관리중인 Connection Queue들이 Mysql Process에서 제거되었는데도(wait_timeout이 초과되서) Application(Queue가 저장된 메모리)에는 DB 프로세스와 링크되어 있었고,

이를 사용하면서 발생한 문제.(database의 wait_timeout이 지나서 Sleep중인 프로세스들이 삭제되서)

 

위 테이블의 Time Column이 Process가 사용되지 않은 후 경과된 시간.

=> Application의 Mysql client에 Recycle을 통해 wait_timeout이 발생하기 전에 새로 연결하도록 설정.(wait_timeout=600(DB설정), recycle_time=500(sqlalchemy설정))

=> 600초가 지나더라도 500초를 지나는시점에 application단에서 refresh(recycle옵션을 통해)하기때문에 삭제된 프로세스를 가져오는 일이 없어짐.

recycle설정 이후 connection이 자동으로 recycling되는 과정

c: [MySQL server has gone away.]

일반적으로 아래 케이스 중 한가지 경우에 해당한다.
(뜻: MySQL 서버가 죽었습니다.)

c-1) 생성된 Connection에 문제가 생기는 경우.

=> 3-2의 b상태에서 요청이 들어오면 발생.

 

c-2) 패킷 전송에 문제가 있는 경우 : Mysql의 max_allowed_packet값을 초과할 경우 발생(한 쿼리에 전송되는 데이터양)

=> 4mb로 설정되있었기 때문에 아님.(한 쿼리에 4MB가 넘는 건 없음)

packet 설정 값

c-3) 이전 연결 세션에 영향을 받은 경우

=> 일어날 수도 있는 상황이지만 아님.

 

문제 해결 후 데이터베이스 성능개선을 위한 튜닝

a. wait_timeout 수정

=> 기존에 default값인 28800을 600으로 변경

=> 이 작업을 통해 Sleep Process를 더 자주 정리하게됬고, 이를 통해 Too many connections 도달률을 0%가깝게 만들었다.(진행중인 프로젝트 기준)

b. max_connections 수정

=> 151 -> 1000으로 수정

=> 이 부분은 성능 개선 튜닝이라고 보긴 애매하지만 개선을 위한 밑작업으로 진행하였다.

 

c. 적용한 내용 전 후 비교

 

튜닝 전

튜닝 전 connect관련 값

Aborted_connections : 연결 과정중 fail된 연결 수
Threads_connected : 현재 오픈된 연결 수
connections : 연결 시도된 총 수
Max_used_connections : 동시 최대 접속자 수

---

Cache Miss Rate(%) =  Threads_created / Connections * 100 = (67 / 1146) * 100 = 5.84%

Connection Miss Rate(%) = Aborted_connects / Connections * 100 = (63 / 1146) * 100 = 5.49%

Connection Usage(%) = Threads_connected / max_connections * 100 = (64 / 1000) * 100 = 6.4%

 

튜닝 후

튜닝 후 connect관련 값

Cache Miss Rate(%) =  Threads_created / Connections * 100 = (280/1507) * 100 = 18.57%

Connection Miss Rate(%) = Aborted_connects / Connections * 100 = (2 / 1507) * 100 = 0.13%

Connection Usage(%) = Threads_connected / max_connections * 100 = (280 / 1000) * 100 = 28.97%

 

튜닝 전과 후의 비교

Cache Miss Rate: 5.84% -> 18.57% 증가함 (0%에 가까울수록 좋음)

Cache Miss Rate?

Mysql에는 thread_cache_size라는 것이 있다.

이것의 역할은 connection 쓰레드를 생성하고 해제할 때 메모리를 할당하고 캐시할 메모리를 미리 생성해놓고 적절하게 관리하는 용도이다.

일종의 Connection Pool기능을 하는 역할

 

쉽게 말하자면 미리 준비한 캐시 쓰레드를 맞는 비율로 설정했는가에 대한 비율

위에서 전후 대비 CacheMissRate가 오른이유는 부하 테스트를 위해 진행해서 Connections이 늘고 그에 따라 Threads가 계속 추가됬기 때문

 

그림 1

thread_cache_size를 적절하게 조절해서 아래의 Threads_created수를 줄여야 좋은 성능을 낼수있다.

아래의 그림2는 테스트를 위해 동시에 0.5초당 3개의 커넥션을 사용하도록 1시간동안 계속 쿼리를 날려서 동시처리량이 늘어나서 Thread가 급격하게 생성된 예시

그렇기 때문에 thread_cache_size는 Max_used_connections와 Threads_connected를 보고 임계 workload를 판단하여 적절하게 조절해야한다.

 

그림 2

Connection Usage : 6.4% -> 28.97%  증가함

Connection Usage?

Max_connection대비 사용한 커넥션 비율(100%에 가까울수록 적당한 max_connections를 설정한 것)

전후대비 증가한 이유는 위에 처럼 부하테스트를 진행하면서 커넥션이 늘어서임.

 

 

결론

위 내용을 통해 실제로 퍼포먼스 개선이 가능하며,

비용적인 부분도 줄일수가 있다.

예를들면 max_connections를 감당할수있는 물리 메모리를 미리 계산해볼 수 있고 거기에 맞춰 클라우드 스펙을 선택하면 비용 절감이 가능하다.

 

 

참고 링크

https://blog.naver.com/didim365_/220213486050

https://knight76.tistory.com/entry/30031445050

https://sungkipyung.wordpress.com/2011/12/20/mysql-thread-cache-%EC%B5%9C%EC%A0%81%ED%99%94/