← Back to branch summary

~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

« back to all changes in this revision

Viewing changes to mysql-test/suite/perfschema/t/aggregate.test

  • Committer: Package Import Robot
  • Author(s): James Page
  • Date: 2014-02-12 11:54:27 UTC
  • Revision ID: package-import@ubuntu.com-20140212115427-oq6tfsqxl1wuwehi
Tags: upstream-5.6.15
Import upstream version 5.6.15

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/suite/perfschema/t/aggregate.test
 
1
# Tests for PERFORMANCE_SCHEMA
 
2
# Verify that statistics aggregated by different criteria are consistent.
 
3
 
 
4
--source include/not_embedded.inc
 
5
--source include/have_perfschema.inc
 
6
--source include/have_QC_Disabled.inc
 
7
 
 
8
--echo "General cleanup"
 
9
 
 
10
--disable_warnings
 
11
drop table if exists t1;
 
12
--enable_warnings
 
13
 
 
14
update performance_schema.setup_instruments set enabled = 'NO';
 
15
update performance_schema.setup_consumers set enabled = 'NO';
 
16
 
 
17
# Cleanup statistics
 
18
truncate table performance_schema.file_summary_by_event_name;
 
19
truncate table performance_schema.file_summary_by_instance;
 
20
truncate table performance_schema.socket_summary_by_event_name;
 
21
truncate table performance_schema.socket_summary_by_instance;
 
22
truncate table performance_schema.events_waits_summary_global_by_event_name;
 
23
truncate table performance_schema.events_waits_summary_by_instance;
 
24
truncate table performance_schema.events_waits_summary_by_thread_by_event_name;
 
25
 
 
26
# Start recording data
 
27
update performance_schema.setup_consumers set enabled = 'YES';
 
28
update performance_schema.setup_instruments
 
29
  set enabled = 'YES', timed = 'YES';
 
30
 
 
31
 
 
32
create table t1 (
 
33
  id INT PRIMARY KEY,
 
34
  b CHAR(100) DEFAULT 'initial value')
 
35
  ENGINE=MyISAM;
 
36
 
 
37
insert into t1 (id) values (1), (2), (3), (4), (5), (6), (7), (8);
 
38
 
 
39
# Stop recording data, so the select below don't add noise.
 
40
update performance_schema.setup_instruments SET enabled = 'NO';
 
41
# Disable all consumers, for long standing waits
 
42
update performance_schema.setup_consumers set enabled = 'NO';
 
43
 
 
44
# Helper to debug
 
45
set @dump_all=FALSE;
 
46
 
 
47
# Note that in general:
 
48
# - COUNT/SUM/MAX(file_summary_by_event_name) >=
 
49
#   COUNT/SUM/MAX(file_summary_by_instance).
 
50
# - MIN(file_summary_by_event_name) <=
 
51
#   MIN(file_summary_by_instance).
 
52
# There will be equality only when file instances are not removed,
 
53
# aka when a file is not deleted from the file system,
 
54
# because doing so removes a row in file_summary_by_instance.
 
55
 
 
56
# Likewise:
 
57
# - COUNT/SUM/MAX(events_waits_summary_global_by_event_name) >=
 
58
#   COUNT/SUM/MAX(events_waits_summary_by_instance)
 
59
# - MIN(events_waits_summary_global_by_event_name) <=
 
60
#   MIN(events_waits_summary_by_instance)
 
61
# There will be equality only when an instrument instance
 
62
# is not removed, which is next to impossible to predictably guarantee
 
63
# in the server.
 
64
# For example, a MyISAM table removed from the table cache
 
65
# will cause a mysql_mutex_destroy on myisam/MYISAM_SHARE::intern_lock.
 
66
# Another example, a thread terminating will cause a mysql_mutex_destroy
 
67
# on sql/LOCK_delete
 
68
# Both cause a row to be deleted from events_waits_summary_by_instance.
 
69
 
 
70
# Likewise:
 
71
# - COUNT/SUM/MAX(events_waits_summary_global_by_event_name) >=
 
72
#   COUNT/SUM/MAX(events_waits_summary_by_thread_by_event_name)
 
73
# - MIN(events_waits_summary_global_by_event_name) <=
 
74
#   MIN(events_waits_summary_by_thread_by_event_name)
 
75
# There will be equality only when no thread is removed,
 
76
# that is if no thread disconnects, or no sub thread (for example insert
 
77
# delayed) ever completes.
 
78
# A thread completing will cause rows in
 
79
# events_waits_summary_by_thread_by_event_name to be removed.
 
80
 
 
81
--echo "Verifying file aggregate consistency"
 
82
 
 
83
# Since the code generating the load in this test does:
 
84
# - create table
 
85
# - insert
 
86
# - does not cause temporary tables to be used
 
87
# we can test for equality here for file aggregates.
 
88
 
 
89
# If any of these queries returns data, the test failed.
 
90
 
 
91
SELECT EVENT_NAME, e.COUNT_READ, SUM(i.COUNT_READ)
 
92
FROM performance_schema.file_summary_by_event_name AS e
 
93
JOIN performance_schema.file_summary_by_instance AS i USING (EVENT_NAME)
 
94
GROUP BY EVENT_NAME
 
95
HAVING (e.COUNT_READ <> SUM(i.COUNT_READ))
 
96
OR @dump_all;
 
97
 
 
98
SELECT EVENT_NAME, e.COUNT_WRITE, SUM(i.COUNT_WRITE)
 
99
FROM performance_schema.file_summary_by_event_name AS e
 
100
JOIN performance_schema.file_summary_by_instance AS i USING (EVENT_NAME)
 
101
GROUP BY EVENT_NAME
 
102
HAVING (e.COUNT_WRITE <> SUM(i.COUNT_WRITE))
 
103
OR @dump_all;
 
104
 
 
105
SELECT EVENT_NAME, e.COUNT_READ, SUM(i.COUNT_READ)
 
106
FROM performance_schema.socket_summary_by_event_name AS e
 
107
JOIN performance_schema.socket_summary_by_instance AS i USING (EVENT_NAME)
 
108
GROUP BY EVENT_NAME
 
109
HAVING (e.COUNT_READ <> SUM(i.COUNT_READ))
 
110
OR @dump_all;
 
111
 
 
112
SELECT EVENT_NAME, e.COUNT_WRITE, SUM(i.COUNT_WRITE)
 
113
FROM performance_schema.socket_summary_by_event_name AS e
 
114
JOIN performance_schema.socket_summary_by_instance AS i USING (EVENT_NAME)
 
115
GROUP BY EVENT_NAME
 
116
HAVING (e.COUNT_WRITE <> SUM(i.COUNT_WRITE))
 
117
OR @dump_all;
 
118
 
 
119
SELECT EVENT_NAME, e.SUM_NUMBER_OF_BYTES_READ, SUM(i.SUM_NUMBER_OF_BYTES_READ)
 
120
FROM performance_schema.file_summary_by_event_name AS e
 
121
JOIN performance_schema.file_summary_by_instance AS i USING (EVENT_NAME)
 
122
GROUP BY EVENT_NAME
 
123
HAVING (e.SUM_NUMBER_OF_BYTES_READ <> SUM(i.SUM_NUMBER_OF_BYTES_READ))
 
124
OR @dump_all;
 
125
 
 
126
SELECT EVENT_NAME, e.SUM_NUMBER_OF_BYTES_WRITE, SUM(i.SUM_NUMBER_OF_BYTES_WRITE)
 
127
FROM performance_schema.file_summary_by_event_name AS e
 
128
JOIN performance_schema.file_summary_by_instance AS i USING (EVENT_NAME)
 
129
GROUP BY EVENT_NAME
 
130
HAVING (e.SUM_NUMBER_OF_BYTES_WRITE <> SUM(i.SUM_NUMBER_OF_BYTES_WRITE))
 
131
OR @dump_all;
 
132
 
 
133
--echo "Verifying waits aggregate consistency (instance)"
 
134
 
 
135
SELECT EVENT_NAME, e.SUM_TIMER_WAIT, SUM(i.SUM_TIMER_WAIT)
 
136
FROM performance_schema.events_waits_summary_global_by_event_name AS e
 
137
JOIN performance_schema.events_waits_summary_by_instance AS i USING (EVENT_NAME)
 
138
GROUP BY EVENT_NAME
 
139
HAVING (e.SUM_TIMER_WAIT < SUM(i.SUM_TIMER_WAIT))
 
140
OR @dump_all;
 
141
 
 
142
SELECT EVENT_NAME, e.MIN_TIMER_WAIT, MIN(i.MIN_TIMER_WAIT)
 
143
FROM performance_schema.events_waits_summary_global_by_event_name AS e
 
144
JOIN performance_schema.events_waits_summary_by_instance AS i USING (EVENT_NAME)
 
145
GROUP BY EVENT_NAME
 
146
HAVING (e.MIN_TIMER_WAIT > MIN(i.MIN_TIMER_WAIT))
 
147
AND (MIN(i.MIN_TIMER_WAIT) != 0)
 
148
OR @dump_all;
 
149
 
 
150
SELECT EVENT_NAME, e.MAX_TIMER_WAIT, MAX(i.MAX_TIMER_WAIT)
 
151
FROM performance_schema.events_waits_summary_global_by_event_name AS e
 
152
JOIN performance_schema.events_waits_summary_by_instance AS i USING (EVENT_NAME)
 
153
GROUP BY EVENT_NAME
 
154
HAVING (e.MAX_TIMER_WAIT < MAX(i.MAX_TIMER_WAIT))
 
155
OR @dump_all;
 
156
 
 
157
--echo "Verifying waits aggregate consistency (thread)"
 
158
 
 
159
SELECT EVENT_NAME, e.SUM_TIMER_WAIT, SUM(t.SUM_TIMER_WAIT)
 
160
FROM performance_schema.events_waits_summary_global_by_event_name AS e
 
161
JOIN performance_schema.events_waits_summary_by_thread_by_event_name AS t
 
162
USING (EVENT_NAME)
 
163
GROUP BY EVENT_NAME
 
164
HAVING (e.SUM_TIMER_WAIT < SUM(t.SUM_TIMER_WAIT))
 
165
OR @dump_all;
 
166
 
 
167
SELECT EVENT_NAME, e.MIN_TIMER_WAIT, MIN(t.MIN_TIMER_WAIT)
 
168
FROM performance_schema.events_waits_summary_global_by_event_name AS e
 
169
JOIN performance_schema.events_waits_summary_by_thread_by_event_name AS t
 
170
USING (EVENT_NAME)
 
171
GROUP BY EVENT_NAME
 
172
HAVING (e.MIN_TIMER_WAIT > MIN(t.MIN_TIMER_WAIT))
 
173
AND (MIN(t.MIN_TIMER_WAIT) != 0)
 
174
OR @dump_all;
 
175
 
 
176
SELECT EVENT_NAME, e.MAX_TIMER_WAIT, MAX(t.MAX_TIMER_WAIT)
 
177
FROM performance_schema.events_waits_summary_global_by_event_name AS e
 
178
JOIN performance_schema.events_waits_summary_by_thread_by_event_name AS t
 
179
USING (EVENT_NAME)
 
180
GROUP BY EVENT_NAME
 
181
HAVING (e.MAX_TIMER_WAIT < MAX(t.MAX_TIMER_WAIT))
 
182
OR @dump_all;
 
183
 
 
184
 
 
185
# Cleanup
 
186
 
 
187
update performance_schema.setup_consumers set enabled = 'YES';
 
188
update performance_schema.setup_instruments
 
189
  set enabled = 'YES', timed = 'YES';
 
190
 
 
191
drop table test.t1;