MySQL에서 Lock모니터링 하는 방법
0. Autocommit 관련 파라 미터 확인
mysql> show global variables like 'autocommit' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select @@autocommit ;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
1. Lock 관련 파라 미터 확인
mysql> show global variables like 'lock%' ;
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
+-------------------+----------+
2 rows in set (0.00 sec)
mysql> select @@lock_wait_timeout ;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
| 31536000 |
+---------------------+
1 row in set (0.00 sec)
2. Lock 관련 View
information_schema.innodb_lock_waits ; // lock waiting 중인 트랜잭션 정보를 보여줌 (트랜잭션 ID만 있음)
information_schema.innodb_trx ; // 전체 트랜잭션 프로세스 정보를 보여 줌 (트랜잭션 ID, Thread ID, Query가 있음)
information_schema.processlist ; // 전체 프로세스 리스트를 보여 줌
Lock이 있는 상태에서 각각의 뷰들을 조회한 결과
mysql> SELECT * FROM information_schema.innodb_lock_waits ;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 655000 | 655000:49:3:3 | 654968 | 654968:49:3:3 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM information_schema.innodb_trx ;
많아서 뒷 쪽 칼럼은 생략
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------+---------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------+---------------------+
| 655034 | RUNNING | 2016-06-23 18:10:11 | NULL | NULL | 54 | 44 | COMMIT | NULL |
| 655000 | LOCK WAIT | 2016-06-23 18:10:11 | 655000:49:3:3 | 2016-06-23 18:10:11 | 2 | 45 | UPDATE warehouse SET w_ytd = w_ytd + p_h_amount WHERE w_id = p_w_id | starting index read |
| 654968 | RUNNING | 2016-06-23 18:10:10 | NULL | NULL | 2 | 46 | UPDATE warehouse SET w_ytd = w_ytd + p_h_amount WHERE w_id = p_w_id | starting index read |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.processlist ;
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------+
| 37 | root | 192.168.122.7:35450 | information_schema | Sleep | 2 | | NULL |
| 40 | root | 192.168.122.7:35460 | information_schema | Sleep | 7 | | NULL |
| 39 | root | 192.168.122.7:35455 | information_schema | Sleep | 2 | | NULL |
| 35 | root | 192.168.122.7:35442 | information_schema | Sleep | 2 | | NULL |
| 11 | repl | Smysql:41648 | NULL | Binlog Dump | 16833 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 45 | root | 192.168.10.1:50686 | tpcc | Query | 0 | updating | UPDATE warehouse SET w_ytd = w_ytd + p_h_amountWHERE w_id = p_w_id |
| 60 | root | 192.168.10.1:52936 | tpch | Query | 73 | Sending data | select o_year, sum(case when NATION = 'PERU' then volume else 0 end) / sum(volume) as mkt_share |
| 22 | root | 192.168.120.31:52617 | NULL | Sleep | 520 | | NULL |
| 21 | root | 192.168.120.31:52616 | NULL | Sleep | 520 | | NULL |
| 36 | root | 192.168.122.7:35446 | information_schema | Sleep | 7 | | NULL |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | updating | UPDATE warehouse SET w_ytd = w_ytd + p_h_amount WHERE w_id = p_w_id |
| 61 | root | 192.168.10.1:52937 | tpch | Query | 97 | Creating sort index | select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from CUSTOMER,|
| 34 | root | 192.168.122.7:35440 | information_schema | Sleep | 2 | | NULL |
| 44 | root | 192.168.10.1:50685 | tpcc | Sleep | 0 | | NULL |
| 38 | root | 192.168.122.7:35453 | information_schema | Sleep | 7 | | NULL |
| 33 | root | 192.168.122.7:35439 | information_schema | Sleep | 2 | | NULL |
| 59 | root | localhost | mysql | Query | 0 | executing | SELECT * FROM information_schema.processlist |
| 62 | root | 192.168.10.1:52938 | tpch | Query | 14 | Sending data | select c_count, count(*) as custdist from ( select c_custkey as c_custkey, count(o_orderkey) as|
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
3. 위의 3개의 뷰를 이용하여 Lock 모니터링 쿼리 작성
SELECT wait.id ,
pl.host ,
pl.user ,
pl.db ,
wait.state ,
wait.wait_started ,
wait.wait_query ,
hold.id holder_id ,
hold.trx_started holder_trx_started ,
hold.hold_query ,
CONCAT('kill ',FORMAT (hold.id,0)) kill_command
FROM (SELECT trx.trx_mysql_thread_id as id , lw.requesting_trx_id, lw.blocking_trx_id, trx.trx_state as state, lw.requested_lock_id , trx.trx_wait_started as wait_started, trx.trx_query as wait_query FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_trx trx ON lw.requesting_trx_id = trx.trx_id ) wait ,
(SELECT trx.trx_mysql_thread_id as id , trx.trx_id, trx.trx_state as state, lw.blocking_lock_id , trx.trx_started as trx_started , trx.trx_query as hold_query FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_trx trx ON lw.blocking_trx_id = trx.trx_id ) hold ,
(SELECT id , user , host , db FROM information_schema.processlist process) pl
WHERE wait.id = pl.id
AND wait.blocking_trx_id = hold.trx_id ;
4. TEST
|
세션 1 |
세션 2 |
|
mysql> create table test (id int) ; Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values (1) ; Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (2) ; Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (3) ; Query OK, 1 row affected (0.01 sec)
mysql> insert into test values (4) ; Query OK, 1 row affected (0.01 sec)
mysql> set autocommit=0 ; // Autocommit OFF Query OK, 0 rows affected (0.00 sec)
mysql> update test set id = 10 where id = 4 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
|
|
mysql> set autocommit=0 ; // AutoCommit OFF Query OK, 0 rows affected (0.00 sec)
mysql> update test set id = 20 ; |
모니터링 쿼리 수행
mysql> SELECT wait.id ,
pl.user ,
pl.host ,
pl.db ,
wait.state ,
wait.wait_started ,
wait.wait_query ,
hold.id holder_id ,
hold.trx_started holder_trx_started ,
hold.hold_query ,
CONCAT('kill ',FORMAT (hold.id,0)) kill_command
FROM (SELECT trx.trx_mysql_thread_id as id ,
lw.requesting_trx_id,
lw.blocking_trx_id,
trx.trx_state as state,
lw.requested_lock_id ,
trx.trx_wait_started as wait_started,
trx.trx_query as wait_query
FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_trx trx
ON lw.requesting_trx_id = trx.trx_id ) wait ,
(SELECT trx.trx_mysql_thread_id as id ,
trx.trx_id,
trx.trx_state as state,
lw.blocking_lock_id ,
trx.trx_started as trx_started ,
trx.trx_query as hold_query
FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_trx trx
ON lw.blocking_trx_id = trx.trx_id ) hold ,
(SELECT id , user , host , db
FROM information_schema.processlist process) pl
WHERE wait.id = pl.id
AND wait.blocking_trx_id = hold.trx_id ;
+----+------+-----------+------+-----------+---------------------+-------------------------+-----------+---------------------+------------+--------------+
| id | user | host | db | state | wait_started | wait_query | holder_id | holder_trx_started | hold_query | kill_command |
+----+------+-----------+------+-----------+---------------------+-------------------------+-----------+---------------------+------------+--------------+
| 71 | root | localhost | tpcc | LOCK WAIT | 2016-06-24 15:17:43 | update test set id = 20 | 70 | 2016-06-24 14:56:47 | NULL | kill 70 |
+----+------+-----------+------+-----------+---------------------+-------------------------+-----------+---------------------+------------+--------------+
1 row in set (0.00 sec)