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

• 查看当前被禁用的performance_schema配置信息，通过查询setup_actors表enabled为NO、setup_objects表enabled为NO、setup_consumers表enabled为NO、threads表INSTRUMENTED为NO、setup_instruments表enabled为NO 实现，其中，调用时传递的两个布尔型值，第一个为控制是否打印setup_instruments表中关闭的配置(instruments关闭选项的返回值可能比较长)，第二个控制是否打印threads表关闭的配置，因为这俩表打印的信息最多，所以需要单独传值控制

• 参数：

  • in_show_instruments BOOLEAN：是否显示关闭的instruments，返回的instruments关闭状态列表可能比较长
  • in_show_threads BOOLEAN：是否显示关闭的线程

• 定义语句

DROP PROCEDURE IF EXISTS ps_setup_show_disabled;

DELIMITER $$

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

            Shows all currently disable Performance Schema configuration.

            Disabled users is only available for MySQL 5.7.6 and later.
            In earlier versions it was only possible to enable users.

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

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

            in_show_threads (BOOLEAN):
              Whether to print disabled threads

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

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

   ......
            
            +---------------------------------------------------------------------------------------+-------+
            | disabled_instruments                                                                  | timed |
            +---------------------------------------------------------------------------------------+-------+
            | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc                                            | NO    |
            | wait/synch/mutex/sql/LOCK_des_key_file                                                | NO    |
            | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit                                      | NO    |
            ...
            | memory/sql/servers_cache                                                              | NO    |
            | memory/sql/udf_mem                                                                    | NO    |
            | wait/lock/metadata/sql/mdl                                                            | NO    |
            +---------------------------------------------------------------------------------------+-------+
            547 rows in set (0.00 sec)

            Query OK, 0 rows affected (0.01 sec)
            '
    SQL SECURITY INVOKER
    NOT 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.
    -- So only execute the query in 5.7.6+
    /*!50706
    SELECT CONCAT('\'', user, '\'@\'', host, '\'') AS disabled_users
      FROM performance_schema.setup_actors
    WHERE enabled = 'NO'
    ORDER BY disabled_users;
    */

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

    SELECT name AS disabled_consumers
      FROM performance_schema.setup_consumers
    WHERE enabled = 'NO'
    ORDER BY disabled_consumers;

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

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

DELIMITER ;

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

---

上一篇： ps_setup_save()存储过程 | 下一篇： ps_setup_show_disabled_consumers()存储过程