diff options
Diffstat (limited to 'scripts/sys_schema/procedures/ps_truncate_all_tables.sql')
-rw-r--r-- | scripts/sys_schema/procedures/ps_truncate_all_tables.sql | 90 |
1 files changed, 90 insertions, 0 deletions
diff --git a/scripts/sys_schema/procedures/ps_truncate_all_tables.sql b/scripts/sys_schema/procedures/ps_truncate_all_tables.sql new file mode 100644 index 00000000..42d904f5 --- /dev/null +++ b/scripts/sys_schema/procedures/ps_truncate_all_tables.sql @@ -0,0 +1,90 @@ +-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved. +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; version 2 of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + +DROP PROCEDURE IF EXISTS ps_truncate_all_tables; + +DELIMITER $$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE ps_truncate_all_tables ( + IN in_verbose BOOLEAN + ) + COMMENT ' + Description + ----------- + + Truncates all summary tables within Performance Schema, + resetting all aggregated instrumentation as a snapshot. + + Parameters + ----------- + + in_verbose (BOOLEAN): + Whether to print each TRUNCATE statement before running + + Example + ----------- + + mysql> CALL sys.ps_truncate_all_tables(false); + +---------------------+ + | summary | + +---------------------+ + | Truncated 44 tables | + +---------------------+ + 1 row in set (0.10 sec) + + Query OK, 0 rows affected (0.10 sec) + ' + SQL SECURITY INVOKER + DETERMINISTIC + MODIFIES SQL DATA +BEGIN + DECLARE v_done INT DEFAULT FALSE; + DECLARE v_total_tables INT DEFAULT 0; + DECLARE v_ps_table VARCHAR(64); + DECLARE ps_tables CURSOR FOR + SELECT table_name + FROM INFORMATION_SCHEMA.TABLES + WHERE table_schema = 'performance_schema' + AND (table_name LIKE '%summary%' + OR table_name LIKE '%history%'); + DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; + + OPEN ps_tables; + + ps_tables_loop: LOOP + FETCH ps_tables INTO v_ps_table; + IF v_done THEN + LEAVE ps_tables_loop; + END IF; + + SET @truncate_stmt := CONCAT('TRUNCATE TABLE performance_schema.', v_ps_table); + IF in_verbose THEN + SELECT CONCAT('Running: ', @truncate_stmt) AS status; + END IF; + + PREPARE truncate_stmt FROM @truncate_stmt; + EXECUTE truncate_stmt; + DEALLOCATE PREPARE truncate_stmt; + + SET v_total_tables = v_total_tables + 1; + END LOOP; + + CLOSE ps_tables; + + SELECT CONCAT('Truncated ', v_total_tables, ' tables') AS summary; + +END$$ + +DELIMITER ; |