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 ;

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

---

上一篇： ps_setup_enable_consumer()存储过程 | 下一篇： ps_setup_enable_thread()存储过程