您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 5.7中如何定位DDL被阻塞的问题》中

5.7中如何定位DDL被阻塞的问题》中

发布时间:2019-12-31 22:27编辑:威尼斯官方网站登录浏览(52)

    在上生机勃勃篇小说《MySQL 5.7中什么稳固DDL被封堵的标题》中,对于DDL被卡住难点的固定,我们入眼是借助MySQL 5.7新引进的performance_schema.metadata_locks表。提议的定位方法,颇负种"锦上添花"的表示,并且,也只适用于MySQL 5.7从头的本子。

    但在其实生产中,MySQL 5.6如故占不用超多。即使MySQL 8.0都曾经GA了,但由于数据库的特殊性,在看待晋级的这几个事情上,卓殊一些人大概秉持着后生可畏种“不积极”的姿态。

    既然MySQL 5.6用者众多,有未有风流浪漫种办法,来解决MySQL 5.6的那个痛点呢?

     

    依然事先的测验德姆o

    会话1展开了政工并施行了八个操作,但未提交,这个时候,会话2推行了alter table操作,被卡住。

    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    |
    +------+------+
    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 |
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    rows in set (0.00 sec)
    

     

    事实上,招致DDL梗塞的操作,无非两类: 

    1. 慢查询  

    2. 表上有事务未提交

    个中,第蓬蓬勃勃类比较好确定地点,通过show processlist即能觉察。而第二类基本没有办法定位,因为未提交业务的接连几天在show processlist中的输出同空闲连接同样。

    如上面Id为2的连年,即便Command展现为“Sleep”,其实是专门的工作未提交。

    mysql> show processlist;
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    | Id | User | Host      | db   | Command | Time | State                           | Info                               |
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    |  2 | root | localhost | NULL | Sleep   |   77 |                                 | NULL                               |
    |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
    |  4 | root | localhost | NULL | Query   |   44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
    +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    3 rows in set (0.00 sec)
    

     

    就此,英特网有kill空闲(Command为Sleep)连接的传道,其实也说得过去,但诸有此类做就太简单狂暴了,难免会误杀。

    实际上,既然是事情,在information_schema. innodb_trx中自然会有记录,如会话1中的事务,在表中的笔录如下,

    mysql> select * from information_schema.innodb_trxG
    *************************** 1. row ***************************
                        trx_id: 1050390
                     trx_state: RUNNING
                   trx_started: 2018-07-17 08:55:32
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 4
           trx_mysql_thread_id: 2
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 1
              trx_lock_structs: 2
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 3
             trx_rows_modified: 2
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    1 row in set (0.00 sec)
    

     

    其中trx_mysql_thread_id是线程id,结合performance_schema.threads,能够清楚当前哪些连接上设有着活跃事务,那样就愈加压缩了可被kill的线程范围。

     但从事电影工作响程度上,和kill全体Command为Sleep的连接没太大分别,究竟,kill真正的空闲连接对作业的震慑相当的小。

     当时,依旧能够凭仗performance_schema. events_statements_history表。

     在上篇MySQL 5.7的解析中,我们是率先知道引发短路的线程ID,然后采纳events_statements_history表,查看该线程的相关SQL。

     而在MySQL 5.6中,大家并不知道引发短路的线程ID,不过,大家能够违背,利用穷举法,首先计算出装有线程在现阶段业务试行过的有所SQL,然后再推断那个SQL中是还是不是带有指标表。

     

    具体SQL如下,

    SELECT
        processlist_id,
        sql_text 
    FROM
        (
        SELECT
            c.processlist_id,
            substring_index( sql_text, "transaction_begin;",-1 ) sql_text 
        FROM
            information_schema.innodb_trx a,
            (
            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 
            ) b,
            performance_schema.threads c 
        WHERE
            a.trx_mysql_thread_id = c.processlist_id 
            AND b.thread_id = c.thread_id 
        ) t 
    WHERE
        sql_text LIKE '%t1%';
    
    +----------------+---------------------------------------------------------------------------------------------------------+
    | processlist_id | sql_text                                                                                                |
    +----------------+---------------------------------------------------------------------------------------------------------+
    |              2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 |
    +----------------+---------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)
    

    从出口来看,确实也达成了预想功用。

     

    急需注意的是,在MySQL5.6中,events_statements_history暗中同意是未曾拉开的。

    mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
    +--------------------------------+---------+
    | NAME                           | ENABLED |
    +--------------------------------+---------+
    | events_statements_current      | YES     |
    | events_statements_history      | NO      |
    | events_statements_history_long | NO      |
    | statements_digest              | YES     |
    +--------------------------------+---------+
    4 rows in set (0.00 sec)
    

     

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:5.7中如何定位DDL被阻塞的问题》中

    关键词: