您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 的层面看看如何定位MDL的相关问题

的层面看看如何定位MDL的相关问题

发布时间:2019-12-30 19:57编辑:威尼斯官方网站登录浏览(132)

    在上篇小说《MySQL表布局退换,不可不知的Metadata Lock》中,我们介绍了MDL引进的背景,及基本概念,从“道”的范围知道了什么样是MDL。上边就从“术”的范畴看看怎么着稳定MDL的相干主题素材。

    在MySQL 5.7中,针对MDL,引进了一张新表performance_schema.metadata_locks,该表可对外显示MDL的连锁消息,包蕴其职能对象,类型及全体等待情况。

     

    开启MDL的instrument

    而是相关instrument并未拉开(MySQL 8.0是私下认可开启的),其可经过如下三种办法拉开,

    一时半刻生效

    修改performance_schema.setup_instrume nts表,但实例重启后,又会出山小草为暗许值。

    UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME = 'wait/lock/metadata/sql/mdl';
    

     

    恒久生效

    在布署文件中设置

    [mysqld]
    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
    

     

    测量检验场景

    上边结合一个粗略的德姆o,来看看在MySQL 5.7中如何稳固DDL操作的不通难点。

    session1> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> delete from slowtech.t1 where id=2;
    Query OK, 1 row affected (0.00 sec)
    
    session1> select * from slowtech.t1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    +------+------+
    1 row in set (0.00 sec)
    
    session1> update slowtech.t1 set name='c' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    session2> alter table slowtech.t1 add c1 int; ##被阻塞
    
    session3> show processlist;
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    | Id | User | Host      | db   | Command | Time | State                           | Info                               |
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    |  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |
    |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
    |  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    3 rows in set (0.00 sec)
    
    session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
    +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
    | object_type | object_schema      | object_name    | lock_type           | lock_duration | lock_status | owner_thread_id |
    +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
    | TABLE       | slowtech           | t1             | SHARED_WRITE        | TRANSACTION   | GRANTED     |              27 |
    | GLOBAL      | NULL               | NULL           | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |              29 |
    | SCHEMA      | slowtech           | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |              29 |
    | TABLE       | slowtech           | t1             | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |              29 |
    | TABLE       | slowtech           | t1             | EXCLUSIVE           | TRANSACTION   | PENDING     |              29 |
    | TABLE       | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   | GRANTED     |              28 |
    +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
    6 rows in set (0.00 sec)
    

    此处,入眼关心lock_status,"PENDING"代表线程在等候MDL,而"GRANTED"则代表线程持有MDL。

     

    怎么寻找引起短路的对话

    结合owner_thread_id,能够可到,是29号线程在等待27号线程的MDL,那个时候,可kill掉52号线程。

    但需求注意的是,owner_thread_id给出的只是线程ID,并不是show processlist中的ID。假使要查究线程对应的processlist id,需询问performance_schema.threads表。

    session3> select * from performance_schema.threads where thread_id in (27,29)G
    *************************** 1. row ***************************
              THREAD_ID: 27
                   NAME: thread/sql/one_connection
                   TYPE: FOREGROUND
         PROCESSLIST_ID: 2
       PROCESSLIST_USER: root
       PROCESSLIST_HOST: localhost
         PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: Sleep
       PROCESSLIST_TIME: 214
      PROCESSLIST_STATE: NULL
       PROCESSLIST_INFO: NULL
       PARENT_THREAD_ID: 1
                   ROLE: NULL
           INSTRUMENTED: YES
                HISTORY: YES
        CONNECTION_TYPE: Socket
           THREAD_OS_ID: 9800
    *************************** 2. row ***************************
              THREAD_ID: 29
                   NAME: thread/sql/one_connection
                   TYPE: FOREGROUND
         PROCESSLIST_ID: 4
       PROCESSLIST_USER: root
       PROCESSLIST_HOST: localhost
         PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: Query
       PROCESSLIST_TIME: 172
      PROCESSLIST_STATE: Waiting for table metadata lock
       PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
       PARENT_THREAD_ID: 1
                   ROLE: NULL
           INSTRUMENTED: YES
                HISTORY: YES
        CONNECTION_TYPE: Socket
           THREAD_OS_ID: 9907
    2 rows in set (0.00 sec)
    

    将这两张表组成,借鉴sys.innodb_lock _waits的输出,实际上大家也足以直观地表现MDL的等待关系。

    SELECT
        a.OBJECT_SCHEMA AS locked_schema,
        a.OBJECT_NAME AS locked_table,
        "Metadata Lock" AS locked_type,
        c.PROCESSLIST_ID AS waiting_processlist_id,
        c.PROCESSLIST_TIME AS waiting_age,
        c.PROCESSLIST_INFO AS waiting_query,
        c.PROCESSLIST_STATE AS waiting_state,
        d.PROCESSLIST_ID AS blocking_processlist_id,
        d.PROCESSLIST_TIME AS blocking_age,
        d.PROCESSLIST_INFO AS blocking_query,
        concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
    FROM
        performance_schema.metadata_locks a
    JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
    AND a.OBJECT_NAME = b.OBJECT_NAME
    AND a.lock_status = 'PENDING'
    AND b.lock_status = 'GRANTED'
    AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
    AND a.lock_type = 'EXCLUSIVE'
    JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
    JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_IDG
    
    *************************** 1. row ***************************
                   locked_schema: slowtech
                    locked_table: t1
                     locked_type: Metadata Lock
          waiting_processlist_id: 4
                     waiting_age: 259
                   waiting_query: alter table slowtech.t1 add c1 int
                   waiting_state: Waiting for table metadata lock
         blocking_processlist_id: 2
                    blocking_age: 301
                  blocking_query: NULL
    sql_kill_blocking_connection: KILL 2
    1 row in set (0.00 sec)
    

    输出了然于目,DDL操作假使要获取MDL,试行kill 2就能够。

     

    官方的sys.schematablelock_waits

    实在,MySQL 5.7在sys库中也合并了相通意义,同样的现象,其出口如下,

    mysql> select * from sys.schema_table_lock_waitsG
    *************************** 1. row ***************************
                   object_schema: slowtech
                     object_name: t1
               waiting_thread_id: 29
                     waiting_pid: 4
                 waiting_account: root@localhost
               waiting_lock_type: EXCLUSIVE
           waiting_lock_duration: TRANSACTION
                   waiting_query: alter table slowtech.t1 add c1 int
              waiting_query_secs: 446
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
              blocking_thread_id: 27
                    blocking_pid: 2
                blocking_account: root@localhost
              blocking_lock_type: SHARED_READ
          blocking_lock_duration: TRANSACTION
         sql_kill_blocking_query: KILL QUERY 2
    sql_kill_blocking_connection: KILL 2
    *************************** 2. row ***************************
                   object_schema: slowtech
                     object_name: t1
               waiting_thread_id: 29
                     waiting_pid: 4
                 waiting_account: root@localhost
               waiting_lock_type: EXCLUSIVE
           waiting_lock_duration: TRANSACTION
                   waiting_query: alter table slowtech.t1 add c1 int
              waiting_query_secs: 446
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
              blocking_thread_id: 29
                    blocking_pid: 4
                blocking_account: root@localhost
              blocking_lock_type: SHARED_UPGRADABLE
          blocking_lock_duration: TRANSACTION
         sql_kill_blocking_query: KILL QUERY 4
    sql_kill_blocking_connection: KILL 4
    2 rows in set (0.00 sec)
    

    具体解析下官方的输出,

    唯有一个alter table操作,却发生了两条记下,並且两条记下的kill对象竟是还不生龙活虎致,对表布局素不相识及不用心看记录内容的话,难免会kill错对象。

    不止如此,如若有N个查询被DDL操作拥塞,则会发生N*2条记下。在窒碍操作比较多的景观下,那N*2条记下完全都以个噪音。

    而此前的SQL,无论有多少操作被封堵,叁个alter table操作,就只会输出一条记下。

     

    何以查看拥塞会话已经施行过的操作

    但上边这几个SQL也许有缺憾,其blocking_query为NULL,而在对话1中,其刚大风流洒脱度进行了四个SQL。

    这个与performance_schema.threads(相符于show processlist)有关,其只会输出当前正值运转的SQL,对于曾经试行过的,实际上是不能够来看。

    但在线上,kill是一个急需步步为营的操作,毕竟你很难精通kill的是不是事情首要操作?又大概,是个批量update操作?那么,有未有方法抓到该事情早前的操作呢?

    答案,有。

    即Performance Schema中记录Statement Event(操作事件)的表,具体满含events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

    常用的是前方四个。

    图片 1

    三者的表布局完全生机勃勃致,此中,events_statements_history又包罗了events_statements_current的操作,所以大家这边会动用events_statements_history。

    终极SQL如下,

    SELECT
        locked_schema,
        locked_table,
        locked_type,
        waiting_processlist_id,
        waiting_age,
        waiting_query,
        waiting_state,
        blocking_processlist_id,
        blocking_age,
        substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
        sql_kill_blocking_connection
    FROM
        (
            SELECT
                b.OWNER_THREAD_ID AS granted_thread_id,
                a.OBJECT_SCHEMA AS locked_schema,
                a.OBJECT_NAME AS locked_table,
                "Metadata Lock" AS locked_type,
                c.PROCESSLIST_ID AS waiting_processlist_id,
                c.PROCESSLIST_TIME AS waiting_age,
                c.PROCESSLIST_INFO AS waiting_query,
                c.PROCESSLIST_STATE AS waiting_state,
                d.PROCESSLIST_ID AS blocking_processlist_id,
                d.PROCESSLIST_TIME AS blocking_age,
                d.PROCESSLIST_INFO AS blocking_query,
                concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
            FROM
                performance_schema.metadata_locks a
            JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
            AND a.OBJECT_NAME = b.OBJECT_NAME
            AND a.lock_status = 'PENDING'
            AND b.lock_status = 'GRANTED'
            AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
            AND a.lock_type = 'EXCLUSIVE'
            JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
            JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
        ) t1,
        (
            SELECT
                thread_id,
                group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
            FROM
                performance_schema.events_statements_history
            GROUP BY thread_id
        ) t2
    WHERE
        t1.granted_thread_id = t2.thread_id G
    
    *************************** 1. row ***************************
                   locked_schema: slowtech
                    locked_table: t1
                     locked_type: Metadata Lock
          waiting_processlist_id: 4
                     waiting_age: 294
                   waiting_query: alter table slowtech.t1 add c1 int
                   waiting_state: Waiting for table metadata lock
         blocking_processlist_id: 2
                    blocking_age: 336
                  blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
    sql_kill_blocking_connection: KILL 2
    1 row in set, 1 warning (0.00 sec)
    

    从地点的出口能够看来,blocking_query中包括了会话1中当前事务的拥有操作,按实践的前后相继顺序输出。

    急需小心的是,暗中认可意况下,events_statements_history只会保留每一个线程前段时间的十多少个操作,如果事情中打开的操作超多,实际上也是无法抓全的。

    Anyway, it is better than nothing!

     

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:的层面看看如何定位MDL的相关问题

    关键词:

上一篇:对数值型数据举行舍入操作

下一篇:没有了