[mysql] sleep session과 wait_timeout

요 몇주간.. 골머리를 썩었던 DB와의 사투를 적어보려 합니다..

- 문제 상황 : 서비스 내부에서 주기적으로 특정 테이블을 조회하는 쿼리가 있는데 해당 쿼리를 실행할 때 기준 없이 15분간 행이 걸리는 현상이 발생하였습니다. 

- 해결 시도 : 특정 쿼리에서만 발생한다고 생각하여 행걸리는 쿼리를 메모리에 올린 후 테스트 해봤지만 이번에는 그 다음 쿼리에서 발생하였습니다. 따라서 특정 쿼리에서 발생하는 것이 아니라 DB에 connection할 때 문제가 생기는 것은 아닐까 생각하게 되었습니다.

- 원인 파악 과정: 

 

netstat

netstat -an | grep 3306 | grep EST

해당 명령어를 통해 DB에 붙어있는 세션을 검사하던 중 send-q 부분에 계속해서 버퍼가 빠지지 않는 것을 확인했습니다.

이렇게 관찰된 부분이 DB에서 select할 때 15분간 행 걸렸던 현상하고 연관이 있는지는 모르겠지만 대략 15분간 해당 현상이 지속이 되었고 드물게 발생한다는 것을 알아냈습니다.

 

tcpdump 

tcpdump까지 떠서 wireshark로 확인해본 결과 특정 포트에서 request query를 날렸는데 그 후 DB서버에서 응답이 오지 못해서 대략 13분 정도 tcp retransaction 요청을 날리다가 끊어진 것을 확인했습니다.

 

위의 내용을 토대로 DB 컨설팅 업체에 문의한 결과, 

- wait_timetout, aborted connection 로그가 지속적을로 확인되는 것으로 보아, connection pool의 자원부족으로 판단

- sleep process 의 개수 증가는 connection 지연 또는 실패 등의 원인

따라서 wait_timeout 이 현재는 8시간으로 적용되어있으니 해당 시간을 조절해보자는 답변이 왔었습니다.

해당 경험을 토대로 sleep process와 wait_timeout에 대해서 간단하게 정리해보려합니다.

 

sleep session 이란

client가 mysql 서버와 연결 후 다음 query 수행 시까지 대기중인 상태의 세션(프로세스)입니다. 

sleep session이 많고 정리가 안되는 경우 connection 이 부족하게 되어 신규 세션 접속이 지연되거나 실패할 수도 있다고 합니다.

show processlist;

해당 명령어를 통해서는 현재 processlist를 확인할 수 있고 사용하고 있는 db에서 실행해본 결과 많은 수의 sleep process를 확인할 수 있었습니다.

 

wait_timeout 이란

활동하지 않는 커넥션을 끊을때까지 서버가 대기하는 시간

show variables like 'wait_timeout';

해당 명령어를 쳐서 확인해보면 default 값은 28800으로 8시간 입니다. 따라서 활동하지 않는 커넥션을 끊을때까지는 8시간이 걸린다는 

뜻입니다. (현장 상황에 맞게 조정하면 된다고 합니다. 서비스의 특성에 맞게)

 

- 결과 : 

sleep process가 이번 현상의 원인이라고는 할 수 없지만 wait_timeout을 조절해서 모니터링을 진행해본다고 했습니다. 추후에 이 현상들이 해결이 되었는지에 대해서 작성해보겠습니다.ㅎㅎ 그래도....해결이 될 수도 있다는 것이 얼마나 다행인지ㅜㅜ