千金良方：MySQL性能优化金字塔法则
上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
上一章目录下一章

4.2 performance_schema使用快速入门

通过上面介绍，相信你对于什么是performance_schema这个问题了解得更清晰了。下面开始介绍performance_schema的使用。

4.2.1 检查当前数据库版本是否支持

performance_schema被视为存储引擎，如果该引擎可用，则应该在INFORMATION_SCHEMA.ENGINES表或show engines语句的输出中可以看到它的Support字段值为YES，如下所示。

使用INFORMATION_SCHEMA.ENGINES表来查询数据库实例是否支持PERFORMANCE_SCHEMA存储引擎。

mysql> 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语句查询数据库实例是否支持PERFORMANCE_SCHEMA存储引擎。

mysql> 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对应的Support字段值为YES时，就表示当前的数据库版本是支持performance_schema的。但确认了数据库实例支持performance_schema存储引擎就可以使用了吗？NO，很遗憾，performance_schema在MySQL 5.6及之前的版本中默认没有启用，在MySQL 5.7及之后的版本中才修改为默认启用。下面我们来看看如何设置performance_schema默认启用。

4.2.2 启用performance_schema

如果要显式启用或关闭performance_schema，则需要使用参数performance_schema=ON|OFF来设置，并在my.cnf中进行配置。

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

mysqld启动之后，通过如下语句查看performance_schema启用是否生效（值为ON表示performance_schema已初始化成功且可以使用了；值为OFF表示在启用performance_schema时发生某些错误，可以查看错误日志进行排查）。

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set（0.00 sec）

现在，可以通过查询INFORMATION_SCHEMA.TABLES表中与performance_schema存储引擎相关的元数据，或者在performance_schema库下使用show tables语句来了解其存在哪些表。

通过INFORMATION_SCHEMA.TABLES表查询有哪些performance_schema引擎表。

mysql>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）

使用show tables语句来查询有哪些performance_schema引擎表。

mysql> use performance_schema
Database changed
mysql> 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个表，那么这些表都用于存放什么数据呢？我们如何使用它们来查询数据呢？别着急，先来看看这些表是如何分类的。

4.2.3 performance_schema表的分类

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

下面介绍按照事件类型分组记录性能事件数据的表。

● 语句事件记录表：记录语句事件信息的表，包括：events_statements_current（当前语句事件表）、events_statements_history（历史语句事件表）、events_statements_history_long（长语句历史事件表）以及一些summary表（聚合后的摘要表）。其中，summary表还可以根据账号（account）、主机（host）、程序（program）、线程（thread）、用户（user）和全局（global）再进行细分。

mysql> 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）

● 等待事件记录表：与语句事件记录表类似。

mysql> 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）

● 阶段事件记录表：记录语句执行阶段事件的表，与语句事件记录表类似。

mysql> 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）

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

mysql> 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）

● 监视文件系统层调用的表：

mysql> 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）

● 监视内存使用的表：

mysql> 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进行配置的配置表：

mysql> 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。

4.2.4 performance_schema简单配置与使用

当数据库初始化完成并启动时，并非所有的instruments（在采集配置项的配置表中，每一项都有一个开关字段，或为YES，或为NO）和consumers（与采集配置项类似，也有一个对应的事件类型保存表配置项，为YES表示对应的表保存性能数据，为NO表示对应的表不保存性能数据）都启用了，所以默认不会收集所有的事件，可能你想检测的事件并没有打开，需要进行设置。可以使用如下两条语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异），我们以配置监测等待事件数据为例进行说明。

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

mysql> 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

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

mysql> 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

配置好之后，我们就可以查看Server当前正在做什么了。可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）。

mysql> 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皮秒（＊_ID列表示事件来自哪个线程、事件编号是多少；EVENT_NAME表示检测到的具体内容；SOURCE 表示这个检测代码在哪个源文件中以及行号；计时器字段 TIMER_START、TIMER_END、TIMER_WAIT分别表示该事件的开始时间、结束时间和总的花费时间，如果该事件正在运行而没有结束，那么TIMER_END和TIMER_WAIT的值显示为NULL。注：计时器统计的值是近似值，并不是完全精确的）

*_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中就不会再记录该线程的事件信息了。*_history表中记录每个线程已经执行完成的事件信息，但每个线程的事件信息只记录10条，再多就会被覆盖掉。*_history_long表中记录所有线程的事件信息，但总记录数量是10000行，超过会被覆盖掉。现在我们查看历史表events_waits_history中记录了什么。

mysql>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表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（如：按用户、按主机、按线程等汇总）。例如：要查看哪些instruments占用的时间最多，则可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来的）。

mysql> 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 |
+----------------------------------------------------+------------+
mysql>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操作及其关联文件名。

mysql> 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/nnodb_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来查询性能数据，而是使用sys schema下的视图，为什么不直接学习sys schema呢？那你知道sys schema中的数据是从哪里来的吗？sys schema中的数据实际上主要是从performance_schema、information_schema中获取的，所以要想玩转sys schema，全面了解performance_schema非常有必要。另外，对于sys schema、information_schema甚至mysql schema，我们在后续章节中也会进行介绍。

上一章目录下一章