MySQL의 timeout 설정

sleep 세션

client-mysql 서버와 연결 후 다음 query 수행까지 대기중인 상태의 세션
sleep 세션이 너무 많고 정리가 안되는 경우 connection full 로 인해 신규 세션 접속이 불가능해지고
session 별 할당 되는 메모리로 인해 memory 부족 현상 발생할 수 있음

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을 롤백하는 과정에서 발생
  • net_read_timeout : Client => Net => MySQL Server 구성에서 mysql 이 net으로부터 read하는 상황에서 세션을 끊을때까지 기다리는 시간,
    IDC => RDS 이관할때 timeout 난다면 설정 고려해볼것
  • net_write_timeout : MySQL Server => Net => Client 구성에서 mysql 이 net으로 data write 하는 상황에서 세션을 끊을때 까지 기다리는 시간
  • slave_net_timeout : 마스터/슬레이브로 서버가 클라이언트로부터 데이터를 읽어들이는 것을 중단하기까지 대기하는 시간
  • table_lock_wait_timeout : 테이블 락을 중단하기까지 대기하는 시간
  • wait_timeout : 활동하지 않는 커넥션을 끊을때까지 서버가 대기하는 시간 (php,jdbc 등을 통한 connection)
  • interactive_timeout : 활동중인 커넥션이 닫히기 전까지 서버가 대기하는 시간 (mysql command line)

=> DB의 Timeout 설정으로 sleep 세션들을 정리할 수 있으며 이번 글에서 살펴볼 설정은 wait_timeout 과 interactive_timeout

sleep 세션 자동으로 정리하는 방법

  • sleep session 확인
mysql> select count(*) from information_schema.processlist where command='Sleep';
+----------+
| count(*) |
+----------+
|      326 |
+----------+
1 row in set (0.00 sec)

=> sleep 상태의 세션이 326개

  • timeout 확인
mysql> show variables like 'interactive%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

=>28800 초 이후 interactive 세션들을 정리하게 설정되어있음

mysql> show variables like 'wait_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| wait_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

=>28800 초 이후 non-interactive 세션들을 정리하게 설정되어있음

  • timeout 설정
mysql> set global interactive_timeout=30;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'interactive%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 30    |
+---------------------+-------+
1 row in set (0.00 sec)
 
mysql> set global wait_timeout=30;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 30    |
+---------------+-------+
1 row in set (0.00 sec)

=> sleep 상태가 30초 이상 된 세션들 정리하도록 설정

  • 설정 적용 확인
mysql> show processlist;
+----+-------------+--------------------+------+---------+------+-----------------------------------------------------------------------------+-----------------+----------+
| Id | User        | Host               | db   | Command | Time  |State                                                                       | Info             |Progress |
+----+-------------+--------------------+------+---------+------+-----------------------------------------------------------------------------+-----------------+----------+
|  3 | system user |                    | NULL | Connect | 99200 | Connecting tomaster                                                        | NULL             |    0.000 |
|  4 | system user |                    | NULL | Connect | 99200 | Slave has read all relay log;waiting for the slave I/O thread to update it | NULL             |    0.000 |
| 25 | root        | 165.243.5.20:29883 | NULL | Sleep   |    29|                                                                             | NULL            |    0.000 |
| 26 | root        | localhost          | NULL | Query   |     0 |init                                                                        | show processlist|    0.000 |
+----+-------------+--------------------+------+---------+------+-----------------------------------------------------------------------------+-----------------+----------+
4 rows in set (0.00 sec)
 
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+-----------------+----------+
| Id | User        | Host      | db   | Command | Time  |State                                                                       | Info             |Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+-----------------+----------+
|  3 | system user |           | NULL | Connect | 99201 | Connecting tomaster                                                        | NULL             |    0.000 |
|  4 | system user |           | NULL | Connect | 99201 | Slave has read all relay log; waitingfor the slave I/O thread to update it | NULL             |    0.000 |
| 26 | root        | localhost | NULL | Query   |     0 |init                                                                        | show processlist|    0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+-----------------+----------+
3 rows in set (0.00 sec)

=> Sleep 상태의 세션이 30초 지난 후 자동으로 정리됨 ★ 설정 변경 전 존재하는 sleep 세션들에 대해서는 적용안됨, 신규 세션들만 적용됨