[MySQL] 8.0 사고 기록

굉장히 오랜만에 블로그를 작성하네요 ~~

 

오늘 아주 큰?! 사고로 이어질뻔했던 이슈가 하나 있어서 기록으로 남겨 보려고 합니다.

 

mysql 8.0 db 의 커넥션 상태가 증가만 하고 해소가 되지 않는 것!!!!

 

오늘 테스트를 빡시게 돌려보다가 보니 too many connections 오류가 다수 발생 하는 것 !!

 

곧바로 db 설정 원인 분석 ~~

 

show variables like 'max_connections';

로 확인 했을 때 맥스 커넥션은 151로 설정 되어 있으며

 

SHOW STATUS LIKE 'Threads_connected' -- 커넥션 맺어져 있는 수

의 값이 계속 증가만 하고 해소가 되지 않았다. (여기서 151개가 초과 하면 바로 too many 오류 발생 ~~)

 

SHOW STATUS LIKE 'Threads_running' -- 그중 실행되고 있는 커넥션 query 상태

의 상태 값은 여전히 2개(내가 날린 프로세스 명령어 1, db event 1(empty 상태임 db 이벤트가 없어서)

 

threads 의 값들은 증가하고 있고, SHOW FULL PROCESSLIST; 모든 프로세스를 확인 결과 위 2개의 프로세스를 제외 하고는

 

모든 프로세스들이 command -> sleep 상태 였다..(이것이 주 원인)

 

하여 왜 이놈들이 슬립상태이고 해소가 되지 않았을까 원인을 찾아보니 

 

show variables like 'wait_timeout'; -- 최대 연결시간, 아무 요청이 없으면 연결은 취소

show variables like 'interactive_timeout'; -- 콘솔에서의 mysqld와 클라이언트 커넥션을 맺은 후 다음 요청을 기다리는 최대 시간

값이 28800 인것이다.. 8시간!!!  그래서 한번 커넥션을 맺은 커넥션들은 8시간동안 슬립상태이며 결국은 우리가 테스트를 빡시게? 돌린탓에 too many 오류가 발생 된 것이다 ~~ 이것을 10초로 설정 한후 모든 오류 해소 ~

 

 

 

참고 : https://code-factory.tistory.com/47

Mysql Connection 설정 (too many connections error)

1. Mysql의 최대 접속자 수 확인하기.

show variables like '%max_connection%';

max_connections는 mysql에서 connect할 수 있는 최대 소켓수를 정의한 값입니다.

접속 최대수를 초과해 접속 시도를 하는 경우 "too many connections error"이 발생 합니다.

 

2. 접속수 보기.

show status like 'Max_used_connections';

 

3.  Connection 설정을 위한 변수.

 

- variables (예 show variables like '%max_connection%';)

 

max_connections – 최대 동시 접속 가능 수. max_connection 수가 늘어 나면 innodb 의 thread별 메모리 사용량이 늘어나 같이 고려해야 합니다.

wait_timeout – 지정된 시간 동안 아무런 요청 없이 대기하는 경우 MySQL 서버는 해당 커넥션을 강제로 종료해 버립니다. (기본값은 28800초(8시간))

thread_cache_size – thread 재 사용을 위한 thread pool, 최대 몇 개 까지의 thread를 pool에 보관할지 설정.

 

- status (예 show status like '%clients%';)

 

Aborted_clients : 클라이언트 프로그램이 비 정상적으로 종료된 수

Aborted_connects : MySQL 서버에 접속이 실패된 수

Max_used_connections : 최대로 동시에 접속한 수

Threads_cached : Thread Cache의 Thread 수

Threads_connected : 현재 연결된 Thread 수

Threads_created : 접속을 위해 생성된 Thread 수

Threads_running : Sleeping 되어 있지 않은 Thread 수

 

4. 튜닝

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

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

Connection Usage(%) = Threads_connected / max_connections * 100

 

- Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.

 

- DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.

 

- Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.

 

- MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

 

 

5. Aborted_clients 값이 증가하는 원인

클라이언트 프로그램이 mysql_close() 를 호출하지 않고 종료.

클라이언트 프로그램에서 wait_timeout 혹은 interactive_timeout 시간동안 요청이 없는 경우.

전송도중 클라이언트 프로그램이 비정상적으로 종료.

비정상적인 패킷을 가진 통신 시도.

접속을 시도한 사용자가 데이터베이스에 접근할 권한이 없거나, 패스워드가 잘못된 경우.

접속을 시도하는 동안 connect_timeout 시간을 초과.

live 서비스중 해당 값이 과도하게 증가 된다면 해킹 시도에 대한 의심을 해봐야 합니다.