资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划,从策略到执行的一站式服务

MySQL通过performance_schema定位未提交事务所执行的SQL

经常会遇到这样一个场景:
业务那边觉得数据库“很慢”,上去通过show processlist查看发现大量State为在等待lock,如:
Waiting for table metadata/level lock等


比如在执行一个DDL时,发现被hang住,查看到目前进程状态,有MDL

  1. MySQL> SHOW PROCESSLIST;
  2. +-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
  3. | Id  | User | Host      | db   | Command| Time | State                           | Info                                     |
  4. +-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
  5. | 585| root| localhost| test| Sleep  | 1658|                                | NULL                                       |
  6. | 586| root| localhost| test| Query  | 1654| Waitingfor table metadata lock| altertable t change name namevarchar(32) |
  7. | 590| root| localhost| test| Query  | 0    | starting                      | show processlist                         |
  8. +-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
  9. 3 rowsin set (0.00 sec)

在实验环境里,我们很快就能定位到,应该是id为585这个线程,但是无法知道正在执行什么sql:
通过information_schema.innodb_trx\G,也不能查询到具体执行了什么sql。
通过简单的kill的确可以解决眼前的问题,但如果继续遇到该问题,也难以定位具体内容。


但其实,performance_schema.events_statements_current提供了相关信息,此处复现一下:

  1. session1> BEGIN;
  2. Query OK, 0 rows affected(0.00 sec)

  3. session1> UPDATE t SETname='fasdfsad';
  4. Query OK, 3 rows affected(0.00 sec)
  5. Rows matched: 3 Changed: 3 Warnings: 0


  1. session2> ALTER TABLE t CHANGEname name varchar(32)
发现被hang住


查看一下是否有事务未提交,可以发现的确有:
该事务内的语句执行完毕(处于Sleep),但未提交,就会看不到对应的trx_query:

  1. session3> SELECT * FROM information_schema.innodb_trx\G
  2. *************************** 1. row***************************
  3.                     trx_id: 9614
  4.                  trx_state: RUNNING
  5.                trx_started: 2017-09-19 15:58:05
  6.      trx_requested_lock_id:NULL
  7.           trx_wait_started:NULL
  8.                 trx_weight: 2
  9.        trx_mysql_thread_id: 585
  10.                  trx_query:NULL
  11.        trx_operation_state:NULL
  12.          trx_tables_in_use: 0
  13.          trx_tables_locked: 1
  14.           trx_lock_structs: 2
  15.      trx_lock_memory_bytes: 1136
  16.            trx_rows_locked: 4
  17.          trx_rows_modified: 0
  18.    trx_concurrency_tickets: 0
  19.        trx_isolation_level: REPEATABLE READ
  20.          trx_unique_checks: 1
  21.     trx_foreign_key_checks: 1
  22. trx_last_foreign_key_error:NULL
  23.  trx_adaptive_hash_latched: 0
  24.  trx_adaptive_hash_timeout: 0
  25.           trx_is_read_only: 0
  26. trx_autocommit_non_locking: 0
  27. 1 rowin set (0.00 sec)


只能根据trx_mysql_thread_id看到未提交的事务的process id,看一下processlist,INFO内也没有具体内容:


  1. session3> SHOW PROCESSLIST;
  2. +-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
  3. | Id  | User | Host      | db   | Command| Time | State                          | Info                                       |
  4. +-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
  5. | 585| root| localhost| test| Sleep   | 42   |                                | NULL                                       |
  6. | 586| root| localhost| test| Query   | 37   | Waitingfor table metadata lock| ALTERTABLE t CHANGE name namevarchar(32) |
  7. | 590| root| localhost| test| Query   | 0    | starting                       | SHOW PROCESSLIST                         |
  8. +-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
  9. 3 rowsin set (0.00 sec)


但只要打开了P_S,就可以通过performance_schema.events_statements_current来查看到对应的sql,包括已经执行完,但没有提交的。

  1. session3> SELECT sql_textFROM performance_schema.events_statements_current;
  2. +-------------------------------------------------------------------+
  3. | sql_text                                                          |
  4. +-------------------------------------------------------------------+
  5. | UPDATE tSET name='fasdfsad'                                      |
  6. | ALTERTABLE t CHANGE name namevarchar(32)                        |
  7. | select sql_textfrom performance_schema.events_statements_current|
  8. +-------------------------------------------------------------------+
  9. 3 rowsin set (0.00 sec)
当然,在复杂的生产环境中,光凭上面的语句查出来的信息,是远远不够的。

通过如下语句,可以扩展show processlist的显示结果,并提供对应的SQL。

  1. SELECT b.processlist_id, c.db, a.sql_text, c.command, c.time, c.state
  2. FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id)
  3. JOIN information_schema.processlist c ON b.processlist_id= c.id
  4. WHERE a.sql_textNOT LIKE '%performance%';

结果:
  1. +----------------+------+--------------------------------------------+---------+------+---------------------------------+
  2. | processlist_id| db   | sql_text                                   | command| time | state                           |
  3. +----------------+------+--------------------------------------------+---------+------+---------------------------------+
  4. | 585            | test| UPDATE tSET name='fasdfsad'              | Sleep   | 243 |                                |
  5. | 586           | test| ALTERTABLE t CHANGE name namevarchar(32) | Query   | 238 | Waitingfor table metadata lock|
  6. +----------------+------+--------------------------------------------+---------+------+---------------------------------+
  7. 2 rowsin set (0.01 sec)
也可以很容易定位到执行的内容是做了一个update操作未提交。


作者微信公众号(持续更新)
MySQL通过performance_schema定位未提交事务所执行的SQL


分享题目:MySQL通过performance_schema定位未提交事务所执行的SQL
本文地址:http://www.cdkjz.cn/article/pedoec.html
多年建站经验

多一份参考,总有益处

联系快上网,免费获得专属《策划方案》及报价

咨询相关问题或预约面谈,可以通过以下方式与我们联系

大客户专线   成都:13518219792   座机:028-86922220