在本系列中前面用了大量篇幅介绍完了 sys 系统库的视图,利用这些视图我们可以方便快捷地查询到performance_schema、information_schema下的内容,但对于performance_schema下的instrument和consumer配置信息属于需要修改的内容,除了直接使用update语句修改配置表之外,是不是也有类似查询视图一样的快捷方式呢?有的,本期的内容开始给大家介绍一些修改、确认配置相关的存储过程。
PS:下文中如果存储过程定义文本较短的会列出部分存储过程的定义文本,以便大家更直观地学习它们。过长的存储过程定义文本请自行按照《初相识|全方位认识 sys 系统库》一文中介绍的下载路径下载查看。
禁用所有后台线程的性能事件采集功能,该存储过程通过修改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)
禁用指定的consumers,该存储过程通过修改performance_schema.setup_consumers表实现,调用时需要传入一个consumer name字符串作为参数值,修改performance_schema.setup_consumers表的enabled字段为NO,返回一个被关闭的consumers数量(已经处于关闭状态的consumers不会计数,因为是使用ROW_COUNT()函数作为返回值,该函数只记录实际发生变更的行)
参数:
存储过程定义语句文本
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部分,不再熬述)
禁用指定的instruments,通过修改performance_schema.setup_instruments表实现,调用时传入值作为存储过程内部更新语句的name字段值,修改enabled和timed字段为NO,返回一个被关闭的instruments数量(已经处于关闭状态的instruments不会计数,因为是使用ROW_COUNT()函数作为返回值,该函数只记录实际发生变更的行)
参数:
存储过程定义语句文本
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 ;
禁用指定线程的性能事件采集功能,通过修改performance_schema.threads表实现,调用时传入值作为存储过程内部语句的processlist_id字段值(或者是show processlist;输出的id字段值),修改instrumented字段为NO,返回一个被关闭的线程数量(已经处于关闭状态的线程不会计数,因为是使用ROW_COUNT()函数作为返回值,该函数只记录实际发生变更的行)
参数:
存储过程定义语句文本
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 ;
启用所有后台线程的性能事件采集功能,通过修改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 ;
启用指定的consumers,通过修改performance_schema.setup_consumers表实现,调用时传入值作为存储过程内部语句的name字段值,修改enabled字段为YES,返回一个已启用的consumers数量(已经处于启用状态的consumers不会计数,因为是使用ROW_COUNT()函数作为返回值,该函数只记录实际发生变更的行)
参数:
存储过程定义语句文本
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 ;
启用指定的instruments,通过修改performance_schema.setup_instruments表实现,调用时传入值作为存储过程内部语句的name字段值,修改enabled和timed字段为YES,返回一个已启用的instruments数量(已经处于启用状态的instruments不会计数,因为是使用ROW_COUNT()函数作为返回值,该函数只记录实际发生变更的行)
参数:
存储过程定义语句文本
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 ;
启用指定的线程性能事件采集功能,通过修改performance_schema.threads表实现,调用时传入值作为存储过程内部语句的processlist_id字段值,修改instrumented字段为YES,返回一个已启用性能事件采集功能的线程数量(已经处于启用状态的线程不会计数,因为是使用ROW_COUNT()函数作为返回值,该函数只记录实际发生变更的行)
参数:
存储过程定义语句文本
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 ;
调用该存储过程时,会重新加载之前调用ps_setup_save()存储过程时保存在临时表中的performance_schema.threads、performance_schema.setup_consumers、performance_schema.setup_instruments、performance_schema.setup_actors配置信息,调用该存储过程依赖于在相同会话中之前调用ps_setup_save()存储过程创建的配置备份临时表,如果之前没有调用过ps_setup_save()存储过程,该存储过程无法执行
示例
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)
重置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语句)
参数:
示例
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)
保存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() 解释部分
参数:
示例
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)
本期内容就介绍到这里,本期内容参考链接如下:
罗小波·沃趣科技高级数据库技术专家
IT从业多年,历任运维工程师,高级运维工程师,运维经理,数据库工程师,曾参与版本发布系统,轻量级监控系统,运维管理平台,数据库管理平台的设计与编写,熟悉MySQL的体系结构时,InnoDB存储引擎,喜好专研开源技术,追求完美。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。