MySQL locates the SQL executed by the unsubmitted transaction through performance_schema
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)
