03. MySQL - timeout parameter
connect_timeout
- MySQL 서버 접속시에 접속실패 메시지를 보내기까지 대기하는 시간
delayed_insert_timeout
- insert시 delay될 경우 대기하는 시간
innodb_lock_wait_timeout
- innodb에 transaction 처리중 lock이 걸렸을 시 롤백 될때까지 대기하는 시간으로 innodb는 자동으로 데드락을 검색해서 롤백시킴
innodb_rollback_on_timeout
- innodb의 마지막 구문을 롤백시킬지 결정하는 파라미터 timeout은 진행중인 transaction을 중단하고 전체 transaction을 롤백하는 과정에서 발생
net_read_timeout
- clinet > net > MySQL server 구성에서 MySQL이 net으로붙터 read하는 상황에서 세션을 끊을때까지 기다리는 시간
idc에서 rds 이관시 timeout 발생하면 설정부분에서 판단 필요
net_write_timeout
- MySQL Server > net > clioent 구성에서 mysql이 net으로 data write 하는 상황에서 세션을 끊을때 까지 기다리는 시간
slave_net_timeout
- 마스터/슬레이브로 서버가 클라이언트로부터 데이터를 읽어들이는 것을 중단하기까지 대기하는 시간
table_lock_wait_timeout
- 테이블 락을 중단하기까지 대기하는 시간
wait_timeout
- 활동하지 않는 커넥션을 끊을때까지 서버가 대기하는 시간
interactive_timeout
- 활동중인 커넥션이 닫히기 전까지 서버가 대기하는 시간 (MySQL command line)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
공작기계 데이터를 50ms 단위로 여러 머신에서 데이터를 받아와 처리해줘야 하는 상황
time out 설정 목적
- 제한된 자원(메모리)의 효율적 이용
- MySQL 성능 극대(한계점에 극대, 한계점을 초과하지 않음)
- 원활한 커넥션 처리
- 절대적으로 불필요한 커넥션을 강제로 close(불필요한 커넥션 반환)
connect_timeout, interactive_timeout은 튜닝과 좀 거리가 멀고, 실제로 바쁜 서버라면 반드시 wait_timeout을 따로 튜닝하여 설정해줘야 함
가장 중요한 wait_timeout에 대해 정리
interactive 모드(mysql> 과 같은 프롬프트 있는 콘솔이나 터미널 모드)가 아닌 경우에 해당되며, mysqld와 mysql client가 연결을 맺은 후 다음 쿼리까지 기다리는 최대 시간을 의미함
즉 대부분 php나 C, PERL, PYTHON 등의 API를 이용한 client 프로그램 모드를 말함
interactive_timeout과 마찬가지로 wait_timeout 까지 아무런 요청(쿼리)이 없으면 연결은 취소되고 그 결과는 Aborted_clients에 누계됨
wait_timeout 안에 다시 요청이 들어오면 wait time 은 0으로 초기화 됨
연결 취소율(POC) = Aborted_clients * 100 / Connections
연결이 취소되는 경우(강제 종료됨)
- 종료(exit) 되기전 mysql_close()가 없는 경우
- wait_timeout 이나 interactive_timeout 시간까지 아무런 요청(쿼리)이 없는 경우
기본 값은 interactive_timeout과 마찬가지로 28800(8시간)초로 설정 되어 있는데 너무 관대한 설정이라고 봄
앞에서 연결 취소율(POC)을 계산해 보면 MySQL 서버가 어느 정도 비율로 강제 종료하는지 알 수 있음
예를 들어 POC가 1% 라면, 100개의 커넥션당 하나정도는 mysql_close() 없이 강제 종료(exit)되고 있다는 의미임
이 값은 0%에 가까울수록 좋은 것이며, 클라이언트 프로그램에서 모두 정상적으로 종료했다는 의미임
연결 취소율 값이 지나치게 높으면, wait_timeout 설정이 너무 짧거나 대부분 client 프로그램이 exit(종료)를 정상적으로 하지 않을 경우이므로 반드시 튜닝이 필요함
특히 서버가 매우 바쁜 경우는 이 wait_timeout 시간을 짧게 설정하여 불필요한 커넥션을 모두 제거해 줘야함(메모리 한계 문제)
wait_timeout 튜닝이 필요한 경우
- 보통 POC(연결 취소율)가 1% 이상인 경우
- 아주 바쁜 서버에서는 튜닝전 0.01% 이상인 경우
- 기타 튜닝 기준에 따라 다름
프로그램을 수정하지 않은 상태에서 wait_timeout을 튜닝하면 튜닝전에 비해서 POC가 더올라가야 정상임.
이 의미는 비정삭적인 커넥션을 반환하는 비율이므로, 정상적인 서비스하에서 이 값이 올라가야 함을 의미함.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aborted_clients 값이 증가하는 원인
- 클라이언트 프로그램이 mysql_close()를 호출하지 않고 종료
- 클라이언트 프로그램에서 wait_timeout 혹은 interactive_timeout 시간동안 요청이 없는 경우
- 전송도중 클라이언트 프로그램이 비정상적으로 종료
- 비정상적인 패킷을 가진 통신 시도
- 접속을 시도한 사용자가 데이터베이스에 접근할 권한이 없거나, 패스워드가 잘못된 경우
- 접속을 시도하는 동안 connect_timeout 시간을 초과
- live 서비스중 해당 값이 과도하게 증가 된다면 해킹 시도에 대한 의심을 해봐야 함