performance_schema详解 - xiaoboluo768/mysql-system-schema GitHub Wiki

performance_schema详解

1、简介

2、配置

关于performance_schema的配置，按照官方文档的顺序梳理为三个部分，即编译安装时指定的编译配置选项、启动server时指定的配置选项、以及在server运行期间给定的配置选项分别进行讲解

2.1. 编译安装配置

2.2. 启动时配置

2.3. 运行时配置

performance_schema使用如下几张配置表来配置相关监视功能配置：

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME LIKE 'setup%';

+-------------------+
| TABLE_NAME        |
+-------------------+
| setup_actors      |
| setup_consumers   |
| setup_instruments |
| setup_objects     |
| setup_timers      |
+-------------------+
5 rows in set (0.01 sec)

通过这些表你可以配置哪些instruments需要打开，哪些consumers记录表需要打开，instruments使用的时间单位，以及查询相关设置信息等待，如果您具有UPDATE这些表的权限，则可以使用UPDATE语句来修改这些配置表中的配置信息
- 另外，还有一张用于设置线程对象的配置表：threads，该表是在线程创建的时候根据前面5张配置表中的设置自动生成的，生成之后可以在threads表中单独对某个线程进行配置

2.3.1. performance_schema事件计时器配置

2.3.2. performance_schema事件过滤配置

2.3.3. Pre-Filtering 事件配置

2.3.3.1. Pre-Filtering by instruments

2.3.3.2. Pre-Filtering by objects

2.3.3.3. Pre-Filtering by threads

2.3.3.4. Pre-Filtering by consumers

2.3.3.5. setup_instruments和setup_consumers配置协作示例

3、performance_schema表描述

performance_schema数据库的名称是小写的，表中的表名也是小写的。查询应该以小写形式指定名称，除非lower_case_table_names参数设置为ON忽略大小写
performance_schema数据库中的许多表都是只读的，无法修改，例如对 TRUNCATE TABLE setup_instruments;时会报错 ERROR 1683 (HY000): Invalid performance_schema usage.
一些配置表具的开关列可以使用update语句修改，并且修改之后立即影响performance_schema（如：setup_instruments表中的enabled字段），有些表允许插入或删除行，甚至允许使用truncate语句来清除之前收集的事件信息，如：events_waits_*表(consumers表)
摘要表也可以使用truncate语句，但是这些表中的行不会被删除，而仅仅只是将一些聚合计数列给重置为0或者null值，这在你想单独查看某类操作的汇总数据时可能有用
要访问这些表，也需要与其他业务表类似地需要对用户帐号授予相应权限，规则如下：
- 要从perform_schema表检索数据，您必须至少具有SELECT权限
- 要修改某些表的可修改的列，您必须具有UPDATE权限
- 要对某些可执行truncate的表执行truncate语句，你必须具有DROP特权
- 注意：对performance_schema只允许某些特定权限被授予，并不是所有权限都可以被赋予（使用grant all on *.*除外），所以尝试使用GRANT ALL语句只对performance_schema库授予数据库或表的权限时会报错：

# 对performance_schema下所有表或某个指定表授予all权限将报错
mysql> GRANT ALL ON performance_schema.* TO 'u1'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'

mysql> GRANT ALL ON performance_schema.setup_instruments TO 'u2'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'

# 你需要显式指定具体的权限：
mysql> GRANT SELECT ON performance_schema.* TO 'u1'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT SELECT, UPDATE ON performance_schema.setup_instruments TO 'u2'@'localhost';
Query OK, 0 rows affected (0.02 sec)

参考链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-table-descriptions.html

3.1. setup_*配置表

这些表提供运行时配置，根据你的需要，自行开关相应的instruments、consumers，如果你具有这些表的update权限，就可以使用update语句来修改这些表中的相关字段
- setup_actors：配置如何监控、监控哪些用户的前台线程
- setup_consumers：配置instruments采集信息的发送和存储目的地
- setup_instruments：配置哪些事件和对象可以被监控
- setup_objects：配置哪些对象可以被监控
- setup_timers：当前事件收集时间信息的定时器
参考链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-setup-tables.html

3.1.1. setup_actors

3.1.2. setup_consumers

3.1.3. setup_instruments

3.1.4. setup_objects

3.1.5. setup_timers

3.2. instance tables

instance表记录了哪些类型的对象被检测。这些表中记录了事件名称（提供收集功能的instruments名称）及其一些解释性的状态信息（例如：file_instances表中的FILE_NAME文件名称和OPEN_COUNT文件打开次数），instance表主要有如下几个：
- cond_instances：wait sync相关的condition对象实例
- file_instances：文件对象实例
- mutex_instances：wait sync相关的Mutex对象实例
- rwlock_instances：wait sync相关的lock对象实例
- socket_instances：活跃连接实例
这些表列出了instruments的wait sync相关的对象、文件、连接。其中wait sync相关的对象类型有三种(详见1.2.3.3.1. "Pre-Filtering" by instruments小节wait/sync部分)：cond、mutex、rwlock。每个实例表都有一个EVENT_NAME或NAME列，用于显示与每行记录相关联的instruments名称。instruments名称可能具有多个部分并形成层次结构，详见1.2.3.3.1. "Pre-Filtering" by instruments小节
mutex_instances.LOCKED_BY_THREAD_ID和rwlock_instances.WRITE_LOCKED_BY_THREAD_ID列对于排查性能瓶颈或死锁问题至关重要。有关如何用于排查性能问题演示示例详见1.6. 性能分析思路小节
PS：对于mutexes、conditions和rwlocks，在运行时虽然允许修改配置，且配置能够修改成功，但是有一部分instruments不生效，需要在启动时配置才会生效，如果你尝试着使用一些应用场景来追踪锁信息，你可能在这些instance表中无法查询到相应的示例信息。
参考链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-instance-tables.html

3.2.1. cond_instances

3.2.2. file_instances

3.2.3. mutex_instances

3.2.4. rwlock_instances

3.2.5. socket_instances

3.3. wait event tables

performance_schema中instruments waits，等待事件消耗了多少时间。在事件的层次结构中，等待事件嵌套在阶段事件、语句事件、事务事件中。等待事件记录在如下几张表中（不包含summary表）
- events_waits_current表：记录当前正在执行的等待事件的表，每个线程只记录1行记录
- events_waits_history表：记录已经执行完的最近的等待事件的表，默认每个线程只记录10行记录
- events_waits_history_long表：记录已经执行完的全局最近的等待事件的表，总记录行数默认限制为10000行
等待事件相关配置中，setup_instruments表中wait/synch/sxlock/*开头的instruments配置默认没有开启，setup_consumers表中waits相关的consumers配置默认没有开启，setup_timers表中默认配置的计时器单位为cycle
参考链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-wait-tables.html

3.3.1. events_waits_current

3.3.2. events_waits_history

3.3.3. events_waits_history_long

3.4. stage event tables

performance_schema stage instruments记录语句执行过程中的步骤，例如：parsing 、opening tables、filesort操作等。阶段事件与SHOW PROCESSLIST或INFORMATION_SCHEMA.PROCESSLIST表中查看到的相同
在事件层次结构中，等待事件可能嵌套在阶段事件、语句事件、事务事件、等待事件中
记录等待事件的表有如下几张：
- events_stages_current：当前阶段事件记录表
- events_stages_history：每个线程最近的stage事件历史表
- events_stages_history_long：全局最新的阶段事件历史表（跨所有线程）
阶段事件相关配置中，setup_instruments表中stage/*开头的绝大多数instruments配置默认没有开启（少数stage/开头的instruments除外，如DDL语句执行过程的stage/innodb/alter开头的instruments默认开启的），setup_consumers表中stages相关的consumers配置默认没有开启，setup_timers表中默认配置的计时器单位为NANOSECOND
参考链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-stage-tables.html

3.4.1. events_stages_current

3.4.2. events_stages_history

3.4.3. events_stages_history_long

3.5. statement event tables

3.5.1. events_statements_current

3.5.2. events_statements_history

3.5.3. events_statements_history_long

3.5.4.prepared_statements_instances

3.6. transacton tables

3.6.1. events_transactions_current

3.6.2. events_transactions_history

3.6.3. events_transactions_history_long

3.7. connection tables

3.7.1. accounts

3.7.2. hosts

3.7.3. users

3.8. connection attribute tables

3.8.1. session_account_connect_attrs

3.8.2. session_connect_attrs

3.9. user variables tables

3.9.1. user_variables_by_thread

3.10. replication tables

3.10.1. replication_applier_configuration

3.10.2.replication_applier_status

3.10.3.replication_applier_status_by_coordinator

3.10.4.replication_applier_status_by_worker

3.10.5.replication_connection_configuration

3.10.6.replication_connection_status

3.10.7.replication_group_member_stats

3.10.8.replication_group_members

3.11. lock tables

performance_schema通过如下表来记录相关的锁信息：
- metadata_locks：元数据锁的持有和请求记录
- table_handles：表锁的持有和请求记录

3.11.1. metadata_locks

3.11.2. table_handles

3.12. system variables tables

3.12.1. global_variables与session_variables

3.13. status variables tables

3.13.1. global_status与session_status

3.14. summary tables

3.14.1. events_waits_summary_*表与objects_summary_global_by_type

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合等待事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意：等待事件的采集功能有一部分默认是禁用的，需要的时候可以通过setup_instruments和setup_objects表动态开启（但对于锁、互斥、condition相关的等待事件，需要在server启动之前配置才会生效）

admin@localhost : performance_schema 06:17:11> show tables like '%events_waits_summary%';
+-------------------------------------------------------+
| Tables_in_performance_schema (%events_waits_summary%) |
+-------------------------------------------------------+
| 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            |
+-------------------------------------------------------+
6 rows in set (0.00 sec)

admin@localhost : performance_schema 06:47:55> show tables like '%objects_%';
+-------------------------------------------+
| Tables_in_performance_schema (%objects_%) |
+-------------------------------------------+
| objects_summary_global_by_type            |
+-------------------------------------------+
1 row in set (0.00 sec)

3.14.2. events_stages_summary_*表

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合阶段事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，阶段事件有一部分是默认禁用的，阶段事件的instruments也有一部分是开启的，所以events_stages_xxx consumers在未开启的情况下，stage的摘要表中也会有一些stage事件信息

admin@localhost : performance_schema 06:23:02> show tables like '%events_stages_summary%';
+--------------------------------------------------------+
| Tables_in_performance_schema (%events_stages_summary%) |
+--------------------------------------------------------+
| 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            |
+--------------------------------------------------------+
5 rows in set (0.00 sec)

3.14.3. events_statements_summary_*表与prepared_statements_instances

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合语句事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，语句事件instruments默认全部开启，但是consumers配置中events_statements_history_long默认关闭，所以，语句事件摘要表中默认会记录所有的语句事件聚合信息

admin@localhost : performance_schema 06:27:58> show tables like '%events_statements_summary%';
+------------------------------------------------------------+
| Tables_in_performance_schema (%events_statements_summary%) |
+------------------------------------------------------------+
| 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            |
+------------------------------------------------------------+
7 rows in set (0.00 sec)

admin@localhost : performance_schema 06:28:48> show tables like '%prepare%';
+------------------------------------------+
| Tables_in_performance_schema (%prepare%) |
+------------------------------------------+
| prepared_statements_instances            |
+------------------------------------------+
1 row in set (0.00 sec)

3.14.4. events_transactions_summary_*表

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合事务事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，事务事件instruments只有一个transaction，默认禁用，对应的consumers也默认禁用，所以默认情况下事务事件摘要表中只有对应连接的一个统计行，但是相关聚合字段值都为0，需要启用之后才会进行收集并在摘要表中进行聚合

admin@localhost : performance_schema 06:37:45> show tables like '%events_transactions_summary%';
+--------------------------------------------------------------+
| Tables_in_performance_schema (%events_transactions_summary%) |
+--------------------------------------------------------------+
| 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            |
+--------------------------------------------------------------+
5 rows in set (0.00 sec)

3.14.5. objects_summary_global_by_type表

3.14.6.file_summary_*表

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合文件I/O事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，文件I/O事件instruments默认开启，在consumers表中无具体的对应配置，默认文件I/O事件摘要表中就会聚合相关事件信息

admin@localhost : performance_schema 06:48:12> show tables like '%file_summary%';
+-----------------------------------------------+
| Tables_in_performance_schema (%file_summary%) |
+-----------------------------------------------+
| file_summary_by_event_name                    |
| file_summary_by_instance                      |
+-----------------------------------------------+
2 rows in set (0.00 sec)

3.14.7. table_summary表

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合表IO等待和锁等待事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，表IO等待和锁等待事件instruments（wait/io/table/sql/handler）默认开启，在consumers表中无具体的对应配置，默认表IO等待和锁等待事件摘要表中就会聚合相关事件信息

admin@localhost : performance_schema 06:50:03> show tables like '%table%summary%';
+------------------------------------------------+
| Tables_in_performance_schema (%table%summary%) |
+------------------------------------------------+
| table_io_waits_summary_by_index_usage          |
| table_io_waits_summary_by_table                |
| table_lock_waits_summary_by_table              |
+------------------------------------------------+
3 rows in set (0.00 sec)

3.14.8. socket_summary_*表

包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合socket事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，socket事件instruments默认关闭，在consumers表中无具体的对应配置

admin@localhost : performance_schema 06:53:42> show tables like '%socket%summary%';
+-------------------------------------------------+
| Tables_in_performance_schema (%socket%summary%) |
+-------------------------------------------------+
| socket_summary_by_event_name                    |
| socket_summary_by_instance                      |
+-------------------------------------------------+
2 rows in set (0.00 sec)

3.14.9. memory_summary_*表

有如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合内存事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，内存事件instruments 除了performance_schema自身内存分配相关的事件采集默认开启之外，其他的内存事件都是默认关闭的，在consumers表中无具体的对应配置

admin@localhost : performance_schema 06:56:56> show tables like '%memory%summary%';
+-------------------------------------------------+
| Tables_in_performance_schema (%memory%summary%) |
+-------------------------------------------------+
| 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.00 sec)

3.14.10. status_by_account、status_by_host、status_by_user

3.15. 其他混杂表

以下这些表不属于前面讨论的任何类别，不便分类，所以单独在这个章节进行描述：
- host_cache：内部主机缓存信息表
- performance_timers：可用的事件计时器
- threads：有关server线程信息表，包含前台和后台线程

3.15.1. host_cache

3.15.2. performance_timers

3.15.3. threads

3.16. performance_schema中信息摘要表

4、performance_schema内存分配模型

5、性能分析思路

6、相关system variables和status variables

7、使用示例

7.1. 快速入门

8、附录

profiling功能在5.6开始逐渐被performance_schema取代，performance_schema使用配置方法参考链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html
information_schema中部分性能数据收集迁移到了performance_schema中，详见链接：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html

上一篇： firewall_whitelist表 | 下一篇：简介