MySQL-Performance_Schema

Performance_Schema 性能相关信息库

Chapter 25 MySQL Performance Schema
https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html

---

查看是否支持 performance_schema

performance_schema 是 MySQL 中的一种存储引擎，通过 show engines 查看 MySQL 版本是否支持 performance_schema，或者直接查看 INFORMATION_SCHEMA.ENGINES 表
PERFORMANCE_SCHEMA 对应的 Support 字段为 YES 表示当前的数据库版本是支持 performance_schema 的

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.02 sec)

---

开启 performance_schema

performance_schema 在 5.7.x 及以上版本中默认启用，在 5.6.x 及以下版本中默认关闭

1、在 my.cnf 中配置开启

[mysqld]
performance_schema=ON

2、通过变量 performance_schema 查看是否开启

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

---

配置

语句事件记录表与等待事件记录表一样，也有三张表，这些表记录了当前与最近在 MySQL 实例中发生了哪些语句事件，时间消耗是多少。记录了各种各样的语句执行产生的语句事件信息。

语句事件相关配置中，setup_instruments 表中 statement/* 开头的所有 instruments 配置默认开启， setup_consumers 表中 statements 相关的 consumers 配置默认开启了 events_statements_current、events_statements_history、statements_digest（对应 events_statements_summary_by_digest 表）但没有开启 events_statements_history_long

setup_instruments 采集项开关

setup_instruments 表中是各种指标采集项的开关，默认全部开启。

mysql> select * from performance_schema.setup_instruments where name like 'statement%' limit 5;
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| statement/sql/select       | YES     | YES   |
| statement/sql/create_table | YES     | YES   |
| statement/sql/create_index | YES     | YES   |
| statement/sql/alter_table  | YES     | YES   |
| statement/sql/update       | YES     | YES   |
+----------------------------+---------+-------+

---

setup_consumers 记录项开关

setup_consumers 表中是各种事件记录项的开关，只开启指标采集，不开启记录，最终统计表中也没有数据

mysql> select * from performance_schema.setup_consumers where name like '%statement%' limit 5;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | NO      |
| statements_digest              | YES     |
+--------------------------------+---------+

---

语句事件记录表

语句事件记录表也有三张表，这些表记录了当前与最近在 MySQL 实例中发生了哪些语句事件，时间消耗是多少。记录了各种各样的语句执行产生的语句事件信息。
包括：

• events_statements_current 当前语句事件表
• events_statements_history 历史语句事件表
• events_statements_history_long 长语句历史事件表

上面三张表结构相同
以及多个 summary 聚合表

setup_instruments 表中 statement/* 开头的所有 instruments 配置默认开启
setup_consumers 表中 statements 相关的 consumers 配置默认开启了 events_statements_current，events_statements_history，statements_digest（对应events_statements_summary_by_digest表），但没有开启 events_statements_history_long

手动开启 events_statements_history_long 记录：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' where name = 'events_statements_history_long';

events_statements_current 表包含当前语句事件，每个线程只显示一行最近被监视语句事件的当前状态。

events_statements_history 表包含每个线程最新的 N（默认值10）个语句事件。 在server启动时，N的值会自动调整。 要显式设置N的大小，可以在server启动之前设置系统变量 performance_schema_events_statements_history_size 的值。 statement事件执行完成时才会添加到该表中。 当添加新事件到该表时，如果对应线程的事件在该表中的配额已满，则会丢弃对应线程的较旧的事件

events_statements_history_long 表包含每个线程最近的 N（默认10000）个语句事件。在server启动时，N的值会自动调整。 要显式设置N的大小，可以在server启动之前设置系统变量 performance_schema_events_statements_history_long_size 的值。 statement事件需要执行结束时才会添加到该表中。 当添加新事件到该表时，如果该表的全局配额已满，则会丢弃该表中较旧的事件

EVENT_NAME 产生事件的监视仪器的名称。该列值来自 setup_instruments 表的NAME值。对于SQL语句，EVENT_NAME值最初的instruments是statement/com/Query，直到语句被解析之后，会更改为更合适的具体instruments名称，如：statement/sql/insert
TIMER_START, TIMER_END, TIMER_WAIT 事件的时间信息。这些值的单位是皮秒（万亿分之一秒）。 TIMER_START 和 TIMER_END 值表示事件的开始时间和结束时间。TIMER_WAIT是事件执行消耗的时间（持续时间）

查找超过5秒的慢查询语句

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as duration_seconds, SQL_TEXT        
FROM performance_schema.events_statements_history_long 
WHERE timer_wait > 5000000000000;

---