Shulou(Shulou.com)06/01 Report--

You often encounter a scene like this:

The business side thinks that the database is "very slow". Through show processlist inspection, it is found that a large number of State are waiting for lock, such as:

Waiting for table metadata/level lock et al.

For example, when executing a DDL, it is found that it is occupied by hang, and the current process status is checked, and there is MDL.

Mysql > SHOW PROCESSLIST

+-+

| | Id | User | Host | db | Command | Time | State | Info |

+-+

| | 585 | root | localhost | test | Sleep | 1658 | | NULL |

| | 586 | root | localhost | test | Query | 1654 | Waiting for table metadata lock | alter table t change name name varchar (32) |

| | 590 | root | localhost | test | Query | 0 | starting | show processlist |

+-+

3 rows in set (0.00 sec)

In the experimental environment, we can quickly locate the thread whose id is 585, but there is no way to know what sql is being executed:

Through information_schema.innodb_trx\ G, there is no way to find out exactly what sql is executed.

It is true that the immediate problem can be solved through a simple kill, but if you continue to encounter this problem, it will be difficult to locate the specific content.

But in fact, performance_schema.events_statements_current provides relevant information, which is repeated here:

Session1 > BEGIN

Query OK, 0 rows affected (0.00 sec)

Session1 > UPDATE t SET name='fasdfsad'

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

Session2 > ALTER TABLE t CHANGE name name varchar (32) found to be lived by hang

Check to see if there are any transactions that have not been committed, and you can see that there are:

When the statement within the transaction is executed (in Sleep), but not committed, the corresponding trx_query will not be seen:

Session3 > SELECT * FROM information_schema.innodb_trx\ G

* * 1. Row *

Trx_id: 9614

Trx_state: RUNNING

Trx_started: 2017-09-19 15:58:05

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 2

Trx_mysql_thread_id: 585

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: 4

Trx_rows_modified: 0

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)

You can only see the process id of uncommitted transactions based on trx_mysql_thread_id, and there is no specific content in processlist,INFO:

Session3 > SHOW PROCESSLIST

+-+

| | Id | User | Host | db | Command | Time | State | Info |

+-+

| | 585 | root | localhost | test | Sleep | 42 | | NULL |

| | 586 | root | localhost | test | Query | 37 | Waiting for table metadata lock | ALTER TABLE t CHANGE name name varchar (32) |

| | 590 | root | localhost | test | Query | 0 | starting | SHOW PROCESSLIST |

+-+

3 rows in set (0.00 sec)

However, as long as packs is opened, you can view the corresponding sql through performance_schema.events_statements_current, including those that have been executed but have not been submitted.

Session3 > SELECT sql_text FROM performance_schema.events_statements_current

+-+

| | sql_text |

+-+

| | UPDATE t SET name='fasdfsad' |

| | ALTER TABLE t CHANGE name name varchar (32) | |

| | select sql_text from performance_schema.events_statements_current |

+-+

3 rows in set (0.00 sec) of course, in a complex production environment, the information found by the above statement is far from enough.

You can extend the display of the show processlist and provide the corresponding SQL with the following statement.

SELECT b.processlist_id, c.db, a.sql_text, c.command, c.time, c.state

FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING (thread_id)

JOIN information_schema.processlist c ON b.processlist_id = c.id

WHERE a.sql_text NOT LIKE'% performance%'

Results:

+-+-

| | processlist_id | db | sql_text | command | time | state | |

+-+-

| | 585 | test | UPDATE t SET name='fasdfsad' | Sleep | 243 |

| | 586 | test | ALTER TABLE t CHANGE name name varchar (32) | Query | 238 | Waiting for table metadata lock |

+-+-

2 rows in set (0.01sec) can also be easily located that the content executed is a update operation that has not been submitted.

Author's official account on Wechat (continuously updated)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.