ps_setup_show_enabled() - xiaoboluo768/mysql-system-schema GitHub Wiki

• 显示当前启用的performance配置（所有配置表），通过查询setup_actors表enabled为YES、setup_objects表enabled为YES、setup_consumers表enabled为YES、threads表INSTRUMENTED为YES、setup_instruments表enabled为YES实现，其中，调用时传递的两个布尔型值，第一个为控制是否打印setup_instruments表中启用的配置，第二个控制是否打印threads表启用的配置，因为这俩表打印的信息最多，所以需要单独传值控制

• 参数：

  • in_show_instruments BOOLEAN：是否显示启用的instruments，返回的instruments启用状态列表可能比较长
  • in_show_threads BOOLEAN：是否显示启用监控功能的线程

• 定义语句

DROP PROCEDURE IF EXISTS ps_setup_show_enabled;

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_show_enabled (
        IN in_show_instruments BOOLEAN,
        IN in_show_threads BOOLEAN
    )
    COMMENT '
            Description
            -----------

            Shows all currently enabled Performance Schema configuration.

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

            in_show_instruments (BOOLEAN):
              Whether to print enabled instruments (can print many items)

            in_show_threads (BOOLEAN):
              Whether to print enabled threads

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

            mysql> CALL sys.ps_setup_show_enabled(TRUE, TRUE);
            +----------------------------+
            | performance_schema_enabled |
            +----------------------------+
            |                          1 |
            +----------------------------+
            1 row in set (0.00 sec)

      ......
            +-------------------------------------+-------+
            | enabled_instruments                | timed |
            +-------------------------------------+-------+
            | wait/io/file/sql/map                | YES  |
            | wait/io/file/sql/binlog            | YES  |
            ...
            | statement/com/Error                | YES  |
            | statement/com/                      | YES  |
            | idle                                | YES  |
            +-------------------------------------+-------+
            210 rows in set (0.08 sec)

            Query OK, 0 rows affected (0.89 sec)
            '
    SQL SECURITY INVOKER
    DETERMINISTIC
    READS SQL DATA
BEGIN
    SELECT @@performance_schema AS performance_schema_enabled;

    -- In 5.7.6 and later the setup_actors table has an ENABLED column to
    -- specify whether the actor is enabled. Before that all actors matched
    -- in the setup_actors table were enabled.
    SELECT CONCAT('\'', user, '\'@\'', host, '\'') AS enabled_users
      FROM performance_schema.setup_actors
    /*!50706 WHERE enabled = 'YES' */
    ORDER BY enabled_users;

    SELECT object_type,
          CONCAT(object_schema, '.', object_name) AS objects,
          enabled,
          timed
      FROM performance_schema.setup_objects
    WHERE enabled = 'YES'
    ORDER BY object_type, objects;

    SELECT name AS enabled_consumers
      FROM performance_schema.setup_consumers
    WHERE enabled = 'YES'
    ORDER BY enabled_consumers;

    IF (in_show_threads) THEN
        SELECT IF(name = 'thread/sql/one_connection',
                  CONCAT(processlist_user, '@', processlist_host),
                  REPLACE(name, 'thread/', '')) AS enabled_threads,
        TYPE AS thread_type
          FROM performance_schema.threads
        WHERE INSTRUMENTED = 'YES'
        ORDER BY enabled_threads;
    END IF;

    IF (in_show_instruments) THEN
        SELECT name AS enabled_instruments,
              timed
          FROM performance_schema.setup_instruments
        WHERE enabled = 'YES'
        ORDER BY enabled_instruments;
    END IF;
END$$

DELIMITER ;

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

---

上一篇： ps_setup_show_disabled_instruments()存储过程 | 下一篇： ps_setup_show_enabled_consumers()存储过程