概述
今天主要介绍如何通过Performance-Schema得到DBA关心的数据,比如哪个SQL执行次数最多,哪个表访问最频繁,哪个锁最热等信息。通过充分利用Performance-Schema表的数据,让DBA更了解DB的运行状态,也更有助于排查定位问题。
这里主要借助了events_statements_summary_by_digest ,这个表保存着许多关键指标,抓取了与每条标准化语句有关的延迟、错误和查询量信息。
一、统计信息(SQL维度)
关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中,通过将SQL语句抽象出digest,可以统计某类SQL语句在各个维度的统计信息(比如:执行次数,排序次数,使用临时表等)
1、执行次数最多的SQL
SELECT DIGEST_TEXT, COUNT_STAR, FIRST_SEEN, LAST_SEEN FROM `performance_schema`.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
![abdf5e795307361c46d7640e516511e6.png](https://i-blog.csdnimg.cn/blog_migrate/aa335c8b0ce968f5819dc661108530b6.jpeg)
可以看到执行次数最多的SQL是INSERT INTO `t_ocs_group_production_temporary` VALUES (...) ,FIRST_SEEN和LAST_SEEN分别显示了语句第一次执行和最后一次执行的时间点。
2、平均响应时间最多的sql
SELECT DIGEST_TEXT, AVG_TIMER_WAIT, COUNT_STAR, FIRST_SEEN, LAST_SEEN FROM `performance_schema`.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC;
![af9a33a6b1e0c7fcdb8f62eb5f0282b7.png](https://i-blog.csdnimg.cn/blog_migrate/75d0150350b5e16ae0290d5b555ea3af.jpeg)
可以看到平均响应时间最长的sql是SELECT * FROM `lcp_mq_record` ,这个是一张日志表。
3、排序记录数最多的sql
SELECT DIGEST_TEXT,SUM_SORT_ROWS, COUNT_STAR, FIRST_SEEN, LAST_SEEN FROM `performance_schema`.events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC;
![7c6cf5f4b6908749cb94a67546360822.png](https://i-blog.csdnimg.cn/blog_migrate/48010bf051394643201bdfbcae8a1352.jpeg)
4、扫描记录数最多的sql
SELECT DIGEST_TEXT, SUM_ROWS_EXAMINED, COUNT_STAR, FIRST_SEEN, LAST_SEEN FROM `performance_schema`.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC;
![6deb50e2b69be17194329862f1ceed49.png](https://i-blog.csdnimg.cn/blog_migrate/d9d17a2fb7d33adfbda7457be7e882a1.jpeg)
5、使用临时表最多的sql
SELECT DIGEST_TEXT, SUM_CREATED_TMP_TABLES, SUM_CREATED_TMP_DISK_TABLES, COUNT_STAR, FIRST_SEEN, LAST_SEEN FROM `performance_schema`.events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES desc,SUM_CREATED_TMP_DISK_TABLES desc
![4c8ef4c05f03f4fd4d61a34914780d33.png](https://i-blog.csdnimg.cn/blog_migrate/ff2012bffe8dfaa6f5cd5e8b6508dcc4.jpeg)
6、返回结果集最多的SQL
SELECT DIGEST_TEXT, SUM_ROWS_SENT, COUNT_STAR, FIRST_SEEN, LAST_SEEN FROM `performance_schema`.events_statements_summary_by_digestORDER BY SUM_ROWS_SENT desc;
![573a10ed550f6618ae54066d04f4c554.png](https://i-blog.csdnimg.cn/blog_migrate/79dfc7645ae8d6b36dbce92f268f07cb.jpeg)
通过上述指标我们可以间接获得某类SQL的逻辑IO(SUM_ROWS_EXAMINED),CPU消耗(SUM_SORT_ROWS),网络带宽(SUM_ROWS_SENT)的对比,但还无法得到某类SQL的物理IO消耗,以及某类SQL访问数据的buffer命中率。
二、统计信息(对象维度)
1、哪个表物理IO最多?
SELECT file_name, event_name, SUM_NUMBER_OF_BYTES_READ, SUM_NUMBER_OF_BYTES_WRITE FROM `performance_schema`.file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;
![acab28480b921de0be225d79d3cab771.png](https://i-blog.csdnimg.cn/blog_migrate/3e6ffd17ad54d7f07a213125d5c12a3e.jpeg)
通过file_summary_by_instance表,可以获得系统运行到现在,哪个文件(表)物理IO最多,这可能意味着这个表经常需要访问磁盘IO,从结果来看perf_stat库里面的is_global_variables的数据文件访问最多。
2、哪个表逻辑IO最多?
SELECT object_schema, object_name, COUNT_READ, COUNT_WRITE, COUNT_FETCH, SUM_TIMER_WAIT FROM `performance_schema`.table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC;
![9a23e423b37a13549a408cec73f05640.png](https://i-blog.csdnimg.cn/blog_migrate/e14f349dc57d416e7a8d6beb66900485.jpeg)
通过table_io_waits_summary_by_table表,可以获得系统运行到现在,哪个表逻辑IO最多,亦即最“热”的表,从结果来看fsl_prod库里面的t_ocs_employee_attendace表访问次数最多。
3、哪个索引访问最多?
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM `performance_schema`.table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC;
![bed9fcff343ebfc676ed3b146e888cb4.png](https://i-blog.csdnimg.cn/blog_migrate/a2c2ffbc9947ac4f1f5e79ee930331d8.jpeg)
通过table_io_waits_summary_by_index_usage表,可以获得系统运行到现在,哪个表的具体哪个索引(包括主键索引,二级索引)使用最多,从结果来看,我们知道t_ocs_employee_attendace表访问最多,并且都是通过t_ocs_production_lines_n1索引访问。
4、哪个索引从来没有使用过?
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM `performance_schema`.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
![6faa41dc7904aadf079066845a93a969.png](https://i-blog.csdnimg.cn/blog_migrate/24c90802d3ae25f7e8363783e328e483.jpeg)
通过table_io_waits_summary_by_index_usage表,我们还可以获得系统运行到现在,哪些索引从来没有被用过。由于索引也会占用大量的空间,我们可以利用这个统计信息,结合一定的时间策略将无用的索引删除。上面的结果显示,fsl_prod库act_hi_actinst表的ACT_IDX_HI_ACT_INST_END索引从来没有被使用过。
三、统计信息(等待事件维度)
1、哪个等待事件消耗的时间最多?
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM `performance_schema`.events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC;
![e9f42ae2b224e7493efa89ecbe95895b.png](https://i-blog.csdnimg.cn/blog_migrate/03bc5219b281080dc1faceae1cf005a4.jpeg)
通过events_waits_summary_global_by_event_name表,可以获取到系统运行到现在,消耗时间最多的事件,当然还可以根据其它维度排序,比如平均等待时间,从结果来看wait/io/table/sql/handler这个事件消耗的累计时间最长。
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
![757baad4b17738b160c4eb362dc64e3d.gif](https://i-blog.csdnimg.cn/blog_migrate/59d628059e6c811c9c65da162c85aa57.gif)