查看长时间运行的事务：
SELECT thr.processlist_id AS mysql_thread_id,
concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
Command,
FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
current_statement as `latest_statement`
FROM performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id)
LEFT JOIN sys.processlist p ON p.thd_id=thread_id
WHERE thr.processlist_id IS NOT NULL
AND PROCESSLIST_USER IS NOT NULL
AND trx.state = 'ACTIVE'
GROUP BY thread_id, timer_wait
ORDER BY TIMER_WAIT DESC LIMIT 10;
+-----------------+--------------------+---------+--------------+-------------------------------------------------------------------+
| mysql_thread_id | User | Command | trx_duration | latest_statement |
+-----------------+--------------------+---------+--------------+-------------------------------------------------------------------+
| 3062872 | root@192.168.11.23 | Sleep | 5.13 s | NULL |
| 3062771 | root@localhost | Query | 309.02 us | SELECT thr.processlist_id AS m ... ER BY TIMER_WAIT DESC LIMIT 10 |
+-----------------+--------------------+---------+--------------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)
从查看结果可以看到，有个活跃事务运行了5.13s，而且latest_statement的结果是NULL，目前什么也看不到。sleep状态的会话通常可能是引起问题的会话。他们可能是被遗忘的非活跃会话，持续存在很长时间。默认的超时设置是8小时(interactive_timeout参数设定)。
如果开启了相应的instrumentation，也可以通过performance_schema_events_statements_history_size来查看事务中执行过的语句：
开启以下两个instrumentation：
UPDATE performance_schema.setup_consumers
SET enabled = 'yes'
WHERE name LIKE 'events_statements_history_long'
OR name LIKE 'events_transactions_history_long';
根据上面查询结果中的mysql_thread_id的值进行查看：
SELECT DATE_SUB(now(), INTERVAL (
SELECT variable_value
FROM performance_schema.global_status
WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) start_time,
SQL_TEXT
FROM performance_schema.events_statements_history
WHERE nesting_event_id=(
SELECT EVENT_ID
FROM performance_schema.events_transactions_current t
LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id
WHERE conn_id=<VALUE OF mysql_thread_id COLUMN>)
ORDER BY event_id;
这会列出一些之前运行过的长事务语句。performance_schema几乎包含了我们所有想要的。
