( Mysql ) Dead Lock 걸리는 원인중 하나

My sql 이 자꾸 죽는다.

아래와 같이 현재 진행중인 query 들을 조사해보자

mysql> show processlist;

+---------+------+----------------------+-------------+---------+------+-------+------------------+

| Id      | User | Host                 | db          | Command | Time | State | Info             |

+---------+------+----------------------+-------------+---------+------+-------+------------------+

| 1378947 | root | localhost:53384      | mymy         | Sleep   |    0 |       | NULL             |

| 5297475 | root | localhost            | mydatabases | Sleep   |  203 |       | NULL             |

| 5331984 | root | localhost            | mydatabases | Sleep   | 1965 |       | NULL             |

| 5332009 | root | localhost            | mydatabases | Sleep   | 1963 |       | NULL             |

| 5332822 | root | localhost            | mydatabases | Sleep   | 1863 |       | NULL             |

| 5332872 | root | localhost            | mydatabases | Sleep   | 1855 |       | NULL             |

| 5333411 | root | xxx.xxx.xxx.xxx:49104 | mydatabases | Sleep   | 1759 |       | NULL             |

| 5333653 | root | localhost            | mydatabases | Sleep   | 1739 |       | NULL             |

| 5333717 | root | localhost            | mydatabases | Sleep   | 1731 |       | NULL             |

| 5333810 | root | localhost            | mydatabases | Sleep   | 1720 |       | NULL             |

| 5334323 | root | localhost            | mydatabases | Sleep   | 1660 |       | NULL             |

| 5334343 | root | localhost            | mydatabases | Sleep   | 1658 |       | NULL             |

| 5334664 | root | localhost            | mydatabases | Sleep   | 1626 |       | NULL             |

| 5334974 | root | localhost            | mydatabases | Sleep   | 1598 |       | NULL             |

| 5335028 | root | localhost            | mydatabases | Sleep   | 1592 |       | NULL             |

| 5335777 | root | localhost            | mydatabases | Sleep   | 1510 |       | NULL             |

| 5336011 | root | localhost            | mydatabases | Sleep   | 1476 |       | NULL             |

| 5336320 | root | xxx.xxx.xxx.xxx:51499 | mydatabases | Sleep   | 1426 |       | NULL             |

| 5336339 | root | xxx.xxx.xxx.xxx:51500 | mydatabases | Sleep   |  157 |       | NULL             |

| 5336466 | root | localhost            | mydatabases | Sleep   | 1408 |       | NULL             |

| 5336764 | root | xxx.xxx.xxx.xxx:51573 | mydatabases | Sleep   | 1363 |       | NULL             |

| 5336813 | root | xxx.xxx.xxx.xxx:51610 | mydatabases | Sleep   |   90 |       | NULL             |

| 5338546 | root | localhost            | mydatabases | Sleep   | 1182 |       | NULL             |

| 5338659 | root | localhost            | mydatabases | Sleep   | 1170 |       | NULL             |

| 5338678 | root | localhost            | mydatabases | Sleep   | 1168 |       | NULL             |

| 5339760 | root | localhost            | mydatabases | Sleep   | 1052 |       | NULL             |

| 5340281 | root | localhost            | mydatabases | Sleep   | 1008 |       | NULL             |

| 5340326 | root | localhost            | mydatabases | Sleep   | 1005 |       | NULL             |

| 5340998 | root | localhost            | mydatabases | Sleep   |  947 |       | NULL             |

| 5341425 | root | localhost            | mydatabases | Sleep   |  917 |       | NULL             |

| 5341632 | root | localhost            | mydatabases | Sleep   |  903 |       | NULL             |

| 5341920 | root | localhost            | mydatabases | Sleep   |  885 |       | NULL             |

| 5342212 | root | localhost            | mydatabases | Sleep   |  868 |       | NULL             |

| 5342708 | root | localhost            | mydatabases | Sleep   |  838 |       | NULL             |

| 5344717 | root | xxx.xxx.xxx.xxx:53656 | mydatabases | Sleep   |  667 |       | NULL             |

| 5344895 | root | localhost            | mydatabases | Sleep   |  674 |       | NULL             |

| 5345437 | root | localhost            | mydatabases | Sleep   |  625 |       | NULL             |

| 5346453 | root | localhost            | NULL        | Query   |    0 | init  | show processlist |

| 5346914 | root | xxx.xxx.xxx.xxx:54106 | mydatabases | Sleep   |  134 |       | NULL             |

| 5348053 | root | xxx.xxx.xxx.xxx:54211 | mydatabases | Sleep   |   60 |       | NULL             |

| 5348510 | root | localhost            | mydatabases | Sleep   |  394 |       | NULL             |

| 5348864 | root | localhost            | mydatabases | Sleep   |  370 |       | NULL             |

| 5350444 | root | xxx.xxx.xxx.xxx:54579 | mydatabases | Sleep   |  265 |       | NULL             |

| 5350470 | root | localhost            | mydatabases | Sleep   |  268 |       | NULL             |

| 5350516 | root | xxx.xxx.xxx.xxx:54580 | mydatabases | Sleep   |   75 |       | NULL             |

| 5350706 | root | xxx.xxx.xxx.xxx:56174 | mydatabases | Sleep   |  236 |       | NULL             |

| 5351451 | root | xxx.xxx.xxx.xxx:54628 | mydatabases | Sleep   |  207 |       | NULL             |

| 5351471 | root | localhost            | mydatabases | Sleep   |  210 |       | NULL             |

| 5351528 | root | xxx.xxx.xxx.xxx:54629 | mydatabases | Sleep   |   18 |       | NULL             |

| 5351573 | root | xxx.xxx.xxx.xxx:56492 | mydatabases | Sleep   |  200 |       | NULL             |

| 5351657 | root | xxx.xxx.xxx.xxx:54659 | mydatabases | Sleep   |  188 |       | NULL             |

| 5351658 | root | xxx.xxx.xxx.xxx:56547 | mydatabases | Sleep   |    5 |       | NULL             |

| 5351676 | root | localhost            | mydatabases | Sleep   |  199 |       | NULL             |

| 5351737 | root | xxx.xxx.xxx.xxx:56613 | mydatabases | Sleep   |  187 |       | NULL             |

| 5351899 | root | xxx.xxx.xxx.xxx:54668 | mydatabases | Sleep   |  157 |       | NULL             |

| 5351903 | root | xxx.xxx.xxx.xxx:56718 | mydatabases | Sleep   |  178 |       | NULL             |

| 5352205 | root | xxx.xxx.xxx.xxx:56895 | mydatabases | Sleep   |  170 |       | NULL             |

| 5352932 | root | localhost            | mydatabases | Sleep   |  130 |       | NULL             |

| 5353937 | root | localhost            | mydatabases | Sleep   |   72 |       | NULL             |

| 5354270 | root | xxx.xxx.xxx.xxx:57319 | mydatabases | Sleep   |   51 |       | NULL             |

| 5354329 | root | xxx.xxx.xxx.xxx:57320 | mydatabases | Sleep   |   39 |       | NULL             |

| 5354408 | root | xxx.xxx.xxx.xxx:55020 | mydatabases | Sleep   |   47 |       | NULL             |

| 5354410 | root | xxx.xxx.xxx.xxx:55025 | mydatabases | Sleep   |   42 |       | NULL             |

| 5354499 | root | xxx.xxx.xxx.xxx:55059 | mydatabases | Sleep   |   34 |       | NULL             |

| 5354747 | root | xxx.xxx.xxx.xxx:57370 | mydatabases | Sleep   |   26 |       | NULL             |

| 5354759 | root | localhost            | mydatabases | Sleep   |   28 |       | NULL             |

| 5354808 | root | xxx.xxx.xxx.xxx:57371 | mydatabases | Sleep   |   14 |       | NULL             |

| 5354909 | root | xxx.xxx.xxx.xxx:55261 | mydatabases | Sleep   |    4 |       | NULL             |

| 5355012 | root | xxx.xxx.xxx.xxx:55304 | mydatabases | Sleep   |    8 |       | NULL             |

| 5355058 | root | xxx.xxx.xxx.xxx:55322 | mydatabases | Sleep   |    1 |       | NULL             |

| 5355179 | root | xxx.xxx.xxx.xxx:55355 | mydatabases | Sleep   |    8 |       | NULL             |

| 5355238 | root | xxx.xxx.xxx.xxx:57422 | mydatabases | Sleep   |    0 |       | NULL             |

| 5355263 | root | xxx.xxx.xxx.xxx:55385 | mydatabases | Sleep   |    4 |       | NULL             |

| 5355304 | root | xxx.xxx.xxx.xxx:57425 | mydatabases | Sleep   |    0 |       | NULL             |

| 5355331 | root | xxx.xxx.xxx.xxx:55422 | mydatabases | Sleep   |    1 |       | NULL             |

+---------+------+----------------------+-------------+---------+------+-------+------------------+

75 rows in set (0.00 sec)

보다시피 Sleep 상태에 걸려있는 쿼리들이 꽤 많다.

원인은.. mysql 을 이용하다가... close 를 하지 않거나.. 정상적인 종료를 하지 않았을 경우

따라서, 가장 근본적인 해결 방법은 사용 후 언제나 close 를 제대로 해 주는것이다.

그러나, 항상 예외상황은 있다. 

따라서, mysql side에서 wait_timeout 을 조절하여 sleep 상태에 빠진 process들을 강제로 죽일 수 있도록 하자.

mysql> show variables like '%timeout%';

+-----------------------------+----------+

| Variable_name               | Value    |

+-----------------------------+----------+

| connect_timeout             | 10       |

| delayed_insert_timeout      | 300      |

| innodb_flush_log_at_timeout | 1        |

| innodb_lock_wait_timeout    | 50       |

| innodb_rollback_on_timeout  | OFF      |

| interactive_timeout         | 28800    |

| lock_wait_timeout           | 31536000 |

| net_read_timeout            | 30       |

| net_write_timeout           | 60       |

| rpl_stop_slave_timeout      | 31536000 |

| slave_net_timeout           | 3600     |

| wait_timeout                | 28800    |

+-----------------------------+----------+

위와 같이 wait_timeout 변수를 조정하여 설정이 가능함.