磯野ー、MySQLのロック競合を表示しようぜー

0deae06ab5d86b39feeec2e23a30b88a?s=47 yoku0825
February 10, 2020

 磯野ー、MySQLのロック競合を表示しようぜー

2020/02/10 PHPerKaigi 2020
https://phperkaigi.jp/2020/

0deae06ab5d86b39feeec2e23a30b88a?s=128

yoku0825

February 10, 2020
Tweet

Transcript

  1. 6.

    サーバーコアで実装されているロック サーバーコアで実装されているロックの競合はサーバーコアの機能で観測可能 たとえば FLUSH TABLES WITH READ LOCK と競合するクエリーはは SHOW

    PROCESSLIST で Waiting for global read lock として観測可能 ‐ 逆に、サーバーコアで実装されていないロックの競合はサーバーコアの機能からは 観測ができない 典型的にはInnoDBの行ロック競合は SHOW PROCESSLIST では Sending data としか表示されな い ‐ 5/37
  2. 7.

    サーバーコアで実装されているロック | stage/sql/System lock | | stage/mysys/Waiting for table level

    lock | | stage/sql/Waiting for global read lock | | stage/sql/Waiting for backup lock | | stage/sql/Waiting for tablespace metadata lock | | stage/sql/Waiting for schema metadata lock | | stage/sql/Waiting for table metadata lock | | stage/sql/Waiting for stored function metadata lock | | stage/sql/Waiting for stored procedure metadata lock | | stage/sql/Waiting for trigger metadata lock | | stage/sql/Waiting for event metadata lock | | stage/sql/Waiting for commit lock | | stage/sql/User lock | | stage/sql/Waiting for locking service lock | | stage/sql/Waiting for spatial reference system lock | | stage/sql/Waiting for acl cache lock | | stage/sql/Waiting for column statistics lock | | stage/sql/Waiting for resource groups metadata lock | | stage/sql/Waiting for foreign key metadata lock | | stage/sql/Waiting for check constraint metadata lock 6/37
  3. 8.

    ストレージエンジンで実装されているロック ストレージエンジンで実装されているロックの競合はストレージエンジンの機能で のみ観測可能 InnoDBの information_schema.INNODB_LOCK_WAITS (8.0でなくなりましたが) ‐ innodb_status_output_locks をONにした上で SHOW

    ENGINE INNODB STATUS の TRANSACTIONS セ クション ‐ ストレージエンジンでの実装がサーバーコアの実装を呼び出している場合もある MyISAMの読み書きロックはサーバーコアのテーブルロックを呼び出している ‐ 7/37
  4. 18.

    脳トレ mysql80 40> SHOW CREATE TABLE card\G *************************** 1. row

    *************************** Table: card Create Table: CREATE TABLE `card` ( `suite` varchar(8) NOT NULL, `number` tinyint unsigned NOT NULL, PRIMARY KEY (`suite`,`number`), KEY `suite` (`suite`), KEY `number` (`number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql80 40> SELECT * FROM card WHERE number = 10 FOR UPDATE; +---------+--------+ | suite | number | +---------+--------+ | club | 10 | | diamond | 10 | | heart | 10 | | spade | 10 | +---------+--------+ 4 rows in set (0.00 sec) mysql80 41> SELECT * FROM card WHERE suite = 'spade' AND number = 10 FOR UPDATE; ERROR: 1205: Lock wait timeout exceeded; try restarting transaction 17/37
  5. 19.

    方法1 SET GLOBAL innodb_status_output_locks = ON してから SHOW ENGINE INNODB

    STATUS mysql80 94> SET GLOBAL innodb_status_output_locks = ON; Query OK, 0 rows affected (0.00 sec) mysql80 94> SHOW ENGINE INNODB STATUS\G .. 18/37
  6. 20.

    SHOW ENGINE INNODB STATUS ------------ TRANSACTIONS ------------ .. ---TRANSACTION 192750,

    ACTIVE 283 sec 4 lock struct(s), heap size 1136, 9 row lock(s) MySQL thread id 40, OS thread handle 140237461325568, query id 839 localhost root TABLE LOCK table `d1`.`card` trx id 192750 lock mode IX RECORD LOCKS space id 30 page no 6 n bits 120 index number of table `d1`.`card` trx id 192750 lock_mode X Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 5; hex 7370616465; asc spade;; Record lock, heap no 24 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 4; hex 636c7562; asc club;; Record lock, heap no 37 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 5; hex 6865617274; asc heart;; Record lock, heap no 50 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 7; hex 6469616d6f6e64; asc diamond;; RECORD LOCKS space id 30 page no 4 n bits 120 index PRIMARY of table `d1`.`card` trx id 192750 lock_mode X locks rec but not gap Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 5; hex 7370616465; asc spade;; 1: len 1; hex 0a; asc ;; 2: len 6; hex 00000002f08c; asc ;; 3: len 7; hex 82000000d00110; asc ;; .. RECORD LOCKS space id 30 page no 6 n bits 120 index number of table `d1`.`card` trx id 192750 lock_mode X locks gap before rec Record lock, heap no 25 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0b; asc ;; 1: len 4; hex 636c7562; asc club;; 19/37
  7. 21.

    SHOW ENGINE INNODB STATUS(pros) オプション1つだけでOFF/ON切り替え可能 SET GLOBAL innodb_status_output_locks = ON

    ‐ 実は古い(5.1 Plugin InnoDB)バージョンからずっと使える SET GLOBAL は使わないんだけど5.5とそれ以前でもやり方調べればできる ‐ 20/37
  8. 22.

    SHOW ENGINE INNODB STATUS(cons) 人間が読むべきものではない気がする Record lock, heap no 11

    PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 5; hex 7370616465; asc spade;; RECORD LOCKS space id 30 page no 4 n bits 120 index PRIMARY of table `d1`.`card` trx id 1 92750 lock_mode X locks rec but not gap Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 5; hex 7370616465; asc spade;; 1: len 1; hex 0a; asc ;; 2: len 6; hex 00000002f08c; asc ;; 3: len 7; hex 82000000d00110; asc ;; 21/37
  9. 23.

    方法2 information_schemaの INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS あたりを 駆使する MySQL InnoDBにおけるロック競合の解析手順 -

    SH2の日記 ‐ 5.7とそれ以降なら sys.innodb_lock_waits ビューがほぼ同じ中身を提供してくれる ‐ 8.0では使えない ⇒ 方法3 へ ‐ INNODB_LOCKS は「競合したロック」しか出力してくれないので、競合していない ロックがどれだけあろうとわからない 22/37
  10. 24.

    information_schemaを駆使する mysql57 55> SELECT * FROM sys.innodb_lock_waits\G *************************** 1. row

    *************************** wait_started: 2020-02-07 18:48:57 wait_age: 00:00:10 wait_age_secs: 10 locked_table: `d1`.`card` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 6473 waiting_trx_started: 2020-02-07 18:48:57 waiting_trx_age: 00:00:10 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 56 waiting_query: SELECT * FROM card WHERE suite ... ' AND number = '10' FOR UPDATE waiting_lock_id: 6473:51:3:11 waiting_lock_mode: X blocking_trx_id: 6471 blocking_pid: 2 blocking_query: NULL blocking_lock_id: 6471:51:3:11 blocking_lock_mode: X blocking_trx_started: 2020-02-07 18:36:41 blocking_trx_age: 00:12:26 blocking_trx_rows_locked: 9 blocking_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 2 sql_kill_blocking_connection: KILL 2 1 row in set, 3 warnings (0.01 sec) 23/37
  11. 28.

    performance_schema.data_locks mysql80 97> SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status,

    lock_data FROM performance_schema.data_locks; +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'spade', 10 | | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | number | RECORD | X | GRANTED | 10, 'spade' | | d1 | card | number | RECORD | X | GRANTED | 10, 'club' | | d1 | card | number | RECORD | X | GRANTED | 10, 'heart' | | d1 | card | number | RECORD | X | GRANTED | 10, 'diamond' | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'spade', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'club', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'heart', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'diamond', 10 | | d1 | card | number | RECORD | X,GAP | GRANTED | 11, 'club' | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ 12 rows in set (0.00 sec) 27/37
  12. 31.

    見えると何が良いことがあるの? 例えば「ロック競合を減らすためにトランザクション分離レベルをREAD- COMMITTEDに下げる」 mysql80 96> SET SESSION transaction_isolation = 'READ-COMMITTED';

    Query OK, 0 rows affected (0.00 sec) mysql80 96> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql80 96> SELECT * FROM card WHERE number = 10 FOR UPDATE; +---------+--------+ | suite | number | +---------+--------+ | club | 10 | | diamond | 10 | | heart | 10 | | spade | 10 | +---------+--------+ 4 rows in set (0.00 sec) 30/37
  13. 32.

    READ-COMMITTED mysql80 97> SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status,

    lock_data FROM performance_schema.data_locks; +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'spade', 10 | | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'spade' | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'club' | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'heart' | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'diamond' | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'spade', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'club', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'heart', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'diamond', 10 | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ 11 rows in set (0.00 sec) 31/37
  14. 33.

    REPEATABLE-READ mysql80 97> SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status,

    lock_data FROM performance_schema.data_locks; +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'spade', 10 | | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | number | RECORD | X | GRANTED | 10, 'spade' | | d1 | card | number | RECORD | X | GRANTED | 10, 'club' | | d1 | card | number | RECORD | X | GRANTED | 10, 'heart' | | d1 | card | number | RECORD | X | GRANTED | 10, 'diamond' | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'spade', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'club', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'heart', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'diamond', 10 | | d1 | card | number | RECORD | X,GAP | GRANTED | 11, 'club' | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ 12 rows in set (0.00 sec) 32/37
  15. 34.

    READ-COMMITTED vs REPEATABLE-READ セカンダリーインデックス ( number )がネクストキーロックからインデックスレ コードロックに ギャップロックがなくなった ‐

    (11, club) の手前のギャップをロックしないのでだいぶ直観的 ‐ 見えれば比べられる トランザクション分離レベルの話だけじゃなくて、「このクエリーとこっちのクエリーはどっ ちがロックの競合が少なくなるか?」も比べられる ‐ 33/37