-
-
Save bluerabbit/051a3bceb8162a552349 to your computer and use it in GitHub Desktop.
MySQL ロック競合の解析手順
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 実行中のプロセスを調べる | |
mysql> show processlist; | |
-- 実行中のプロセスのSQL詳細を調べる | |
mysql> show full processlist \G; | |
-- processlistを元にプロセスをkill | |
mysql> kill [プロセスId] | |
-- 実行中のプロセスのSQL詳細を調べる | |
mysql> SHOW ENGINE INNODB STATUS \G; | |
-- see | |
http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-general-monitor.html | |
-- In_use がTableロック | |
show open tables from [データベース名]; | |
-- http://d.hatena.ne.jp/sh2/20090618 | |
-- MySQLのディクショナリテーブルを調べる | |
mysql> use information_schema | |
mysql> show tables like 'INNO%'; | |
+--------------------------------------+ | |
| Tables_in_information_schema (INNO%) | | |
+--------------------------------------+ | |
| INNODB_CMP | | |
| INNODB_CMP_RESET | | |
| INNODB_CMPMEM | | |
| INNODB_CMPMEM_RESET | | |
| INNODB_LOCK_WAITS | | |
| INNODB_LOCKS | | |
| INNODB_TRX | | |
+--------------------------------------+ | |
SELECT * FROM INNODB_LOCK_WAITS; | |
mysql> desc INNODB_LOCK_WAITS; | |
+-------------------+-------------+------+-----+---------+-------+ | |
| Field | Type | Null | Key | Default | Extra | | |
+-------------------+-------------+------+-----+---------+-------+ | |
| requesting_trx_id | varchar(18) | NO | | | | | |
| requested_lock_id | varchar(81) | NO | | | | | |
| blocking_trx_id | varchar(18) | NO | | | | | |
| blocking_lock_id | varchar(81) | NO | | | | | |
+-------------------+-------------+------+-----+---------+-------+ | |
* blocking_trx_id先にロックを獲得していたtx | |
* requesting_trx_idはロック取得待ちのtx | |
-- ロックしているSQLを取得する | |
SELECT INNODB_TRX.trx_id, INNODB_TRX.trx_query FROM INNODB_LOCK_WAITS INNER JOIN INNODB_TRX ON INNODB_LOCK_WAITS.requesting_trx_id = INNODB_TRX.trx_id; | |
-- ロック取得待ちのtxのプロセスIDを取得する | |
SELECT INNODB_TRX.trx_id FROM INNODB_LOCK_WAITS INNER JOIN INNODB_TRX ON INNODB_LOCK_WAITS.blocking_trx_id = INNODB_TRX.trx_id; | |
SELECT * FROM INNODB_LOCKS; | |
mysql> desc INNODB_LOCKS; | |
+-------------+---------------------+------+-----+---------+-------+ | |
| Field | Type | Null | Key | Default | Extra | | |
+-------------+---------------------+------+-----+---------+-------+ | |
| lock_id | varchar(81) | NO | | | | | |
| lock_trx_id | varchar(18) | NO | | | | | |
| lock_mode | varchar(32) | NO | | | | | |
| lock_type | varchar(32) | NO | | | | | |
| lock_table | varchar(1024) | NO | | | | | |
| lock_index | varchar(1024) | YES | | NULL | | | |
| lock_space | bigint(21) unsigned | YES | | NULL | | | |
| lock_page | bigint(21) unsigned | YES | | NULL | | | |
| lock_rec | bigint(21) unsigned | YES | | NULL | | | |
| lock_data | varchar(8192) | YES | | NULL | | | |
+-------------+---------------------+------+-----+---------+-------+ | |
SELECT * FROM INNODB_TRX; | |
mysql> desc INNODB_TRX; | |
+----------------------------+---------------------+------+-----+---------------------+-------+ | |
| Field | Type | Null | Key | Default | Extra | | |
+----------------------------+---------------------+------+-----+---------------------+-------+ | |
| trx_id | varchar(18) | NO | | | | | |
| trx_state | varchar(13) | NO | | | | | |
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | | | |
| trx_requested_lock_id | varchar(81) | YES | | NULL | | | |
| trx_wait_started | datetime | YES | | NULL | | | |
| trx_weight | bigint(21) unsigned | NO | | 0 | | | |
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | | |
| trx_query | varchar(1024) | YES | | NULL | | | |
| trx_operation_state | varchar(64) | YES | | NULL | | | |
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | | | |
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | | | |
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | | | |
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | | | |
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | | | |
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | | | |
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | | | |
| trx_isolation_level | varchar(16) | NO | | | | | |
| trx_unique_checks | int(1) | NO | | 0 | | | |
| trx_foreign_key_checks | int(1) | NO | | 0 | | | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | | | |
| trx_adaptive_hash_latched | int(1) | NO | | 0 | | | |
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | | | |
+----------------------------+---------------------+------+-----+---------------------+-------+ | |
-- どのSQLで何行ロックされているかとlock_typeを調べる | |
SELECT | |
INNODB_TRX.trx_mysql_thread_id, | |
INNODB_TRX.trx_query, | |
INNODB_TRX.trx_rows_locked, | |
INNODB_TRX.trx_tables_in_use, | |
INNODB_TRX.trx_tables_locked, | |
INNODB_LOCKS.lock_mode, | |
INNODB_LOCKS.lock_type, | |
INNODB_LOCKS.lock_table, | |
INNODB_LOCKS.lock_index | |
FROM | |
INNODB_TRX INNER JOIN INNODB_LOCKS | |
ON INNODB_TRX.trx_id = INNODB_LOCKS.lock_trx_id | |
WHERE trx_state = 'LOCK WAIT'; | |
-- タイムアウト時間を調べる | |
mysql> show global variables like 'innodb_lock_wait_timeout'; | |
+--------------------------+-------+ | |
| Variable_name | Value | | |
+--------------------------+-------+ | |
| innodb_lock_wait_timeout | 50 | | |
+--------------------------+-------+ | |
-- INDEXの有無を調べる | |
SHOW INDEX FROM [テーブル名]; |
# ruby watch.rb
while true
sql = <<-SQL
SELECT
requesting_ps.user AS requesting_user,
requesting_ps.host AS requesting_host,
requesting_ps.db AS requesting_db,
requesting_lock.lock_mode AS requesting_lock_mode,
requesting_lock.lock_type AS requesting_lock_type,
requesting_lock.lock_table AS requesting_lock_table,
requesting_lock.lock_index AS requesting_lock_index,
requesting_trx.trx_id AS requesting_trx_id,
requesting_trx.trx_query AS requesting_trx_query,
requesting_trx.trx_state AS requesting_trx_state,
requesting_trx.trx_started AS requesting_trx_started,
requesting_trx.trx_wait_started AS requesting_trx_wait_started,
requesting_trx.trx_lock_structs AS requesting_trx_lock_structs,
requesting_trx.trx_rows_locked AS requesting_trx_rows_locked,
requesting_trx.trx_isolation_level AS requesting_trx_isolation_level,
blocking_ps.user AS blocking_user,
blocking_ps.host AS blocking_host,
blocking_ps.db AS blocking_db,
blocking_lock.lock_mode AS blocking_lock_mode,
blocking_lock.lock_type AS blocking_lock_type,
blocking_lock.lock_table AS blocking_lock_table,
blocking_lock.lock_index AS blocking_lock_index,
blocking_trx.trx_id AS blocking_trx_id,
blocking_trx.trx_state AS blocking_trx_state,
blocking_trx.trx_started AS blocking_trx_started,
blocking_trx.trx_lock_structs AS blocking_trx_lock_structs,
blocking_trx.trx_rows_locked AS blocking_trx_rows_locked,
blocking_trx.trx_isolation_level AS blocking_trx_isolation_level
FROM
INNODB_LOCK_WAITS
INNER JOIN INNODB_TRX requesting_trx
ON INNODB_LOCK_WAITS.requesting_trx_id = requesting_trx.trx_id
INNER JOIN PROCESSLIST requesting_ps
ON requesting_ps.id = requesting_trx.trx_mysql_thread_id
INNER JOIN INNODB_LOCKS requesting_lock
ON requesting_lock.lock_id = INNODB_LOCK_WAITS.requested_lock_id
INNER JOIN INNODB_TRX blocking_trx
ON INNODB_LOCK_WAITS.blocking_trx_id = blocking_trx.trx_id
INNER JOIN PROCESSLIST blocking_ps
ON blocking_ps.id = blocking_trx.trx_mysql_thread_id
INNER JOIN INNODB_LOCKS blocking_lock
ON blocking_lock.lock_id = INNODB_LOCK_WAITS.blocking_lock_id
SQL
# https://stackoverflow.com/questions/15640287/change-output-format-for-mysql-command-line-results-to-csv
result = `mysql -uroot -pyour_pass -P 3306 -h 127.0.0.1 -D information_schema --skip-column-names -e '#{sql}' | awk -F'\\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\\\\t/,"\\t",$i); gsub(/\\\\n/,"\\n",$i); gsub(/\\\\\\\\/,"\\\\",$i); gsub(/"/,"\\"\\"",$i); printf sep"\\""$i"\\""; sep=","; if(i==NF){printf"\\n"}}}'`
puts result
File.write("watch.log", result)
sleep 1
end
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
MySQL 8からは下記