performance_schema 笔记（一）—— 简介与快速入门

标签：入门 innodb file performance schema events wait

系列文章参考自《MySQL 性能优化金字塔法则》，删除了书里重复说明和过于复杂的一些解释，完整版请参考原书。

第一篇将简单介绍performance_schema是什么、有什么用、用法快速入门，它由哪些表组成以及这些表的用途。

一、performance_schema简介

performance schema 是运行在较低级别的用于监控MySQL运行过程中的资源消耗、等待等情况的功能特性。

1. 特点如下

1. 提供了一种在数据库运行时实时检查server的内部执行情况的方法。其中的表使用performance_schema存储引擎，主要关注数据库运行过程中的性能相关的数据，而information_schema主要关注server运行过程中的元数据信息

2. 通过监视server的事件来实现监视其内部运行情况。"事件"指MySQL某些活动中所做的事情，对应的时间、资源消耗，活动执行次数等，利用这些信息来判断相关资源消耗在了哪里。

3. 收集的事件数据只记录在本地performance_schema库的表中，这些表中数据发生变化时不会被写入binlog，也不会被复制到其他server。可以使用SQL语句查询、更新这些表（配置表的更改会立即生效，这会影响数据收集）。

4. performance_schema的表中的数据不会持久化到磁盘，而是保存在内存中，一旦服务器重启，这些数据会丢失。

5. 对于MySQL支持的所有平台，事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有差异。

2. 实现机制遵循以下设计目标

1. 启用后会持续进行监测，但开销很小，不会导致server行为（线程调度机制、执行计划等）变化，更不会导致server不可用。即使对某事件执行监测失败，也不会影响server正常运行。

2. 如果在开始收集事件数据时正好有其他线程在查询这些事件信息，查询会优先于事件数据收集，因为事件数据的收集是一个持续不断的过程，而查询这些事件数据仅仅只是在需要查看的时候才进行，也可能某些事件数据永远都不会被查询。

3. 如果需要，可以很容易地添加新的instruments监测点；如果instruments的代码发生了变更，旧的代码还可以继续工作。

二、使用快速入门

1. 检查当前版本是否支持performance_schema

performance_schema被视为存储引擎。如果支持，则应该在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES。

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE ='PERFORMANCE_SCHEMA';
+--------------------+---------+--------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT            | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+--------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema | NO           | NO   | NO         |
+--------------------+---------+--------------------+--------------+------+------------+
1 row in set (0.00 sec)

或者

show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        
| Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
......
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             
| NO           | NO   | NO         |
......
9 rows in set (0.00 sec)

performance_schema在5.6及之前的版本中默认没有启用，从5.7开始修改为默认启用。

2. 启用performance_schema

要显式启用或关闭时，需要在my.cnf中配置参数performance_schema=ON|OFF

[mysqld]
performance_schema = ON  # 注意：该参数为只读参数，需要在实例启动之前设置才生效

mysqld启动后，查看启用是否生效（如果值为OFF表示在启用时发生某些错误，可以查看错误日志进行排查）

SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

三、 performance_schema下的表

1. 查看方法

可以通过从INFORMATION_SCHEMA.tables表查询有哪些performance_schema引擎的表：

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
WHERE TABLE_SCHEMA ='performance_schema' and engine='performance_schema';
+------------------------------------------------------+
| TABLE_NAME                                           |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
......
| users                                                |
| variables_by_thread                                  |
+------------------------------------------------------+
87 rows in set (0.00 sec)

也可以直接在performance_schema库下使用show tables语句：

use performance_schema
Database changed

show tables from performance_schema;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
......
| users                                                |
| variables_by_thread                                  |
+------------------------------------------------------+
87 rows in set (0.00 sec)

可以看到在MySQL 5.7.17 版本中，performance_schema下一共有87张表，它们都是存放什么数据呢？如何用来查询想要的数据呢？先别着急，我们先来看看这些表是如何分类的。

2. performance_schema表的分类

performance_schema库下的表可以按照监视不同的维度进行了分组。例如：按照不同数据库对象进行分组，按照不同的事件类型进行分组，或在按照事件类型分组之后再进一步按照帐号、主机、程序、线程、用户等细分。

语句事件记录表：记录语句事件信息。

包括：events_statements_current（当前语句事件表）、events_statements_history（历史语句事件表）和events_statements_history_long（长语句历史事件表）、以及一些聚合后的summary摘要表。

*_current表 -- 类似v$session

*_history表 -- 类似v$ash，记录每个线程已经执行完成的事件信息，但每个线程的信息只记录10条，再多就会被覆盖掉

*_history_long表 -- 类似dba_ash，记录所有线程的事件信息，总记录数量是10000行，超过会被覆盖掉

*_summary表 -- 提供所有事件的汇总信息，还可以根据account、host、program、thread、user)、global等再进行细分

show tables like 'events_statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (%statement%)         |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_program               |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
+----------------------------------------------------+
11 rows in set (0.00 sec)

等待事件记录表：与语句事件类型的相关记录表类似

show tables like 'events_wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (%wait%)         |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
+-----------------------------------------------+
12 rows in set (0.01 sec)

阶段事件记录表：记录语句执行阶段事件的表，与语句事件类型的相关记录表类似

show tables like 'events_stage%';
+------------------------------------------------+
| Tables_in_performance_schema (%stage%)         |
+------------------------------------------------+
| events_stages_current                          |
| events_stages_history                          |
| events_stages_history_long                     |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name    |
| events_stages_summary_by_thread_by_event_name  |
| events_stages_summary_by_user_by_event_name    |
| events_stages_summary_global_by_event_name     |
+------------------------------------------------+
8 rows in set (0.00 sec)

事务事件记录表：记录事务相关事件的表，与语句事件类型的相关记录表类似

show tables like 'events_transaction%';
+------------------------------------------------------+
| Tables_in_performance_schema (%transaction%)         |
+------------------------------------------------------+
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
+------------------------------------------------------+
8 rows in set (0.00 sec)

监视文件系统层调用的表

*_instance表 -- 记录了哪些类型的对象会被检测。这些对象被server使用时，会在该表中产生一条事件记录

show tables like '%file%';
+---------------------------------------+
| Tables_in_performance_schema (%file%) |
+---------------------------------------+
| file_instances                        |
| file_summary_by_event_name            |
| file_summary_by_instance              |
+---------------------------------------+
3 rows in set (0.01 sec)

监视内存使用的表

show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name    |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name    |
| memory_summary_global_by_event_name     |
+-----------------------------------------+
5 rows in set (0.01 sec)

动态对performance_schema进行配置的配置表：

show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+----------------------------------------+
5 rows in set (0.00 sec)

现在我们已经大概知道了performance_schema中的主要表的分类，如何使用来为我们提供需要的性能事件数据呢？下面介绍如何通过配置表来配置与使用performance_schema。

四、 performance_schema简单配置与使用

数据库刚初始化并启动时，默认不会收集所有的事件，需要按需进行设置。

可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异）

打开等待事件的采集器配置项（instruments）开关，需要修改setup_instruments 配置表中对应的采集器配置项

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 323  Changed: 0  Warnings: 0

打开等待事件的保存表配置项（consumers）开关，修改修改setup_consumers 配置表中对应的配置项

UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

配置好之后，我们就可以通过查询events_waits_current表查看server当前正在做什么，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情），一旦线程完成工作，该表中不会再记录该线程的事件信息：

SELECT * FROM events_waits_current limit 1\G
*************************** 1. row ***************************
        THREAD_ID: 4
         EVENT_ID: 60
     END_EVENT_ID: 60
       EVENT_NAME: wait/synch/mutex/innodb/log_sys_mutex
           SOURCE: log0log.cc:1572
      TIMER_START: 1582395491787124480
        TIMER_END: 1582395491787190144
       TIMER_WAIT: 65664
            SPINS: NULL
    OBJECT_SCHEMA: NULL
      OBJECT_NAME: NULL
       INDEX_NAME: NULL
      OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 955681576
 NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
        OPERATION: lock
  NUMBER_OF_BYTES: NULL
            FLAGS: NULL
1 row in set (0.02 sec)

该事件信息表示线程ID为4的线程正在等待innodb存储引擎的log_sys_mutex锁，这是innodb存储引擎的一个互斥锁，等待时间为65664皮秒。

再看看历史表events_waits_history 中记录了什么，可以看到记录了每个线程已经执行完成的事件信息，但每个线程的信息只记录10条。

SELECT THREAD_ID,EVENT_ID,EVENT_NAME,TIMER_WAIT FROM events_waits_history ORDER BY THREAD_ID limit 21;
+-----------+----------+------------------------------------------+------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                               | TIMER_WAIT |
+-----------+----------+------------------------------------------+------------+
|         4 |      341 | wait/synch/mutex/innodb/fil_system_mutex |      84816 |
|         4 |      342 | wait/synch/mutex/innodb/fil_system_mutex |      32832 |
|         4 |      343 | wait/io/file/innodb/innodb_log_file      |  544126864 |
......
|         4 |      348 | wait/io/file/innodb/innodb_log_file      |  693076224 |
|         4 |      349 | wait/synch/mutex/innodb/fil_system_mutex |      65664 |
|         4 |      350 | wait/synch/mutex/innodb/log_sys_mutex    |      25536 |
|        13 |     2260 | wait/synch/mutex/innodb/buf_pool_mutex   |     111264 |
|        13 |     2259 | wait/synch/mutex/innodb/fil_system_mutex |    8708688 |
......
|        13 |     2261 | wait/synch/mutex/innodb/flush_list_mutex |     122208 |
|        15 |      291 | wait/synch/mutex/innodb/buf_dblwr_mutex  |      37392 |
+-----------+----------+------------------------------------------+------------+
21 rows in set (0.00 sec)

*_summary表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（按用户、主机、线程等）。

例如：要查看哪些事件项占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询，这两列是对事件的记录数执行COUNT(*)、对事件记录的TIMER_WAIT列执行SUM(TIMER_WAIT)统计而来。

SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name \
ORDER BY COUNT_STAR DESC LIMIT 10;
| EVENT_NAME                                        | COUNT_STAR |
+---------------------------------------------------+------------+
| wait/synch/mutex/mysys/THR_LOCK_malloc            |       6419 |
| wait/io/file/sql/FRM                              |        452 |
| wait/synch/mutex/sql/LOCK_plugin                  |        337 |
| wait/synch/mutex/mysys/THR_LOCK_open              |        187 |
| wait/synch/mutex/mysys/LOCK_alarm                 |        147 |
| wait/synch/mutex/sql/THD::LOCK_thd_data           |        115 |
| wait/io/file/myisam/kfile                         |        102 |
| wait/synch/mutex/sql/LOCK_global_system_variables |         89 |
| wait/synch/mutex/mysys/THR_LOCK::mutex            |         89 |
| wait/synch/mutex/sql/LOCK_open                    |         88 |
+---------------------------------------------------+------------+

SELECT EVENT_NAME,SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name\
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
+----------------------------------------+----------------+
| EVENT_NAME                             | SUM_TIMER_WAIT |
+----------------------------------------+----------------+
| wait/io/file/sql/MYSQL_LOG             |     1599816582 |
| wait/synch/mutex/mysys/THR_LOCK_malloc |     1530083250 |
| wait/io/file/sql/binlog_index          |     1385291934 |
| wait/io/file/sql/FRM                   |     1292823243 |
| wait/io/file/myisam/kfile              |      411193611 |
| wait/io/file/myisam/dfile              |      322401645 |
| wait/synch/mutex/mysys/LOCK_alarm      |      145126935 |
| wait/io/file/sql/casetest              |      104324715 |
| wait/synch/mutex/sql/LOCK_plugin       |       86027823 |
| wait/io/file/sql/pid                   |       72591750 |
+----------------------------------------+----------------+
# 结果表明，THR_LOCK_malloc互斥事件是最热的（THR_LOCK_malloc仅在DEBUG版本中存在,GA版本不存在）

*_instance表记录了哪些类型的对象会被检测。这些对象被server使用时，会在该表中产生一条事件记录

例如，file_instances表列出了文件I/O操作及其关联文件名：

SELECT * FROM file_instances limit 20;
+------------------------------------------------------+--------------------------------------+------------+
| FILE_NAME                                            | EVENT_NAME                           | OPEN_COUNT |
+------------------------------------------------------+--------------------------------------+------------+
| /home/mysql/program/share/english/errmsg.sys         | wait/io/file/sql/ERRMSG              
|          0 |
| /home/mysql/program/share/charsets/Index.xml         | wait/io/file/mysys/charset           
|          0 |
| /data/mysqldata1/innodb_ts/ibdata1                   | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/innodb_log/ib_logfile0              | wait/io/file/innodb/innodb_log_file  |          2 |
| /data/mysqldata1/innodb_log/ib_logfile1              | wait/io/file/innodb/innodb_log_file  |          2 |
| /data/mysqldata1/undo/undo001                        | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/undo/undo002                        | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/undo/undo003                        | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/undo/undo004                        | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/multi_master/test.ibd        | wait/io/file/innodb/innodb_data_file |          1 |
| /data/mysqldata1/mydata/mysql/engine_cost.ibd        | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/gtid_executed.ibd      | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/help_category.ibd      | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/help_keyword.ibd       | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/help_relation.ibd      | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/help_topic.ibd         | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/innodb_index_stats.ibd | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/innodb_table_stats.ibd | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/plugin.ibd             | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysqldata1/mydata/mysql/server_cost.ibd        | wait/io/file/innodb/innodb_data_file |          3 |
+------------------------------------------------------+
20 rows in set (0.00 sec)

本篇只是关于performance_schema最基础的一些介绍，如何详细配置，参考下篇。

参考：《MySQL 性能优化金字塔法则》

标签：入门,innodb,file,performance,schema,events,wait
From： https://blog.51cto.com/u_13631369/6202562