ps_setup_save()

• 保存performance_schema当前的配置表，通过对threads、setup_actors、setup_consumers、setup_instruments表都创建一张相同结构的临时表，通过insert ... select...语句拷贝当前配置数据到临时表实现，期间关闭了sql_log_bin参数防止该操作写入binlog中(需要SUPER权限)，操作完之后再改回默认值，还原配置通过调用ps_setup_reload_saved()存储过程实现，ps_setup_reload_saved()存储过程解释 详见2.4.12. ps_setup_reload_saved() 小节

  • 为了防止其他会话同时调用该存储过程执行保存配置操作，ps_setup_save()存储过程内部通过调用GET_LOCK()函数来获取一个名为"sys.ps_setup_save"的咨询锁来阻止其他进程执行ps_setup_save()存储过程。 ps_setup_save()存储过程接受一个timeout参数，用于GET_LOCK()函数来获取名为"sys.ps_setup_save"的咨询锁的超时时间(如果名为"sys.ps_setup_save"的咨询锁已存在，则会等待timeout参数指定的秒数），如果超过timeout参数指定的秒数之后其他会话还没有释放这把咨询锁，则GET_LOCK()函数返回0，否则返回1（在ps_setup_save()存储过程内部捕获该函数返回值做if判断，1返回值才会继续执行保存配置的操作，0返回值则报错： 'Could not lock the sys.ps_setup_save user lock, another thread has a saved configuration'）
  • ps_setup_reload_saved()存储过程和ps_setup_save()存储过程需要在同一个会话中执行，因为配置是保存在TEMPORARY表中，另外，如果在执行了ps_setup_save()存储过程之后没有调用ps_setup_reload_saved()存储过程还原配置而直接断开会话连接，ps_setup_save()存储过程创建的临时表和获取的咨询锁会自动删除

• 参数：

  • in_timeout INT：等待获取sys.ps_setup_save锁的超时秒数，注意：如果该值为负数则会导致无限等待

• 定义语句

DROP PROCEDURE IF EXISTS ps_setup_save;

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_save (
        IN in_timeout INT
    )
    COMMENT '
            Description
            -----------

            Saves the current configuration of Performance Schema, 
            so that you can alter the setup for debugging purposes, 
            but restore it to a previous state.

            Use the companion procedure - ps_setup_reload_saved(), to 
            restore the saved config.

            The named lock "sys.ps_setup_save" is taken before the
            current configuration is saved. If the attempt to get the named
            lock times out, an error occurs.

            The lock is released after the settings have been restored by
            calling ps_setup_reload_saved().

            Requires the SUPER privilege for "SET sql_log_bin = 0;".

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

            in_timeout INT
              The timeout in seconds used when trying to obtain the lock.
              A negative timeout means infinite timeout.

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

            mysql> CALL sys.ps_setup_save(-1);
            Query OK, 0 rows affected (0.08 sec)

            mysql> UPDATE performance_schema.setup_instruments 
                ->    SET enabled = \'YES\', timed = \'YES\';
            Query OK, 547 rows affected (0.40 sec)
            Rows matched: 784  Changed: 547  Warnings: 0

            /* Run some tests that need more detailed instrumentation here */

            mysql> CALL sys.ps_setup_reload_saved();
            Query OK, 0 rows affected (0.32 sec)
            '
    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN
    DECLARE v_lock_result INT;

    SET @log_bin := @@sql_log_bin;
    SET sql_log_bin = 0;

    SELECT GET_LOCK('sys.ps_setup_save', in_timeout) INTO v_lock_result;

    IF v_lock_result THEN
        DROP TEMPORARY TABLE IF EXISTS tmp_setup_actors;
        DROP TEMPORARY TABLE IF EXISTS tmp_setup_consumers;
        DROP TEMPORARY TABLE IF EXISTS tmp_setup_instruments;
        DROP TEMPORARY TABLE IF EXISTS tmp_threads;

        CREATE TEMPORARY TABLE tmp_setup_actors LIKE performance_schema.setup_actors;
        CREATE TEMPORARY TABLE tmp_setup_consumers LIKE performance_schema.setup_consumers;
        CREATE TEMPORARY TABLE tmp_setup_instruments LIKE performance_schema.setup_instruments;
        CREATE TEMPORARY TABLE tmp_threads (THREAD_ID bigint unsigned NOT NULL PRIMARY KEY, INSTRUMENTED enum('YES','NO') NOT NULL);

        INSERT INTO tmp_setup_actors SELECT * FROM performance_schema.setup_actors;
        INSERT INTO tmp_setup_consumers SELECT * FROM performance_schema.setup_consumers;
        INSERT INTO tmp_setup_instruments SELECT * FROM performance_schema.setup_instruments;
        INSERT INTO tmp_threads SELECT THREAD_ID, INSTRUMENTED FROM performance_schema.threads;
    ELSE
        SIGNAL SQLSTATE VALUE '90000'
          SET MESSAGE_TEXT = 'Could not lock the sys.ps_setup_save user lock, another thread has a saved configuration';
    END IF;

    SET sql_log_bin = @log_bin;
END$$

DELIMITER ;

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

---

上一篇： ps_setup_reset_to_default()存储过程 | 下一篇： ps_setup_show_disabled()存储过程