Merge lp:~vlad-lesin/percona-server/5.6-query_response_time-rw-MDL into lp:percona-server/5.6

Proposed by Vlad Lesin on 2014-11-06
Status: Rejected
Rejected by: Laurynas Biveinis on 2015-01-13
Proposed branch: lp:~vlad-lesin/percona-server/5.6-query_response_time-rw-MDL
Merge into: lp:percona-server/5.6
Diff against target: 1342 lines (+1095/-28)
13 files modified
mysql-test/include/plugin.defs (+1/-1)
mysql-test/mysql-test-run.pl (+1/-0)
plugin/query_response_time/plugin.cc (+33/-2)
plugin/query_response_time/query_response_time.cc (+45/-24)
plugin/query_response_time/query_response_time.h (+8/-1)
plugin/query_response_time/tests/mtr/basic.result (+12/-0)
plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc (+16/-0)
plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc (+10/-0)
plugin/query_response_time/tests/mtr/query_response_time-rw.result (+761/-0)
plugin/query_response_time/tests/mtr/query_response_time-rw.test (+201/-0)
sql/mdl.cc (+3/-0)
sql/mdl.h (+2/-0)
sql/sql_parse.cc (+2/-0)
To merge this branch: bzr merge lp:~vlad-lesin/percona-server/5.6-query_response_time-rw-MDL
Reviewer Review Type Date Requested Status
Percona core 2014-11-06 Pending
Review via email: mp+240797@code.launchpad.net

Description of the change

The two new IS tables QUERY_RESPONSE_TIME_READ and QUERY_RESPONSE_TIME_WRITE
are implemented for 'query_response_time' plugin.

QUERY_RESPONSE_TIME_READ table shows RTD for 'read' queries and
QUERY_RESPONSE_TIME_WRITE shows statistics for 'write' queries.

Consider statement as 'write' if restrictive enough
(MDL_SHARED_WRITE and above) MDL lock is acquired.

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/750/

To post a comment you must log in.

Unmerged revisions

691. By Vlad Lesin on 2014-11-05

Consider statement as 'write' if restrictive enough
(MDL_SHARED_WRITE and above) MDL lock is acquired.

690. By Vlad Lesin on 2014-10-29

The two new IS tables QUERY_RESPONSE_TIME_READ and QUERY_RESPONSE_TIME_WRITE
are implemented for 'query_response_time' plugin.

QUERY_RESPONSE_TIME_READ table shows RTD for 'read' queries and
QUERY_RESPONSE_TIME_WRITE shows statistics for 'write' queries.

689. By Vlad Lesin on 2014-10-29

Bug #1352501 fix.

The issue is that semicolons are not escaped in gdbinit.* file, as a result
all options after first semicolon are truncated when server is launched with
gdb.

The fix is in escaping semicolons in options string for gdb.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'mysql-test/include/plugin.defs'
2--- mysql-test/include/plugin.defs 2014-09-25 06:33:00 +0000
3+++ mysql-test/include/plugin.defs 2014-11-06 00:11:26 +0000
4@@ -49,4 +49,4 @@
5 scalability_metrics plugin/scalability_metrics SCALABILITY_METRICS
6 adt_null plugin/audit_null AUDIT_NULL
7 audit_log plugin/audit_log AUDIT_LOG audit_log
8-query_response_time plugin/query_response_time PLUGIN_QUERY_RESPONSE_TIME QUERY_RESPONSE_TIME_AUDIT,QUERY_RESPONSE_TIME
9+query_response_time plugin/query_response_time PLUGIN_QUERY_RESPONSE_TIME QUERY_RESPONSE_TIME_AUDIT,QUERY_RESPONSE_TIME,QUERY_RESPONSE_TIME_READ,QUERY_RESPONSE_TIME_WRITE
10
11=== modified file 'mysql-test/mysql-test-run.pl'
12--- mysql-test/mysql-test-run.pl 2014-10-29 15:21:53 +0000
13+++ mysql-test/mysql-test-run.pl 2014-11-06 00:11:26 +0000
14@@ -6055,6 +6055,7 @@
15
16 # Put $args into a single string
17 my $str= join(" ", @$$args);
18+ $str =~ s/\;/\\;/g;
19 my $runline= $input ? "run $str < $input" : "run $str";
20
21 # write init file for mysqld or client
22
23=== modified file 'plugin/query_response_time/plugin.cc'
24--- plugin/query_response_time/plugin.cc 2014-09-25 06:33:00 +0000
25+++ plugin/query_response_time/plugin.cc 2014-11-06 00:11:26 +0000
26@@ -130,6 +130,7 @@
27 if (event_general->event_subclass == MYSQL_AUDIT_GENERAL_STATUS &&
28 opt_query_response_time_stats)
29 {
30+ QUERY_TYPE query_type= thd->is_write ? WRITE : READ;
31 #ifndef DBUG_OFF
32 if (THDVAR(thd, exec_time_debug)) {
33 ulonglong t = THDVAR(thd, exec_time_debug);
34@@ -139,11 +140,11 @@
35 SQLCOM_SET_OPTION )) {
36 t = 0;
37 }
38- query_response_time_collect(t);
39+ query_response_time_collect(query_type, t);
40 }
41 else
42 #endif
43- query_response_time_collect(thd->utime_after_query - thd->utime_after_lock);
44+ query_response_time_collect(query_type, my_micro_time() - thd->start_utime);
45 }
46 }
47
48@@ -172,6 +173,36 @@
49 0,
50 },
51 {
52+ MYSQL_INFORMATION_SCHEMA_PLUGIN,
53+ &query_response_time_info_descriptor,
54+ "QUERY_RESPONSE_TIME_READ",
55+ "Percona",
56+ "Query Response Time Distribution INFORMATION_SCHEMA Plugin",
57+ PLUGIN_LICENSE_GPL,
58+ query_response_time_info_init,
59+ query_response_time_info_deinit,
60+ 0x0100,
61+ NULL,
62+ NULL,
63+ (void *)"1.0",
64+ 0,
65+},
66+{
67+ MYSQL_INFORMATION_SCHEMA_PLUGIN,
68+ &query_response_time_info_descriptor,
69+ "QUERY_RESPONSE_TIME_WRITE",
70+ "Percona",
71+ "Query Response Time Distribution INFORMATION_SCHEMA Plugin",
72+ PLUGIN_LICENSE_GPL,
73+ query_response_time_info_init,
74+ query_response_time_info_deinit,
75+ 0x0100,
76+ NULL,
77+ NULL,
78+ (void *)"1.0",
79+ 0,
80+},
81+{
82 MYSQL_AUDIT_PLUGIN,
83 &query_response_time_audit_descriptor,
84 "QUERY_RESPONSE_TIME_AUDIT",
85
86=== modified file 'plugin/query_response_time/query_response_time.cc'
87--- plugin/query_response_time/query_response_time.cc 2014-09-25 06:33:00 +0000
88+++ plugin/query_response_time/query_response_time.cc 2014-11-06 00:11:26 +0000
89@@ -170,17 +170,17 @@
90 {
91 my_atomic_rwlock_destroy(&time_collector_lock);
92 }
93- uint32 count(uint index)
94+ uint32 count(QUERY_TYPE type, uint index)
95 {
96 my_atomic_rwlock_rdlock(&time_collector_lock);
97- uint32 result= my_atomic_load32((int32*)&m_count[index]);
98+ uint32 result= my_atomic_load32((int32*)&m_count[type][index]);
99 my_atomic_rwlock_rdunlock(&time_collector_lock);
100 return result;
101 }
102- uint64 total(uint index)
103+ uint64 total(QUERY_TYPE type, uint index)
104 {
105 my_atomic_rwlock_rdlock(&time_collector_lock);
106- uint64 result= my_atomic_load64((int64*)&m_total[index]);
107+ uint64 result= my_atomic_load64((int64*)&m_total[type][index]);
108 my_atomic_rwlock_rdunlock(&time_collector_lock);
109 return result;
110 }
111@@ -192,7 +192,7 @@
112 memset((void*)&m_total,0,sizeof(m_total));
113 my_atomic_rwlock_wrunlock(&time_collector_lock);
114 }
115- void collect(uint64 time)
116+ void collect(QUERY_TYPE type, uint64 time)
117 {
118 int i= 0;
119 for(int count= m_utility->bound_count(); count > i; ++i)
120@@ -200,8 +200,10 @@
121 if(m_utility->bound(i) > time)
122 {
123 my_atomic_rwlock_wrlock(&time_collector_lock);
124- my_atomic_add32((int32*)(&m_count[i]), 1);
125- my_atomic_add64((int64*)(&m_total[i]), time);
126+ my_atomic_add32((int32*)(&m_count[0][i]), 1);
127+ my_atomic_add64((int64*)(&m_total[0][i]), time);
128+ my_atomic_add32((int32*)(&m_count[type][i]), 1);
129+ my_atomic_add64((int64*)(&m_total[type][i]), time);
130 my_atomic_rwlock_wrunlock(&time_collector_lock);
131 break;
132 }
133@@ -209,12 +211,18 @@
134 }
135 private:
136 utility* m_utility;
137- /* The lock for atomic operations on m_count and m_total. Only actually
138- used on architectures that do not have atomic implementation of atomic
139- operations. */
140+ /* The lock for atomic operations on
141+ m_count, m_total, m_r_count, m_r_total, m_w_count, m_w_total.
142+ Only actually used on architectures that do not have atomic
143+ implementation of atomic operations. */
144 my_atomic_rwlock_t time_collector_lock;
145- uint32 m_count[OVERALL_POWER_COUNT + 1];
146- uint64 m_total[OVERALL_POWER_COUNT + 1];
147+ /*
148+ The fisrt row is for overall statistics,
149+ the second row is for 'read' queries,
150+ the third row is for 'write' queries.
151+ */
152+ uint32 m_count[3][OVERALL_POWER_COUNT + 1];
153+ uint64 m_total[3][OVERALL_POWER_COUNT + 1];
154 };
155
156 class collector
157@@ -231,7 +239,9 @@
158 m_utility.setup(opt_query_response_time_range_base);
159 m_time.flush();
160 }
161- int fill(THD* thd, TABLE_LIST *tables, COND *cond)
162+ int fill(QUERY_TYPE type,
163+ THD* thd,
164+ TABLE_LIST *tables, COND *cond)
165 {
166 DBUG_ENTER("fill_schema_query_response_time");
167 TABLE *table= static_cast<TABLE*>(tables->table);
168@@ -250,10 +260,10 @@
169 else
170 {
171 print_time(time, sizeof(time), TIME_STRING_FORMAT, this->bound(i));
172- print_time(total, sizeof(total), TOTAL_STRING_FORMAT, this->total(i));
173+ print_time(total, sizeof(total), TOTAL_STRING_FORMAT, this->total(type, i));
174 }
175 fields[0]->store(time,strlen(time),system_charset_info);
176- fields[1]->store(this->count(i));
177+ fields[1]->store(this->count(type, i));
178 fields[2]->store(total,strlen(total),system_charset_info);
179 if (schema_table_store_record(thd, table))
180 {
181@@ -262,9 +272,9 @@
182 }
183 DBUG_RETURN(0);
184 }
185- void collect(ulonglong time)
186+ void collect(QUERY_TYPE type, ulonglong time)
187 {
188- m_time.collect(time);
189+ m_time.collect(type, time);
190 }
191 uint bound_count() const
192 {
193@@ -274,13 +284,13 @@
194 {
195 return m_utility.bound(index);
196 }
197- ulonglong count(uint index)
198+ ulonglong count(QUERY_TYPE type, uint index)
199 {
200- return m_time.count(index);
201+ return m_time.count(type, index);
202 }
203- ulonglong total(uint index)
204+ ulonglong total(QUERY_TYPE type, uint index)
205 {
206- return m_time.total(index);
207+ return m_time.total(type, index);
208 }
209 private:
210 utility m_utility;
211@@ -304,12 +314,23 @@
212 {
213 query_response_time::g_collector.flush();
214 }
215-void query_response_time_collect(ulonglong query_time)
216+
217+void query_response_time_collect(QUERY_TYPE type,
218+ ulonglong query_time)
219 {
220- query_response_time::g_collector.collect(query_time);
221+ query_response_time::g_collector.collect(type, query_time);
222 }
223
224 int query_response_time_fill(THD* thd, TABLE_LIST *tables, COND *cond)
225 {
226- return query_response_time::g_collector.fill(thd,tables,cond);
227+ QUERY_TYPE query_type = ANY;
228+ if (!strncmp(tables->table->alias,
229+ "QUERY_RESPONSE_TIME_READ",
230+ sizeof("QUERY_RESPONSE_TIME_READ") - 1))
231+ query_type = READ;
232+ else if (!strncmp(tables->table->alias,
233+ "QUERY_RESPONSE_TIME_WRITE",
234+ sizeof("QUERY_RESPONSE_TIME_WRITE") - 1))
235+ query_type = WRITE;
236+ return query_response_time::g_collector.fill(query_type, thd, tables, cond);
237 }
238
239=== modified file 'plugin/query_response_time/query_response_time.h'
240--- plugin/query_response_time/query_response_time.h 2014-09-25 06:33:00 +0000
241+++ plugin/query_response_time/query_response_time.h 2014-11-06 00:11:26 +0000
242@@ -66,6 +66,13 @@
243 MY_MAX( (QRT_TOTAL_STRING_POSITIVE_POWER_LENGTH + 1 /* '.' */ + 6 /*QRT_TOTAL_STRING_NEGATIVE_POWER_LENGTH*/), \
244 (sizeof(QRT_TIME_OVERFLOW) - 1) )
245
246+enum QUERY_TYPE
247+{
248+ ANY= 0,
249+ READ= 1,
250+ WRITE= 2
251+};
252+
253 extern ST_SCHEMA_TABLE query_response_time_table;
254
255 typedef class Item COND;
256@@ -73,7 +80,7 @@
257 extern void query_response_time_init ();
258 extern void query_response_time_free ();
259 extern void query_response_time_flush ();
260-extern void query_response_time_collect(ulonglong query_time);
261+extern void query_response_time_collect(QUERY_TYPE type, ulonglong query_time);
262 extern int query_response_time_fill (THD* thd, TABLE_LIST *tables, COND *cond);
263
264 extern ulong opt_query_response_time_range_base;
265
266=== modified file 'plugin/query_response_time/tests/mtr/basic.result'
267--- plugin/query_response_time/tests/mtr/basic.result 2014-09-25 06:33:00 +0000
268+++ plugin/query_response_time/tests/mtr/basic.result 2014-11-06 00:11:26 +0000
269@@ -23,6 +23,18 @@
270 PLUGIN_AUTHOR Percona and Sergey Vojtovich
271 PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin
272 PLUGIN_LICENSE GPL
273+PLUGIN_NAME QUERY_RESPONSE_TIME_READ
274+PLUGIN_VERSION 1.0
275+PLUGIN_TYPE INFORMATION SCHEMA
276+PLUGIN_AUTHOR Percona
277+PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin
278+PLUGIN_LICENSE GPL
279+PLUGIN_NAME QUERY_RESPONSE_TIME_WRITE
280+PLUGIN_VERSION 1.0
281+PLUGIN_TYPE INFORMATION SCHEMA
282+PLUGIN_AUTHOR Percona
283+PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin
284+PLUGIN_LICENSE GPL
285 SET GLOBAL QUERY_RESPONSE_TIME_STATS=1;
286 SELECT 1;
287 1
288
289=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc'
290--- plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc 1970-01-01 00:00:00 +0000
291+++ plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc 2014-11-06 00:11:26 +0000
292@@ -0,0 +1,16 @@
293+--disable_query_log
294+--disable_result_log
295+SET GLOBAL QUERY_RESPONSE_TIME_STATS = 0;
296+SET GLOBAL query_response_time_flush = 1;
297+SET SESSION query_response_time_exec_time_debug = 500000;
298+--enable_query_log
299+--enable_result_log
300+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
301+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
302+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
303+--disable_query_log
304+--disable_result_log
305+SET GLOBAL QUERY_RESPONSE_TIME_STATS = 1;
306+--enable_query_log
307+--enable_result_log
308+--echo ------------------Test body begin--------------------
309
310=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc'
311--- plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc 1970-01-01 00:00:00 +0000
312+++ plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc 2014-11-06 00:11:26 +0000
313@@ -0,0 +1,10 @@
314+--echo ------------------Test body end----------------------
315+--disable_query_log
316+--disable_result_log
317+SET GLOBAL QUERY_RESPONSE_TIME_STATS = 0;
318+SET SESSION query_response_time_exec_time_debug = default;
319+--enable_query_log
320+--enable_result_log
321+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
322+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
323+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
324
325=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw.result'
326--- plugin/query_response_time/tests/mtr/query_response_time-rw.result 1970-01-01 00:00:00 +0000
327+++ plugin/query_response_time/tests/mtr/query_response_time-rw.result 2014-11-06 00:11:26 +0000
328@@ -0,0 +1,761 @@
329+CREATE FUNCTION DML_FUNC()
330+RETURNS INT
331+BEGIN
332+INSERT INTO t1 (b) VALUES (1), (2);
333+UPDATE t1 SET b = 1;
334+RETURN 1;
335+END|
336+CREATE PROCEDURE NESTED_PROC()
337+BEGIN
338+SELECT DML_FUNC();
339+END|
340+SET default_storage_engine=InnoDB;
341+
342+============================
343+= Test for simple DML and DDL
344+============================
345+
346+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
347+TIME COUNT TOTAL
348+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
349+TIME COUNT TOTAL
350+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
351+TIME COUNT TOTAL
352+------------------Test body begin--------------------
353+CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
354+b INT UNSIGNED NOT NULL DEFAULT 0);
355+INSERT INTO t1 (b) VALUES (1), (2);
356+UPDATE t1 SET b = 1;
357+SELECT * FROM t1;
358+a b
359+1 1
360+2 1
361+DELETE FROM t1;
362+ALTER TABLE t1 ADD COLUMN (c INT);
363+DROP TABLE t1;
364+------------------Test body end----------------------
365+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
366+TIME COUNT TOTAL
367+ 0.000001 1 0.000000
368+ 1.000000 7 3.500000
369+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
370+TIME COUNT TOTAL
371+ 0.000001 1 0.000000
372+ 1.000000 1 0.500000
373+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
374+TIME COUNT TOTAL
375+ 1.000000 6 3.000000
376+CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
377+b INT(10) UNSIGNED NOT NULL DEFAULT 0);
378+
379+============================
380+= Test for SELECT+DML
381+============================
382+
383+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
384+TIME COUNT TOTAL
385+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
386+TIME COUNT TOTAL
387+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
388+TIME COUNT TOTAL
389+------------------Test body begin--------------------
390+SELECT DML_FUNC();
391+DML_FUNC()
392+1
393+------------------Test body end----------------------
394+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
395+TIME COUNT TOTAL
396+ 0.000001 1 0.000000
397+ 1.000000 3 1.500000
398+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
399+TIME COUNT TOTAL
400+ 0.000001 1 0.000000
401+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
402+TIME COUNT TOTAL
403+ 1.000000 3 1.500000
404+
405+============================
406+= Test for nested calls
407+============================
408+
409+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
410+TIME COUNT TOTAL
411+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
412+TIME COUNT TOTAL
413+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
414+TIME COUNT TOTAL
415+------------------Test body begin--------------------
416+CALL NESTED_PROC();
417+DML_FUNC()
418+1
419+------------------Test body end----------------------
420+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
421+TIME COUNT TOTAL
422+ 0.000001 1 0.000000
423+ 1.000000 4 2.000000
424+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
425+TIME COUNT TOTAL
426+ 0.000001 1 0.000000
427+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
428+TIME COUNT TOTAL
429+ 1.000000 4 2.000000
430+
431+============================
432+= Test for 'PREPARE'
433+============================
434+
435+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
436+TIME COUNT TOTAL
437+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
438+TIME COUNT TOTAL
439+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
440+TIME COUNT TOTAL
441+------------------Test body begin--------------------
442+PREPARE rw_stmt FROM 'DELETE FROM t1';
443+PREPARE ro_stmt FROM 'SELECT * FROM t1';
444+PREPARE select_dml_stmt FROM 'SELECT DML_FUNC()';
445+PREPARE nested_call_stmt FROM 'CALL NESTED_PROC()';
446+------------------Test body end----------------------
447+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
448+TIME COUNT TOTAL
449+ 0.000001 1 0.000000
450+ 1.000000 4 2.000000
451+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
452+TIME COUNT TOTAL
453+ 0.000001 1 0.000000
454+ 1.000000 4 2.000000
455+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
456+TIME COUNT TOTAL
457+
458+============================
459+= Test for 'EXECUTE'
460+============================
461+
462+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
463+TIME COUNT TOTAL
464+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
465+TIME COUNT TOTAL
466+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
467+TIME COUNT TOTAL
468+------------------Test body begin--------------------
469+EXECUTE rw_stmt;
470+EXECUTE ro_stmt;
471+a b
472+EXECUTE select_dml_stmt;
473+DML_FUNC()
474+1
475+EXECUTE nested_call_stmt;
476+DML_FUNC()
477+1
478+------------------Test body end----------------------
479+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
480+TIME COUNT TOTAL
481+ 0.000001 1 0.000000
482+ 1.000000 9 4.500000
483+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
484+TIME COUNT TOTAL
485+ 0.000001 1 0.000000
486+ 1.000000 1 0.500000
487+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
488+TIME COUNT TOTAL
489+ 1.000000 8 4.000000
490+
491+============================
492+= Test for 'DEALLOCATE PREPARE'
493+============================
494+
495+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
496+TIME COUNT TOTAL
497+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
498+TIME COUNT TOTAL
499+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
500+TIME COUNT TOTAL
501+------------------Test body begin--------------------
502+DEALLOCATE PREPARE rw_stmt;
503+DEALLOCATE PREPARE ro_stmt;
504+DEALLOCATE PREPARE select_dml_stmt;
505+DEALLOCATE PREPARE nested_call_stmt;
506+------------------Test body end----------------------
507+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
508+TIME COUNT TOTAL
509+ 0.000001 1 0.000000
510+ 1.000000 4 2.000000
511+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
512+TIME COUNT TOTAL
513+ 0.000001 1 0.000000
514+ 1.000000 4 2.000000
515+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
516+TIME COUNT TOTAL
517+
518+============================
519+= Test for transactions
520+============================
521+
522+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
523+TIME COUNT TOTAL
524+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
525+TIME COUNT TOTAL
526+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
527+TIME COUNT TOTAL
528+------------------Test body begin--------------------
529+BEGIN;
530+SELECT * FROM t1;
531+a b
532+5 1
533+6 1
534+7 1
535+8 1
536+COMMIT;
537+------------------Test body end----------------------
538+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
539+TIME COUNT TOTAL
540+ 0.000001 1 0.000000
541+ 1.000000 3 1.500000
542+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
543+TIME COUNT TOTAL
544+ 0.000001 1 0.000000
545+ 1.000000 3 1.500000
546+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
547+TIME COUNT TOTAL
548+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
549+TIME COUNT TOTAL
550+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
551+TIME COUNT TOTAL
552+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
553+TIME COUNT TOTAL
554+------------------Test body begin--------------------
555+BEGIN;
556+INSERT INTO t1 (b) VALUES (1), (2);
557+UPDATE t1 SET b = 1;
558+SELECT * FROM t1;
559+a b
560+5 1
561+6 1
562+7 1
563+8 1
564+9 1
565+10 1
566+DELETE FROM t1;
567+COMMIT;
568+------------------Test body end----------------------
569+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
570+TIME COUNT TOTAL
571+ 0.000001 1 0.000000
572+ 1.000000 6 3.000000
573+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
574+TIME COUNT TOTAL
575+ 0.000001 1 0.000000
576+ 1.000000 3 1.500000
577+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
578+TIME COUNT TOTAL
579+ 1.000000 3 1.500000
580+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
581+TIME COUNT TOTAL
582+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
583+TIME COUNT TOTAL
584+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
585+TIME COUNT TOTAL
586+------------------Test body begin--------------------
587+BEGIN;
588+SELECT DML_FUNC();
589+DML_FUNC()
590+1
591+CALL NESTED_PROC();
592+DML_FUNC()
593+1
594+COMMIT;
595+------------------Test body end----------------------
596+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
597+TIME COUNT TOTAL
598+ 0.000001 1 0.000000
599+ 1.000000 9 4.500000
600+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
601+TIME COUNT TOTAL
602+ 0.000001 1 0.000000
603+ 1.000000 2 1.000000
604+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
605+TIME COUNT TOTAL
606+ 1.000000 7 3.500000
607+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
608+TIME COUNT TOTAL
609+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
610+TIME COUNT TOTAL
611+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
612+TIME COUNT TOTAL
613+------------------Test body begin--------------------
614+BEGIN;
615+INSERT INTO t1 (b) VALUES (1), (2);
616+UPDATE t1 SET b = 1;
617+ROLLBACK;
618+------------------Test body end----------------------
619+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
620+TIME COUNT TOTAL
621+ 0.000001 1 0.000000
622+ 1.000000 4 2.000000
623+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
624+TIME COUNT TOTAL
625+ 0.000001 1 0.000000
626+ 1.000000 2 1.000000
627+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
628+TIME COUNT TOTAL
629+ 1.000000 2 1.000000
630+
631+============================
632+= Test for implicit commit
633+============================
634+
635+SET autocommit = 0;
636+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
637+TIME COUNT TOTAL
638+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
639+TIME COUNT TOTAL
640+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
641+TIME COUNT TOTAL
642+------------------Test body begin--------------------
643+INSERT INTO t1 (b) VALUES (1), (2);
644+CREATE INDEX ab ON t1 (a, b);
645+------------------Test body end----------------------
646+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
647+TIME COUNT TOTAL
648+ 0.000001 1 0.000000
649+ 1.000000 2 1.000000
650+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
651+TIME COUNT TOTAL
652+ 0.000001 1 0.000000
653+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
654+TIME COUNT TOTAL
655+ 1.000000 2 1.000000
656+SET autocommit = 1;
657+
658+============================
659+= Test for trigger
660+============================
661+
662+CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
663+b INT UNSIGNED NOT NULL DEFAULT 0);
664+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
665+TIME COUNT TOTAL
666+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
667+TIME COUNT TOTAL
668+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
669+TIME COUNT TOTAL
670+------------------Test body begin--------------------
671+CREATE TRIGGER test_trigger BEFORE INSERT ON t1
672+FOR EACH ROW
673+BEGIN
674+INSERT INTO t2 SET b = NEW.b;
675+INSERT INTO t2 SET b = NEW.b;
676+END;
677+|
678+------------------Test body end----------------------
679+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
680+TIME COUNT TOTAL
681+ 0.000001 1 0.000000
682+ 1.000000 1 0.500000
683+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
684+TIME COUNT TOTAL
685+ 0.000001 1 0.000000
686+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
687+TIME COUNT TOTAL
688+ 1.000000 1 0.500000
689+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
690+TIME COUNT TOTAL
691+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
692+TIME COUNT TOTAL
693+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
694+TIME COUNT TOTAL
695+------------------Test body begin--------------------
696+INSERT INTO t1 (b) VALUES (1), (2);
697+------------------Test body end----------------------
698+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
699+TIME COUNT TOTAL
700+ 0.000001 1 0.000000
701+ 1.000000 5 2.500000
702+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
703+TIME COUNT TOTAL
704+ 0.000001 1 0.000000
705+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
706+TIME COUNT TOTAL
707+ 1.000000 5 2.500000
708+DROP TABLE t1;
709+DROP TABLE t2;
710+SET default_storage_engine=InnoDB;
711+SET default_storage_engine=MyISAM;
712+
713+==========================
714+= Test for Innodb engine
715+==========================
716+
717+============================
718+= Test for simple DML and DDL
719+============================
720+
721+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
722+TIME COUNT TOTAL
723+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
724+TIME COUNT TOTAL
725+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
726+TIME COUNT TOTAL
727+------------------Test body begin--------------------
728+CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
729+b INT UNSIGNED NOT NULL DEFAULT 0);
730+INSERT INTO t1 (b) VALUES (1), (2);
731+UPDATE t1 SET b = 1;
732+SELECT * FROM t1;
733+a b
734+1 1
735+2 1
736+DELETE FROM t1;
737+ALTER TABLE t1 ADD COLUMN (c INT);
738+DROP TABLE t1;
739+------------------Test body end----------------------
740+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
741+TIME COUNT TOTAL
742+ 0.000001 1 0.000000
743+ 1.000000 7 3.500000
744+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
745+TIME COUNT TOTAL
746+ 0.000001 1 0.000000
747+ 1.000000 1 0.500000
748+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
749+TIME COUNT TOTAL
750+ 1.000000 6 3.000000
751+CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
752+b INT(10) UNSIGNED NOT NULL DEFAULT 0);
753+
754+============================
755+= Test for SELECT+DML
756+============================
757+
758+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
759+TIME COUNT TOTAL
760+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
761+TIME COUNT TOTAL
762+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
763+TIME COUNT TOTAL
764+------------------Test body begin--------------------
765+SELECT DML_FUNC();
766+DML_FUNC()
767+1
768+------------------Test body end----------------------
769+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
770+TIME COUNT TOTAL
771+ 0.000001 1 0.000000
772+ 1.000000 3 1.500000
773+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
774+TIME COUNT TOTAL
775+ 0.000001 1 0.000000
776+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
777+TIME COUNT TOTAL
778+ 1.000000 3 1.500000
779+
780+============================
781+= Test for nested calls
782+============================
783+
784+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
785+TIME COUNT TOTAL
786+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
787+TIME COUNT TOTAL
788+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
789+TIME COUNT TOTAL
790+------------------Test body begin--------------------
791+CALL NESTED_PROC();
792+DML_FUNC()
793+1
794+------------------Test body end----------------------
795+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
796+TIME COUNT TOTAL
797+ 0.000001 1 0.000000
798+ 1.000000 4 2.000000
799+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
800+TIME COUNT TOTAL
801+ 0.000001 1 0.000000
802+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
803+TIME COUNT TOTAL
804+ 1.000000 4 2.000000
805+
806+============================
807+= Test for 'PREPARE'
808+============================
809+
810+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
811+TIME COUNT TOTAL
812+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
813+TIME COUNT TOTAL
814+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
815+TIME COUNT TOTAL
816+------------------Test body begin--------------------
817+PREPARE rw_stmt FROM 'DELETE FROM t1';
818+PREPARE ro_stmt FROM 'SELECT * FROM t1';
819+PREPARE select_dml_stmt FROM 'SELECT DML_FUNC()';
820+PREPARE nested_call_stmt FROM 'CALL NESTED_PROC()';
821+------------------Test body end----------------------
822+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
823+TIME COUNT TOTAL
824+ 0.000001 1 0.000000
825+ 1.000000 4 2.000000
826+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
827+TIME COUNT TOTAL
828+ 0.000001 1 0.000000
829+ 1.000000 4 2.000000
830+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
831+TIME COUNT TOTAL
832+
833+============================
834+= Test for 'EXECUTE'
835+============================
836+
837+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
838+TIME COUNT TOTAL
839+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
840+TIME COUNT TOTAL
841+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
842+TIME COUNT TOTAL
843+------------------Test body begin--------------------
844+EXECUTE rw_stmt;
845+EXECUTE ro_stmt;
846+a b
847+EXECUTE select_dml_stmt;
848+DML_FUNC()
849+1
850+EXECUTE nested_call_stmt;
851+DML_FUNC()
852+1
853+------------------Test body end----------------------
854+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
855+TIME COUNT TOTAL
856+ 0.000001 1 0.000000
857+ 1.000000 9 4.500000
858+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
859+TIME COUNT TOTAL
860+ 0.000001 1 0.000000
861+ 1.000000 1 0.500000
862+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
863+TIME COUNT TOTAL
864+ 1.000000 8 4.000000
865+
866+============================
867+= Test for 'DEALLOCATE PREPARE'
868+============================
869+
870+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
871+TIME COUNT TOTAL
872+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
873+TIME COUNT TOTAL
874+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
875+TIME COUNT TOTAL
876+------------------Test body begin--------------------
877+DEALLOCATE PREPARE rw_stmt;
878+DEALLOCATE PREPARE ro_stmt;
879+DEALLOCATE PREPARE select_dml_stmt;
880+DEALLOCATE PREPARE nested_call_stmt;
881+------------------Test body end----------------------
882+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
883+TIME COUNT TOTAL
884+ 0.000001 1 0.000000
885+ 1.000000 4 2.000000
886+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
887+TIME COUNT TOTAL
888+ 0.000001 1 0.000000
889+ 1.000000 4 2.000000
890+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
891+TIME COUNT TOTAL
892+
893+============================
894+= Test for transactions
895+============================
896+
897+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
898+TIME COUNT TOTAL
899+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
900+TIME COUNT TOTAL
901+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
902+TIME COUNT TOTAL
903+------------------Test body begin--------------------
904+BEGIN;
905+SELECT * FROM t1;
906+a b
907+5 1
908+6 1
909+7 1
910+8 1
911+COMMIT;
912+------------------Test body end----------------------
913+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
914+TIME COUNT TOTAL
915+ 0.000001 1 0.000000
916+ 1.000000 3 1.500000
917+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
918+TIME COUNT TOTAL
919+ 0.000001 1 0.000000
920+ 1.000000 3 1.500000
921+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
922+TIME COUNT TOTAL
923+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
924+TIME COUNT TOTAL
925+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
926+TIME COUNT TOTAL
927+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
928+TIME COUNT TOTAL
929+------------------Test body begin--------------------
930+BEGIN;
931+INSERT INTO t1 (b) VALUES (1), (2);
932+UPDATE t1 SET b = 1;
933+SELECT * FROM t1;
934+a b
935+5 1
936+6 1
937+7 1
938+8 1
939+9 1
940+10 1
941+DELETE FROM t1;
942+COMMIT;
943+------------------Test body end----------------------
944+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
945+TIME COUNT TOTAL
946+ 0.000001 1 0.000000
947+ 1.000000 6 3.000000
948+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
949+TIME COUNT TOTAL
950+ 0.000001 1 0.000000
951+ 1.000000 3 1.500000
952+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
953+TIME COUNT TOTAL
954+ 1.000000 3 1.500000
955+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
956+TIME COUNT TOTAL
957+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
958+TIME COUNT TOTAL
959+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
960+TIME COUNT TOTAL
961+------------------Test body begin--------------------
962+BEGIN;
963+SELECT DML_FUNC();
964+DML_FUNC()
965+1
966+CALL NESTED_PROC();
967+DML_FUNC()
968+1
969+COMMIT;
970+------------------Test body end----------------------
971+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
972+TIME COUNT TOTAL
973+ 0.000001 1 0.000000
974+ 1.000000 9 4.500000
975+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
976+TIME COUNT TOTAL
977+ 0.000001 1 0.000000
978+ 1.000000 2 1.000000
979+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
980+TIME COUNT TOTAL
981+ 1.000000 7 3.500000
982+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
983+TIME COUNT TOTAL
984+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
985+TIME COUNT TOTAL
986+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
987+TIME COUNT TOTAL
988+------------------Test body begin--------------------
989+BEGIN;
990+INSERT INTO t1 (b) VALUES (1), (2);
991+UPDATE t1 SET b = 1;
992+ROLLBACK;
993+Warnings:
994+Warning 1196 Some non-transactional changed tables couldn't be rolled back
995+------------------Test body end----------------------
996+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
997+TIME COUNT TOTAL
998+ 0.000001 1 0.000000
999+ 1.000000 5 2.500000
1000+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1001+TIME COUNT TOTAL
1002+ 0.000001 1 0.000000
1003+ 1.000000 3 1.500000
1004+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1005+TIME COUNT TOTAL
1006+ 1.000000 2 1.000000
1007+
1008+============================
1009+= Test for implicit commit
1010+============================
1011+
1012+SET autocommit = 0;
1013+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
1014+TIME COUNT TOTAL
1015+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1016+TIME COUNT TOTAL
1017+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1018+TIME COUNT TOTAL
1019+------------------Test body begin--------------------
1020+INSERT INTO t1 (b) VALUES (1), (2);
1021+CREATE INDEX ab ON t1 (a, b);
1022+------------------Test body end----------------------
1023+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
1024+TIME COUNT TOTAL
1025+ 0.000001 1 0.000000
1026+ 1.000000 2 1.000000
1027+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1028+TIME COUNT TOTAL
1029+ 0.000001 1 0.000000
1030+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1031+TIME COUNT TOTAL
1032+ 1.000000 2 1.000000
1033+SET autocommit = 1;
1034+
1035+============================
1036+= Test for trigger
1037+============================
1038+
1039+CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
1040+b INT UNSIGNED NOT NULL DEFAULT 0);
1041+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
1042+TIME COUNT TOTAL
1043+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1044+TIME COUNT TOTAL
1045+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1046+TIME COUNT TOTAL
1047+------------------Test body begin--------------------
1048+CREATE TRIGGER test_trigger BEFORE INSERT ON t1
1049+FOR EACH ROW
1050+BEGIN
1051+INSERT INTO t2 SET b = NEW.b;
1052+INSERT INTO t2 SET b = NEW.b;
1053+END;
1054+|
1055+------------------Test body end----------------------
1056+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
1057+TIME COUNT TOTAL
1058+ 0.000001 1 0.000000
1059+ 1.000000 1 0.500000
1060+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1061+TIME COUNT TOTAL
1062+ 0.000001 1 0.000000
1063+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1064+TIME COUNT TOTAL
1065+ 1.000000 1 0.500000
1066+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
1067+TIME COUNT TOTAL
1068+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1069+TIME COUNT TOTAL
1070+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1071+TIME COUNT TOTAL
1072+------------------Test body begin--------------------
1073+INSERT INTO t1 (b) VALUES (1), (2);
1074+------------------Test body end----------------------
1075+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
1076+TIME COUNT TOTAL
1077+ 0.000001 1 0.000000
1078+ 1.000000 5 2.500000
1079+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
1080+TIME COUNT TOTAL
1081+ 0.000001 1 0.000000
1082+SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
1083+TIME COUNT TOTAL
1084+ 1.000000 5 2.500000
1085+DROP TABLE t1;
1086+DROP TABLE t2;
1087+SET default_storage_engine=default;
1088+DROP FUNCTION DML_FUNC;
1089+DROP PROCEDURE NESTED_PROC;
1090
1091=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw.test'
1092--- plugin/query_response_time/tests/mtr/query_response_time-rw.test 1970-01-01 00:00:00 +0000
1093+++ plugin/query_response_time/tests/mtr/query_response_time-rw.test 2014-11-06 00:11:26 +0000
1094@@ -0,0 +1,201 @@
1095+--source include/have_query_response_time_plugin.inc
1096+--source include/have_debug.inc
1097+
1098+# The file with expected results fits only to a run without
1099+# ps-protocol/sp-protocol/cursor-protocol/view-protocol.
1100+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
1101+ + $VIEW_PROTOCOL > 0`)
1102+{
1103+ --skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled
1104+}
1105+
1106+--delimiter |
1107+
1108+CREATE FUNCTION DML_FUNC()
1109+RETURNS INT
1110+BEGIN
1111+ INSERT INTO t1 (b) VALUES (1), (2);
1112+ UPDATE t1 SET b = 1;
1113+ RETURN 1;
1114+END|
1115+
1116+CREATE PROCEDURE NESTED_PROC()
1117+BEGIN
1118+ SELECT DML_FUNC();
1119+END|
1120+
1121+--delimiter ;
1122+
1123+--let $i=2
1124+
1125+while ($i)
1126+{
1127+
1128+ SET default_storage_engine=InnoDB;
1129+ if ($i==1)
1130+ {
1131+ SET default_storage_engine=MyISAM;
1132+ }
1133+
1134+ if ($i==0)
1135+ {
1136+ --echo
1137+ --echo ==========================
1138+ --echo = Test for MyISAM engine
1139+ --echo ==========================
1140+ --echo
1141+ }
1142+ if ($i==1)
1143+ {
1144+ --echo
1145+ --echo ==========================
1146+ --echo = Test for Innodb engine
1147+ --echo ==========================
1148+ }
1149+
1150+ --echo
1151+ --echo ============================
1152+ --echo = Test for simple DML and DDL
1153+ --echo ============================
1154+ --echo
1155+ --source query_response_time-rw-begin.inc
1156+ CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
1157+ b INT UNSIGNED NOT NULL DEFAULT 0);
1158+ INSERT INTO t1 (b) VALUES (1), (2);
1159+ UPDATE t1 SET b = 1;
1160+ SELECT * FROM t1;
1161+ DELETE FROM t1;
1162+ ALTER TABLE t1 ADD COLUMN (c INT);
1163+ DROP TABLE t1;
1164+ --source query_response_time-rw-end.inc
1165+
1166+ CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
1167+ b INT(10) UNSIGNED NOT NULL DEFAULT 0);
1168+ --echo
1169+ --echo ============================
1170+ --echo = Test for SELECT+DML
1171+ --echo ============================
1172+ --echo
1173+ --source query_response_time-rw-begin.inc
1174+ SELECT DML_FUNC();
1175+ --source query_response_time-rw-end.inc
1176+
1177+ --echo
1178+ --echo ============================
1179+ --echo = Test for nested calls
1180+ --echo ============================
1181+ --echo
1182+ --source query_response_time-rw-begin.inc
1183+ CALL NESTED_PROC();
1184+ --source query_response_time-rw-end.inc
1185+
1186+ --echo
1187+ --echo ============================
1188+ --echo = Test for 'PREPARE'
1189+ --echo ============================
1190+ --echo
1191+ --source query_response_time-rw-begin.inc
1192+ PREPARE rw_stmt FROM 'DELETE FROM t1';
1193+ PREPARE ro_stmt FROM 'SELECT * FROM t1';
1194+ PREPARE select_dml_stmt FROM 'SELECT DML_FUNC()';
1195+ PREPARE nested_call_stmt FROM 'CALL NESTED_PROC()';
1196+ --source query_response_time-rw-end.inc
1197+
1198+ --echo
1199+ --echo ============================
1200+ --echo = Test for 'EXECUTE'
1201+ --echo ============================
1202+ --echo
1203+ --source query_response_time-rw-begin.inc
1204+ EXECUTE rw_stmt;
1205+ EXECUTE ro_stmt;
1206+ EXECUTE select_dml_stmt;
1207+ EXECUTE nested_call_stmt;
1208+ --source query_response_time-rw-end.inc
1209+
1210+ --echo
1211+ --echo ============================
1212+ --echo = Test for 'DEALLOCATE PREPARE'
1213+ --echo ============================
1214+ --echo
1215+ --source query_response_time-rw-begin.inc
1216+ DEALLOCATE PREPARE rw_stmt;
1217+ DEALLOCATE PREPARE ro_stmt;
1218+ DEALLOCATE PREPARE select_dml_stmt;
1219+ DEALLOCATE PREPARE nested_call_stmt;
1220+ --source query_response_time-rw-end.inc
1221+
1222+ --echo
1223+ --echo ============================
1224+ --echo = Test for transactions
1225+ --echo ============================
1226+ --echo
1227+ --source query_response_time-rw-begin.inc
1228+ BEGIN;
1229+ SELECT * FROM t1;
1230+ COMMIT;
1231+ --source query_response_time-rw-end.inc
1232+ --source query_response_time-rw-begin.inc
1233+ BEGIN;
1234+ INSERT INTO t1 (b) VALUES (1), (2);
1235+ UPDATE t1 SET b = 1;
1236+ SELECT * FROM t1;
1237+ DELETE FROM t1;
1238+ COMMIT;
1239+ --source query_response_time-rw-end.inc
1240+ --source query_response_time-rw-begin.inc
1241+ BEGIN;
1242+ SELECT DML_FUNC();
1243+ CALL NESTED_PROC();
1244+ COMMIT;
1245+ --source query_response_time-rw-end.inc
1246+ --source query_response_time-rw-begin.inc
1247+ BEGIN;
1248+ INSERT INTO t1 (b) VALUES (1), (2);
1249+ UPDATE t1 SET b = 1;
1250+ ROLLBACK;
1251+ --source query_response_time-rw-end.inc
1252+
1253+ --echo
1254+ --echo ============================
1255+ --echo = Test for implicit commit
1256+ --echo ============================
1257+ --echo
1258+ SET autocommit = 0;
1259+ --source query_response_time-rw-begin.inc
1260+ INSERT INTO t1 (b) VALUES (1), (2);
1261+ CREATE INDEX ab ON t1 (a, b);
1262+ --source query_response_time-rw-end.inc
1263+ SET autocommit = 1;
1264+
1265+ --echo
1266+ --echo ============================
1267+ --echo = Test for trigger
1268+ --echo ============================
1269+ --echo
1270+ CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
1271+ b INT UNSIGNED NOT NULL DEFAULT 0);
1272+ --source query_response_time-rw-begin.inc
1273+ --delimiter |
1274+ CREATE TRIGGER test_trigger BEFORE INSERT ON t1
1275+ FOR EACH ROW
1276+ BEGIN
1277+ INSERT INTO t2 SET b = NEW.b;
1278+ INSERT INTO t2 SET b = NEW.b;
1279+ END;
1280+ |
1281+ --delimiter ;
1282+ --source query_response_time-rw-end.inc
1283+ --source query_response_time-rw-begin.inc
1284+ INSERT INTO t1 (b) VALUES (1), (2);
1285+ --source query_response_time-rw-end.inc
1286+
1287+ DROP TABLE t1;
1288+ DROP TABLE t2;
1289+
1290+ --dec $i
1291+}
1292+
1293+SET default_storage_engine=default;
1294+DROP FUNCTION DML_FUNC;
1295+DROP PROCEDURE NESTED_PROC;
1296
1297=== modified file 'sql/mdl.cc'
1298--- sql/mdl.cc 2014-05-12 14:47:30 +0000
1299+++ sql/mdl.cc 2014-11-06 00:11:26 +0000
1300@@ -2098,6 +2098,9 @@
1301 is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE));
1302 DBUG_ASSERT(mdl_request->ticket == NULL);
1303
1304+ if (mdl_request->type >= MDL_SHARED_WRITE)
1305+ get_owner()->is_write= true;
1306+
1307 /* Don't take chances in production. */
1308 mdl_request->ticket= NULL;
1309 mysql_mutex_assert_not_owner(&LOCK_open);
1310
1311=== modified file 'sql/mdl.h'
1312--- sql/mdl.h 2014-05-12 14:47:30 +0000
1313+++ sql/mdl.h 2014-11-06 00:11:26 +0000
1314@@ -63,6 +63,7 @@
1315 class MDL_context_owner
1316 {
1317 public:
1318+ MDL_context_owner() : is_write(false) {}
1319 virtual ~MDL_context_owner() {}
1320
1321 /**
1322@@ -121,6 +122,7 @@
1323 */
1324 virtual bool notify_shared_lock(MDL_context_owner *in_use,
1325 bool needs_thr_lock_abort) = 0;
1326+ bool is_write;
1327 };
1328
1329 /**
1330
1331=== modified file 'sql/sql_parse.cc'
1332--- sql/sql_parse.cc 2014-10-17 14:36:56 +0000
1333+++ sql/sql_parse.cc 2014-11-06 00:11:26 +0000
1334@@ -1867,6 +1867,8 @@
1335 thd->get_stmt_da()->sql_errno() : 0,
1336 command_name[command].str);
1337
1338+ thd->is_write = false;
1339+
1340 log_slow_statement(thd);
1341
1342 THD_STAGE_INFO(thd, stage_cleaning_up);

Subscribers

People subscribed via source and target branches