【MySQL】MySQL之 performance_schema 库

MySQL自带四个数据库，分别是：

1. information_schema
2. performance_schema
3. mysql
4. test

本文介绍 performance_schema 库。

简述

performance_schema 库是MySQL 5.5新增一个存储引擎，主要用于收集数据库服务器性能参数，监控 MySQL server 在一个较低级别的运行过程中的资源消耗、资源等待等情况。

MySQL 的 performance_schema 库中所有表的存储引擎都是PERFORMANCE_SCHEMA，但是用户不能创建存储引擎为 PERFORMANCE_SCHEMA 的表。

performance_schema 库功能可总结为以下三点：

1. 提供进程等待的详细信息，包括锁、互斥变量、文件信息；
2. 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
3. 对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

通过以上得到的信息，DBA能够较明细得了解性能降低可能是由于哪些原因。

具体如下（总结自网络）：

1、performance_schema提供了一种在数据库运行时实时检查服务器的内部执行情况的方法。performance_schema数据库中的表使用performance_schema存储引擎，关注服务器运行过程中的元数据信息。
2、performance_schema通过监视服务器的事件来实现监视服务器内部运行情况，“事件”就是服务器内部活动中发生的任何事情以及对应的时间消耗，利用这些信息来判断服务器中的相关资源消耗在了哪里？首先，事件可以是函数调用，操作系统的等待，SQL语句执行的阶段（如sql语句执行过程中的解析或排序阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供服务器中的相关存储引擎对磁盘文件，表I / O，表锁等资源的同步调用信息。
3、performance_schema中的事件与写入二进制日志中的事件（描述数据修改的事件），事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是服务器执行某些活动对某些资源的消耗，耗时，这些活动执行的次数等情况。
4、performance_schema中的事件只记录在本地服务器的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他服务器中。
5、当前活动事件，历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数，使用时长。而且可以使用分析某个特定线程，特定对象（例如Mutex或文件）的相关联的活动。
6、performance_schema存储引擎使用服务器源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。
7、收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_ *开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）。
8、performance_schema的表中的数据不会持久化存储在磁盘中，重新保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表内部的整个Performance_schema下的所有数据）。
9、MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所不同。

使用 show variables like 'performance_schema'; 查看MySQL服务中 performance_schema 的状态：

MariaDB [(none)]> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | OFF   |
+--------------------+-------+
1 row in set (0.001 sec)

默认状态下是未启动的，需要在配置文件中手动添加开启：

[mysqld]
performance_schema=ON

简述存储引擎（Storage Engine）：

何为MySQL数据库的存储引擎？在此之前先搞清楚引擎。

引擎是发动机的核心部分，发动机又是车辆的核心部分，因此引擎就是驱使汽车运动的核心，不同的引擎有不同的工作方式。

以此类推，MySQL的存储引擎就是MySQL中对数据处理（增删改查、存储机制、索引技巧等）的核心，也就是处理的工作方式，不同的存储引擎有不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。

目前MySQL最常用的存储引擎是 MyISAM 和 InnoDB ：

1. MyISAM： 拥有较高的插入，查询速度，但不支持事务
2. InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定

包含表

目前 performance_schema 库包含的所有表如下：

MariaDB [mysql]> use performance_schema;
Database changed
MariaDB [performance_schema]> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
| accounts                                           |
| cond_instances                                     |
| 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         |
| 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_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| 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          |
| file_instances                                     |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |
| host_cache                                         |
| hosts                                              |
| mutex_instances                                    |
| objects_summary_global_by_type                     |
| performance_timers                                 |
| rwlock_instances                                   |
| session_account_connect_attrs                      |
| session_connect_attrs                              |
| setup_actors                                       |
| setup_consumers                                    |
| setup_instruments                                  |
| setup_objects                                      |
| setup_timers                                       |
| socket_instances                                   |
| socket_summary_by_event_name                       |
| socket_summary_by_instance                         |
| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |
| threads                                            |
| users                                              |
+----------------------------------------------------+
52 rows in set (0.002 sec)

一些常用表：

1. setup：配置表，配置MySQL各种信息
2. instance：实例表，存储各种对象的实例
3. Wait：等待事件信息表
4. Stage：执行阶段记录表
5. Statement：事件声明表
6. Connection：连接信息表
7. Summary：统计信息表（各个维度）
8. performance_timers：系统支持的统计时间单位表
9. threads：监视线程表
10. users ：用户表

常用表详解

1. ——setup表——（配置表）

查看所有 setup 相关表：

MariaDB [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.001 sec)

1）setup_actors： 配置用户监控信息，监控所有用户。

2）setup_consumers： 配置events的消费者类型，即收集的events写入到哪些统计表中。

3）setup_instruments： 配置具体的instrument，主要包含4大类：idle、stage/xxx、statement/xxx、wait/xxx。（idle表示socket空闲的时间，stage类表示语句的每个执行阶段的统计，statement类统计语句维度的信息，wait类统计各种等待事件，比如IO，mutux，spin_lock,condition等。）

4）setup_objects： 配置监控对象，默认对mysql，performance_schema和information_schema中的表都不监控，而其它DB的所有表都监控。

5）setup_timers： 配置统计时间单位。

1. ——instance——（实例表）

查看所有 instance 相关表：

MariaDB [performance_schema]> show tables like "%instance%";
+-------------------------------------------+
| Tables_in_performance_schema (%instance%) |
+-------------------------------------------+
| cond_instances                            |
| //events_waits_summary_by_instance |
| file_instances                            |
| //file_summary_by_instance |
| mutex_instances                           |
| rwlock_instances                          |
| socket_instances                          |
| //socket_summary_by_instance |
+-------------------------------------------+
8 rows in set (0.001 sec)

1）cond_instances： 条件等待对象实例
表中记录了系统中使用的条件变量的对象，OBJECT_INSTANCE_BEGIN为对象的内存地址。

2）file_instances： 文件实例
表中记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等，open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。

3）mutex_instances： 互斥同步对象实例
表中记录了系统中使用互斥量对象的所有记录，其中name为：wait/synch/mutex/*。LOCKED_BY_THREAD_ID显示哪个线程正持有mutex，若没有线程持有，则为NULL。

4）rwlock_instances： 读写锁同步对象实例
表中记录了系统中使用读写锁对象的所有记录，其中name为 wait/synch/rwlock/*。WRITE_LOCKED_BY_THREAD_ID为正在持有该对象的thread_id，若没有线程持有，则为NULL。READ_LOCKED_BY_COUNT为记录了同时有多少个读者持有读锁。（通过 events_waits_current 表可以知道，哪个线程在等待锁；通过rwlock_instances知道哪个线程持有锁。rwlock_instances的缺陷是，只能记录持有写锁的线程，对于读锁则无能为力）。

5）socket_instances： 活跃会话对象实例
表中记录了thread_id,socket_id,ip和port，其它表可以通过thread_id与socket_instance进行关联，获取IP-PORT信息，能够与应用对接起来。

1. ——Wait——（等待事件信息表）

查看所有 Wait 相关表：

MariaDB [performance_schema]> show tables like "%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 |
| //table_io_waits_summary_by_index_usage |
| //table_io_waits_summary_by_table |
| //table_lock_waits_summary_by_table |
+-----------------------------------------------+
12 rows in set (0.001 sec)

1）events_waits_current： 记录了当前线程等待的事件

2）events_waits_history： 记录了每个线程最近等待的10个事件

3）events_waits_history_long： 记录了最近所有线程产生的10000个事件

1. ——Stage——（执行阶段记录表）

查看所有 Stage 相关表：

MariaDB [performance_schema]> show tables like "%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.001 sec)

1）events_stages_current： 记录了当前线程所处的执行阶段

2）events_stages_history： 记录了当前线程所处的执行阶段10条历史记录

3）events_stages_history_long： 记录了当前线程所处的执行阶段10000条历史记录

1. Statement（事件声明表）

查看所有 Statement相关表：

MariaDB [performance_schema]> show tables like "%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_thread_by_event_name |
| //events_statements_summary_by_user_by_event_name |
| //events_statements_summary_global_by_event_name |
+----------------------------------------------------+
9 rows in set (0.001 sec)

1）events_statements_current： 通过 thread_id+event_id可以唯一确定一条记录。Statments表只记录最顶层的请求，SQL语句或是COMMAND，每条语句一行。event_name形式为statement/sql/，或statement/com/

2）events_statements_history

3）events_statements_history_long

1. ——Connection——（连接信息）

1）users： 记录用户连接数信息

2）hosts： 记录了主机连接数信息

3）accounts： 记录了用户主机连接数信息

1. ——Summary——（不同维度的统计信息表）

查看 Summary 相关表：

MariaDB [performance_schema]> show tables like "%Summary%";
+----------------------------------------------------+
| Tables_in_performance_schema (%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         |
| 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_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| 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          |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |
| objects_summary_global_by_type                     |
| socket_summary_by_event_name                       |
| socket_summary_by_instance                         |
| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |
+----------------------------------------------------+
25 rows in set (0.002 sec)

1）events_waits_summary_global_by_event_name： 按等待事件类型聚合，每个事件一条记录

2）events_waits_summary_by_instance： 按等待事件对象聚合，同一种等待事件，可能有多个实例，每个实例有不同的内存地址，因此event_name+object_instance_begin唯一确定一条记录。

3）events_waits_summary_by_thread_by_event_name： 按每个线程和事件来统计，thread_id+event_name唯一确定一条记录。

4）events_stages_summary_global_by_event_name： 按事件阶段类型聚合，每个事件一条记录，表结构同上。

5）events_stages_summary_by_thread_by_event_name： 按每个线程和事件来阶段统计，表结构同上。

6）events_statements_summary_by_digest： 按照事件的语句进行聚合。

7）events_statements_summary_global_by_event_name： 按照事件的语句进行聚合。表结构同上。

8）events_statements_summary_by_thread_by_event_name： 按照线程和事件的语句进行聚合。

9）file_summary_by_instance： 按事件类型统计（物理IO维度）

10）file_summary_by_event_name： 具体文件统计（物理IO维度）

11）table_io_waits_summary_by_table： 根据wait/io/table/sql/handler，聚合每个表的I/O操作（逻辑IO纬度）

12）table_io_waits_summary_by_index_usage： 与table_io_waits_summary_by_table类似，按索引维度统计

13）table_lock_waits_summary_by_table： 聚合了表锁等待事件，包括internal lock 和 external lock

暂且总结至此。