Categories

Tags


개요

  • 기존 5.7 까지 information_schema database 에 존재하던 INNODB_LOCK_WAITS, INNODB_LOCKS 테이블이 performance_schema로 이관되면서 두가지 장점을 제공해 주었다.

    • performance_schema.data_locks 을 통한 더욱 자세한 lock 정보

    • Waiting 하지 않는 상태에서도 현재의 lock 정보를 표시

      • 해당 정보를 얻기 위해서는 gdb를 이용하여, 해당 thread가 가지는 lock정보를 확인해야 했다.
      • 5.7에서 INFORMATION_SCHEMA.INNODB_LOCKS 의 경우, Waiting 하는 session이 발생했을때만 나타나지만 8.0의 performance_schema.data_locks 의 경우 현재 lock을 걸고 있는 모든 세션에 대해 lock상태를 보여준다. 이는 아주 중요한 정보로서, 현재의 session 들이 잡고 있는 lock들을 확인할 수 있다.
    • 5.7

      mysql 5.7 [localhost] {msandbox} (test) > create table lock_t (id int primary key, name varchar(10));
      Query OK, 0 rows affected (0.04 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > insert into lock_t values (1,'Min'),(2,'Chan'),(3,'Tim');
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
      
      mysql 5.7 [localhost] {msandbox} (test) > begin;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select * from lock_t where id=2 for update;
      +----+------+
      | id | name |
      +----+------+
      |  2 | Chan |
      +----+------+
      1 row in set (0.01 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select * from information_schema.INNODB_LOCKS;
      Empty set, 1 warning (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > select * from information_schema.INNODB_LOCK_WAITS;
      Empty set, 1 warning (0.00 sec)
      
      mysql 5.7 [localhost] {msandbox} (test) > show warnings;
      +---------+------+-----------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                       |
      +---------+------+-----------------------------------------------------------------------------------------------+
      | Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. |
      +---------+------+-----------------------------------------------------------------------------------------------+
      1 row in set (0.01 sec)
      
    • 8.0

      mysql 8.0 [localhost] {msandbox} (test) > create table lock_t (id int primary key, name varchar(10));
      Query OK, 0 rows affected (0.04 sec)
      
      mysql 8.0 [localhost] {msandbox} (test) > insert into lock_t values (1,'Min'),(2,'Chan'),(3,'Tim');
      Query OK, 3 rows affected (0.08 sec)
      Records: 3  Duplicates: 0  Warnings: 0
      
      mysql 8.0 [localhost] {msandbox} (test) > begin;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql 8.0 [localhost] {msandbox} (test) > select * from lock_t where id=2 for update;
      +----+------+
      | id | name |
      +----+------+
      |  2 | Chan |
      +----+------+
      1 row in set (0.00 sec)
      
      
      mysql 8.0 [localhost] {msandbox} (test) > select * from performance_schema.data_locks;
      +--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
      | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
      +--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
      | INNODB | 90919:1132     |                 90919 |        50 |       44 | test          | lock_t      | NULL           | NULL              | NULL       |       140613502769240 | TABLE     | IX            | GRANTED     | NULL      |
      | INNODB | 90919:73:4:3   |                 90919 |        50 |       44 | test          | lock_t      | NULL           | NULL              | PRIMARY    |       140613521814040 | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
      +--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
      2 rows in set (0.00 sec)
      

사용예제

  • session 1

    mysql 8.0 [localhost] {msandbox} (test) > begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > select 's1', a from tbl where a=1 for update;
    +----+------+
    | s1 | a    |
    +----+------+
    | s1 |    1 |
    +----+------+
    1 row in set (0.00 sec)
    
  • 모니터링세션

    SELECT straight_join
       dl.THREAD_ID
      , est.SQL_TEXT
      , dl.OBJECT_SCHEMA
      , dl.OBJECT_NAME
      , dl.INDEX_NAME
      , dl.LOCK_TYPE
      , dl.LOCK_MODE
      , dl.LOCK_STATUS
      , dl.LOCK_DATA
    FROM
      performance_schema.data_locks dl inner join performance_schema.events_statements_current est on dl.THREAD_ID = est.THREAD_ID
    ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;
    
    +-----------+----------------------------------------------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
    | THREAD_ID | SQL_TEXT                                     | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME      | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
    +-----------+----------------------------------------------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
    |        47 | select 's1', a from tbl where a=1 for update | test          | tbl         | NULL            | TABLE     | IX        | GRANTED     | NULL                   |
    |        47 | select 's1', a from tbl where a=1 for update | test          | tbl         | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | supremum pseudo-record |
    |        47 | select 's1', a from tbl where a=1 for update | test          | tbl         | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000221         |
    +-----------+----------------------------------------------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
    3 rows in set (0.00 sec)
    
    
  • session 2

    mysql 8.0 [localhost] {msandbox} (test) > select 's2',a from tbl where a=1 for update;
    --- 대기상태
    
  • 모니터링 세션

    SELECT straight_join
       dl.THREAD_ID
      , est.SQL_TEXT
      , dl.OBJECT_SCHEMA
      , dl.OBJECT_NAME
      , dl.INDEX_NAME
      , dl.LOCK_TYPE
      , dl.LOCK_MODE
      , dl.LOCK_STATUS
      , dl.LOCK_DATA
    FROM
      performance_schema.data_locks dl inner join performance_schema.events_statements_current est on dl.THREAD_ID = est.THREAD_ID
    ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;
    +-----------+----------------------------------------------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
    | THREAD_ID | SQL_TEXT                                     | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME      | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
    +-----------+----------------------------------------------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
    |        47 | select 's1', a from tbl where a=1 for update | test          | tbl         | NULL            | TABLE     | IX        | GRANTED     | NULL                   |
    |        47 | select 's1', a from tbl where a=1 for update | test          | tbl         | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | supremum pseudo-record |
    |        47 | select 's1', a from tbl where a=1 for update | test          | tbl         | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000221         |
    |        48 | select 's2',a from tbl where a=1 for update  | test          | tbl         | NULL            | TABLE     | IX        | GRANTED     | NULL                   |
    |        48 | select 's2',a from tbl where a=1 for update  | test          | tbl         | GEN_CLUST_INDEX | RECORD    | X         | WAITING     | 0x000000000221         |
    +-----------+----------------------------------------------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
    5 rows in set (0.00 sec)
    
    

적용범위

  • 현재 information_schema 의 INNODB_LOCK_WAITS, INNODB_LOCKS 테이블을 사용하는 모든 모니터링을 Performance_Schema 의 data_lock_waits , data_locks 로 수정한다.

  • 추가로, Performance_Schema.data_lock_waits 를 주기적으로 모니터링하며, 어떤 쿼리들이 지속적으로 많은 양의 lock을 잡고 있는지 확인해 볼 수 있다.