performance_schema 笔记（二）——配置详解

最新推荐文章于 2024-05-08 17:53:24 发布

Hehuyi_In

最新推荐文章于 2024-05-08 17:53:24 发布

阅读量2.7k

点赞数

分类专栏： MySQL 性能

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/100933404

版权

性能同时被 2 个专栏收录

149 篇文章 21 订阅

订阅专栏

MySQL

55 篇文章 4 订阅

订阅专栏

提前预警：这一篇巨长。。。做好心理准备。。。删除了书里重复说明和过于复杂的一些解释，完整版请参考原书《MySQL 性能优化金字塔法则》

零、基本概念

instruments：生产者，用于采集MySQL 中各种各样的操作产生的事件信息，可以称为监控采集配置项

consumers：消费者，用于存储来自instruments采集的数据，可以称为消费存储配置项

配置可以在三个阶段进行 —— 编译时、启动时、运行时，下面也就是从这三个阶段分别来介绍配置方法。

一、编译时配置

如果选择编译安装，可以使用cmake的编译选项来决定MySQL实例是否支持performance_schema的某个等待事件类别

cmake . \
-DDISABLE_PSI_STAGE=1 \   #关闭STAGE事件监视器
-DDISABLE_PSI_STATEMENT=1  #关闭STATEMENT事件监视器

注意：虽然可以通过cmake的编译选项关闭某些performance_schema的功能模块，但是通常不建议，除非你非常清楚后续不可能使用到这些功能模块，否则后续想要使用时还需要重新编译。

当我们接手一个别人安装的MySQL数据库服务器时，或者并不清楚自己安装的版本是否支持performance_schema时，可以通过mysqld命令查看：

# 如果发现performance_schema开头的几个选项，则表示当前mysqld支持performance_schema，如果没有，说明当前数据库版本不支持，可能需要升级mysql版本：
shell> mysqld --verbose --help
...
--performance_schema
                  Enable the performance schema.
--performance_schema_events_waits_history_long_size=#
                  Number of rows in events_waits_history_long.

还可以登录到MySQL实例中使用SQL命令查看是否支持performance_schema：

# Support列值为YES表示数据库支持，否则你可能需要升级mysql版本：
mysql> SHOW ENGINES\G
...
admin@localhost : (none) 12:54:00> show engines;
*************************** 6. row ***************************
  Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
      XA: NO
Savepoints: NO
9 rows in set (0.00 sec)

注意：支持不等于已启用，还需要在服务器启动时使用performance_schema=on（5.7之前的版本默认关闭）显式开启

二、启动时配置

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

1. 启动选项

performance_schema有哪些启动选项呢？可以通过如下命令行命令进行查看:

mysqld --verbose --help |grep performance-schema |grep -v '\-\-' |sed '1d' |sed '/[0-9]\+/d'
......
performance-schema-consumer-events-stages-current FALSE
performance-schema-consumer-events-stages-history FALSE
performance-schema-consumer-events-stages-history-long FALSE
performance-schema-consumer-events-statements-current TRUE
performance-schema-consumer-events-statements-history TRUE
performance-schema-consumer-events-statements-history-long FALSE
performance-schema-consumer-events-transactions-current FALSE
performance-schema-consumer-events-transactions-history FALSE
performance-schema-consumer-events-transactions-history-long FALSE
performance-schema-consumer-events-waits-current FALSE
performance-schema-consumer-events-waits-history FALSE
performance-schema-consumer-events-waits-history-long FALSE
performance-schema-consumer-global-instrumentation TRUE
performance-schema-consumer-statements-digest TRUE
performance-schema-consumer-thread-instrumentation TRUE
performance-schema-instrument  
......

之所以叫做启动选项，是因为它们在mysqld启动时就通过命令行指定或者在my.cnf中指定。这些启动选项无法使用show variables语句查看，但可以通过setup_instruments和setup_consumers表查询选项值。

下面将对这些启动选项进行简单描述：

performance_schema_consumer_events_statements_current=TRUE

是否在mysql启动时就开启events_statements_current表的记录功能（记录当前的语句事件信息），启动后也可以在setup_consumers表中使用UPDATE语句动态更新setup_consumers配置表中的events_statements_current配置项，默认为TRUE

performance_schema_consumer_events_statements_history=TRUE

同上，用于配置是否记录语句事件历史信息，默认为TRUE

performance_schema_consumer_events_stages_history_long=FALSE

同上，用于配置是否记录语句事件长历史信息，默认为FALSE

除了statement事件之外，还支持：wait事件、state事件、transaction事件，它们与statement事件一样都有三个启动项分别进行配置，但这些等待事件默认未启用，如果需要在MySQL 启动时一同启动，通常需要写进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'

2. 系统变量

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

show variables like '%performance_schema%';

下面对几个需要关注的变量进行简单解释，变量是-1代表会自动调整，无需太多关注。另外，大于-1的变量在大多数时候也够用，如果无特殊需求，不建议调整，调整这些参数会增加内存使用量。

performance_schema=ON

控制performance_schema功能的开关，5.7开始默认开启。如果mysqld在初始化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表中的最大行数，超过这个限制之后，最早的记录将被覆盖。

performance_schema_events_statements_history_size=10

控制events_statements_history表中单个线程的最大行数，超过这个限制之后，单个会话最早的记录将被覆盖。除statement事件外，wait、state、transaction事件也都有三个参数分别进行存储限制配置，这里不再赘述。

performance_schema_max_digest_length=1024

控制标准化形式的SQL文本在存入performance_schema时的最大长度，该变量与max_digest_length变量相关。默认值1024字节，取值范围0~1048576

performance_schema_max_sql_text_length=1024

控制存入events_statements_current，events_statements_history和events_statements_history_long语句事件表中的SQL_TEXT列的最大SQL长度字节数。超出变量设置值的部分将被丢弃，不会记录。一般情况下不需要调整该参数，默认值为1024字节，取值范围为0~1048576，5.7.6版本引入。降低系统变量该值可以减少内存使用，但如果SQL被截断部分有较大差异可能会导致无法区分这些SQL。

三、运行时配置

MySQL启动之后，我们就无法使用启动选项来开关相应的consumers和instruments了，此时，可以通过修改performance_schema下的配置表中的配置项进行修改。

这些配置表中的配置项之间存在着关联关系，按照配置影响的先后顺序，可整理为如下图(该表仅代表个人理解)：

1. performance_timers表

performance_timers表（只读表）记录了server中有哪些可用的事件计时器，setup_timers配置表中的配置项引用该表中的计时器。每个计时器的精度和数量相关的特征值会有所不同，可以通过如下语句查看performance_timers表中记录的计时器和相关的特征信息：

mysql> SELECT * FROM performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE | 2389029850 | 1 | 72 |
| NANOSECOND | 1000000000 | 1 | 112 |
| MICROSECOND | 1000000 | 1 | 136 |
| MILLISECOND | 1036 | 1 | 168 |
| TICK | 105 | 1 | 2416 |
+-------------+-----------------+------------------+----------------+

字段含义如下

TIMER_NAME：可用计时器名称，CYCLE指基于CPU周期计数器的定时器。在setup_timers表中可以使用performance_timers表中列值不为null的计时器（为NULL表示该定时器可能不支持当前server所在平台）
TIMER_FREQUENCY：表示每秒对应的计时器单位值（即单位转换）。CYCLE计时器的换算值通常与CPU的频率相关；TICK计时器的换算值可能因平台而异
TIMER_RESOLUTION：计时器精度值，表示在每个计时器被调用时额外增加的值（即使用该计时器时，计时器被调用一次，需要额外增加的值）。如果计时器的分辨率为10，则计时器的时间值在计时器每次被调用时，相当于TIMER_FREQUENCY值+10
TIMER_OVERHEAD：在使用定时器获取事件时开销的最小周期值（performance_schema在初始化期间调用计时器20次，选择一个最小值作为此字段值），每个事件的时间开销值是计时器显示值的两倍，因为在事件的开始和结束时都调用计时器。注意：计时器代码仅用于支持计时事件，对于非计时类事件（如调用次数），计时器统计开销方法不适用

2. setup_timers表

setup_timers表中记录了当前使用的事件计时器信息（该表只支持select和update）

可以通过UPDATE更改setup_timers.TIMER_NAME列值，给不同的事件类别选择不同的计时器，列有效值来自前面提到的performance_timers.TIMER_NAME列值。

对setup_timers表的修改会立即生效。这可能导致正在执行的事件使用修改前的计时器作为开始时间，使用修改之后的计时器作为结束时间，为了避免这种情况下，请在修改之后使用TRUNCATE TABLE语句重置performance_schema中相关表中的统计信息。

mysql> SELECT * FROM setup_timers;
+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
+-------------+-------------+

3. setup_consumers表

setup_consumers表列出了consumers可配置列表项，即启用下面哪些功能。该表只支持select和update，修改会立即生效

mysql> SELECT * FROM setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+

字段含义如下：

NAME：consumers配置名称
ENABLED：consumers是否启用，有效值为YES或NO。设置为NO时，server不会维护这些consumers表的内容新增和删除，也会关闭consumers对应的instruments

setup_consumers表中的consumers配置项具有优先级顺序，可以关闭不需要的低级别consumers减少性能开销。

优先级从高到低如下：

从图中可以看到：

global_instrumentation处于顶级位置，优先级最高。

* 当global_instrumentation为YES时，会检查setup_consumers表中的statements_digest和thread_instrumentation的配置，附带检查setup_instruments、setup_objects、setup_timers配置表；会维护全局events输出表：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance、file_summary_by_event_name、objects_summary_global_by_type、memory_summary_global_by_event_name、table_lock_waits_summary_by_table、table_io_waits_summary_by_index_usage、table_io_waits_summary_by_table、events_waits_summary_by_instance、events_waits_summary_global_by_event_name、events_stages_summary_global_by_event_name、events_statements_summary_global_by_event_name、events_transactions_summary_global_by_event_name

* 当global_instrumentation为NO时，不会检查任何更低级别的consumers配置，不会维护任何events输出表(memory_%开头的events输出表除外，这些表维护只受setup_instruments配置表控制)

statements_digest和thread_instrumentation处于同一级别，global_instrumentation为YES时配置才会被检测

* 当statements_digest为YES时，statements_digest consumers没有更低级别的配置，依赖于global_instrumentation为YES时配置才会被检测，会维护events输出表events_statements_summary_by_digest

* 当statements_digest为NO时，不维护events输出表events_statements_summary_by_digest

* 当thread_instrumentation为YES时，会检查setup_consumers表中的events_xxx_current配置（xxx表示：waits、stages、statements、transactions），会附带检查setup_actors、threads配置表。会维护events输出表 events_xxx_summary_by_yyy_by_event_name，其中： xxx含义同上; yyy表示：thread、user、host、account

* 当thread_instrumentation为NO时，不检查setup_consumers表中的events_xxx_current配置，不维护events_xxx_current及其更低级别的events输出表

events_xxx_current系列consumers处于同一级别，thread_instrumentation为YES时配置才会被检测

* 当events_xxx_current为YES时，会检测setup_consumers配置表中的events_xxx_history和events_xxx_history_long系列 consumers配置，会维护events_xxx_current系列表

* 当events_xxx_current为NO时，不检测setup_consumers配置表中的events_xxx_history和events_xxx_history_long系列 consumers配置，不维护events_xxx_current系列表

events_xxx_history和events_xxx_history_long系列consumers处于同一级别，优先级次于events_xxx_current 系列consumers，events_xxx_current 系列consumers为YES时才会被检测

* 当events_xxx_history为YES时，没有更低级别的conosumers配置需要检测，但会附带检测setup_actors、threads配置表中的HISTORY列值，会维护events_xxx_history系列表，反之不维护

* 当events_xxx_history_long为YES时，没有更低级别的conosumers配置需要检测，但会附带检测setup_actors、threads配置表中的HISTORY列值，会维护events_xxx_history_long系列表，反之不维护

注意：

events_xxx_summary_by_yyy_by_event_name的开关由global_instrumentation控制，且表中是有固定数据行，不可清理，truncate或者关闭相关的consumers时只是不统计相关的instruments收集的events数据，相关字段为0值
如果performance_schema在对setup_consumers表做检查时发现某个consumers配置行的ENABLED 列值不为YES，则与这个consumers相关联的events输出表中就不会接收存储任何事件记录
高级别的consumers设置为NO时，依赖于它的更低级别的consumers将一同被禁用

配置项修改示例：

-- 关闭events_waits_current表当前等待事件记录功能
UPDATE setup_consumers SET ENABLED ='NO' WHERE NAME ='events_waits_current';
-- 关闭历史事件记录功能
UPDATE setup_consumers SET ENABLED ='NO' where name like '％history％';

4. setup_instruments表

setup_instruments 表列出了instruments 列表配置项，即哪些事件支持被收集。大多数setup_instruments配置行修改会立即生效，但对于某些instruments（mutexes, conditions, rwlocks），修改需要重启生效

SELECT * FROM setup_instruments;
+------------------------------------------------------------+---------+-------+
| NAME                                                      | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
...
| wait/synch/mutex/sql/LOCK_global_read_lock                | YES    | YES  |
| wait/synch/mutex/sql/LOCK_global_system_variables          | YES    | YES  |
...
| wait/synch/rwlock/sql/LOCK_grant                          | YES    | YES  |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger                  | YES    | YES  |

字段含义如下：

NAME：instruments名称，具有树形结构的命名空间
ENABLED：instruments是否启用，有效值为YES或NO。设置为NO时，不会产生任何事件信息
TIMED：instruments是否收集时间信息，不收集则TIMER_START，TIMER_END和TIMER_WAIT都为NULL

对于内存相关instruments，setup_instruments中的TIMED列将被忽略（可以使用update语句设置，但会发现执行update之后这些instruments的timed列还是NO），因为内存操作没有定时器信息。

setup_instruments中的instruments name层级结构图如下：

setup_instruments表中顶级instruments 组件分类如下：

Idle 组件（1个）
transaction 组件（1个）
Memory 组件（377个），默认情况下禁用了大多数memory instruments
Stage Instrument 组件（129个）
Statement Instrument 组件（193个）
Wait Instrument 组件（319个），包含如下几个子类

wait/io：用于检测I/O操作的instruments
wait/io/table/sql/handler：与表I/O操作相关的instruments
wait/lock：锁操作相关的instruments 
wait/synch：磁盘同步对象相关的instruments

下面来看一些setup_instruments表修改示例：

-- 禁用所有instruments，修改之后，生效的instruments修改会立即产生影响，即立即关闭收集功能：
UPDATE setup_instruments SET ENABLED = 'NO';
-- 禁用所有文件类instruments，使用NAME字段结合like模糊匹配：
UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'wait/io/file/%';
-- 仅禁用文件类instruments，启用所有其他instruments，使用NAME字段结合if函数，LIKE模糊匹配到就改为NO，没有匹配到的就改为YES：
UPDATE setup_instruments SET ENABLED = IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES');
-- 启用所有类型的events的mysys子系统的instruments：
UPDATE setup_instruments SET ENABLED = CASE WHEN NAME LIKE '%/mysys/%' THEN 'YES' ELSE 'NO' END;
-- 禁用指定的instruments：
UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';

查找innodb存储引擎的文件相关的instruments，可以用如下语句

select * from setup_instruments where name like 'wait/io/file/innodb/%';
+--------------------------------------+---------+-------+
| NAME                                 | ENABLED | TIMED |
+--------------------------------------+---------+-------+
| wait/io/file/innodb/innodb_data_file | YES     | YES   |
| wait/io/file/innodb/innodb_log_file  | YES     | YES   |
| wait/io/file/innodb/innodb_temp_file | YES     | YES   |
+--------------------------------------+---------+-------+
3 rows in set (0.00 sec)

常用场景及相关设置如下：

metadata locks监控。需要打开'wait/lock/metadata/sql/mdl' instruments，开启后在metadata_locks表中查询MDL锁信息
原profiing功能监控。profiing功能即将废弃，监控原profiing功能相关事件需要启用以下instruments：

select * from setup_instruments where name like '%stage/sql%' and name not like '%stage/sql/Waiting%' and name not like '%stage/sql/%relay%' and name not like '%stage/sql/%binlog%' and name not like '%stage/sql/%load%' ;

表锁监控。需要打开'wait/io/table/sql/handler' instruments，开启后在table_handles表中会记录了当前打开了哪些表（执行flush tables强制关闭打开的表时，该表中的信息会被清空），哪些表已经被加了表锁，表锁被哪个会话持有
top sql监控。需要打开'statement/sql/select' instruments，通过查询events_statements_xxx表的SQL_TEXT字段可以看到原始的SQL语句，查询TIMER_WAIT字段为总响应时间，LOCK_TIME字段为加锁时间（时间单位是皮秒，除以10^12才是秒）

5. setup_actors表

setup_actors用于配置是否为新的前台线程启用监视和历史事件日志记录。默认情况下，此表的最大行数为100。可以使用系统变量performance_schema_setup_actors_size在server启动之前更改此表的最大配置行数。setup_actors表允许TRUNCATE TABLE或DELETE。

对于每个新的前台线程，perfromance_schema会与该表中的User,Host列进行匹配，如果匹配到某个配置行，则继续匹配该行的ENABLED和HISTORY列值，ENABLED和HISTORY列值也用于填充threads表中的ENABLED和HISTORY列值。如果没有匹配到User,Host列，则该线程的INSTRUMENTED和HISTORY列将设置为NO，表示不对这个线程进行监控，不记录该线程的历史事件信息。

对于后台线程，INSTRUMENTED和HISTORY列值默认为YES。后台线程在创建时不会查看setup_actors表的配置，因为该表只能控制前台线程。如果要干预后台线程默认的设置，需要查询threads表找到相应的线程，使用UPDATE直接修改threads表中的INSTRUMENTED和HISTORY列值。

setup_actors表默认匹配任何用户和主机，因此对于所有前台线程，默认启用监视和历史事件收集功能。如果清空该表，则所有前台线程都不启用监视和历史事件收集功能。

mysql> SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES    | YES    |
+------+------+------+---------+---------+

setup_actors表字段含义如下：

HOST：要匹配的主机名/ip
USER：要匹配的用户名
ROLE：当前未使用，MySQL 8.0中才启用角色功能
ENABLED：是否启用与HOST，USER，ROLE匹配的前台线程的监控功能，有效值为：YES或NO
HISTORY：是否启用与HOST， USER，ROLE匹配的前台线程的历史事件记录功能，有效值为：YES或NO

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

一个修改示例如下：

-- 首先使用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列生效值如下：
1. 当joe从localhost连接到mysql server时，则连接符合第一个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED和HISTORY列值变为YES
2. 当joe从hosta.example.com连接到mysql server时，则连接符合第二个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED列值为YES，HISTORY列值为NO
3. 当joe从其他任意主机连接到mysql server时，则连接符合第三个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED和HISTORY列值变为NO
4. 当sam从任意主机连接到mysql server时，则连接符合第三个INSERT语句插入的配置行，threads表中对应配置行的INSTRUMENTED列值变为NO，HISTORY列值为YES
5. 除了joe和sam用户之外，其他任何用户从任意主机连接到mysql server时，匹配到第一个UPDATE语句更新之后的默认配置行，threads表中对应配置行的INSTRUMENTED和HISTORY列值变为NO
6. 如果把UPDATE语句改成DELETE，让未明确指定的用户在setup_actors表中找不到任何匹配行，则threads表中对应配置行的INSTRUMENTED和HISTORY列值变为NO */

6. setup_objects表

setup_objects表控制performance_schema是否监视特定对象。默认情况下，此表的最大行数为100行。要更改表行数大小，可以在server启动之前修改系统变量performance_schema_setup_objects_size的值。对setup_objects表的修改会立即生效。

setup_objects表初始内容如下所示：

注意：对于INFORMATION_SCHEMA数据库，虽然该表中有一行配置，但是无论在该表中如何修改，都不会监控该库

mysql> SELECT * FROM setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT      | mysql              | %          | NO      | NO    |
| EVENT      | performance_schema | %          | NO      | NO    |
| EVENT      | information_schema | %          | NO      | NO    |
| EVENT      | %                  | %          | YES    | YES  |
| FUNCTION    | mysql              | %          | NO      | NO    |
| FUNCTION    | performance_schema | %          | NO      | NO    |
| FUNCTION    | information_schema | %          | NO      | NO    |
| FUNCTION    | %                  | %          | YES    | YES  |
| PROCEDURE  | mysql              | %          | NO      | NO    |
| PROCEDURE  | performance_schema | %          | NO      | NO    |
| PROCEDURE  | information_schema | %          | NO      | NO    |
| PROCEDURE  | %                  | %          | YES    | YES  |
| TABLE      | mysql              | %          | NO      | NO    |
| TABLE      | performance_schema | %          | NO      | NO    |
| TABLE      | information_schema | %          | NO      | NO    |
| TABLE      | %                  | %          | YES    | YES  |
| TRIGGER    | mysql              | %          | NO      | NO    |
| TRIGGER    | performance_schema | %          | NO      | NO    |
| TRIGGER    | information_schema | %          | NO      | NO    |
| TRIGGER    | %                  | %          | YES    | YES  |
+-------------+--------------------+-------------+---------+-------+

setup_objects表列含义如下：

OBJECT_TYPE：instruments类型
OBJECT_SCHEMA：数据库名
OBJECT_NAME：对象名
ENABLED：是否开启对某个类型对象的监视功能，有效值为：YES或NO
TIMED：是否开启对某个类型对象的时间收集功能，有效值为：YES或NO

说明：

对于表对象相关事件，instruments是否生效需要结合setup_objects与setup_instruments两个表中的配置内容，以确定是否启用instruments以及计时器功能：

只有在Setup_instruments和setup_objects中的ENABLED列都为YES时，表的instruments才会生成事件信息
只有在Setup_instruments和setup_objects中的TIMED列都为YES时，表的instruments才会启用计时器功能

对于存储程序对象相关的事件，只需要从setup_objects表中读取配置项的ENABLED和TIMED列值，因为存储程序对象在setup_instruments表中没有对应的配置项

如果普通表和临时表名称相同，则在setup_objects表中进行匹配时，针对这两种类型的表的匹配规则都同时生效

7. threads表

threads表对于每个server线程生成一行信息。performance_schema初始化时，会为当时存在每个线程生成一行信息记录在threads表中。此后，每新建一个线程在该表中就会新增一行对应线程的记录，新线程信息的INSTRUMENTED和HISTORY列值由setup_actors表中的配置决定。当某个线程结束时，会从threads表中删除对应行，不允许执行truncate。

PROCESSLIST_*开头的列提供与INFORMATION_SCHEMA.PROCESSLIST表或SHOW PROCESSLIST语句类似的信息。但threads表中与其他两个信息来源有所不同：

对threads表的访问不需要互斥体，对server性能影响最小。使用INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST查询线程信息的方式会损耗一定性能，因为需要互斥体。
threads表为每个线程提供附加信息，例如：它是前台还是后台线程，以及与线程相关联的server内部信息
threads表提供有关后台线程的信息，而INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST不提供
可以通过threads表中的instrumented字段灵活地动态开关某个线程的监视功能
对于INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST，需要有PROCESS权限，对于threads表只要有SELECT权限就可以查看所有用户的线程信息

select * from threads where TYPE='FOREGROUND' limit 1\G;

*************************** 1. row ***************************
      THREAD_ID: 43
          NAME: thread/sql/compress_gtid_table
          TYPE: FOREGROUND
PROCESSLIST_ID: 1
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
PROCESSLIST_TIME: 27439
PROCESSLIST_STATE: Suspending
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 1
          ROLE: NULL
  INSTRUMENTED: YES
        HISTORY: YES
CONNECTION_TYPE: NULL
  THREAD_OS_ID: 3652

threads表字段含义如下：

THREAD_ID：线程的唯一标识符（ID）
NAME：与server中的线程检测代码相关联的名称（注意这里不是instruments名）。例如 thread/sql/one_connection对应于负责处理用户连接的代码中的线程函数，thread/sql/main表示server的main()函数
TYPE：线程类型，有效值为 FOREGROUND、BACKGROUND
PROCESSLIST_ID：该列值与show processlist语句、INFORMATION_SCHEMA.PROCESSLIST表、connection_id()函数返回的线程ID值相等。另外，threads表中记录了内部线程，内部线程在threads表中该字段为NULL，因此在threads表中NULL值不唯一（可能有多个后台线程）
PROCESSLIST_USER：与前台线程相关联的用户名，对于后台线程为NULL
PROCESSLIST_HOST：与前台线程关联的客户端的主机名，对于后台线程为NULL。不包括TCP/IP连接的端口号。端口信息需要查询socket_instances表
PROCESSLIST_DB：线程的默认数据库，如果没有则为NULL
PROCESSLIST_COMMAND：对于前台线程，代表当前客户端正在执行的command类型，如果是sleep则表示当前会话处于空闲状态。详细参考 https://dev.mysql.com/doc/refman/5.7/en/thread-information.html。后台线程不会执行这些command，此列值可能为NULL
PROCESSLIST_TIME：当前线程已处于当前线程状态的持续时间（秒）
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
HISTORY：是否记录线程的历史事件。有效值：YES、NO
CONNECTION_TYPE：用于建立连接的协议，如果是后台线程则为NULL。有效值为：TCP/IP、SSL/TLS（使用SSL建立的TCP/IP连接）、Socket、Named Pipe（Windows命名管道连接）、Shared Memory(Windows共享内存连接）
THREAD_OS_ID：由操作系统层定义的线程或任务标识符（ID）

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

-- 关闭所有后台线程的事件采集
update threads set INSTRUMENTED='NO' where TYPE='BACKGROUND';
Query OK, 40 rows affected (0.00 sec)
Rows matched: 40  Changed: 40  Warnings: 0

-- 开启所有后台线程的事件采集
update threads set INSTRUMENTED='YES' where TYPE='BACKGROUND';
Query OK, 40 rows affected (0.00 sec)
Rows matched: 40  Changed: 40  Warnings: 0

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

-- 关闭除了当前连接之外的所有前台线程的事件采集
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

-- 开启除了当前连接之外的所有前台线程的事件采集
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;

本篇内容到这里就接近尾声了，建议按照如下步骤动动手、看一看：

使用命令行命令 mysqld --verbose --help |grep performance-schema |grep -v '--' |sed '1d' |sed '/[0-9]+/d'; 查看完整的启动选项列表
登录到数据库中使用 show variables like '%performance_schema%';语句查看完整的system variables列表
登录到数据库中使用 use performance_schema;语句切换到schema下，然后使用show tables;语句查看一下完整的table列表，并手工执行show create table tb_xxx;查看表结构，select * from xxx;查看表中的内容

Hehuyi_In

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
performance_schema 笔记（二）——配置详解

一、基本概念instruments：生产者，用于采集MySQL 中各种各样的操作产生的事件信息，可以称为监控采集配置项consumers：消费者，用于存储来自instruments采集的数据，可以称为消费存储配置项二、启动时配置performance_schema中的配置保存在内存中，是易失的，也就是说保存在performance_schema配置表中的配置项在MySQL...
复制链接

扫一扫