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
|
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
UPDATE performance_schema.setup_instruments SET enabled = 'YES'
WHERE name LIKE 'wait/io/file/%';
update performance_schema.threads SET instrumented = 'YES'
WHERE PROCESSLIST_ID=connection_id();
flush status;
DROP TABLE IF EXISTS t1;
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);
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;
id b
1 initial value
SET @before_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
AND (OBJECT_NAME LIKE '%t1.MYD'));
SELECT (@before_count >= 0) as have_before_count;
have_before_count
1
SELECT IF(@before_count > 0, 'Success', 'Failure') has_instrumentation;
has_instrumentation
Success
SELECT * FROM t1 WHERE id < 4;
id b
1 initial value
2 initial value
3 initial value
SET @after_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
AND (OBJECT_NAME LIKE '%t1.MYD') AND (1 = 1));
SELECT (@after_count >= 0) as have_after_count;
have_after_count
1
SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_ff1_timed;
test_ff1_timed
Success
UPDATE performance_schema.setup_instruments SET enabled='NO';
SET @before_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
AND (OBJECT_NAME LIKE '%t1.MYD') AND (2 = 2));
SELECT (@before_count >= 0) as have_before_count;
have_before_count
1
SELECT * FROM t1 WHERE id < 6;
id b
1 initial value
2 initial value
3 initial value
4 initial value
5 initial value
SET @after_count = (SELECT SUM(TIMER_WAIT)
FROM performance_schema.events_waits_history_long
WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
AND (OBJECT_NAME LIKE '%t1.MYD') AND (3 = 3));
SELECT (@after_count >= 0) as have_after_count;
have_after_count
1
SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_ff2_timed;
test_ff2_timed
Success
UPDATE performance_schema.setup_instruments SET enabled = 'YES'
WHERE name LIKE 'wait/io/file/%';
UPDATE performance_schema.setup_instruments SET timed = 'NO';
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 > 4;
id b
5 initial value
6 initial value
7 initial value
8 initial value
SELECT * FROM performance_schema.events_waits_history_long
WHERE TIMER_WAIT != NULL
OR TIMER_START != NULL
OR TIMER_END != NULL;
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID NESTING_EVENT_TYPE OPERATION NUMBER_OF_BYTES FLAGS
SELECT * FROM performance_schema.events_waits_history
WHERE TIMER_WAIT != NULL
OR TIMER_START != NULL
OR TIMER_END != NULL;
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID NESTING_EVENT_TYPE OPERATION NUMBER_OF_BYTES FLAGS
SELECT * FROM performance_schema.events_waits_current
WHERE TIMER_WAIT != NULL
OR TIMER_START != NULL
OR TIMER_END != NULL;
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID NESTING_EVENT_TYPE OPERATION NUMBER_OF_BYTES FLAGS
UPDATE performance_schema.setup_instruments SET timed = 'YES';
SELECT * FROM t1 WHERE id < 4;
id b
1 initial value
2 initial value
3 initial value
DROP TABLE t1;
SELECT SUM(COUNT_READ) AS sum_count_read,
SUM(COUNT_WRITE) AS sum_count_write,
SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read,
SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write
FROM performance_schema.file_summary_by_instance
WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL;
SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
SELECT p.processlist_user, SUM(TIMER_WAIT) SUM_WAIT
# ((TIME_TO_SEC(TIMEDIFF(NOW(), i.startup_time)) * 1000) / SUM(TIMER_WAIT)) * 100 WAIT_PERCENTAGE
FROM performance_schema.events_waits_history_long h
INNER JOIN performance_schema.threads p USING (THREAD_ID)
GROUP BY p.processlist_user
ORDER BY SUM_WAIT DESC
LIMIT 20;
SELECT h.EVENT_NAME, SUM(h.TIMER_WAIT) TOTAL_WAIT
FROM performance_schema.events_waits_history_long h
INNER JOIN performance_schema.threads p USING (THREAD_ID)
WHERE p.PROCESSLIST_ID = 1
GROUP BY h.EVENT_NAME
HAVING TOTAL_WAIT > 0;
SELECT p.processlist_user, h.operation, SUM(NUMBER_OF_BYTES) bytes
FROM performance_schema.events_waits_history_long h
INNER JOIN performance_schema.threads p USING (THREAD_ID)
GROUP BY p.processlist_user, h.operation
HAVING BYTES > 0
ORDER BY p.processlist_user, h.operation;
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
show status like "performance_schema%";
Variable_name Value
Performance_schema_accounts_lost 0
Performance_schema_cond_classes_lost 0
Performance_schema_cond_instances_lost 0
Performance_schema_digest_lost 0
Performance_schema_file_classes_lost 0
Performance_schema_file_handles_lost 0
Performance_schema_file_instances_lost 0
Performance_schema_hosts_lost 0
Performance_schema_locker_lost 0
Performance_schema_mutex_classes_lost 0
Performance_schema_mutex_instances_lost 0
Performance_schema_rwlock_classes_lost 0
Performance_schema_rwlock_instances_lost 0
Performance_schema_socket_classes_lost 0
Performance_schema_socket_instances_lost 0
Performance_schema_stage_classes_lost 0
Performance_schema_statement_classes_lost 0
Performance_schema_table_handles_lost 0
Performance_schema_table_instances_lost 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Performance_schema_users_lost 0
|
