用于修改配置的存储过程 | 全方位认识 sys 系统库
作者 罗小波·沃趣科技高级数据库技术专家
出品 沃趣科技
在本系列中前面用了大量篇幅介绍完了 sys 系统库的视图，利用这些视图我们可以方便快捷地查询到performance_schema、information_schema下的内容，但对于performance_schema下的instrument和consumer配置信息属于需要修改的内容，除了直接使用update语句修改配置表之外，是不是也有类似查询视图一样的快捷方式呢？有的，本期的内容开始给大家介绍一些修改、确认配置相关的存储过程。
PS：下文中如果存储过程定义文本较短的会列出部分存储过程的定义文本，以便大家更直观地学习它们。过长的存储过程定义文本请自行按照《初相识|全方位认识 sys 系统库》一文中介绍的下载路径下载查看。
01
ps_setup_disable_background_threads()
禁用所有后台线程的性能事件采集功能，该存储过程通过修改performance_schema.threads表实现，把所有后台线程的instrumented字段设置为NO
该存储过程执行时无需任何参数，返回一个被关闭的线程数量值（已经处于关闭状态的线程不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行数），被关闭的线程不会再收集任何性能事件数据
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_disable_background_threads;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_disable_background_threads ()
COMMENT '
......
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.threads
SET instrumented = 'NO'
WHERE type = 'BACKGROUND';
SELECT CONCAT('Disabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
示例(无需传参)
admin@localhost : sys 09:48:12> CALL sys.ps_setup_disable_background_threads();
+--------------------------------+
| summary |
+--------------------------------+
| Disabled 40 background threads |
+--------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
02
ps_setup_disable_consumer()
禁用指定的consumers，该存储过程通过修改performance_schema.setup_consumers表实现，调用时需要传入一个consumer name字符串作为参数值，修改performance_schema.setup_consumers表的enabled字段为NO，返回一个被关闭的consumers数量（已经处于关闭状态的consumers不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
传入的参数字符串在内部使用 like ％consumer％;的形式模糊匹配performance_schema.setup_consumers表的name字段
参数：
consumer VARCHAR(128)：匹配consumers名称的值，通过使用like ％consumer％;的形式模糊匹配setup_consumers表的name字段执行UPDATE操作，注意，如果传入值为''空值，则会匹配到所有的consumers
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_disable_consumer;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_disable_consumer (
IN consumer VARCHAR(128)
)
COMMENT '
Description
-----------
Disables consumers within Performance Schema
matching the input pattern.
Parameters
-----------
consumer (VARCHAR(128)):
A LIKE pattern match (using "%consumer%") of consumers to disable
Example
-----------
To disable all consumers:
mysql> CALL sys.ps_setup_disable_consumer(\'\');
+--------------------------+
| summary |
+--------------------------+
| Disabled 15 consumers |
+--------------------------+
1 row in set (0.02 sec)
To disable just the event_stage consumers:
mysql> CALL sys.ps_setup_disable_comsumers(\'stage\');
+------------------------+
| summary |
+------------------------+
| Disabled 3 consumers |
+------------------------+
1 row in set (0.00 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.setup_consumers
SET enabled = 'NO'
WHERE name LIKE CONCAT('%', consumer, '%');
SELECT CONCAT('Disabled ', @rows := ROW_COUNT(), ' consumer', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
示例（见存储过程comment部分，下文中凡是存储过程定义语句带comment的使用示例都参考comment部分，不再熬述）
03
ps_setup_disable_instrument()
禁用指定的instruments，通过修改performance_schema.setup_instruments表实现，调用时传入值作为存储过程内部更新语句的name字段值，修改enabled和timed字段为NO，返回一个被关闭的instruments数量（已经处于关闭状态的instruments不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
传入的参数字符串使用 like ％in_pattern％;的形式模糊匹配setup_instruments表的name字段
参数：
in_pattern VARCHAR(128)：匹配instruments名称的值，通过使用like ％in_pattern％;的形式模糊匹配setup_instrumentss表的name字段执行UPDATE操作，注意，如果传入值为''空值，则会匹配到所有的instruments
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_disable_instrument;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_disable_instrument (
IN in_pattern VARCHAR(128)
)
COMMENT '
Description
-----------
Disables instruments within Performance Schema
matching the input pattern.
Parameters
-----------
in_pattern (VARCHAR(128)):
A LIKE pattern match (using "%in_pattern%") of events to disable
Example
-----------
To disable all mutex instruments:
mysql> CALL sys.ps_setup_disable_instrument(\'wait/synch/mutex\');
+--------------------------+
| summary |
+--------------------------+
| Disabled 155 instruments |
+--------------------------+
1 row in set (0.02 sec)
To disable just a specific TCP/IP based network IO instrument:
mysql> CALL sys.ps_setup_disable_instrument(\'wait/io/socket/sql/server_tcpip_socket\');
+------------------------+
| summary |
+------------------------+
| Disabled 1 instruments |
+------------------------+
1 row in set (0.00 sec)
To disable all instruments:
mysql> CALL sys.ps_setup_disable_instrument(\'\');
+--------------------------+
| summary |
+--------------------------+
| Disabled 547 instruments |
+--------------------------+
1 row in set (0.01 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.setup_instruments
SET enabled = 'NO', timed = 'NO'
WHERE name LIKE CONCAT('%', in_pattern, '%');
SELECT CONCAT('Disabled ', @rows := ROW_COUNT(), ' instrument', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
04
ps_setup_disable_thread()
禁用指定线程的性能事件采集功能，通过修改performance_schema.threads表实现，调用时传入值作为存储过程内部语句的processlist_id字段值（或者是show processlist;输出的id字段值），修改instrumented字段为NO，返回一个被关闭的线程数量（已经处于关闭状态的线程不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
参数：
in_connection_id BIGINT：连接ID(进程ID)，为performance_schema.theads表的PROCESSLIST_ID列或SHOW PROCESSLIST输出的id列值
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_disable_thread;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_disable_thread (
IN in_connection_id BIGINT
)
COMMENT '
Description
-----------
Disable the given connection/thread in Performance Schema.
Parameters
-----------
in_connection_id (BIGINT):
The connection ID (PROCESSLIST_ID from performance_schema.threads
or the ID shown within SHOW PROCESSLIST)
Example
-----------
mysql> CALL sys.ps_setup_disable_thread(3);
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)
To disable the current connection:
mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.threads
SET instrumented = 'NO'
WHERE processlist_id = in_connection_id;
SELECT CONCAT('Disabled ', @rows := ROW_COUNT(), ' thread', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
05
ps_setup_enable_background_threads()
启用所有后台线程的性能事件采集功能，通过修改performance_schema.threads表实现，把所有后台线程的instrumented字段设置为YES实现，返回一个已启用性能事件采集功能的线程数量（已经处于启用状态的线程不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
该存储过程执行时无需给定任何参数
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_enable_background_threads;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_enable_background_threads ()
COMMENT '
Description
-----------
Enable all background thread instrumentation within Performance Schema.
Parameters
-----------
None.
Example
-----------
mysql> CALL sys.ps_setup_enable_background_threads();
+-------------------------------+
| summary |
+-------------------------------+
| Enabled 18 background threads |
+-------------------------------+
1 row in set (0.00 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.threads
SET instrumented = 'YES'
WHERE type = 'BACKGROUND';
SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
06
ps_setup_enable_consumer()
启用指定的consumers，通过修改performance_schema.setup_consumers表实现，调用时传入值作为存储过程内部语句的name字段值，修改enabled字段为YES，返回一个已启用的consumers数量（已经处于启用状态的consumers不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
传入的参数字符串使用 like ％consumer％;的形式模糊匹配setup_consumers表的name字段
参数：
consumer VARCHAR(128)：匹配consumers名称的值，通过使用like ％consumer％;的形式模糊匹配setup_consumers表的name字段执行UPDATE操作，注意，如果传入值为''空值，则会匹配到所有的consumers
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_enable_consumer;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_enable_consumer (
IN consumer VARCHAR(128)
)
COMMENT '
Description
-----------
Enables consumers within Performance Schema
matching the input pattern.
Parameters
-----------
consumer (VARCHAR(128)):
A LIKE pattern match (using "%consumer%") of consumers to enable
Example
-----------
To enable all consumers:
mysql> CALL sys.ps_setup_enable_consumer(\'\');
+-------------------------+
| summary |
+-------------------------+
| Enabled 10 consumers |
+-------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
To enable just "waits" consumers:
mysql> CALL sys.ps_setup_enable_consumer(\'waits\');
+-----------------------+
| summary |
+-----------------------+
| Enabled 3 consumers |
+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.setup_consumers
SET enabled = 'YES'
WHERE name LIKE CONCAT('%', consumer, '%');
SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' consumer', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
07
ps_setup_enable_instrument()
启用指定的instruments，通过修改performance_schema.setup_instruments表实现，调用时传入值作为存储过程内部语句的name字段值，修改enabled和timed字段为YES，返回一个已启用的instruments数量（已经处于启用状态的instruments不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
传入的参数字符串使用 like ％in_pattern％;的形式模糊匹配setup_instruments表的name字段
参数：
in_pattern VARCHAR(128)：匹配instruments名称的值，通过使用like ％in_pattern％;的形式模糊匹配setup_instrumentss表的name字段执行UPDATE操作，注意，如果传入值为''空值，则会匹配到所有的instruments
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_enable_instrument;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_enable_instrument (
IN in_pattern VARCHAR(128)
)
COMMENT '
Description
-----------
Enables instruments within Performance Schema
matching the input pattern.
Parameters
-----------
in_pattern (VARCHAR(128)):
A LIKE pattern match (using "%in_pattern%") of events to enable
Example
-----------
To enable all mutex instruments:
mysql> CALL sys.ps_setup_enable_instrument(\'wait/synch/mutex\');
+-------------------------+
| summary |
+-------------------------+
| Enabled 155 instruments |
+-------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
To enable just a specific TCP/IP based network IO instrument:
mysql> CALL sys.ps_setup_enable_instrument(\'wait/io/socket/sql/server_tcpip_socket\');
+-----------------------+
| summary |
+-----------------------+
| Enabled 1 instruments |
+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
To enable all instruments:
mysql> CALL sys.ps_setup_enable_instrument(\'\');
+-------------------------+
| summary |
+-------------------------+
| Enabled 547 instruments |
+-------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.setup_instruments
SET enabled = 'YES', timed = 'YES'
WHERE name LIKE CONCAT('%', in_pattern, '%');
SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' instrument', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
08
ps_setup_enable_thread()
启用指定的线程性能事件采集功能，通过修改performance_schema.threads表实现，调用时传入值作为存储过程内部语句的processlist_id字段值，修改instrumented字段为YES，返回一个已启用性能事件采集功能的线程数量（已经处于启用状态的线程不会计数，因为是使用ROW_COUNT()函数作为返回值，该函数只记录实际发生变更的行）
参数：
in_connection_id BIGINT：连接ID(进程ID)，为performance_schema.theads表的PROCESSLIST_ID列或SHOW PROCESSLIST输出的id列值
存储过程定义语句文本
DROP PROCEDURE IF EXISTS ps_setup_enable_thread;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_enable_thread (
IN in_connection_id BIGINT
)
COMMENT '
Description
-----------
Enable the given connection/thread in Performance Schema.
Parameters
-----------
in_connection_id (BIGINT):
The connection ID (PROCESSLIST_ID from performance_schema.threads
or the ID shown within SHOW PROCESSLIST)
Example
-----------
mysql> CALL sys.ps_setup_enable_thread(3);
+------------------+
| summary |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.01 sec)
To enable the current connection:
mysql> CALL sys.ps_setup_enable_thread(CONNECTION_ID());
+------------------+
| summary |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.00 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE performance_schema.threads
SET instrumented = 'YES'
WHERE processlist_id = in_connection_id;
SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' thread', IF(@rows != 1, 's', '')) AS summary;
END$$
DELIMITER ;
09
ps_setup_reload_saved()
调用该存储过程时，会重新加载之前调用ps_setup_save()存储过程时保存在临时表中的performance_schema.threads、performance_schema.setup_consumers、performance_schema.setup_instruments、performance_schema.setup_actors配置信息，调用该存储过程依赖于在相同会话中之前调用ps_setup_save()存储过程创建的配置备份临时表，如果之前没有调用过ps_setup_save()存储过程，该存储过程无法执行
该存储过程执行需要有SUPER权限，因为执行期间会修改sql_log_bin系统变量禁用二进制日志记录功能
示例
mysql> CALL sys.ps_setup_save();
Query OK, 0 rows affected (0.08 sec)
mysql> UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
Query OK, 547 rows affected (0.40 sec)
Rows matched: 784 Changed: 547 Warnings: 0
/* Run some tests that need more detailed instrumentation here */
mysql> CALL sys.ps_setup_reload_saved();
Query OK, 0 rows affected (0.32 sec)
10
ps_setup_reset_to_default()
重置performance_schema配置为默认值，对于performance_schema.setup_actors表直接清空重新插入(使用delete，INSERT IGNORE INTO语句)，对于performance_schema.setup_instruments表，通过查询sys.ps_is_instrument_default_enabled(NAME)和sys.ps_is_instrument_default_timed(NAME)函数返回event_name的默认值来执行更新配置表(使用update语句)，对于performance_schema.setup_consumers表直接使用IF(NAME IN (''xxx'',...), ''YES'', ''NO'')函数返回值更新配置表(使用update语句)，对于setup_objects表delete ... where not in ('xxx'...)来删除除了默认配置行之外的配置行，然后按照默认的配置行字段值重新更新这些默认配置行(使用delete，INSERT IGNORE INTO语句)，对于threads表，更新所有线程的INSTRUMENTED字段为YES(使用update语句)
参数：
in_verbose BOOLEAN：是否在该存储过程执行期间显示每个配置表还原的阶段信息，其中包括执行更新配置表的SQL语句
示例
mysql> CALL sys.ps_setup_reset_to_default(true)\G
*************************** 1. row ***************************
status: Resetting: setup_actors
DELETE
FROM performance_schema.setup_actors
WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%')
1 row in set (0.00 sec)
*************************** 1. row ***************************
status: Resetting: setup_actors
INSERT IGNORE INTO performance_schema.setup_actors
...
mysql> CALL sys.ps_setup_reset_to_default(false)\G
Query OK, 0 rows affected (0.00 sec)
11
ps_setup_save()
保存performance_schema当前的配置表，通过对performance_schema下的threads、setup_actors、setup_consumers、setup_instruments表都创建一张相同结构的临时表，通过insert ... select...语句拷贝当前配置数据到临时表实现，期间关闭了sql_log_bin参数防止该操作写入binlog中(需要SUPER权限)，操作完之后再改回默认值，还原配置通过调用sys.ps_setup_reload_saved()存储过程实现，sys.ps_setup_reload_saved()存储过程详见sys.ps_setup_reload_saved() 解释部分
为了防止其他会话同时调用该存储过程执行保存配置操作，sys.ps_setup_save()存储过程内部通过调用GET_LOCK()函数来获取一个名为"sys.ps_setup_save"的咨询锁来阻止其他进程执行sys.ps_setup_save()存储过程。 sys.ps_setup_save()存储过程接受一个timeout参数，用于GET_LOCK()函数来获取名为"sys.ps_setup_save"的咨询锁的超时时间(如果名为"sys.ps_setup_save"的咨询锁已存在，则会等待timeout参数指定的秒数），如果超过timeout参数指定的秒数之后其他会话还没有释放这把咨询锁，则GET_LOCK()函数返回0，否则返回1（在ps_setup_save()存储过程内部捕获该函数返回值做if判断，1返回值才会继续执行保存配置的操作，0返回值则报错： 'Could not lock the sys.ps_setup_save user lock, another thread has a saved configuration'）
sys.ps_setup_reload_saved()存储过程和sys.ps_setup_save()存储过程需要在同一个会话中执行，因为配置是保存在TEMPORARY表中，另外，如果在执行了sys.ps_setup_save()存储过程之后没有调用sys.ps_setup_reload_saved()存储过程还原配置而直接断开会话连接，sys.ps_setup_save()存储过程创建的临时表和获取的咨询锁会自动删除
参数：
in_timeout INT：等待获取sys.ps_setup_save锁的超时秒数，注意：如果该值为负数则会导致无限等待
示例
mysql> CALL sys.ps_setup_save(1);
Query OK, 0 rows affected (0.08 sec)
mysql> UPDATE performance_schema.setup_instruments
-> SET enabled = 'YES', timed = 'YES';
Query OK, 547 rows affected (0.40 sec)
Rows matched: 784 Changed: 547 Warnings: 0
/* Run some tests that need more detailed instrumentation here */
mysql> CALL sys.ps_setup_reload_saved();
Query OK, 0 rows affected (0.32 sec)
本期内容就介绍到这里，本期内容参考链接如下：
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-save.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-consumer.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-instrument.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-thread.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-background-threads.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-consumer.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-instrument.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-thread.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reload-saved.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reset-to-default.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-background-threads.html
| 作者简介
罗小波·沃趣科技高级数据库技术专家
IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。
相关链接
更多干货，欢迎来撩~