江苏龙网

  1. 主页 > 生活百科 > >

MySQL运行时的可观测性( 三 )

mysql


greatsql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXTFROM performance_schema.events_statements_history WHERE thread_id = 85 order by event_id desc limit 5;+----------+----------+-------------------------------------------------------------------------------------------------------------------------------+| EVENT_ID | Duration | SQL_TEXT|+----------+----------+-------------------------------------------------------------------------------------------------------------------------------+|149845 |0.6420 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 ||149803 |0.6316 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 ||149782 |0.6245 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 ||149761 |0.6361 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 ||149740 |0.6245 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 |+----------+----------+-------------------------------------------------------------------------------------------------------------------------------+# 再根据 EVENT_ID 值去查询 events_stages_history_longgreatsql> SELECT thread_id ,event_Id, event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS DurationFROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID = 149845 order by event_id;+-----------+----------+------------------------------------------------+----------+| thread_id | event_Id | Stage| Duration |+-----------+----------+------------------------------------------------+----------+|85 |149846 | stage/sql/starting|0.0000 ||85 |149847 | stage/sql/Executing hook on transaction begin. |0.0000 ||85 |149848 | stage/sql/starting|0.0000 ||85 |149849 | stage/sql/checking permissions|0.0000 ||85 |149850 | stage/sql/Opening tables|0.0000 ||85 |149851 | stage/sql/init|0.0000 ||85 |149852 | stage/sql/System lock|0.0000 ||85 |149854 | stage/sql/optimizing|0.0000 ||85 |149855 | stage/sql/statistics|0.0000 ||85 |149856 | stage/sql/preparing|0.0000 ||85 |149857 | stage/sql/Creating tmp table|0.0000 ||85 |149858 | stage/sql/executing|0.6257 ||85 |149859 | stage/sql/end|0.0000 ||85 |149860 | stage/sql/query end|0.0000 ||85 |149861 | stage/sql/waiting for handler commit|0.0000 ||85 |149862 | stage/sql/closing tables|0.0000 ||85 |149863 | stage/sql/freeing items|0.0000 ||85 |149864 | stage/sql/logging slow query|0.0000 ||85 |149865 | stage/sql/cleaning up|0.0000 |+-----------+----------+------------------------------------------------+----------+上面就是这条SQL的运行进度展示 ， 以及各个阶段的耗时 ， 和PROFILING的输出一样 ， 当我们了解一条SQL运行所需要经历的各个阶段时 ， 从上面的输出结果中也就能估算出该SQL大概还要多久能跑完 ， 决定是否要提前kill它 。
如果想要观察DDL SQL的运行进度 ， 可以参考这篇文章：不用MariaDB/Percona也能查看DDL的进度 。
更多的观测指标、维度还有待继续挖掘 ， 以后有机会再写 。
另外 ， 也可以利用MySQL Workbench工具 ， 或MySQL Enterprise Monitor ， 都已集成了很多可观测性指标 ， 相当不错的体验 。

【MySQL运行时的可观测性】


推荐阅读


上一篇：Python 字符串深度总结

下一篇：Linux NFSD软件架构与代码解析