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
|
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
--source include/have_perfschema.inc
--source include/have_query_cache_disabled.inc
CREATE USER user1@localhost;
CREATE USER user2@localhost;
CREATE USER user3@localhost;
grant ALL on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
grant ALL on *.* to user3@localhost;
# To aggregate to accounts
#SET GLOBAL show_compatibility_56=0;
TRUNCATE TABLE performance_schema.accounts;
FLUSH PRIVILEGES;
CREATE TABLE test.t_range(a int, b int, PRIMARY KEY(a));
INSERT INTO test.t_range values (1, 1), (2,2), (3, 3), (4, 4), (5, 5);
INSERT INTO test.t_range values (6, 6), (7,7), (8, 8), (9, 9), (10, 10);
FLUSH STATUS;
let $initial= `SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'`;
# Causes Select_range to increment (+1)
--disable_ps2_protocol
SELECT * from test.t_range where (a >= 3) AND (a <= 5);
--enable_ps2_protocol
SELECT * from performance_schema.session_status
WHERE VARIABLE_NAME = 'Select_range';
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
connect(con1, localhost, user1,,);
# Causes Select_range to increment (+1)
--disable_ps2_protocol
SELECT * from test.t_range where (a >= 3) AND (a <= 5);
--enable_ps2_protocol
SELECT * from performance_schema.session_status
WHERE VARIABLE_NAME = 'Select_range';
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
connect(con2, localhost, user2,,);
# Causes Select_range to increment (+2)
--disable_ps2_protocol
SELECT * from test.t_range where (a >= 3) AND (a <= 5);
SELECT * from test.t_range where (a >= 4) AND (a <= 6);
--enable_ps2_protocol
SELECT * from performance_schema.session_status
WHERE VARIABLE_NAME = 'Select_range';
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
connect(con3, localhost, user3,,);
# Causes Select_range to increment (+3)
--disable_ps2_protocol
SELECT * from test.t_range where (a >= 3) AND (a <= 5);
SELECT * from test.t_range where (a >= 4) AND (a <= 6);
SELECT * from test.t_range where (a >= 5) AND (a <= 7);
--enable_ps2_protocol
SELECT * from performance_schema.session_status
WHERE VARIABLE_NAME = 'Select_range';
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
--connection default
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
SELECT `USER`, `HOST`, VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.status_by_account WHERE VARIABLE_NAME = 'Select_range'
AND `USER` LIKE 'user%'
ORDER BY `USER`;
--disconnect con1
--disconnect con2
# Wait till all disconnects are completed
let $count_sessions= 2;
--source include/wait_until_count_sessions.inc
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
TRUNCATE TABLE performance_schema.accounts;
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
--disconnect con3
# Wait till all disconnects are completed
let $count_sessions= 1;
--source include/wait_until_count_sessions.inc
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
# Make sure TRUNCATE on accounts does not add to global_status
TRUNCATE TABLE performance_schema.accounts;
--disable_query_log
eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
--enable_query_log
DROP TABLE test.t_range;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
DROP USER user1@localhost;
DROP USER user2@localhost;
DROP USER user3@localhost;
#SET GLOBAL show_compatibility_56=1;
FLUSH PRIVILEGES;
|
