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 ;

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

---

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