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 ;

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

---

上一篇： ps_setup_enable_background_threads()存储过程 | 下一篇： ps_setup_enable_instrument()存储过程