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
|
##
## WL#4818, 4.1.3 MUTEXES, RW-LOCKS, ...
##
## Functional testing of mutexes and RW-locks
##
--source include/not_embedded.inc
--source include/have_perfschema.inc
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
UPDATE performance_schema.setup_instruments SET enabled = 'YES'
WHERE name LIKE 'wait/synch/mutex/%'
OR name LIKE 'wait/synch/rwlock/%';
# reset lost counters
truncate table performance_schema.events_statements_summary_by_digest;
flush status;
# Make sure objects are instrumented
select NAME from performance_schema.mutex_instances
where NAME = 'wait/synch/mutex/mysys/THR_LOCK::mutex' GROUP BY NAME;
select NAME from performance_schema.rwlock_instances
where NAME = 'wait/synch/rwlock/sql/LOCK_grant' limit 1;
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
#
# TODO: Change to InnoDB when it gets instrumentation
#
CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
ENGINE=MyISAM;
INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
#
# FM1: Count for mutex should increase with instrumentation enabled and
# FM2: Count for mutex should not increase with instrumentation disabled
#
TRUNCATE TABLE performance_schema.events_waits_history_long;
TRUNCATE TABLE performance_schema.events_waits_history;
TRUNCATE TABLE performance_schema.events_waits_current;
SELECT * FROM t1 WHERE id = 1;
SET @before_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/mutex/mysys/THR_LOCK::mutex'));
SELECT * FROM t1;
SET @after_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/mutex/mysys/THR_LOCK::mutex'));
SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_fm1_timed;
UPDATE performance_schema.setup_instruments SET enabled = 'NO'
WHERE NAME = 'wait/synch/mutex/mysys/THR_LOCK::mutex';
TRUNCATE TABLE performance_schema.events_waits_history_long;
TRUNCATE TABLE performance_schema.events_waits_history;
TRUNCATE TABLE performance_schema.events_waits_current;
SELECT * FROM t1 WHERE id = 1;
SET @before_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/mutex/mysys/THR_LOCK::mutex'));
SELECT * FROM t1;
SET @after_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/mutex/mysys/THR_LOCK::mutex'));
SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_fm2_timed;
#
# Repeat for RW-lock
#
TRUNCATE TABLE performance_schema.events_waits_history_long;
TRUNCATE TABLE performance_schema.events_waits_history;
TRUNCATE TABLE performance_schema.events_waits_current;
show variables like "%not_found%";
SET @before_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_system_variables_hash'));
show variables like "%not_found%";
SET @after_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_system_variables_hash'));
SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_fm1_rw_timed;
UPDATE performance_schema.setup_instruments SET enabled = 'NO'
WHERE NAME = 'wait/synch/rwlock/sql/LOCK_system_variables_hash';
TRUNCATE TABLE performance_schema.events_waits_history_long;
TRUNCATE TABLE performance_schema.events_waits_history;
TRUNCATE TABLE performance_schema.events_waits_current;
show variables like "%not_found%";
SET @before_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_system_variables_hash'));
show variables like "%not_found%";
SET @after_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'LOCK_system_variables_hash'));
SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_fm2_rw_timed;
# Clean-up.
UPDATE performance_schema.setup_instruments SET enabled = 'YES';
DROP TABLE t1;
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
# In case of failure, will indicate the root cause
show global status like "performance_schema%";
|
