[DB] MySQL - timeout 파라미터

서버로그를 체크하던 중 커넥션 관련 문제가 발생하였고, autoReconnect 옵션을 사용하여 해결하였다.

autoReconnect 관련 내용이 궁금하여 알아보던 중 커넥션 관련하여 설정해 줄 수 있는 파라미터들이 있다는 것을 알게되어 정리해본다.

아울러 커넥션과 상관없지만 이름에 timeout이 포함된 파라미터를 모두 알아보도록 하겠다.

MySQL 접속 후 show variables like '%timeout'; 이라는 쿼리를 실행하면 설정된 변수들 중 timeout이 포함된 값들이 모두 나타난다.

현재 로컬에서 검색한 파라미터들이다. 어떠한 의미를 가지고 있는지에 대해서 살펴보도록 하자.

connect_timeout  :  MySQL 서버 접속시에 접속실패를 메시지를 보내기까지 대기하는 시간

delayed_insert_timeout  :  insert시 delay될 경우 대기하는 시간

innodb_lock_wait_timeout  :  innodb에 transaction 처리중 lock이 걸렸을 시 롤백 될때까지 대기하는 시간.

     innodb는 자동으로 데드락을 검색해서 롤백시킨다.
 

innodb_rollback_on_timeout  :  innodb의 마지막 구문을 롤백시킬지 결정하는 파라미터. 

        timeout은 진행중인 transaction을 중단하고 전체 transaction을 롤백하는 과정에서 발생한다.
 

interactive_timeout  :  활동중인 커넥션이 닫히기 전까지 서버가 대기하는 시간
 
net_read_timeout  :  서버가 클라이언트로부터 데이터를 읽어들이는 것을 중단하기까지 대기하는 시간
 
net_write_timeout  :  서버가 클라이언트에 데이터를 쓰는 것을 중단하기까지 대기하는 시간 
 

slave_net_timeout  :  마스터/슬레이브로 서버가 클라이언트로부터 데이터를 읽어들이는 것을 중단하기까지 대기하는 시간
 
table_lock_wait_timeout  :  테이블 락을 중단하기까지 대기하는 시간
 
wait_timeout  :   활동하지 않는 커넥션을 끊을때까지 서버가 대기하는 시간

그러면 실제로 timeout 값을 수정하고 커맨드라인에서 확인해 보도록 하자.

wait_timeout값에 대한 수정을 하였다. 윈도우는 my.ini 리눅스의 경우 my.cnf 파일의 [mysqld] 부분에서 추가하면 되겠다.

MySQL 서버를 재시작 하고 적용된 값이 나타나는지 확인해보도록 하겠다.

분명 wait_timeout을 변경하였으나 변수값을 SELECT시에는 변한게 없는것으로 나온다.

그렇다면 값이 변하지 않은것일까... 이번에는 GLOBAL 변수를 검색해 보았다.

오오!! 이번에는 잘 변한것으로 나온다.

그렇다면 무엇때문에 검색방식에 따라 다르게 표현되는 것일까. 검색해보니 세션 wait_timeout값은 클라이언트의 타입에 따라

wait_timeout 값 혹은 interative_timeout 값으로 초기화 된다고 한다.  

커맨드라인을 통해 접속한 클라이언트는 interative_timeout값을 wait_timeout이 참고하기 때문에 다르게 보이는 것이었다.



설정파일 외에도 커맨드라인 명령어를 통해 해당 파라미터를 설정할 수 있겠다.

해당 세션에서만 설정하는 명령
set session wait_timeout = [설정값]

해당 GLOBAL 파라미터를 설정하는 명령
set GLOBAL wait_timeout = [설정값]

GLOBAL 파라미터로 설정하는 경우에는 재로그인 시에도 값이 동일하게 나타나지만 MySQL 서버를 중지 후 재시작시에는

my.ini 혹은 my.cnf에서 설정한 값으로 다시 변화하는 것을 확인할 수 있었다.

결과적으로 해당 파라미터 값을 설정하려거든 설정파일에 값을 입력해야만 확실하고 영구적인 값의 변경이 이루어진다는 것을 알 수 있었다.





* 번외(interactive_timeout)

show processlist라는 명령어를 실행하면 현재 수행중인 프로세스의 리스트를 표현한다.

이 중 command의 내용이 sleep으로 해당되는 프로세스들이 존재하는데, 이는 활동중인 커넥션이 쿼리를 수행하고 대기중에 있는 것이다.

위의 파라미터 리스트에서 interactive_timeout이라는 값이 활동중인 커넥션이 닫힐때까지 서버가 대기하는 시간의 값이라고 명시한 적이 있다.

그렇다면 과연 이 값을 변경하면 현재 커넥션이 끊길 것인지 테스트 해보도록 하자.

interactive_timeout 값을 20으로 수정하였다.


다른 user로 접속하여 보자.

다음 캡쳐화면은 다른 user로 접속하여 20초간 대기 후 일련의 작업 요청시 어떠한 결과가 나타나는지 보여주고 있다.

알아서 커넥션이 끊겼기 때문에 커넥션 재접속을 수행하고 새로운 커넥션 아이디를 받는다.

이번엔 show processlist;를 지속적으로 수행하여 20초가 되면 정말로 커넥션이 끊기는지 확인해보자.

time 컬럼에 대기시간이 표시되고, kjk3071이라는 user가 20초 이후에는 processlist에서 사라진다는 것을 알수있다.

그리고 이 화면에는 나타나지 않았지만 커넥션 재접속 후에는 해당 커넥션 아이디값이 다시 나타날 것이다.