1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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 ;
|
