在上一篇 《初相识 | performance_schema全方位介绍》 中粗略介绍了如何配置与使用performance_schema,相信大家对performance_schema能够为我们提供什么样的性能数据已经有一个初步的认识,今天将带领大家一起踏上系列第二篇的征程(全系共7个篇章),在这一期里,我们将为大家全面讲解performance_schema配置方式以及各个配置表的作用。下面,请跟随我们一起开始performance_schema系统的学习之旅吧。
instruments:生产者,用于采集MySQL 中各种各样的操作产生的事件信息,对应配置表中的配置项我们可以称为监控采集配置项,以下提及生产者均统称为instruments
consumers:消费者,对应的消费者表用于存储来自instruments采集的数据,对应配置表中的配置项我们可以称为消费存储配置项,以下提及消费者均统称为consumers
友情提示:以下内容阅读起来可能比较烧脑,内容也较长,建议大家端好板凳,坐下来,点上一支烟,细细品读,这也是学习performance_schema路上不得不过的火焰山,坚持下去,"翻过这座山,你就可以看到一片海!"
在以往,我们认为自行编译安装MySQL其性能要优于官方编译好的二进制包、rpm包等。可能在MySQL早期的版本中有这样的情况, 但随着MySQL版本不断迭代,业界不少人亲测证实,目前的MySQL版本并不存在自行编译安装性能比官方编译好的二进制高性能包,所以,通常情况下,我们不建议去耗费数十分钟来编译安装MySQL,因为在大规模部署的场景下,此举十分浪费时间(需要通过编译安装的方式精简模块的场景除外)
可以使用cmake的编译选项来自行决定你的MySQL实例是否支持performance_schema的某个等待事件类别,如下:
shell> cmake . \
-DDISABLE_PSI_STAGE=1 \ #关闭STAGE事件监视器
-DDISABLE_PSI_STATEMENT=1 #关闭STATEMENT事件监视器
注意:虽然我们可以通过cmake的编译选项关闭掉某些performance_schema的功能模块,但是,通常我们不建议这么做,除非你非常清楚后续不可能使用到这些功能模块,否则后续想要使用被编译时关闭的模块,还需要重新编译。
当我们接手一个别人安装的MySQL数据库服务器时,或者你并不清楚自己安装的MySQL版本是否支持performance_schema时,我们可以通过mysqld命令查看是否支持Performance Schema
# 如果发现performance_schema开头的几个选项,则表示当前mysqld支持performance_schema,如果没有发现performance_schema相关的选项,说明当前数据库版本不支持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)
注意:在mysqld选项或show engines语句输出的结果中,如果看到有performance_schema相关的信息,并不代表已经启用了performance_schema,仅仅只是代表数据库支持,如果需要启用它,还需要在服务器启动时使用系统参数performance_schema=on(MySQL 5.7之前的版本默认关闭)显式开启
performance_schema中的配置是保存在内存中的,是易失的,也就是说保存在performance_schema配置表(本章后续内容会讲到)中的配置项在MySQL实例停止时会全部丢失。所以,如果想要把配置项持久化,就需要在MySQL的配置文件中使用启动选项来持久化配置项,让MySQL每次重启都自动加载配置项,而不需要每次重启都再重新配置。
(1) 启动选项
performance_schema有哪些启动选项呢?我们可以通过如下命令行命令进行查看:
[root@localhost ~]# 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
......
下面将对这些启动选项进行简单描述(这些启动选项是用于指定consumers和instruments配置项在MySQL启动时是否跟随打开的,之所以叫做启动选项,是因为这些需要在mysqld启动时就需要通过命令行指定或者需要在my.cnf中指定,启动之后通过show variables命令无法查看,因为他们不属于system variables。
是否在mysql server启动时就开启events_statements_current表的记录功能(该表记录当前的语句事件信息),启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新setup_consumers配置表中的events_statements_current配置项,默认值为TRUE
与performance_schema_consumer_events_statements_current选项类似,但该选项是用于配置是否记录语句事件短历史信息,默认为TRUE
与performance_schema_consumer_events_statements_current选项类似,但该选项是用于配置是否记录语句事件长历史信息,默认为FALSE
是否在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
是否在MySQL Server启动时就开启events_statements_summary_by_digest 表的记录功能,启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新digest配置项
默认值为TRUE
是否在MySQL Server启动时就开启
events_xxx_summary_by_yyy_by_event_name表的记录功能,启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新线程配置项
默认值为TRUE
是否在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表查询这些选项指定的值。
(2) system variables
与performance_schema相关的system variables可以使用如下语句查看,这些variables用于限定consumers表的存储限制,它们都是只读变量,需要在MySQL启动之前就设置好这些变量的值。
root@localhost : (none) 11:43:29> show variables like '%performance_schema%';
.....
42 rows in set (0.01 sec)
下面,我们将对这些system variables(以下称为变量)中几个需要关注的进行简单解释(其中大部分变量是-1值,代表会自动调整,无需太多关注,另外,大于-1值的变量在大多数时候也够用,如果无特殊需求,不建议调整,调整这些参数会增加内存使用量)
在MySQL启动之后,我们就无法使用启动选项来开关相应的consumers和instruments了,此时,我们如何根据自己的需求来灵活地开关performance_schema中的采集信息呢?(例如:默认配置下很多配置项并未开启,我们可能需要即时去修改配置,再如:高并发场景,大量的线程连接到MySQL,执行各种各样的SQL时产生大量的事件信息,而我们只想看某一个会话产生的事件信息时,也可能需要即时去修改配置),我们可以通过修改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 |
+-------------+-----------------+------------------+----------------+
performance_timers表中的字段含义如下:
(2) setup_timers表
setup_timers表中记录当前使用的事件计时器信息(注意:该表不支持增加和删除记录,只支持修改和查询)
可以通过UPDATE语句来更改setup_timers.TIMER_NAME列值,以给不同的事件类别选择不同的计时器,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 |
+-------------+-------------+
setup_timers表字段含义如下:
setup_consumers表列出了consumers可配置列表项(注意:该表不支持增加和删除记录,只支持修改和查询),如下:
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 |
+----------------------------------+---------+
对setup_consumers表的修改会立即影响监控,setup_consumers字段含义如下:
setup_consumers表中的consumers配置项具有层级关系,具有从较高级别到较低级别的层次结构,按照优先级顺序,可列举为如下层次结构(你可以根据这个层次结构,关闭你可能不需要的较低级别的consumers,这样有助于节省性能开销,且后续查看采集的事件信息时也方便进行筛选):
从上图中的信息中可以看到,setup_consumers表中consumers配置层次结构中:
注意:
配置项修改示例:
# 打开events_waits_current表当前等待事件记录功能
mysql> UPDATE setup_consumers SET ENABLED ='NO' WHERE NAME ='events_waits_current';
# 关闭历史事件记录功能
mysql> UPDATE setup_consumers SET ENABLED ='NO' where name like '%history%';
# where条件 ENABLED ='YES' 即为打开对应的记录表功能
......
setup_instruments 表列出了instruments 列表配置项,即代表了哪些事件支持被收集:
mysql> 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/mutex/sql/LOCK_lock_db | YES | YES |
| wait/synch/mutex/sql/LOCK_manager | YES | YES |
...
| wait/synch/rwlock/sql/LOCK_grant | YES | YES |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger | YES | YES |
| wait/synch/rwlock/sql/LOCK_sys_init_connect | YES | YES |
| wait/synch/rwlock/sql/LOCK_sys_init_slave | YES | YES |
...
| wait/io/file/sql/binlog | YES | YES |
| wait/io/file/sql/binlog_index | YES | YES |
| wait/io/file/sql/casetest | YES | YES |
| wait/io/file/sql/dbopt | YES | YES |
...
instruments具有树形结构的命名空间,从setup_instruments表中的NAME字段上可以看到,instruments名称的组成从左到右,最左边的是顶层instruments类型命名,最右边是一个具体的instruments名称,有一些顶层instruments没有其他层级的组件(如:transaction和idle,那么这个顶层类型既是类型又是具体的instruments),有一些顶层instruments具有下层instruments(如:wait/io/file/myisam/log),一个层级的instruments名称对应的组件数量取决于instruments的类型。
一个给定instruments名称的含义,需要看instruments名称的左侧命名而定,例如下边两个myisam相关名称的instruments含义各不相同: 名称中给定组件的解释取决于其左侧的组件。例如,myisam显示在以下两个名称:
# 第一种instruments表示myisam引擎的文件IO相关的instruments
wait/io/file/myisam/log
# 第二种instruments表示myisam引擎的磁盘同步相关的instruments
wait/synch/cond/myisam/MI_SORT_INFO::cond
instruments的命名格式组成:performance_schema实现的一个前缀结构(如:wait/io/file/myisam/log中的wait+由开发人员实现的instruments代码定义的一个后缀名称组成(如:wait/io/file/myisam/log中的io/file/myisam/log)
在源代码中每一个实现的instruments,如果该源代码被加载到server中,那么在该表中就会有一行对应的配置,当启用或执行instruments时,会创建对应的instruments实例,这些实例在* _instances表中可以查看到
大多数setup_instruments配置行修改会立即影响监控,但对于某些instruments,运行时修改不生效(配置表可以修改,但不生效),只有在启动之前修改才会生效(使用system variables写到配置文件中),不生效的instruments主要有mutexes, conditions, and rwlocks
setup_instruments表字段详解如下:
对于内存instruments,setup_instruments中的TIMED列将被忽略(使用update语句对这些内存instruments设置timed列为YES时可以执行成功,但是你会发现执行update之后select这些instruments的timed列还是NO),因为内存操作没有定时器信息
如果某个instruments的enabled设置为YES(表示启用这个instruments),但是timed列未设置为YES(表示计时器功能禁用),则instruments会产生事件信息,但是事件信息对应的TIMER_START,TIMER_END和TIMER_WAIT定时器值都为NULL。后续汇总表中计算sum,minimum,maximum和average时间值时会忽略这些null值
PS:setup_instruments表不允许使用TRUNCATE TABLE语句
setup_instruments中的instruments name层级结构图如下:
在setup_instruments表中的instruments顶级instruments 组件分类如下:
wait/io/table/sql/handler:
1). 表I/O操作相关的instruments。这个类别包括了对持久基表或临时表的行级访问(对数据行获取,插入,更新和删除),对于视图来说,instruments检测时会参照被视图引用的基表访问情况
2). 与大多数等待事件不同,表I/O等待可以包括其他等待。例如,表I/O可能包括文件I/O或内存操作。因此,表I/O等待的事件在events_waits_current表中的记录通常有两行(除了wait/io/table/sql/handler的事件记录之外,可能还包含一行wait/io/file/myisam/dfile的事件记录)。这种可以叫做表IO操作的原子事件
3). 某些行操作可能会导致多个表I/O等待。例如,如果有INSERT的触发器,那么插入操作可能导致触发器更新操作。
wait/lock:锁操作相关的instruments
1). wait/lock/table:表锁操作相关的instruments
2). wait/lock/metadata/sql/mdl:MDL锁操作相关的instruments
wait/synch:磁盘同步object相关的instruments, performance_schema.events_waits_xxx表中的TIMER_WAIT时间列包括了在尝试获取某个object上的锁(如果这个对象上已经存在锁)的时候被阻塞的时长。
1). wait/synch/cond:一个线程使用一个状态来向其他线程发信号通知他们正在等待的事情已经发生了。如果一个线程正在等待这个状态,那么它可以被这个状态唤醒并继续往下执行。如果是几个线程正在等待这个状态,则这些线程都会被唤醒,并竞争他们正在等待的资源,该instruments用于采集某线程等待这个资源时被阻塞的事件信息。
2). wait/synch/mutex:一个线程在访问某个资源时,使用互斥对象防止其他线程同时访问这个资源。该instruments用于采集发生互斥时的事件信息
3). wait/synch/rwlock:一个线程使用一个读写锁对象对某个特定变量进行锁定,以防止其他线程同时访问,对于使用共享读锁锁定的资源,多个线程可以同时访问,对于使用独占写锁锁定的资源,只有一个线程能同时访问,该instruments用于采集发生读写锁锁定时的事件信息
4). wait/synch/sxlock:shared-exclusive(SX)锁是一种rwlock锁 object,它提供对公共资源的写访问的同时允许其他线程的不一致读取。sxlocks锁object可用于优化数据库读写场景下的并发性和可扩展性。
要控制这些instruments的起停,将ENABLED列设置为YES或NO,要配置instruments是否收集计时器信息,将TIMED列值设置为YES或NO
setup_instruments表,对大多数instruments的修改会立即影响监控。但对于某些instruments,修改需要在mysql server重启才生效,运行时修改不生效。因为这些可能会影响mutexes、conditions和rwlocks,下面我们来看一些setup_instruments表修改示例:
# 禁用所有instruments,修改之后,生效的instruments修改会立即产生影响,即立即关闭收集功能:
mysql> UPDATE setup_instruments SET ENABLED = 'NO';
# 禁用所有文件类instruments,使用NAME字段结合like模糊匹配:
mysql> UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'wait/io/file/%';
# 仅禁用文件类instruments,启用所有其他instruments,使用NAME字段结合if函数,LIKE模糊匹配到就改为NO,没有匹配到的就改为YES:
mysql> UPDATE setup_instruments SET ENABLED = IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES');
# 启用所有类型的events的mysys子系统的instruments:
mysql> UPDATE setup_instruments SET ENABLED = CASE WHEN NAME LIKE '%/mysys/%' THEN 'YES' ELSE 'NO' END;
# 禁用指定的某一个instruments:
mysql> UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';
# 切换instruments开关的状态,“翻转”ENABLED值,使用ENABLED字段值+ if函数, IF(ENABLED = 'YES', 'NO', 'YES')表示,如果ENABLED值为YES,则修改为NO,否则修改为YES:
mysql> UPDATE setup_instruments SET ENABLED = IF(ENABLED = 'YES', 'NO', 'YES') WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';
# 禁用所有instruments的计时器:
mysql> UPDATE setup_instruments SET TIMED = 'NO';
查找innodb存储引擎的文件相关的instruments,可以用如下语句查询:
admin@localhost : performance_schema 09:16:59> 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)
PS:
一些可能常用的场景相关的设置 :
* metadata locks监控需要打开'wait/lock/metadata/sql/mdl' instruments才能监控,开启这个instruments之后在表performance_schema.metadata_locks表中可以查询到MDL锁信息 * profiing探针功能即将废弃,监控探针相关的事件信息需要打开语句: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%' ;返回结果集中的instruments,开启这些instruments之后,可以在performance_schema.events_stages_xxx表中查看原探针相关的事件信息。 * 表锁监控需要打开'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用于配置是否为新的前台server线程(与客户端连接相关联的线程)启用监视和历史事件日志记录。默认情况下,此表的最大行数为100。可以使用系统变量performance_schema_setup_actors_size在server启动之前更改此表的最大配置行数
setup_actors表的初始内容是匹配任何用户和主机,因此对于所有前台线程,默认情况下启用监视和历史事件收集功能,如下:
mysql> SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+
setup_actors表字段含义如下:
对setup_actors表的修改仅影响修改之后新创建的前台线程,对于修改之前已经创建的前台线程没有影响,如果要修改已经创建的前台线程的监控和历史事件记录功能,可以修改threads表行的INSTRUMENTED和HISTORY列值:
当一个前台线程初始化连接mysql server时,performance_schema会对表setup_actors执行查询,在表中查找每个配置行,首先尝试使用USER和HOST列(ROLE未使用)依次找出匹配的配置行,然后再找出最佳匹配行并读取匹配行的ENABLED和HISTORY列值,用于填充threads表中的ENABLED和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列生效值如下
## 当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表控制performance_schema是否监视特定对象。默认情况下,此表的最大行数为100行。要更改表行数大小,可以在server启动之前修改系统变量performance_schema_setup_objects_size的值。
setup_objects表初始内容如下所示:
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表的修改会立即影响对象监控
在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表列含义如下:
setup_objects配置表中默认的配置规则是不启用对mysql、INFORMATION_SCHEMA、performance_schema数据库下的对象进行监视的(ENABLED和TIMED列值全都为NO)
performance_schema在setup_objects表中进行查询匹配时,如果发现某个OBJECT_TYPE列值有多行,则会尝试着匹配更多的配置行,如下(performance_schema按照如下顺序进行检查):
对于表对象相关事件,instruments是否生效需要看setup_objects与setup_instruments两个表中的配置内容相结合,以确定是否启用instruments以及计时器功能(例如前面说的I/O事件:wait/io/table/sql/handler instrument和表锁事件:wait/lock/table/sql/handler instrument,在setup_instruments配置表中也有明确的配置选项):
# setup_instruments表
admin@localhost : performance_schema 03:06:01> select * from setup_instruments where name like '%/table/%';
+-----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-----------------------------+---------+-------+
| wait/io/table/sql/handler | YES | YES |
| wait/lock/table/sql/handler | YES | YES |
+-----------------------------+---------+-------+
2 rows in set (0.00 sec)
# 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表对于每个server线程生成一行包含线程相关的信息,例如:显示是否启用监视,是否启用历史事件记录功能,如下:
admin@localhost : performance_schema 04:25:55> select * from threads where TYPE='FOREGROUND' limit 2\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
*************************** 2. row ***************************
............
2 rows in set (0.00 sec)
当performance_schema初始化时,它根据当时存在的线程每个线程生成一行信息记录在threads表中。此后,每新建一个线程在该表中就会新增一行对应线程的记录
新线程信息的INSTRUMENTED和HISTORY列值由setup_actors表中的配置决定。有关setup_actors表的详细信息参见3.3.5. 节
当某个线程结束时,会从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表字段含义如下:
关于线程类对象,前台线程与后台线程还有少许差别
关闭与开启所有后台线程的监控采集功能
# 关闭所有后台线程的事件采集
root@localhost : performance_schema 05:46:17> update threads set INSTRUMENTED='NO' where TYPE='BACKGROUND';
Query OK, 40 rows affected (0.00 sec)
Rows matched: 40 Changed: 40 Warnings: 0
# 开启所有后台线程的事件采集
root@localhost : performance_schema 05:47:08> update threads set INSTRUMENTED='YES' where TYPE='BACKGROUND';
Query OK, 40 rows affected (0.00 sec)
Rows matched: 40 Changed: 40 Warnings: 0
关闭与开启除了当前连接之外的所有线程的事件采集(不包括后台线程)
# 关闭除了当前连接之外的所有前台线程的事件采集
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仍然比较迷糊,那么建议按照如下步骤动动手、看一看:
performance_schema配置部分为整个performance_schema的难点,为了后续更好地学习performance_schema,建议初学者本章内容多读两遍。
罗小波·沃趣科技高级数据库技术专家
IT从业多年,历任运维工程师、高级运维工程师、运维经理、数据库工程师,曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写,熟悉MySQL体系结构,Innodb存储引擎,喜好专研开源技术,追求完美。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。