初相识｜performance_schema全方位介绍

￡神魔★判官ぃ 2022-10-30 09:27 164阅读 0赞

### **｜目 录** ###

--------------------

### *1、什么是performance\_schema* ###

*2、performance\_schema使用快速入门*

*2.1. 检查当前数据库版本是否支持*

*2.2. 启用performance\_schema*

*2.3. performance\_schema表的分类*

*2.4. performance\_schema简单配置与使用*

### **｜导 语** ###

--------------------

### 很久之前，当我还在尝试着系统地学习performance\_schema的时候，通过在网上各种搜索资料进行学习，但很遗憾，学习的效果并不是很明显，很多标称类似 "深入浅出performance\_schema" 的文章，基本上都是那种动不动就贴源码的风格，然后深入了之后却出不来了。对系统学习performance\_schema的作用甚微。  ###

现在，很高兴的告诉大家，我们基于 MySQL 官方文档加上我们的验证，整理了一份可以系统学习 performance\_schema 的资料分享给大家，为了方便大家阅读，我们整理为了一个系列，一共7篇文章。下面，请跟随我们一起开始performance\_schema系统的学习之旅吧。

本文首先，大致介绍了什么是performance\_schema？它能做什么？

然后，简单介绍了如何快速上手使用performance\_schema的方法；

最后，简单介绍了performance\_schema中由哪些表组成，这些表大致的作用是什么。

PS：本系列文章所使用的数据库版本为 MySQL 官方 5.7.17版本

## ##

### **｜1、****什么是performance\_schema** ###

**MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况，它具有以下特点：**

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

**performance\_schema实现机制遵循以下设计目标：**

1.  启用performance\_schema不会导致server的行为发生变化。例如，它不会改变线程调度机制，不会导致查询执行计划（如EXPLAIN）发生变化 
2.  启用performance\_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用 
3.  在该实现机制中没有增加新的关键字或语句，解析器不会变化  
4.  即使performance\_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行 
5.  如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索 
6.  需要很容易地添加新的instruments监测点 
7.  instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作。 
8.  注意：MySQL sys schema是一组对象（包括相关的视图、存储过程和函数），可以方便地访问performance\_schema收集的数据。同时检索的数据可读性也更高(例如：performance\_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)，sys schem在5.7.x版本默认安装

### **｜2、performance\_schema使用快速入门** ###

现在，是否觉得上面的介绍内容太过枯燥呢？如果你这么想，那就对了，我当初学习的时候也是这么想的。但现在，对于什么是performance\_schema这个问题上，比起更早之前更清晰了呢？如果你还没有打算要放弃阅读本文的话，那么，请跟随我们开始进入到"边走边唱"环节吧！

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

**performance\_schema被视为存储引擎。**如果该引擎可用，则应该在INFORMATION\_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES，如下：

使用 INFORMATION\_SCHEMA.ENGINES表来查询你的数据库实例是否支持INFORMATION\_SCHEMA引擎

使用show命令来查询你的数据库实例是否支持INFORMATION\_SCHEMA引擎

当我们看到PERFORMANCE\_SCHEMA 对应的Support 字段输出为YES时就表示我们当前的数据库版本是支持performance\_schema的。但知道我们的实例支持performance\_schema引擎就可以使用了吗？NO，很遗憾，performance\_schema在5.6及其之前的版本中，默认没有启用，从5.7及其之后的版本才修改为默认启用。现在，我们来看看如何设置performance\_schema默认启用吧！

**2.2. 启用performance\_schema**

从上文中我们已经知道，performance\_schema在5.7.x及其以上版本中默认启用（5.6.x及其以下版本默认关闭），如果要显式启用或关闭时，我们需要使用参数performance\_schema=ON|OFF设置，并在my.cnf中进行配置：

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

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

现在，你可以在performance\_schema下使用show tables语句或者通过查询 INFORMATION\_SCHEMA.TABLES表中performance\_schema引擎相关的元数据来了解在performance\_schema下存在着哪些表：

通过从INFORMATION\_SCHEMA.tables表查询有哪些performance\_schema引擎的表：

qogir\_env@localhost : performance\_schema 03:13:22> 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语句来查看有哪些performance\_schema引擎表：

qogir\_env@localhost : performance\_schema 03:20:43> use performance\_schema  
Database changed  
qogir\_env@localhost : performance\_schema 03:21:06> 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张表，那么，这87帐表都是存放什么数据的呢？我们如何使用他们来查询我们想要查看的数据呢？先别着急，我们先来看看这些表是如何分类的。

**2.3. performance\_schema表的分类**

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

**按照事件类型分组记录性能事件数据的表 **

语句事件记录表，这些表记录了语句事件信息，当前语句事件表events\_statements\_current、历史语句事件表events\_statements\_history和长语句历史事件表events\_statements\_history\_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)

qogir\_env@localhost : performance\_schema 03:51:36> 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)

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

qogir\_env@localhost : performance\_schema 03:53:51> 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)

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

qogir\_env@localhost : performance\_schema 03:55:07> 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)

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

qogir\_env@localhost : performance\_schema 03:55:30> 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)

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

qogir\_env@localhost : performance\_schema 03:58:27> 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)

监视内存使用的表：

qogir\_env@localhost : performance\_schema 03:58:38> 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进行配置的配置表：

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

**2.4. performance\_schema简单配置与使用**

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

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

qogir\_env@localhost : performance\_schema 03:34:40> 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 配置表中对应的配置i向

qogir\_env@localhost : performance\_schema 04:23:40> 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表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）：

qogir\_env@localhost : performance\_schema 04:23:52> 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 中记录了什么：

qogir\_env@localhost : performance\_schema 06:14:08> 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）统计而来），如下：

qogir\_env@localhost : performance\_schema 06:17:23> 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 |  
\+---------------------------------------------------+------------+  
qogir\_env@localhost : performance\_schema 06:19:20> 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操作及其关联文件名：

--------------------

### **｜**本文小结 ###

本篇内容到这里就接近尾声了，相信很多人都认为，我们大多数时候并不会直接使用performance\_schema来查询性能数据，而是使用sys schema下的视图代替，为什么不直接学习sys schema呢？那你知道sys schema中的数据是从哪里吐出来的吗？performance\_schema 中的数据实际上主要是从performance\_schema、information\_schema中获取，所以要想玩转sys schema，全面了解performance\_schema必不可少。另外，对于sys schema、informatiion\_schema甚至是mysql schema，我们后续也会推出不同的系列文章分享给大家。