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 '
            Description
            -----------

            Disable all background thread instrumentation within Performance Schema.

            Parameters
            -----------

            None.

            Example
            -----------

            mysql> CALL sys.ps_setup_disable_background_threads();
            +--------------------------------+
            | summary                        |
            +--------------------------------+
            | Disabled 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 = '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)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-background-threads.html

---

上一篇： execute_prepared_stmt()存储过程 | 下一篇： ps_setup_disable_consumer()存储过程