1.简单介绍
MYSQL的Performance_schema用于监控MySQL server 在一个较低级别的运行过程中的资源消耗，资源等待等情况
MySQL默认开启，可以用 SHOW VARIABLES LIKE ‘performance_schema’ 查看性能模板是否开启。
基于Performance_schema的监控项目Gitee路径:mysqlMonitor
- 提供了一种在数据库运行时实时检查server的内部执行情况的方法，performance_schema数据库使用了performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema（信息模式）不同，information_schema主要关注server运行过程中的元数据信息。
- performance_schema通过监视server的事件来实现监视server内部运行情况，"事件"就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里。一般，事件可以是函数调用，操作系统的等待，SQL语句执行的阶段(如SQL语句执行过程中的parsing解析或sorting整理阶段)或者整个SQL语句与SQL语句集合。
- performance_schema中的事件与写入二进制日志中的事件(描述数据修改的events)、事件计划调度程序(一种存储程序)的事件不同，performance_schema中的事件记录是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。同时，该数据库中所有表的数据不会进行持久化，仅在内存中存放，当MYSQL服务重启，数据清空。
- performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表数据发生改变时不会写入binlog中，也不会通过复制机复制到其他server中
- PERFORMANCE_SCHEMA采存储引擎使用server源代码中的"检测点"来实现事件数据的收集。
- 收集的事件存储在performance_schema数据库的表中。这些表使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表数据(如动态修改performance_schema的setup_*开头的配置表，注意：配置表的更改会立即生效，这会影响到数据收集)
-
select * from setup_instruments; ————查看哪些事件的收集器（监控和计时器）是否开启 update setup_instruments set enabled = 'yes',timed='yes' where name like 'wait%'; ————打开等待事件的采集器(生产者)配置项开关，需要修改setup_instruments配置表中对应的采集器配置项 update setup_consumers set enabled ='yes' where name like '%wait%'; ————打开等待事件的保存表(消费者)配置项开关，需要修改setup_consumers配置表中对应的保存器配置项 （配置完成后） select * from events_waits_current\G ————当配置完成后可以查看当前server正在做什么，可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件，如： *************************** 6. row *************************** THREAD_ID: 65 //线程ID EVENT_ID: 73 END_EVENT_ID: 73 EVENT_NAME: wait/synch/mutex/sql/THD::LOCK_query_plan //检测到的具体的内容 SOURCE: sql_class.h:1696 //这个检测代码在哪个源文件中以及行数 TIMER_START: 55444712428283757 //线程何时开启 TIMER_END: 55444712428317117 //线程何时结束 TIMER_WAIT: 33360 //线程执行时间 SPINS: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL INDEX_NAME: NULL OBJECT_TYPE: NULL OBJECT_INSTANCE_BEGIN: 1935545079720 NESTING_EVENT_ID: 62 NESTING_EVENT_TYPE: STATEMENT OPERATION: lock NUMBER_OF_BYTES: NULL FLAGS: NULL ps:_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息 _history表中记录每个线程应该执行完成的事件信息，但每个线程的事件信息只会记录10条，再多就覆盖， 如：SELECT thread_id,event_id,event_name,timer_wait FROM events_waits_history ORDER BY thread_id LIMIT 21; _history_long表中记录了所有线程的事件信息，总记录数是10000，超过就覆盖
2.常用sql语句
events_statements_summary_by_digest 每个模式的语句事件和摘要值
mysql> select * from events_statements_summary_by_digest \G
*************************** 12. row ***************************
SCHEMA_NAME: db4 //对应数据库
DIGEST: 98bf18ef8a1606965a0f2ff85fa992a3 //16进制
DIGEST_TEXT: COMMIT //SQL对应执行的语句
COUNT_STAR: 1575 //运行次数
SUM_TIMER_WAIT: 169951400000 //总时间
MIN_TIMER_WAIT: 16600000 //最小
AVG_TIMER_WAIT: 107900000 //平均
MAX_TIMER_WAIT: 11451500000 //最大
SUM_LOCK_TIME: 0
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2021-10-25 10:24:09
LAST_SEEN: 2021-10-25 16:18:02
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM
events_statements_summary_by_digest ORDER BY count_star DESC;
——哪类SQL执行最多。
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM
events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC;
——哪类SQL的平均响应时间最多
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM
events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC;
——哪类SQL排序记录数最多
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM
events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC;
——哪类SQL扫描次数最多,如：查询语句扫描全表
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES
FROM events_statements_summary_by_digest ORDER BY
SUM_CREATED_TMP_DISK_TABLES DESC;
——哪类SQL创建的临时表最多（SUM_CREATED_TMP_TABLES：内存临时表,SUM_CREATED_TMP_DISK_TABLES：磁盘临时表）
ps:MySQL临时表保存一些临时数据，只在当前连接可用，当关闭连接后，MySQL会自动删除表并释放所有空间，例子:BLOB和TEXT数据类型，由于BLOB数据类型是采用二进制的方式来存储数据，系统不会设置排序规则（采用二进制的方式来存储数据，系统没有字符集的要求）。而TEXT数据类型采用字符形式来存储数据，为此有字符集和排序规则的限制。因为这两种数据类型的容量比较大，为此对这些类型的字段进行操作时，并且需要隐式临时表时，直接用磁盘临时表，不会用到内存。即使没有超过max_heap_table_size的限制<他们的类型决定的>，(varchar,char都会用到内存临时表)。如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。从磁盘临时表与内存临时表的差异中大家可以看到，磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时，可以在硬盘上的一个空间作为其替代品，将内存中的部门数据转移到虚拟内存中。这个磁盘临时表也是相同的道理。
下列几种情况会创建临时表(仅参考，具体看情况，使用show table 也会创建临时表)
UNION查询；
用到TEMPTABLE算法或者是UNION查询中的视图；
ORDER BY和GROUP BY的子句不一样时；
表连接中，ORDER BY的列不是驱动表中的；
DISTINCT查询并且加上ORDER BY时；
SQL中用到SQL_SMALL_RESULT选项时；
FROM中的子查询；
子查询或者semi-join时创建的表；
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM
events_statements_summary_by_digest ORDER BY SUM_ROWS_SENT DESC;
——哪类SQL返回的结果集最多(累加,如返回16条结果，则三次查询返回的结果为16*3=48)
*注:这里是涉及的是Perfomance_schema数据库下的另一个表file_summary_by_instance(每个文件实例的事件)
mysql> SELECT * FROM performance_schema.file_summary_by_instance\G
...
*************************** 2. row ***************************
FILE_NAME: /var/mysql/share/english/errmsg.sys
EVENT_NAME: wait/io/file/sql/ERRMSG
EVENT_NAME: wait/io/file/sql/ERRMSG
OBJECT_INSTANCE_BEGIN: 4686193384
COUNT_STAR: 5
SUM_TIMER_WAIT: 13990154448
MIN_TIMER_WAIT: 26349624
AVG_TIMER_WAIT: 2798030607
MAX_TIMER_WAIT: 8150662536
MySQL 服务器使用多种技术来缓存从文件中读取的信息，从而避免 I/O 操作，因此您可能希望 I/O 事件的语句减少发生。您能够通过刷新缓存或重新启动服务器以重置其状态来确保 I/O操作减少。
SELECT FILE_NAME,EVENT_NAME,COUNT_STAR,MIN_TIMER_WAIT,MAX_TIMER_WAIT,COUNT_READ,MIN_TIMER_READ,MAX_TIMER_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,MIN_TIMER_WRITE,MAX_TIMER_WRITE,SUM_NUMBER_OF_BYTES_WRITE,COUNT_MISC,MIN_TIMER_MISC,MAX_TIMER_MISC
FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ +
SUM_NUMBER_OF_BYTES_WRITE DESC
——哪个表物理IO最多
*注:这里是涉及的是Perfomance_schema数据库下的另一个表table_io_waits_summary_by_table
SELECT object_name,count_read,count_write,count_fetch,sum_timer_wait
FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC;
——哪个表逻辑IO最多
ps:mysql同一条查询语句在不同时候执行涉及的io不同，第一次从表中读取物理IO,第二次内存中读取逻辑IO。物理IO/逻辑IO，InnoDB维护了一个逻辑空间为表空间，向上对接开发者，向下对接物理文件。
当创建表时，会自动为表创建一个对应表名的表空间，并在数据库目录下生成一个"表名.ibd"的表空间文件
具体请看:mysql底层存储及IO过程理解 - 六小扛把子 - 博客园 (cnblogs.com)
*注:这里是涉及的是Perfomance_schema数据库下的另一个表table_io_waits_summary_by_index_usage,即每个索引的table I/O await,对应于记录 tableI/Oawait 事件时使用的索引名称
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
——哪个索引访问最多
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;
——查看那个索引从来没有用过，ps:<>表示不等于，在SQL的一些版本中可写为!=
*注:这里是涉及的是Perfomance_schema数据库下的另一个表events_waits_summary_global_by_event_name,即每个事件的await事件
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;
——哪个等待事件消耗时间最多
**注:summary表提供所有事件的汇总信息，该组中的表以不同的方式汇总事件数据(如：按用户，按主机，按线程等待)。例如：要查看哪些instruments占用最多的事件，可以通过对events_waits_summary_global_by_event_name表的count_star或sum_timer_wait列进行查询
SELECT EVENT_NAME,COUNT_STAR FROM
events_waits_summary_global_by_event_name ORDER BY count_star DESC
LIMIT 10
——对事件的记录数执行count(*),事件记录的TIMER_WAIT列执行SUM(TIMER_WAIT)统计而来。
3.结合官网总结该板块下的一些表字段信息
Threads表
*************************** 33. row ***************************
THREAD_ID: 546 //唯一的线程标识符
NAME: thread/sql/one_connection //与服务器中的线程检测代码关联的名称。例如，thread/sql/one_connection对应于负责处理用户连接的代码中的线程功能，而thread/sql/main代 table 服务器的main()功能。
TYPE: FOREGROUND //线程类型FOREGROUND或BACKGROUND。用户连接线程是前台线程。与内部服务器活动关联的线程是后台线程。示例包括内部InnoDB线程，将信息发送到副本的“ binlog 转储”线程以及复制 I/O 和 SQL 线程。
PROCESSLIST_ID: 520 //对于INFORMATION_SCHEMA.PROCESSLISTtable 中显示的线程，该值与该 table 的ID列中显示的值相同，该值在SHOW PROCESSLIST输出的Id列中显示，并由该线程内的CONNECTION_ID()函数返回。对于后台线程(与用户连接无关的线程)，PROCESSLIST_ID是NULL，因此值不是唯一的。
PROCESSLIST_USER: root //与前台线程关联的用户，NULL为后台线程
PROCESSLIST_HOST: localhost //与前台线程关联的 Client 端的主机名，NULL table后台线程。
PROCESSLIST_DB: performance_schema //线程的默认数据库，如果未选择则为NULL。
PROCESSLIST_COMMAND: Query //对于前台线程，该线程代 tableClient 端执行的命令类型；如果会话空闲，则返回Sleep。
PROCESSLIST_TIME: 0 //线程处于其当前状态的时间(以秒为单位)。对于副本 SQL 线程，该值是最后一个复制事件的时间戳与副本主机的实时之间的秒数
PROCESSLIST_STATE: Sending data //指示线程正在执行的操作，事件或状态。如果值为NULL，则该线程可能对应于一个空闲的 Client 端会话，或者该线程正在执行的工作没有阶段化。
PROCESSLIST_INFO: select * from threads //线程正在执行的语句，如果不执行任何语句，则返回NULL。该语句可能是发送到服务器的那条语句，或者是最内部的语句(如果该语句执行其他语句)。
PARENT_THREAD_ID: NULL //如果此线程是子线程(由另一个线程生成)，则这是生成线程的THREAD_ID值。
ROLE: NULL
INSTRUMENTED: YES //是否检测线程执行的事件。该值为YES或NO。
HISTORY: YES //是否记录线程的历史事件。该值为YES或NO。
CONNECTION_TYPE: TCP/IP //用于构建连接的协议，或用于后台线程的NULL。允许的值为TCP/IP(未加密构建的 TCP/IP 连接)，SSL/TLS(通过加密构建的 TCP/IP 连接)，Socket(Unix 套接字文件连接)，Named Pipe(Windows 命名管道连接)和Shared Memory(Windows 共享内存连接)。
THREAD_OS_ID: 11092 //基础 os 定义的线程或任务标识符(如果有)：
当 MySQL 线程在其生命周期内与同一 os 线程关联时，THREAD_OS_ID包含 os 线程 ID。
当 MySQL 线程在其生命周期内未与同一 os 线程关联时，THREAD_OS_ID包含NULL。
对于 Windows，THREAD_OS_ID对应于在进程资源 Management 器
对于 Linux，THREAD_OS_ID对应于gettid()函数的值
ps:百度：博客园：前台线程和后台线程区别与使用技巧线程有两种：前台线程和后台线程。区别是：应用程序必须运行完所有的前台线程才可以退出；而对于后台线程，应用程序则可以不考虑其是否已经运行完毕而直接退出，所有的后台线程在应用程序退出时都会自动结束。
4.后言
该项目只是有限的利用了性能板块的监控功能，仍有许多功能未实现，可以参考官方文档继续对其扩展。
扫一扫
1815
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
