forked from meob/my2Collector
-
Notifications
You must be signed in to change notification settings - Fork 0
/
my2_80.sql
162 lines (151 loc) · 7.4 KB
/
my2_80.sql
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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
-- by [email protected]
-- My2_80 Collector
-- 0.0.1 2013-02-14 First version for MySQL 5.6
-- 0.0.6 2017-04-01 DBCPU as SUM_TIMER_WAIT from events_statements_summary_global_by_event_name
-- 0.0.7 2017-11-01 bug fixed (0 as first value for delta), MariaDB 10.2 support, new custom statistics
-- 0.0.7a 2018-02-18 substr(EVENT_NAME,15) --> substr(EVENT_NAME,15,60)
-- 0.0.8 2018-04-01 MySQL v.8.0 support
-- 0.0.9a 2018-08-15 Delta statistics (useful for Grafana and others), (a) got some useful global_variable
-- 0.0.10 2018-10-31 Replication Lag (also with multi-threaded slaves)
-- 0.0.11 2019-05-05 MySQL v.8 only
-- 0.0.12 2021-06-22 grants on performance_schema (needed for 8.x)
-- Create Database, Tables, Stored Routines and Jobs for My2 dashboard
create database IF NOT EXISTS my2;
use my2;
CREATE TABLE IF NOT EXISTS status (
VARIABLE_NAME varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
VARIABLE_VALUE varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
TIMEST timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS current (
VARIABLE_NAME varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
VARIABLE_VALUE varchar(1024) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB;
ALTER TABLE status
ADD unique KEY idx01 (VARIABLE_NAME,timest);
-- delete from my2.status where VARIABLE_NAME like 'PROCESSES_HOSTS.%';
-- update my2.status set variable_value=0, timest=timest where VARIABLE_NAME like '%-d' and variable_value<0;
ALTER TABLE current
ADD unique KEY idx02 (VARIABLE_NAME);
DROP PROCEDURE IF EXISTS collect_stats;
DELIMITER // ;
CREATE PROCEDURE collect_stats()
BEGIN
DECLARE a datetime;
DECLARE v varchar(10);
set sql_log_bin = 0;
set a=now();
select substr(version(),1,3) into v;
insert into my2.status(variable_name,variable_value,timest)
select upper(variable_name),variable_value, a
from performance_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.status(variable_name,variable_value,timest)
SELECT 'REPLICATION_MAX_WORKER_TIME', coalesce(max(PROCESSLIST_TIME), 0.1), a
FROM performance_schema.threads
WHERE (NAME = 'thread/sql/slave_worker'
AND (PROCESSLIST_STATE IS NULL
OR PROCESSLIST_STATE != 'Waiting for an event from Coordinator'))
OR NAME = 'thread/sql/slave_sql';
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES.',user),count(*),a
from information_schema.processlist
group by user;
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES_HOSTS.',SUBSTRING_INDEX(host,':',1)),count(*),a
from information_schema.processlist
group by concat('PROCESSES_HOSTS.',SUBSTRING_INDEX(host,':',1));
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES_COMMAND.',command),count(*),a
from information_schema.processlist
group by concat('PROCESSES_COMMAND.',command);
insert into my2.status(variable_name,variable_value,timest)
select substr(concat('PROCESSES_STATE.',state),1,64),count(*),a
from information_schema.processlist
group by substr(concat('PROCESSES_STATE.',state),1,64);
insert into my2.status(variable_name,variable_value,timest)
SELECT 'SUM_TIMER_WAIT', sum(sum_timer_wait*1.0), a
FROM performance_schema.events_statements_summary_global_by_event_name;
-- Delta values
insert into my2.status(variable_name,variable_value,timest)
select concat(upper(s.variable_name),'-d'), greatest(s.variable_value-c.variable_value,0), a
from performance_schema.global_status s, my2.current c
where s.variable_name=c.variable_name;
insert into my2.status(variable_name,variable_value,timest)
SELECT concat('COM_',upper(substr(s.EVENT_NAME,15,58)), '-d'), greatest(s.COUNT_STAR-c.variable_value,0), a
FROM performance_schema.events_statements_summary_global_by_event_name s, my2.current c
WHERE s.EVENT_NAME LIKE 'statement/sql/%'
AND s.EVENT_NAME = c.variable_name;
insert into my2.status(variable_name,variable_value,timest)
SELECT 'SUM_TIMER_WAIT-d', sum(sum_timer_wait*1.0)-c.variable_value, a
FROM performance_schema.events_statements_summary_global_by_event_name, my2.current c
WHERE c.variable_name='SUM_TIMER_WAIT';
insert into my2.status(variable_name, variable_value, timest)
select 'REPLICATION_CONNECTION_STATUS',if(SERVICE_STATE='ON', 1, 0),a
from performance_schema.replication_connection_status;
insert into my2.status(variable_name, variable_value, timest)
select 'REPLICATION_APPLIER_STATUS',if(SERVICE_STATE='ON', 1, 0),a
from performance_schema.replication_applier_status;
delete from my2.current;
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value+0
from performance_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.current(variable_name,variable_value)
SELECT substr(EVENT_NAME,1,40), COUNT_STAR
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%';
insert into my2.current(variable_name,variable_value)
SELECT 'SUM_TIMER_WAIT', sum(sum_timer_wait*1.0)
FROM performance_schema.events_statements_summary_global_by_event_name;
insert into my2.current(variable_name,variable_value)
select concat('PROCESSES_COMMAND.',command),count(*)
from information_schema.processlist
group by concat('PROCESSES_COMMAND.',command);
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value
from performance_schema.global_variables
where variable_name in ('max_connections', 'innodb_buffer_pool_size', 'query_cache_size',
'innodb_log_buffer_size', 'key_buffer_size', 'table_open_cache');
set sql_log_bin = 1;
END //
DELIMITER ; //
-- Collect daily statistics on space usage and delete old statistics (older than 62 days, 1 year for DB size)
DROP PROCEDURE IF EXISTS collect_daily_stats;
DELIMITER // ;
CREATE PROCEDURE collect_daily_stats()
BEGIN
DECLARE a datetime;
set sql_log_bin = 0;
set a=now();
insert into my2.status(variable_name,variable_value,timest)
select concat('SIZEDB.',table_schema), sum(data_length+index_length), a
from information_schema.tables group by table_schema;
insert into my2.status(variable_name,variable_value,timest)
select 'SIZEDB.TOTAL', sum(data_length+index_length), a
from information_schema.tables;
delete from my2.status where timest < date_sub(now(), INTERVAL 62 DAY) and variable_name <>'SIZEDB.TOTAL';
delete from my2.status where timest < date_sub(now(), INTERVAL 365 DAY);
set sql_log_bin = 1;
END //
DELIMITER ; //
-- The event scheduler must also be activated in the my.cnf (event_scheduler=1)
set global event_scheduler=1;
set sql_log_bin = 0;
DROP EVENT IF EXISTS collect_stats;
CREATE EVENT collect_stats
ON SCHEDULE EVERY 10 Minute
DO call collect_stats();
DROP EVENT IF EXISTS collect_daily_stats;
CREATE EVENT collect_daily_stats
ON SCHEDULE EVERY 1 DAY
DO call collect_daily_stats();
set sql_log_bin = 1;
-- Use a specific user (suggested)
-- create user my2@'%' identified by 'P1e@seCh@ngeMe';
-- grant all on my2.* to my2@'%';
-- grant select on performance_schema.* to my2@'%';