MySQL之performance_schema监控

最新推荐文章于 2025-02-26 09:24:42 发布

原创最新推荐文章于 2025-02-26 09:24:42 发布 · 545 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了MySQL PerformanceSchema的功能、特点及如何监控SQL执行性能、资源使用情况。学习如何通过events_statements_summary_by_digest表分析热点SQL和性能瓶颈。

MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况；从MySQL5.6开始，Performance Schema 默认打开。

特点：

提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息
performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。
performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。
PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同
收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）
performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）
MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

查看是否启用;

show variables like 'performance_schema';

events_statements_summary_by_digest表字段对应注释

字段	注释
SCHEMA_NAME	库名
DIGEST	对SQL_TEXT做MD5产生的32位字符串。如果为consumer表中没有打开statement_digest选项，则为NULL
DIGEST_TEXT	将语句中值部分用问号代替，用于SQL语句归类。如果为consumer表中没有打开statement_digest选项，则为NULL
COUNT_STAR	事件计数
SUM_TIMER_WAIT	总的等待时间
MIN_TIMER_WAIT	最小等待时间
AVG_TIMER_WAIT	平均等待时间
MAX_TIMER_WAIT	最大等待时间
SUM_LOCK_TIME	锁时间总时长
SUM_ERRORS	错误的总数
SUM_WARNINGS	警告的总数
SUM_ROWS_AFFECTED	影响的总数
SUM_ROWS_SENT	返回的总数
SUM_ROWS_EXAMINED	总的扫描的数目
SUM_CREATED_TMP_DISK_TABLES	创建磁盘临时表的总数目
SUM_CREATED_TMP_TABLES	创建临时表的总数目
SUM_SELECT_FULL_JOIN	第一个表全表扫描的总数目
SUM_SELECT_FULL_RANGE_JOIN	总的采用range方式扫描的数目
SUM_SELECT_RANGE	第一个表采用range方式扫描的总数目
SUM_SELECT_RANGE_CHECK
SUM_SELECT_SCAN	第一个表位全表扫描的总数目
SUM_SORT_MERGE_PASSES
SUM_SORT_RANGE	范围排序总数
SUM_SORT_ROWS	排序的记录总数目
SUM_SORT_SCAN	第一个表排序扫描总数目
SUM_NO_INDEX_USED	没有使用索引总数
SUM_NO_GOOD_INDEX_USED
FIRST_SEEN	第一次执行时间
LAST_SEEN	最后一次执行时间

执行最多的SQL语句

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest
 ORDER BY COUNT_STAR desc LIMIT 1;

平均响应时间最多的SQL

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY AVG_TIMER_WAIT desc LIMIT 1;

扫描最多行数的SQL

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest
 ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1;

使用临时表最多的SQL

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES desc

返回结果集最多的SQL

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY SUM_ROWS_SENT desc LIMIT 1;

排序数最多的SQL

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY SUM_SORT_ROWS desc LIMIT 1;

哪个表、文件逻辑IO最多（热数据）

SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY
 SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC;

使用最多的索引

SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC  limit 1;

未使用的索引

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM 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;

哪个等待事件消耗的时间最多

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;