【第20天】MYSQL进阶-查询优化- performance_schema系列二：常用配置详解（SQL 小虚竹)

古城微笑少年丶 2023-10-07 20:08 47阅读 0赞

回城传送–》[《100天精通MYSQL从入门到就业》][100_MYSQL]

#### 文章目录 ####

*  零、前言
 *  一、练习题目
 *  二、SQL思路
 *   *  SQL进阶-查询优化- performance\_schema系列二：常用配置详解
     *   *  启动时配置
         *   *  启动选项配置
             *  system variables
         *  运行时设置
         *   *  performance\_timers表
             *  setup\_timers表
             *  setup\_consumers表
             *  setup\_instruments表
             *  setup\_actors表
             *  setup\_objects表
             *  threads表
 *  三、总结
 *  四、参考

## 零、前言 ##

今天是学习 **SQL** 打卡的第 **20** 天，每天我会提供一篇文章供群成员阅读（ **不需要订阅付钱** ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在[小虚竹JAVA社区][JAVA] 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 **SQL** 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：**SQL进阶-查询优化- performance\_schema系列二：常用配置详解**

本文内容有2万多字，内容非常多，建议阅读方式：**记住标题和小模块的介绍** ，内容详解可帮助理解，看不懂也没事，后续章节的实战中也可慢慢体会。  
**本文适用于收藏，需要查阅指定配置时，可快速找到配置的详解。**

## 一、练习题目 ##

## 二、SQL思路 ##

### SQL进阶-查询优化- performance\_schema系列二：常用配置详解 ###

今天会讲解performance\_schema配置方式以及各个配置表的作用。

#### 启动时配置 ####

performance\_schema中的配置是保存在内存中的，是易丢失的。也就是说保存在performance\_schema配置表(本章后续内容会讲到)中的配置项在MySQL实例停止时会全部丢失。所以，如果想要把配置项持久化，就需要在MySQL的配置文件中使用启动选项来持久化配置项，让MySQL每次重启都自动加载配置项，而不需要每次重启都再重新配置。

##### 启动选项配置 #####

performance\_schema有哪些启动选项呢？我们可以通过如下命令行命令进行查看:

mysqld --verbose --help |grep performance-schema |grep -v '\-\-' |sed '1d' |sed '/[0-9]\+/d'

![在这里插入图片描述][3b06ede781d34f3aa0dedd243965c2a8.png]  
这些启动项介绍：

*  performance\_schema\_consumer\_events\_statements\_current=TRUE；

> 是否在mysql server启动时就开启events\_statements\_current表的记录功能(该表记录当前的语句事件信息)，启动之后也可以在setup\_consumers表中使用UPDATE语句进行动态更新setup\_consumers配置表中的events\_statements\_current配置项，默认值为TRUE

*  performance\_schema\_consumer\_events\_statements\_history=TRUE;

> 与performance\_schema\_consumer\_events\_statements\_current选项类似，但该选项是用于配置是否记录语句事件短历史信息，默认为TRUE

*  performance\_schema\_consumer\_events\_stages\_history\_long=FALSE

> 与performance\_schema\_consumer\_events\_statements\_current选项类似，但该选项是用于配置是否记录语句事件长历史信息，默认为FALSE

*  除了statement(语句)事件之外，还支持：wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个启动项分别进行配置，但这些等待事件默认未启用，如果需要在MySQL Server启动时一同启动，则通常需要写进my.cnf配置文件中
 *  performance\_schema\_consumer\_global\_instrumentation=TRUE

> 是否在MySQL Server启动时就开启全局表（如：mutex\_instances、rwlock\_instances、cond\_instances、file\_instances、users、hostsaccounts、socket\_summary\_by\_event\_name、file\_summary\_by\_instance等大部分的全局对象计数统计和事件汇总统计信息表 ）的记录功能，启动之后也可以在setup\_consumers表中使用UPDATE语句进行动态更新全局配置项  
> 默认值为TRUE

*  performance\_schema\_consumer\_statements\_digest=TRUE

> 是否在MySQL Server启动时就开启events\_statements\_summary\_by\_digest 表的记录功能，启动之后也可以在setup\_consumers表中使用UPDATE语句进行动态更新digest配置项  
> 默认值为TRUE

*  performance\_schema\_consumer\_thread\_instrumentation=TRUE

> 是否在MySQL Server启动时就开启  
> events\_xxx\_summary\_by\_yyy\_by\_event\_name表的记录功能，启动之后也可以在setup\_consumers表中使用UPDATE语句进行动态更新线程配置项  
> 默认值为TRUE

*  performance\_schema\_instrument\[=name\]

> 是否在MySQL Server启动时就启用某些采集器，由于instruments配置项多达数千个，所以该配置项支持key-value模式，还支持%号进行通配等，如下:

# [=name]可以指定为具体的Instruments名称（但是这样如果有多个需要指定的时候，就需要使用该选项多次），也可以使用通配符，可以指定instruments相同的前缀+通配符，也可以使用%代表所有的instruments
    ## 指定开启单个instruments
    --performance-schema-instrument='instrument_name=value'
    ## 使用通配符指定开启多个instruments
    --performance-schema-instrument='wait/synch/cond/%=COUNTED'
    ## 开关所有的instruments
    --performance-schema-instrument='%=ON'
    --performance-schema-instrument='%=OFF'

注意，这些启动选项要生效的前提是，需要设置performance\_schema=ON。另外，这些启动选项虽然无法使用show variables语句查看，但我们可以通过setup\_instruments和setup\_consumers表查询这些选项指定的值。

##### system variables #####

与performance\_schema相关的system variables可以使用如下语句查看，这些variables用于限定consumers表的存储限制，它们都是只读变量，需要在MySQL启动之前就设置好这些变量的值。

show variables like '%performance_schema%';

![在这里插入图片描述][ecede56946b94f438f05d37abbf12143.png]  
![在这里插入图片描述][32a023a0eb0341be9948ea0269df61bc.png]  
下面，我们将对这些system variables(以下称为变量)中几个需要关注的进行简单解释(其中大部分变量是-1值，代表会自动调整，无需太多关注，另外，大于-1值的变量在大多数时候也够用，如果无特殊需求，不建议调整，调整这些参数会增加内存使用量):

*  performance\_schema=ON
    
     *  控制performance\_schema功能的开关，要使用MySQL的performance\_schema，需要在mysqld启动时启用，以启用事件收集功能
     *  该参数在5.7.x之前支持performance\_schema的版本中默认关闭，5.7.x版本开始默认开启
     *  注意：如果mysqld在初始化performance\_schema时发现无法分配任何相关的内部缓冲区，则performance\_schema将自动禁用，并将performance\_schema设置为OFF
 *  performance\_schema\_digests\_size=10000
    
     *  控制events\_statements\_summary\_by\_digest表中的最大行数。如果产生的语句摘要信息超过此最大值，便无法继续存入该表，此时performance\_schema会增加状态变量
 *  performance\_schema\_events\_statements\_history\_long\_size=10000
    
     *  控制events\_statements\_history\_long表中的最大行数，该参数控制所有会话在events\_statements\_history\_long表中能够存放的总事件记录数，超过这个限制之后，最早的记录将被覆盖
     *  全局变量，只读变量，整型值，5.6.3版本引入
        
         *  5.6.x版本中，5.6.5及其之前的版本默认为10000，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10000
         *  5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10000
 *  performance\_schema\_events\_statements\_history\_size=10
    
     *  控制events\_statements\_history表中单个线程（会话）的最大行数，该参数控制单个会话在events\_statements\_history表中能够存放的事件记录数，超过这个限制之后，单个会话最早的记录将被覆盖
     *  全局变量，只读变量，整型值，5.6.3版本引入
        
         *  5.6.x版本中，5.6.5及其之前的版本默认为10，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10
         *  5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10
 *  除了statement(语句)事件之外，wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个参数分别进行存储限制配置
 *  performance\_schema\_max\_digest\_length=1024
    
     *  用于控制标准化形式的SQL语句文本在存入performance\_schema时的限制长度，该变量与max\_digest\_length变量相关(max\_digest\_length变量含义请自行查阅相关资料)
     *  全局变量，只读变量，默认值1024字节，整型值，取值范围0~1048576，5.6.26和5.7.8版本中引入
 *  performance\_schema\_max\_sql\_text\_length=1024
    
     *  控制存入events\_statements\_current，events\_statements\_history和events\_statements\_history\_long语句事件表中的SQL\_TEXT列的最大SQL长度字节数。 超出系统变量performance\_schema\_max\_sql\_text\_length的部分将被丢弃，不会记录，一般情况下不需要调整该参数，除非被截断的部分与其他SQL比起来有很大差异
     *  全局变量，只读变量，整型值，默认值为1024字节，取值范围为0~1048576，5.7.6版本引入
     *  降低系统变量performance\_schema\_max\_sql\_text\_length值可以减少内存使用，但如果汇总的SQL中，被截断部分有较大差异，会导致没有办法再对这些有较大差异的SQL进行区分。 增加该系统变量值会增加内存使用，但对于汇总SQL来讲可以更精准地区分不同的部分。

#### 运行时设置 ####

在MySQL启动之后，我们就无法使用启动选项来开关相应的consumers和instruments了，此时，我们如何根据自己的需求来灵活地开关performance\_schema中的采集信息呢？(例如：默认配置下很多配置项并未开启，我们可能需要即时去修改配置，再如：高并发场景，大量的线程连接到MySQL，执行各种各样的SQL时产生大量的事件信息，而我们只想看某一个会话产生的事件信息时，也可能需要即时去修改配置)，我们可以通过修改performance\_schema下的几张配置表中的配置项实现

这些配置表中的配置项之间存在着关联关系，按照配置影响的先后顺序，可整理为如下图(该表仅代表个人理解)：  
![在这里插入图片描述][c0910f02a676430f99ea84bb9be29d54.png]

##### performance\_timers表 #####

performance\_timers表中记录了server中有哪些可用的事件计时器（注意：该表中的配置项不支持增删改，是只读的。有哪些计时器就表示当前的版本支持哪些计时器），setup\_timers配置表中的配置项引用此表中的计时器。

每个计时器的精度和数量相关的特征值会有所不同，可以通过如下查询语句查看performance\_timers表中记录的计时器和相关的特征信息：

use performance_schema;
    
    SELECT * FROM performance_timers;

![在这里插入图片描述][e3f57ddbe0274ea2b70c972df5cc3b74.png]  
performance\_timers表中的字段介绍：

*  TIMER\_NAME：表示可用计时器名称，CYCLE是指基于CPU（处理器）周期计数器的定时器。在setup\_timers表中可以使用performance\_timers表中列值不为null的计时器（如果performance\_timers表中有某字段值为NULL，则表示该定时器可能不支持当前server所在平台）
 *  TIMER\_FREQUENCY：表示每秒钟对应的计时器单位的数量（即，相对于每秒时间换算为对应的计时器单位之后的数值，例如：每秒=1000毫秒=1000000微秒=1000000000纳秒）。对于CYCLE计时器的换算值，通常与CPU的频率相关。对于performance\_timers表中查看到的CYCLE计时器的TIMER\_FREQUENCY列值 ，是根据2.4GHz处理器的系统上获得的预设值（在2.4GHz处理器的系统上，CYCLE可能接近2400000000）。NANOSECOND 、MICROSECOND 、MILLISECOND 计时器是基于固定的1秒换算而来。对于TICK计时器，TIMER\_FREQUENCY列值可能会因平台而异（例如，某些平台使用100个tick/秒，某些平台使用1000个tick/秒）
 *  TIMER\_RESOLUTION：计时器精度值，表示在每个计时器被调用时额外增加的值（即使用该计时器时，计时器被调用一次，需要额外增加的值）。如果计时器的分辨率为10，则其计时器的时间值在计时器每次被调用时，相当于TIMER\_FREQUENCY值+10
 *  TIMER\_OVERHEAD：表示在使用定时器获取事件时开销的最小周期值（performance\_schema在初始化期间调用计时器20次，选择一个最小值作为此字段值），每个事件的时间开销值是计时器显示值的两倍，因为在事件的开始和结束时都调用计时器。注意：计时器代码仅用于支持计时事件，对于非计时类事件（如调用次数的统计事件），这种计时器统计开销方法不适用
 *  **PS：对于performance\_timers表，不允许使用TRUNCATE TABLE语句**

##### setup\_timers表 #####

setup\_timers表中记录当前使用的事件计时器信息（注意：该表不支持增加和删除记录，只支持修改和查询）。

可以通过UPDATE语句来更改setup\_timers.TIMER\_NAME列值，以给不同的事件类别选择不同的计时器，setup\_timers.TIMER\_NAME列有效值来自performance\_timers.TIMER\_NAME列值。

对setup\_timers表的修改会立即影响监控。正在执行的事件可能会使用修改之前的计时器作为开始时间，但可能会使用修改之后的新的计时器作为结束时间，为了避免计时器更改后可能产生时间信息收集到不可预测的结果，请在修改之后使用TRUNCATE TABLE语句来重置performance\_schema中相关表中的统计信息。

use performance_schema;
    SELECT * FROM setup_timers;

![在这里插入图片描述][ee6afb40f57746e88cb27fb2f39adc1c.png]  
setup\_timers表中的字段介绍：

*  NAME：计时器类型，对应着某个事件类别(事件类别详见 3.3.4 节)
 *  TIMER\_NAME：计时器类型名称。此列可以修改，有效值参见performance\_timers.TIMER\_NAME列值
 *  **PS：对于setup\_timers表，不允许使用TRUNCATE TABLE语句**

**注：mysql 8 删除了Performance Schema setup\_timers表**。

##### setup\_consumers表 #####

setup\_consumers表列出了consumers可配置列表项(注意：该表不支持增加和删除记录，只支持修改和查询)，如下：

use performance_schema;
    SELECT * FROM setup_consumers;

![在这里插入图片描述][a7a743f8bbd442e18a6686097c5ad622.png]  
对setup\_consumers表的修改会立即影响监控，setup\_consumers字段含义如下：

*  NAME：consumers配置名称
 *  ENABLED：consumers是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。如果需要禁用consumers就设置为NO，设置为NO时，server不会维护这些consumers表的内容新增和删除，且也会关闭consumers对应的instruments（如果没有instruments发现采集数据没有任何consumers消费的话）
 *  **PS：对于setup\_consumers表，不允许使用TRUNCATE TABLE语句**

##### setup\_instruments表 #####

setup\_instruments 表列出了instruments 列表配置项，即代表了哪些事件支持被收集：

use performance_schema;
    SELECT * FROM setup_instruments;

![在这里插入图片描述][5dad1f50d8434a4ab7ec8a3b1d790787.png]  
![在这里插入图片描述][244f4e2b7e0a4f318e61aa3ee267e162.png]  
1238张表，非常齐全。  
setup\_instruments表字段详解如下：

*  NAME：instruments名称，instruments名称可能具有多个部分并形成层次结构(详见下文)。当instruments被执行时，产生的事件名称就取自instruments的名称，事件没有真正的名称，直接使用instruments来作为事件的名称，可以将instruments与产生的事件进行关联
 *  ENABLED：instrumetns是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。如果设置为NO，则这个instruments不会被执行，不会产生任何的事件信息
 *  TIMED：instruments是否收集时间信息，有效值为YES或NO，此列可以使用UPDATE语句修改，如果设置为NO，则这个instruments不会收集时间信息
 *  **注意：内存操作没有定时器信息**

> 对于内存instruments，setup\_instruments中的TIMED列将被忽略（使用update语句对这些内存instruments设置timed列为YES时可以执行成功，但是你会发现执行update之后select这些instruments的timed列还是NO）

*  setup\_instruments表不允许使用TRUNCATE TABLE语句

官方文档中没有找到每一个instruments具体的说明文档，官方文档中列出如下几个原因：

*  instruments是服务端代码，所以代码可能经常变动
 *  instruments总数量有数百种，全部列出不现实
 *  instruments会因为你安装的版本不同而有所不同，每一个版本所支持的instruments可以通过查询setup\_instruments表获取

一些可能常用的场景相关的设置 :

*  metadata locks监控需要打开’wait/lock/metadata/sql/mdl’ instruments才能监控

> 开启这个instruments之后在表performance\_schema.metadata\_locks表中可以查询到MDL锁信息

*  表锁监控需要打开’wait/io/table/sql/handler’ instruments

> 开启这个instruments之后在表 performance\_schema.table\_handles中会记录了当前打开了哪些表(执行flush tables强制关闭打开的表时，该表中的信息会被清空)，哪些表已经被加了表锁（某会话持有表锁时，相关记录行中的OWNER\_THREAD\_ID和OWNER\_EVENT\_ID列值会记录相关的thread id和event id），表锁被哪个会话持有（释放表锁时，相关记录行中的OWNER\_THREAD\_ID和OWNER\_EVENT\_ID列值会被清零）

*  查询语句top number监控

> 需要打开’statement/sql/select’ instruments，然后打开events\_statements\_xxx表，通过查询performance\_schema.events\_statements\_xxx表的SQL\_TEXT字段可以看到原始的SQL语句，查询TIMER\_WAIT字段可以知道总的响应时间，LOCK\_TIME字段可以知道加锁时间（注意时间单位是皮秒，需要除以1000000000000才是单位秒）

##### setup\_actors表 #####

setup\_actors用于配置是否为新的前台server线程（与客户端连接相关联的线程）启用监视和历史事件日志记录。默认情况下，此表的最大行数为100。可以使用系统变量performance\_schema\_setup\_actors\_size在server启动之前更改此表的最大配置行数:

*  对于每个新的前台server线程，perfromance\_schema会匹配该表中的User,Host列进行匹配，如果匹配到某个配置行，则继续匹配该行的ENABLED和HISTORY列值，ENABLED和HISTORY列值也会用于生成threads配置表中的行INSTRUMENTED和HISTORY列。如果用户线程在创建时在该表中没有匹配到User,Host列，则该线程的INSTRUMENTED和HISTORY列将设置为NO，表示不对这个线程进行监控，不记录该线程的历史事件信息。
 *  对于后台线程（如IO线程，日志线程，主线程，purged线程等），没有关联的用户， INSTRUMENTED和HISTORY列值默认为YES，并且后台线程在创建时，不会查看setup\_actors表的配置，因为该表只能控制前台线程，后台线程也不具备用户、主机属性

setup\_actors表的初始内容是匹配任何用户和主机，因此对于所有前台线程，默认情况下启用监视和历史事件收集功能，如下：

use performance_schema;
    SELECT * FROM setup_actors;

![在这里插入图片描述][aad9bad4eb76440b9d63a3a4b70af154.png]

setup\_actors表字段含义如下：

*  HOST：与grant语句类似的主机名，一个具体的字符串名字，或使用“％”表示“任何主机”
 *  USER：一个具体的字符串名称，或使用“％”表示“任何用户”
 *  ROLE：MySQL 8.0中才启用角色功能
 *  ENABLED：是否启用与HOST，USER，ROLE匹配的前台线程的监控功能，有效值为：YES或NO
 *  HISTORY：是否启用与HOST， USER，ROLE匹配的前台线程的历史事件记录功能，有效值为：YES或NO

PS：setup\_actors表允许使用TRUNCATE TABLE语句清空表，或者DELETE语句删除指定行

对setup\_actors表的修改仅影响修改之后新创建的前台线程，对于修改之前已经创建的前台线程没有影响，如果要修改已经创建的前台线程的监控和历史事件记录功能，可以修改threads表行的INSTRUMENTED和HISTORY列值：

当一个前台线程初始化连接mysql server时，performance\_schema会对表setup\_actors执行查询，在表中查找每个配置行，首先尝试使用USER和HOST列（ROLE未使用）依次找出匹配的配置行，然后再找出最佳匹配行并读取匹配行的ENABLED和HISTORY列值，用于填充threads表中的ENABLED和HISTORY列值。

*  示例，假如setup\_actors表中有如下HOST和USER值：
    
     *  USER =‘literal’ and HOST =‘literal’
     *  USER =‘literal’ and HOST =‘％’
     *  USER =‘％’ and HOST =‘literal’
     *  USER =‘％’ and HOST =‘％’
 *  匹配顺序很重要，因为不同的匹配行可能具有不同的USER和HOST值（mysql中对于用户帐号是使用user@host进行区分的），根据匹配行的ENABLED和HISTORY列值来决定对每个HOST，USER或ACCOUNT（USER和HOST组合，如：user@host）对应的线程在threads表中生成对应的匹配行的ENABLED和HISTORY列值 ，以便决定是否启用相应的instruments和历史事件记录，类似如下：
    
     *  当在setup\_actors表中的最佳匹配行的ENABLED = YES时，threads表中对应线程的配置行中INSTRUMENTED列值将变为YES，HISTORY 列同理
     *  当在setup\_actors表中的最佳匹配行的ENABLED = NO时，threads表中对应线程的配置行中INSTRUMENTED列值将变为NO，HISTORY 列同理
     *  当在setup\_actors表中找不到匹配时，threads表中对应线程的配置行中INSTRUMENTED和HISTORY值值将变为NO
     *  setup\_actors表配置行中的ENABLED和HISTORY列值可以相互独立设置为YES或NO，互不影响，一个是是否启用线程对应的instruments，一个是是否启用线程相关的历史事件记录的consumers
 *  默认情况下，所有新的前台线程启用instruments和历史事件收集，因为setup\_actors表中的预设值是host=‘%’，user=‘%’,ENABLED=‘YES’,HISTORY='YES’的。如果要执行更精细的匹配(例如仅对某些前台线程进行监视），那就必须要对该表中的默认值进行修改，如下：

# 首先使用UPDATE语句把默认配置行禁用
    UPDATE setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';
    # 插入用户joe@'localhost'对应ENABLED和HISTORY都为YES的配置行
    INSERT INTO setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','joe','%','YES','YES');
    # 插入用户joe@'hosta.example.com'对应ENABLED=YES、HISTORY=NO的配置行
    INSERT INTO setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('hosta.example.com','joe','%','YES','NO');
    # 插入用户sam@'%'对应ENABLED=NO、HISTORY=YES的配置行
    INSERT INTO setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('%','sam','%','NO','YES');
    # 此时，threads表中对应用户的前台线程配置行中INSTRUMENTED和HISTORY列生效值如下
    ## 当joe从localhost连接到mysql server时，则连接符合第一个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED和HISTORY列值变为YES
    ## 当joe从hosta.example.com连接到mysql server时，则连接符合第二个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED列值为YES，HISTORY列值为NO
    ## 当joe从其他任意主机(%匹配除了localhost和hosta.example.com之外的主机)连接到mysql server时，则连接符合第三个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED和HISTORY列值变为NO
    ## 当sam从任意主机(%匹配)连接到mysql server时，则连接符合第三个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED列值变为NO，HISTORY列值为YES
    ## 除了joe和sam用户之外，其他任何用户从任意主机连接到mysql server时，匹配到第一个UPDATE语句更新之后的默认配置行，threads表中对应配置行的INSTRUMENTED和HISTORY列值变为NO
    ## 如果把UPDATE语句改成DELETE，让未明确指定的用户在setup_actors表中找不到任何匹配行，则threads表中对应配置行的INSTRUMENTED和HISTORY列值变为NO

**注意：对于后台线程，对setup\_actors表的修改不生效，如果要干预后台线程默认的设置，需要查询threads表找到相应的线程，然后使用UPDATE语句直接修改threads表中的INSTRUMENTED和HISTORY列值。**

##### setup\_objects表 #####

setup\_objects表控制performance\_schema是否监视特定对象。默认情况下，此表的最大行数为100行。要更改表行数大小，可以在server启动之前修改系统变量performance\_schema\_setup\_objects\_size的值。

setup\_objects表初始内容如下所示：

use performance_schema;
    SELECT * FROM setup_objects;

![在这里插入图片描述][2827a90364a94427a14d9a4d994302c2.png]

**对setup\_objects表的修改会立即影响对象监控**

在setup\_objects中列出的监控对象类型，在进行匹配时，performance\_schema基于OBJECT\_SCHEMA和OBJECT\_NAME列依次往后匹配，如果没有匹配的对象则不会被监视

默认配置中开启监视的对象不包含mysql，INFORMATION\_SCHEMA和performance\_schema数据库中的所有表（从上面的信息中可以看到这几个库的enabled和timed字段都为NO，注意：对于INFORMATION\_SCHEMA数据库，虽然该表中有一行配置，但是无论该表中如何设置，都不会监控该库，在setup\_objects表中information\_schema.%的配置行仅作为一个缺省值）

当performance\_schema在setup\_objects表中进行匹配检测时，会尝试首先找到最具体（最精确）的匹配项。例如，在匹配db1.t1表时，它会从setup\_objects表中先查找“db1”和“t1”的匹配项，然后再查找“db1”和“％”，然后再查找“％”和“％”。匹配的顺序很重要，因为不同的匹配行可能具有不同的ENABLED和TIMED列值

如果用户对该表具有INSERT和DELETE权限，则可以对该表中的配置行进行删除和插入新的配置行。对于已经存在的配置行，如果用户对该表具有UPDATE权限，则可以修改ENABLED和TIMED列，有效值为：YES和NO

setup\_objects表列含义如下：

*  OBJECT\_TYPE：instruments类型，有效值为：“EVENT”（事件调度器事件）、“FUNCTION”（存储函数）、“PROCEDURE”（存储过程）、“TABLE”（基表）、“TRIGGER”（触发器），TABLE对象类型的配置会影响表I/O事件（wait/io/table/sql/handler instrument）和表锁事件（wait/lock/table/sql/handler instrument）的收集
 *  OBJECT\_SCHEMA：某个监视类型对象涵盖的数据库名称，一个字符串名称，或“％”(表示“任何数据库”)
 *  OBJECT\_NAME：某个监视类型对象涵盖的表名，一个字符串名称，或“％”(表示“任何数据库内的对象”)
 *  ENABLED：是否开启对某个类型对象的监视功能，有效值为：YES或NO。此列可以修改
 *  TIMED：是否开启对某个类型对象的时间收集功能，有效值为：YES或NO，此列可以修改
 *  **PS：对于setup\_objects表，允许使用TRUNCATE TABLE语句**

setup\_objects配置表中默认的配置规则是不启用对mysql、INFORMATION\_SCHEMA、performance\_schema数据库下的对象进行监视的（ENABLED和TIMED列值全都为NO）

performance\_schema在setup\_objects表中进行查询匹配时，如果发现某个OBJECT\_TYPE列值有多行，则会尝试着匹配更多的配置行，如下（performance\_schema按照如下顺序进行检查）：

*  OBJECT\_SCHEMA =‘literal’ and OBJECT\_NAME =‘literal’
 *  OBJECT\_SCHEMA =‘literal’ and OBJECT\_NAME =‘％’
 *  OBJECT\_SCHEMA =‘％’ and OBJECT\_NAME =‘％’
 *  例如，要匹配表对象db1.t1，performance\_schema在setup\_objects表中先查找“OBJECT\_SCHEMA = db1”和“OBJECT\_NAME = t1”的匹配项，然后查找“OBJECT\_SCHEMA = db1”和“OBJECT\_NAME =％”，然后查找“OBJECT\_SCHEMA = ％”和“OBJECT\_NAME = ％”。匹配顺序很重要，因为不同的匹配行中的ENABLED和TIMED列可以有不同的值，最终会选择一个最精确的匹配项

对于表对象相关事件，instruments是否生效需要看setup\_objects与setup\_instruments两个表中的配置内容相结合，以确定是否启用instruments以及计时器功能（例如前面说的I/O事件：wait/io/table/sql/handler instrument和表锁事件：wait/lock/table/sql/handler instrument，在setup\_instruments配置表中也有明确的配置选项）：

*  只有在Setup\_instruments和setup\_objects中的ENABLED列都为YES时，表的instruments才会生成事件信息
 *  只有在Setup\_instruments和setup\_objects中的TIMED列都为YES时，表的instruments才会启用计时器功能（收集时间信息）
 *  例如：要监视db1.t1、db1.t2、db2.%、db3.%这些表，setup\_instruments和setup\_objects两个表中有如下配置项

# setup_instruments表
    select * from setup_instruments where name like '%/table/%';

![在这里插入图片描述][1392177b93014795a234ef764ff8be3e.png]

# setup_objects表
    +-------------+---------------+-------------+---------+-------+
    | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
    +-------------+---------------+-------------+---------+-------+
    | TABLE       | db1           | t1          | YES     | YES   |
    | TABLE       | db1           | t2          | NO      | NO    |
    | TABLE       | db2           | %           | YES     | YES   |
    | TABLE       | db3           | %           | NO      | NO    |
    | TABLE       | %             | %           | YES     | YES   |
    +-------------+---------------+-------------+---------+-------+
    # 以上两个表中的配置项综合之后，只有db1.t1、db2.%、%.%的表对象的instruments会被启用，db1.t2和db3.%不会启用，因为这两个对象在setup_objects配置表中ENABLED和TIMED字段值为NO

对于存储程序对象相关的事件，performance\_schema只需要从setup\_objects表中读取配置项的ENABLED和TIMED列值。因为存储程序对象在setup\_instruments表中没有对应的配置项

如果持久性表和临时表名称相同，则在setup\_objects表中进行匹配时，针对这两种类型的表的匹配规则都同时生效（不会发生一个表启用监控，另外一个表不启用）

##### threads表 #####

threads表对于每个server线程生成一行包含线程相关的信息，例如：显示是否启用监视，是否启用历史事件记录功能，如下:

select * from threads where TYPE='FOREGROUND' limit 2;

![在这里插入图片描述][68febd582f5c4827bacdedbbc35cb32d.png]  
当performance\_schema初始化时，它根据当时存在的线程每个线程生成一行信息记录在threads表中。此后，每新建一个线程在该表中就会新增一行对应线程的记录

新线程信息的INSTRUMENTED和HISTORY列值由setup\_actors表中的配置决定。

当某个线程结束时，会从threads表中删除对应行。对于与客户端会话关联的线程，当会话结束时会删除threads表中与客户端会话关联的线程配置信息行。如果客户端自动重新连接，则也相当于断开一次（会删除断开连接的配置行）再重新创建新的连接，两次连接创建的PROCESSLIST\_ID值不同。新线程初始INSTRUMENTED和HISTORY值可能与断开之前的线程初始INSTRUMENTED和HISTORY值不同：setup\_actors表在此期间可能已更改，并且如果一个线程在创建之后，后续再修改了setup\_actors表中的INSTRUMENTED或HISTORY列值，那么后续修改的值不会影响到threads表中已经创建好的线程的INSTRUMENTED或HISTORY列值

*  PROCESSLIST\_\*开头的列提供与INFORMATION\_SCHEMA.PROCESSLIST表或SHOW PROCESSLIST语句类似的信息。但threads表中与其他两个信息来源有所不同：
 *  对threads表的访问不需要互斥体，对server性能影响最小。 而使用INFORMATION\_SCHEMA.PROCESSLIST和SHOW PROCESSLIST查询线程信息的方式会损耗一定性能，因为他们需要互斥体
 *  threads表为每个线程提供附加信息，例如：它是前台还是后台线程，以及与线程相关联的server内部信息
 *  threads表提供有关后台线程的信息，而INFORMATION\_SCHEMA.PROCESSLIST和SHOW PROCESSLIST不能提供
 *  可以通过threads表中的INSTRUMENTED字段灵活地动态开关某个线程的监视功能、HISTORY字段灵活地动态开关某个线程的历史事件日志记录功能。要控制新的前台线程的初始INSTRUMENTED和HISTORY列值，通过setup\_actors表的HOST、 USER对某个主机、用户进行配置。要控制已创建线程的采集和历史事件记录功能，通过threads表的INSTRUMENTED和HISTORY列进行设置
 *  对于INFORMATION\_SCHEMA.PROCESSLIST和SHOW PROCESSLIST，需要有PROCESS权限，对于threads表只要有SELECT权限就可以查看所有用户的线程信息

threads表字段含义如下：

*  THREAD\_ID：线程的唯一标识符（ID）
 *  NAME：与server中的线程检测代码相关联的名称(注意，这里不是instruments名称)。例如，thread/sql/one\_connection对应于负责处理用户连接的代码中的线程函数名，thread/sql/main表示server的main（）函数名称
 *  TYPE：线程类型，有效值为：FOREGROUND、BACKGROUND。分别表示前台线程和后台线程，如果是用户创建的连接或者是复制线程创建的连接，则标记为前台线程（如：复制IO和SQL线程，worker线程，dump线程等），如果是server内部创建的线程（不能用户干预的线程），则标记为后台线程，如：innodb的后台IO线程等
 *  PROCESSLIST\_ID：对应INFORMATION\_SCHEMA.PROCESSLIST表中的ID列。该列值与show processlist语句、INFORMATION\_SCHEMA.PROCESSLIST表、connection\_id()函数返回的线程ID值相等。另外，threads表中记录了内部线程，而processlist表中没有记录内部线程，所以，对于内部线程，在threads表中的该字段显示为NULL，因此在threads表中NULL值不唯一（可能有多个后台线程）
 *  PROCESSLIST\_USER：与前台线程相关联的用户名，对于后台线程为NULL。
 *  PROCESSLIST\_HOST：与前台线程关联的客户端的主机名，对于后台线程为NULL。与INFORMATION\_SCHEMA PROCESSLIST表的HOST列或SHOW PROCESSLIST输出的主机列不同，PROCESSLIST\_HOST列不包括TCP/IP连接的端口号。要从performance\_schema中获取端口信息，需要查询socket\_instances表（关于socket的instruments wait/io/socket/sql/\*默认关闭）：
 *  PROCESSLIST\_DB：线程的默认数据库，如果没有，则为NULL。
 *  PROCESSLIST\_COMMAND：对于前台线程，该值代表着当前客户端正在执行的command类型，如果是sleep则表示当前会话处于空闲状态。有关线程command的详细说明，参见链接：  
    https://dev.mysql.com/doc/refman/5.7/en/thread-information.html。对于后台线程不会执行这些command，因此此列值可能为NULL
 *  PROCESSLIST\_TIME：当前线程已处于当前线程状态的持续时间（秒）
 *  PROCESSLIST\_STATE：表示线程正在做什么事情。有关PROCESSLIST\_STATE值的说明，详见链接：  
    https://dev.mysql.com/doc/refman/5.7/en/thread-information.html。如果列值为NULL，则该线程可能处于空闲状态或者是一个后台线程。大多数状态值停留的时间非常短暂。如果一个线程在某个状态下停留了非常长的时间，则表示可能有性能问题需要排查
 *  PROCESSLIST\_INFO：线程正在执行的语句，如果没有执行任何语句，则为NULL。该语句可能是发送到server的语句，也可能是某个其他语句执行时内部调用的语句。例如：如果CALL语句执行存储程序，则在存储程序中正在执行SELECT语句，那么PROCESSLIST\_INFO值将显示SELECT语句
 *  PARENT\_THREAD\_ID：如果这个线程是一个子线程（由另一个线程生成），那么该字段显示其父线程ID
 *  ROLE：暂未使用
 *  INSTRUMENTED：
    
     *  线程执行的事件是否被检测。有效值：YES、NO
     *  1)、对于前台线程，初始INSTRUMENTED值还需要看控制前台线程的setup\_actors表中的INSTRUMENTED字段值。如果在setup\_actors表中找到了对应的用户名和主机行，则会用该表中的INSTRUMENTED字段生成theads表中的INSTRUMENTED字段值，setup\_actors表中的USER和HOST字段值也会一并写入到threads表的PROCESSLIST\_USER和PROCESSLIST\_HOST列。如果某个线程产生一个子线程，则子线程会再次与setup\_actors表进行匹配
     *  2)、对于后台线程，INSTRUMENTED默认为YES。 初始值无需查看setup\_actors表，因为该表不控制后台线程，因为后台线程没有关联的用户
     *  3)、对于任何线程，其INSTRUMENTED值可以在线程的生命周期内更改
     *  要监视线程产生的事件，如下条件需满足：
     *  1)、setup\_consumers表中的thread\_instrumentation consumers必须为YES
     *  2)、threads.INSTRUMENTED列必须为YES
     *  3)、setup\_instruments表中线程相关的instruments配置行的ENABLED列必须为YES
     *  4)、如果是前台线程，那么setup\_actors中对应主机和用户的配置行中的INSTRUMENTED列必须为YES
 *  HISTORY：
    
     *  是否记录线程的历史事件。有效值：YES、NO
     *  1)、对于前台线程，初始HISTORY值还需要看控制前台线程的setup\_actors表中的HISTORY字段值。如果在setup\_actors表中找到了对应的用户名和主机行，则会用该表中的HISTORY字段生成theads表中的HISTORY字段值，setup\_actors表中的USER和HOST字段值也会一并写入到threads表的PROCESSLIST\_USER和PROCESSLIST\_HOST列。如果某个线程产生一个子线程，则子线程会再次与setup\_actors表进行匹配
     *  2)、对于后台线程，HISTORY默认为YES。初始值无需查看setup\_actors表，因为该表不控制后台线程，因为后台线程没有关联的用户
     *  3)、对于任何线程，其HISTORY值可以在线程的生命周期内更改
     *  要记录线程产生的历史事件，如下条件需满足：
     *  1)、setup\_consumers表中相关联的consumers配置必须启用，如：要记录线程的等待事件历史记录，需要启用events\_waits\_history和events\_waits\_history\_long consumers
     *  2)、threads.HISTORY列必须为YES
     *  3)、setup\_instruments表中相关联的instruments配置必须启用
     *  4)、如果是前台线程，那么setup\_actors中对应主机和用户的配置行中的HISTORY列必须为YES
 *  CONNECTION\_TYPE：用于建立连接的协议，如果是后台线程则为NULL。有效值为：TCP/IP（不使用SSL建立的TCP/IP连接）、SSL/TLS（与SSL建立的TCP/IP连接）、Socket（Unix套接字文件连接）、Named Pipe（Windows命名管道连接）、Shared Memory(Windows共享内存连接）
 *  THREAD\_OS\_ID：
    
     *  由操作系统层定义的线程或任务标识符（ID）：
     *  1)、当一个MySQL线程与操作系统中与某个线程关联时，那么THREAD\_OS\_ID字段可以查看到与这个mysql线程相关联的操作系统线程ID
     *  2)、当一个MySQL线程与操作系统线程不关联时，THREAD\_OS\_ID列值为NULL。例如：用户使用线程池插件时
     *  对于Windows，THREAD\_OS\_ID对应于Process Explorer中可见的线程ID
     *  对于Linux，THREAD\_OS\_ID对应于gettid（）函数获取的值。例如：使用perf或ps -L命令或proc文件系统（/proc/\[pid\]/task/\[tid\]）可以查看此值。
 *  **PS：threads表不允许使用TRUNCATE TABLE语句**

关于线程类对象，前台线程与后台线程还有少许差别：

*  对于前台线程（由客户端连接产生的连接，可以是用户发起的连接，也可以是不同server之间发起的连接），当用户或者其他server与某个server创建了一个连接之后（连接方式可能是socket或者TCP/IP），在threads表中就会记录一条这个线程的配置信息行，此时，threads表中该线程的配置行中的INSTRUMENTED和HISTORY列值的默认值是YES还是NO，还需要看与线程相关联的用户帐户是否匹配setup\_actors表中的配置行(查看某用户在setup\_actors表中配置行的ENABLED和HISTORY列配置为YES还是NO，threads表中与setup\_actors表关联用户帐号的线程配置行中的ENABLED和HISTORY列值以setup\_actors表中的值为准)
 *  对于后台线程，不可能存在关联的用户，所以threads表中的 INSTRUMENTED和HISTORY在线程创建时的初始配置列值默认值为YES，不需要查看setup\_actors表

关闭与开启所有后台线程的监控采集功能：

# 关闭所有后台线程的事件采集
    update threads set INSTRUMENTED='NO' where TYPE='BACKGROUND';
    # 开启所有后台线程的事件采集
    update threads set INSTRUMENTED='YES' where TYPE='BACKGROUND';

关闭与开启除了当前连接之外的所有线程的事件采集（不包括后台线程）

# 关闭除了当前连接之外的所有前台线程的事件采集
    root@localhost : performance_schema 05:47:44> update threads set INSTRUMENTED='NO' where PROCESSLIST_ID!=connection_id();
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    # 开启除了当前连接之外的所有前台线程的事件采集
    root@localhost : performance_schema 05:48:32> update threads set INSTRUMENTED='YES' where PROCESSLIST_ID!=connection_id();
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    # 当然，如果要连后台线程一起操作，请带上条件PROCESSLIST_ID is NULL
    update ... where PROCESSLIST_ID!=connection_id() or PROCESSLIST_ID is NULL;

## 三、总结 ##

本文介绍了performance\_schema的常用配置详解，主要有两个模块：启动时配置和运行时设置。  
启动选项配置可以通过如下命令行命令进行查看:

mysqld --verbose --help |grep performance-schema |grep -v '\-\-' |sed '1d' |sed '/[0-9]\+/d'

登录到数据库中使用 show variables like ‘%performance\_schema%’;语句查看完整的system variables列表；

运行时设置主要介绍了七张表：

*  performance\_timers：表中记录了server中有哪些可用的事件计时器；
 *  setup\_timers：表中记录当前使用的事件计时器信息；
 *  setup\_consumers：表列出了consumers可配置列表项；
 *  setup\_instruments：表列出了instruments 列表配置项，即代表了哪些事件支持被收集；
 *  setup\_actors：表用于配置是否为新的前台server线程（与客户端连接相关联的线程）启用监视和历史事件日志记录；
 *  setup\_objects：表控制performance\_schema是否监视特定对象；
 *  threads：表对于每个server线程生成一行包含线程相关的信息；

## 四、参考 ##

[官方文档：thread-information详解][thread-information]  
[配置详解 | performance\_schema全方位介绍][_ performance_schema]

我是虚竹哥，我们明天见~

[100_MYSQL]: https://blog.csdn.net/shi_hong_fei_hei/category_11929488.html
[JAVA]: https://bbs.csdn.net/forums/xiaoxuzhu
[3b06ede781d34f3aa0dedd243965c2a8.png]: https://img-blog.csdnimg.cn/3b06ede781d34f3aa0dedd243965c2a8.png
[ecede56946b94f438f05d37abbf12143.png]: https://img-blog.csdnimg.cn/ecede56946b94f438f05d37abbf12143.png
[32a023a0eb0341be9948ea0269df61bc.png]: https://img-blog.csdnimg.cn/32a023a0eb0341be9948ea0269df61bc.png
[c0910f02a676430f99ea84bb9be29d54.png]: https://img-blog.csdnimg.cn/c0910f02a676430f99ea84bb9be29d54.png
[e3f57ddbe0274ea2b70c972df5cc3b74.png]: https://img-blog.csdnimg.cn/e3f57ddbe0274ea2b70c972df5cc3b74.png
[ee6afb40f57746e88cb27fb2f39adc1c.png]: https://img-blog.csdnimg.cn/ee6afb40f57746e88cb27fb2f39adc1c.png
[a7a743f8bbd442e18a6686097c5ad622.png]: https://img-blog.csdnimg.cn/a7a743f8bbd442e18a6686097c5ad622.png
[5dad1f50d8434a4ab7ec8a3b1d790787.png]: https://img-blog.csdnimg.cn/5dad1f50d8434a4ab7ec8a3b1d790787.png
[244f4e2b7e0a4f318e61aa3ee267e162.png]: https://img-blog.csdnimg.cn/244f4e2b7e0a4f318e61aa3ee267e162.png
[aad9bad4eb76440b9d63a3a4b70af154.png]: https://img-blog.csdnimg.cn/aad9bad4eb76440b9d63a3a4b70af154.png
[2827a90364a94427a14d9a4d994302c2.png]: https://img-blog.csdnimg.cn/2827a90364a94427a14d9a4d994302c2.png
[1392177b93014795a234ef764ff8be3e.png]: https://img-blog.csdnimg.cn/1392177b93014795a234ef764ff8be3e.png
[68febd582f5c4827bacdedbbc35cb32d.png]: https://img-blog.csdnimg.cn/68febd582f5c4827bacdedbbc35cb32d.png
[thread-information]: https://dev.mysql.com/doc/refman/5.7/en/thread-information.html
[_ performance_schema]: https://mp.weixin.qq.com/s/eZ437iw1FW-2kX7J551DQg