sleep session 정리하기
-. 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 : 서버가 클라이언트로부터 데이터를 읽어들이는 것을 중단하기까지 대기하는 시간
net_write_timeout : 서버가 클라이언트에 데이터를 쓰는 것을 중단하기까지 대기하는 시간
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 세션 자동으로 정리하는 방법
mysql> select count(*) from information_schema.processlist where command='Sleep';
+----------+
| count(*) |
+----------+
| 326 |
+----------+
1 row in set (0.00 sec)
=> sleep 상태의 세션이 326개
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 세션들을 정리하게 설정되어있음
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 to master | 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 to master | NULL | 0.000 |
| 4 | system user | | NULL | Connect | 99201 | Slave has read all relay log; waiting for 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 세션들에 대해서는 적용안됨, 신규 세션들만 적용됨
