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

Proposed by Vlad Lesin
Status: Rejected
Rejected by: Laurynas Biveinis
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 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

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

690. By Vlad Lesin

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

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
=== modified file 'mysql-test/include/plugin.defs'
--- mysql-test/include/plugin.defs 2014-09-25 06:33:00 +0000
+++ mysql-test/include/plugin.defs 2014-11-06 00:11:26 +0000
@@ -49,4 +49,4 @@
49scalability_metrics plugin/scalability_metrics SCALABILITY_METRICS49scalability_metrics plugin/scalability_metrics SCALABILITY_METRICS
50adt_null plugin/audit_null AUDIT_NULL50adt_null plugin/audit_null AUDIT_NULL
51audit_log plugin/audit_log AUDIT_LOG audit_log51audit_log plugin/audit_log AUDIT_LOG audit_log
52query_response_time plugin/query_response_time PLUGIN_QUERY_RESPONSE_TIME QUERY_RESPONSE_TIME_AUDIT,QUERY_RESPONSE_TIME 52query_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
5353
=== modified file 'mysql-test/mysql-test-run.pl'
--- mysql-test/mysql-test-run.pl 2014-10-29 15:21:53 +0000
+++ mysql-test/mysql-test-run.pl 2014-11-06 00:11:26 +0000
@@ -6055,6 +6055,7 @@
60556055
6056 # Put $args into a single string6056 # Put $args into a single string
6057 my $str= join(" ", @$$args);6057 my $str= join(" ", @$$args);
6058 $str =~ s/\;/\\;/g;
6058 my $runline= $input ? "run $str < $input" : "run $str";6059 my $runline= $input ? "run $str < $input" : "run $str";
60596060
6060 # write init file for mysqld or client6061 # write init file for mysqld or client
60616062
=== modified file 'plugin/query_response_time/plugin.cc'
--- plugin/query_response_time/plugin.cc 2014-09-25 06:33:00 +0000
+++ plugin/query_response_time/plugin.cc 2014-11-06 00:11:26 +0000
@@ -130,6 +130,7 @@
130 if (event_general->event_subclass == MYSQL_AUDIT_GENERAL_STATUS &&130 if (event_general->event_subclass == MYSQL_AUDIT_GENERAL_STATUS &&
131 opt_query_response_time_stats)131 opt_query_response_time_stats)
132 {132 {
133 QUERY_TYPE query_type= thd->is_write ? WRITE : READ;
133#ifndef DBUG_OFF134#ifndef DBUG_OFF
134 if (THDVAR(thd, exec_time_debug)) {135 if (THDVAR(thd, exec_time_debug)) {
135 ulonglong t = THDVAR(thd, exec_time_debug);136 ulonglong t = THDVAR(thd, exec_time_debug);
@@ -139,11 +140,11 @@
139 SQLCOM_SET_OPTION )) {140 SQLCOM_SET_OPTION )) {
140 t = 0;141 t = 0;
141 }142 }
142 query_response_time_collect(t);143 query_response_time_collect(query_type, t);
143 }144 }
144 else145 else
145#endif146#endif
146 query_response_time_collect(thd->utime_after_query - thd->utime_after_lock);147 query_response_time_collect(query_type, my_micro_time() - thd->start_utime);
147 }148 }
148}149}
149150
@@ -172,6 +173,36 @@
172 0,173 0,
173},174},
174{175{
176 MYSQL_INFORMATION_SCHEMA_PLUGIN,
177 &query_response_time_info_descriptor,
178 "QUERY_RESPONSE_TIME_READ",
179 "Percona",
180 "Query Response Time Distribution INFORMATION_SCHEMA Plugin",
181 PLUGIN_LICENSE_GPL,
182 query_response_time_info_init,
183 query_response_time_info_deinit,
184 0x0100,
185 NULL,
186 NULL,
187 (void *)"1.0",
188 0,
189},
190{
191 MYSQL_INFORMATION_SCHEMA_PLUGIN,
192 &query_response_time_info_descriptor,
193 "QUERY_RESPONSE_TIME_WRITE",
194 "Percona",
195 "Query Response Time Distribution INFORMATION_SCHEMA Plugin",
196 PLUGIN_LICENSE_GPL,
197 query_response_time_info_init,
198 query_response_time_info_deinit,
199 0x0100,
200 NULL,
201 NULL,
202 (void *)"1.0",
203 0,
204},
205{
175 MYSQL_AUDIT_PLUGIN,206 MYSQL_AUDIT_PLUGIN,
176 &query_response_time_audit_descriptor,207 &query_response_time_audit_descriptor,
177 "QUERY_RESPONSE_TIME_AUDIT",208 "QUERY_RESPONSE_TIME_AUDIT",
178209
=== modified file 'plugin/query_response_time/query_response_time.cc'
--- plugin/query_response_time/query_response_time.cc 2014-09-25 06:33:00 +0000
+++ plugin/query_response_time/query_response_time.cc 2014-11-06 00:11:26 +0000
@@ -170,17 +170,17 @@
170 {170 {
171 my_atomic_rwlock_destroy(&time_collector_lock);171 my_atomic_rwlock_destroy(&time_collector_lock);
172 }172 }
173 uint32 count(uint index)173 uint32 count(QUERY_TYPE type, uint index)
174 {174 {
175 my_atomic_rwlock_rdlock(&time_collector_lock);175 my_atomic_rwlock_rdlock(&time_collector_lock);
176 uint32 result= my_atomic_load32((int32*)&m_count[index]);176 uint32 result= my_atomic_load32((int32*)&m_count[type][index]);
177 my_atomic_rwlock_rdunlock(&time_collector_lock);177 my_atomic_rwlock_rdunlock(&time_collector_lock);
178 return result;178 return result;
179 }179 }
180 uint64 total(uint index)180 uint64 total(QUERY_TYPE type, uint index)
181 {181 {
182 my_atomic_rwlock_rdlock(&time_collector_lock);182 my_atomic_rwlock_rdlock(&time_collector_lock);
183 uint64 result= my_atomic_load64((int64*)&m_total[index]);183 uint64 result= my_atomic_load64((int64*)&m_total[type][index]);
184 my_atomic_rwlock_rdunlock(&time_collector_lock);184 my_atomic_rwlock_rdunlock(&time_collector_lock);
185 return result;185 return result;
186 }186 }
@@ -192,7 +192,7 @@
192 memset((void*)&m_total,0,sizeof(m_total));192 memset((void*)&m_total,0,sizeof(m_total));
193 my_atomic_rwlock_wrunlock(&time_collector_lock);193 my_atomic_rwlock_wrunlock(&time_collector_lock);
194 }194 }
195 void collect(uint64 time)195 void collect(QUERY_TYPE type, uint64 time)
196 {196 {
197 int i= 0;197 int i= 0;
198 for(int count= m_utility->bound_count(); count > i; ++i)198 for(int count= m_utility->bound_count(); count > i; ++i)
@@ -200,8 +200,10 @@
200 if(m_utility->bound(i) > time)200 if(m_utility->bound(i) > time)
201 {201 {
202 my_atomic_rwlock_wrlock(&time_collector_lock);202 my_atomic_rwlock_wrlock(&time_collector_lock);
203 my_atomic_add32((int32*)(&m_count[i]), 1);203 my_atomic_add32((int32*)(&m_count[0][i]), 1);
204 my_atomic_add64((int64*)(&m_total[i]), time);204 my_atomic_add64((int64*)(&m_total[0][i]), time);
205 my_atomic_add32((int32*)(&m_count[type][i]), 1);
206 my_atomic_add64((int64*)(&m_total[type][i]), time);
205 my_atomic_rwlock_wrunlock(&time_collector_lock);207 my_atomic_rwlock_wrunlock(&time_collector_lock);
206 break;208 break;
207 }209 }
@@ -209,12 +211,18 @@
209 }211 }
210private:212private:
211 utility* m_utility;213 utility* m_utility;
212 /* The lock for atomic operations on m_count and m_total. Only actually214 /* The lock for atomic operations on
213 used on architectures that do not have atomic implementation of atomic215 m_count, m_total, m_r_count, m_r_total, m_w_count, m_w_total.
214 operations. */216 Only actually used on architectures that do not have atomic
217 implementation of atomic operations. */
215 my_atomic_rwlock_t time_collector_lock;218 my_atomic_rwlock_t time_collector_lock;
216 uint32 m_count[OVERALL_POWER_COUNT + 1];219 /*
217 uint64 m_total[OVERALL_POWER_COUNT + 1];220 The fisrt row is for overall statistics,
221 the second row is for 'read' queries,
222 the third row is for 'write' queries.
223 */
224 uint32 m_count[3][OVERALL_POWER_COUNT + 1];
225 uint64 m_total[3][OVERALL_POWER_COUNT + 1];
218};226};
219227
220class collector228class collector
@@ -231,7 +239,9 @@
231 m_utility.setup(opt_query_response_time_range_base);239 m_utility.setup(opt_query_response_time_range_base);
232 m_time.flush();240 m_time.flush();
233 }241 }
234 int fill(THD* thd, TABLE_LIST *tables, COND *cond)242 int fill(QUERY_TYPE type,
243 THD* thd,
244 TABLE_LIST *tables, COND *cond)
235 {245 {
236 DBUG_ENTER("fill_schema_query_response_time");246 DBUG_ENTER("fill_schema_query_response_time");
237 TABLE *table= static_cast<TABLE*>(tables->table);247 TABLE *table= static_cast<TABLE*>(tables->table);
@@ -250,10 +260,10 @@
250 else260 else
251 {261 {
252 print_time(time, sizeof(time), TIME_STRING_FORMAT, this->bound(i));262 print_time(time, sizeof(time), TIME_STRING_FORMAT, this->bound(i));
253 print_time(total, sizeof(total), TOTAL_STRING_FORMAT, this->total(i));263 print_time(total, sizeof(total), TOTAL_STRING_FORMAT, this->total(type, i));
254 }264 }
255 fields[0]->store(time,strlen(time),system_charset_info);265 fields[0]->store(time,strlen(time),system_charset_info);
256 fields[1]->store(this->count(i));266 fields[1]->store(this->count(type, i));
257 fields[2]->store(total,strlen(total),system_charset_info);267 fields[2]->store(total,strlen(total),system_charset_info);
258 if (schema_table_store_record(thd, table))268 if (schema_table_store_record(thd, table))
259 {269 {
@@ -262,9 +272,9 @@
262 }272 }
263 DBUG_RETURN(0);273 DBUG_RETURN(0);
264 }274 }
265 void collect(ulonglong time)275 void collect(QUERY_TYPE type, ulonglong time)
266 {276 {
267 m_time.collect(time);277 m_time.collect(type, time);
268 }278 }
269 uint bound_count() const279 uint bound_count() const
270 {280 {
@@ -274,13 +284,13 @@
274 {284 {
275 return m_utility.bound(index);285 return m_utility.bound(index);
276 }286 }
277 ulonglong count(uint index)287 ulonglong count(QUERY_TYPE type, uint index)
278 {288 {
279 return m_time.count(index);289 return m_time.count(type, index);
280 }290 }
281 ulonglong total(uint index)291 ulonglong total(QUERY_TYPE type, uint index)
282 {292 {
283 return m_time.total(index);293 return m_time.total(type, index);
284 }294 }
285private:295private:
286 utility m_utility;296 utility m_utility;
@@ -304,12 +314,23 @@
304{314{
305 query_response_time::g_collector.flush();315 query_response_time::g_collector.flush();
306}316}
307void query_response_time_collect(ulonglong query_time)317
318void query_response_time_collect(QUERY_TYPE type,
319 ulonglong query_time)
308{320{
309 query_response_time::g_collector.collect(query_time);321 query_response_time::g_collector.collect(type, query_time);
310}322}
311323
312int query_response_time_fill(THD* thd, TABLE_LIST *tables, COND *cond)324int query_response_time_fill(THD* thd, TABLE_LIST *tables, COND *cond)
313{325{
314 return query_response_time::g_collector.fill(thd,tables,cond);326 QUERY_TYPE query_type = ANY;
327 if (!strncmp(tables->table->alias,
328 "QUERY_RESPONSE_TIME_READ",
329 sizeof("QUERY_RESPONSE_TIME_READ") - 1))
330 query_type = READ;
331 else if (!strncmp(tables->table->alias,
332 "QUERY_RESPONSE_TIME_WRITE",
333 sizeof("QUERY_RESPONSE_TIME_WRITE") - 1))
334 query_type = WRITE;
335 return query_response_time::g_collector.fill(query_type, thd, tables, cond);
315}336}
316337
=== modified file 'plugin/query_response_time/query_response_time.h'
--- plugin/query_response_time/query_response_time.h 2014-09-25 06:33:00 +0000
+++ plugin/query_response_time/query_response_time.h 2014-11-06 00:11:26 +0000
@@ -66,6 +66,13 @@
66 MY_MAX( (QRT_TOTAL_STRING_POSITIVE_POWER_LENGTH + 1 /* '.' */ + 6 /*QRT_TOTAL_STRING_NEGATIVE_POWER_LENGTH*/), \66 MY_MAX( (QRT_TOTAL_STRING_POSITIVE_POWER_LENGTH + 1 /* '.' */ + 6 /*QRT_TOTAL_STRING_NEGATIVE_POWER_LENGTH*/), \
67 (sizeof(QRT_TIME_OVERFLOW) - 1) )67 (sizeof(QRT_TIME_OVERFLOW) - 1) )
6868
69enum QUERY_TYPE
70{
71 ANY= 0,
72 READ= 1,
73 WRITE= 2
74};
75
69extern ST_SCHEMA_TABLE query_response_time_table;76extern ST_SCHEMA_TABLE query_response_time_table;
7077
71typedef class Item COND;78typedef class Item COND;
@@ -73,7 +80,7 @@
73extern void query_response_time_init ();80extern void query_response_time_init ();
74extern void query_response_time_free ();81extern void query_response_time_free ();
75extern void query_response_time_flush ();82extern void query_response_time_flush ();
76extern void query_response_time_collect(ulonglong query_time);83extern void query_response_time_collect(QUERY_TYPE type, ulonglong query_time);
77extern int query_response_time_fill (THD* thd, TABLE_LIST *tables, COND *cond);84extern int query_response_time_fill (THD* thd, TABLE_LIST *tables, COND *cond);
7885
79extern ulong opt_query_response_time_range_base;86extern ulong opt_query_response_time_range_base;
8087
=== modified file 'plugin/query_response_time/tests/mtr/basic.result'
--- plugin/query_response_time/tests/mtr/basic.result 2014-09-25 06:33:00 +0000
+++ plugin/query_response_time/tests/mtr/basic.result 2014-11-06 00:11:26 +0000
@@ -23,6 +23,18 @@
23PLUGIN_AUTHOR Percona and Sergey Vojtovich23PLUGIN_AUTHOR Percona and Sergey Vojtovich
24PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin24PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin
25PLUGIN_LICENSE GPL25PLUGIN_LICENSE GPL
26PLUGIN_NAME QUERY_RESPONSE_TIME_READ
27PLUGIN_VERSION 1.0
28PLUGIN_TYPE INFORMATION SCHEMA
29PLUGIN_AUTHOR Percona
30PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin
31PLUGIN_LICENSE GPL
32PLUGIN_NAME QUERY_RESPONSE_TIME_WRITE
33PLUGIN_VERSION 1.0
34PLUGIN_TYPE INFORMATION SCHEMA
35PLUGIN_AUTHOR Percona
36PLUGIN_DESCRIPTION Query Response Time Distribution INFORMATION_SCHEMA Plugin
37PLUGIN_LICENSE GPL
26SET GLOBAL QUERY_RESPONSE_TIME_STATS=1;38SET GLOBAL QUERY_RESPONSE_TIME_STATS=1;
27SELECT 1;39SELECT 1;
281401
2941
=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc'
--- plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc 1970-01-01 00:00:00 +0000
+++ plugin/query_response_time/tests/mtr/query_response_time-rw-begin.inc 2014-11-06 00:11:26 +0000
@@ -0,0 +1,16 @@
1--disable_query_log
2--disable_result_log
3SET GLOBAL QUERY_RESPONSE_TIME_STATS = 0;
4SET GLOBAL query_response_time_flush = 1;
5SET SESSION query_response_time_exec_time_debug = 500000;
6--enable_query_log
7--enable_result_log
8SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
9SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
10SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
11--disable_query_log
12--disable_result_log
13SET GLOBAL QUERY_RESPONSE_TIME_STATS = 1;
14--enable_query_log
15--enable_result_log
16--echo ------------------Test body begin--------------------
017
=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc'
--- plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc 1970-01-01 00:00:00 +0000
+++ plugin/query_response_time/tests/mtr/query_response_time-rw-end.inc 2014-11-06 00:11:26 +0000
@@ -0,0 +1,10 @@
1--echo ------------------Test body end----------------------
2--disable_query_log
3--disable_result_log
4SET GLOBAL QUERY_RESPONSE_TIME_STATS = 0;
5SET SESSION query_response_time_exec_time_debug = default;
6--enable_query_log
7--enable_result_log
8SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
9SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
10SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
011
=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw.result'
--- plugin/query_response_time/tests/mtr/query_response_time-rw.result 1970-01-01 00:00:00 +0000
+++ plugin/query_response_time/tests/mtr/query_response_time-rw.result 2014-11-06 00:11:26 +0000
@@ -0,0 +1,761 @@
1CREATE FUNCTION DML_FUNC()
2RETURNS INT
3BEGIN
4INSERT INTO t1 (b) VALUES (1), (2);
5UPDATE t1 SET b = 1;
6RETURN 1;
7END|
8CREATE PROCEDURE NESTED_PROC()
9BEGIN
10SELECT DML_FUNC();
11END|
12SET default_storage_engine=InnoDB;
13
14============================
15= Test for simple DML and DDL
16============================
17
18SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
19TIME COUNT TOTAL
20SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
21TIME COUNT TOTAL
22SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
23TIME COUNT TOTAL
24------------------Test body begin--------------------
25CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
26b INT UNSIGNED NOT NULL DEFAULT 0);
27INSERT INTO t1 (b) VALUES (1), (2);
28UPDATE t1 SET b = 1;
29SELECT * FROM t1;
30a b
311 1
322 1
33DELETE FROM t1;
34ALTER TABLE t1 ADD COLUMN (c INT);
35DROP TABLE t1;
36------------------Test body end----------------------
37SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
38TIME COUNT TOTAL
39 0.000001 1 0.000000
40 1.000000 7 3.500000
41SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
42TIME COUNT TOTAL
43 0.000001 1 0.000000
44 1.000000 1 0.500000
45SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
46TIME COUNT TOTAL
47 1.000000 6 3.000000
48CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
49b INT(10) UNSIGNED NOT NULL DEFAULT 0);
50
51============================
52= Test for SELECT+DML
53============================
54
55SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
56TIME COUNT TOTAL
57SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
58TIME COUNT TOTAL
59SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
60TIME COUNT TOTAL
61------------------Test body begin--------------------
62SELECT DML_FUNC();
63DML_FUNC()
641
65------------------Test body end----------------------
66SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
67TIME COUNT TOTAL
68 0.000001 1 0.000000
69 1.000000 3 1.500000
70SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
71TIME COUNT TOTAL
72 0.000001 1 0.000000
73SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
74TIME COUNT TOTAL
75 1.000000 3 1.500000
76
77============================
78= Test for nested calls
79============================
80
81SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
82TIME COUNT TOTAL
83SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
84TIME COUNT TOTAL
85SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
86TIME COUNT TOTAL
87------------------Test body begin--------------------
88CALL NESTED_PROC();
89DML_FUNC()
901
91------------------Test body end----------------------
92SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
93TIME COUNT TOTAL
94 0.000001 1 0.000000
95 1.000000 4 2.000000
96SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
97TIME COUNT TOTAL
98 0.000001 1 0.000000
99SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
100TIME COUNT TOTAL
101 1.000000 4 2.000000
102
103============================
104= Test for 'PREPARE'
105============================
106
107SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
108TIME COUNT TOTAL
109SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
110TIME COUNT TOTAL
111SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
112TIME COUNT TOTAL
113------------------Test body begin--------------------
114PREPARE rw_stmt FROM 'DELETE FROM t1';
115PREPARE ro_stmt FROM 'SELECT * FROM t1';
116PREPARE select_dml_stmt FROM 'SELECT DML_FUNC()';
117PREPARE nested_call_stmt FROM 'CALL NESTED_PROC()';
118------------------Test body end----------------------
119SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
120TIME COUNT TOTAL
121 0.000001 1 0.000000
122 1.000000 4 2.000000
123SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
124TIME COUNT TOTAL
125 0.000001 1 0.000000
126 1.000000 4 2.000000
127SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
128TIME COUNT TOTAL
129
130============================
131= Test for 'EXECUTE'
132============================
133
134SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
135TIME COUNT TOTAL
136SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
137TIME COUNT TOTAL
138SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
139TIME COUNT TOTAL
140------------------Test body begin--------------------
141EXECUTE rw_stmt;
142EXECUTE ro_stmt;
143a b
144EXECUTE select_dml_stmt;
145DML_FUNC()
1461
147EXECUTE nested_call_stmt;
148DML_FUNC()
1491
150------------------Test body end----------------------
151SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
152TIME COUNT TOTAL
153 0.000001 1 0.000000
154 1.000000 9 4.500000
155SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
156TIME COUNT TOTAL
157 0.000001 1 0.000000
158 1.000000 1 0.500000
159SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
160TIME COUNT TOTAL
161 1.000000 8 4.000000
162
163============================
164= Test for 'DEALLOCATE PREPARE'
165============================
166
167SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
168TIME COUNT TOTAL
169SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
170TIME COUNT TOTAL
171SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
172TIME COUNT TOTAL
173------------------Test body begin--------------------
174DEALLOCATE PREPARE rw_stmt;
175DEALLOCATE PREPARE ro_stmt;
176DEALLOCATE PREPARE select_dml_stmt;
177DEALLOCATE PREPARE nested_call_stmt;
178------------------Test body end----------------------
179SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
180TIME COUNT TOTAL
181 0.000001 1 0.000000
182 1.000000 4 2.000000
183SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
184TIME COUNT TOTAL
185 0.000001 1 0.000000
186 1.000000 4 2.000000
187SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
188TIME COUNT TOTAL
189
190============================
191= Test for transactions
192============================
193
194SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
195TIME COUNT TOTAL
196SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
197TIME COUNT TOTAL
198SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
199TIME COUNT TOTAL
200------------------Test body begin--------------------
201BEGIN;
202SELECT * FROM t1;
203a b
2045 1
2056 1
2067 1
2078 1
208COMMIT;
209------------------Test body end----------------------
210SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
211TIME COUNT TOTAL
212 0.000001 1 0.000000
213 1.000000 3 1.500000
214SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
215TIME COUNT TOTAL
216 0.000001 1 0.000000
217 1.000000 3 1.500000
218SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
219TIME COUNT TOTAL
220SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
221TIME COUNT TOTAL
222SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
223TIME COUNT TOTAL
224SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
225TIME COUNT TOTAL
226------------------Test body begin--------------------
227BEGIN;
228INSERT INTO t1 (b) VALUES (1), (2);
229UPDATE t1 SET b = 1;
230SELECT * FROM t1;
231a b
2325 1
2336 1
2347 1
2358 1
2369 1
23710 1
238DELETE FROM t1;
239COMMIT;
240------------------Test body end----------------------
241SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
242TIME COUNT TOTAL
243 0.000001 1 0.000000
244 1.000000 6 3.000000
245SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
246TIME COUNT TOTAL
247 0.000001 1 0.000000
248 1.000000 3 1.500000
249SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
250TIME COUNT TOTAL
251 1.000000 3 1.500000
252SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
253TIME COUNT TOTAL
254SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
255TIME COUNT TOTAL
256SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
257TIME COUNT TOTAL
258------------------Test body begin--------------------
259BEGIN;
260SELECT DML_FUNC();
261DML_FUNC()
2621
263CALL NESTED_PROC();
264DML_FUNC()
2651
266COMMIT;
267------------------Test body end----------------------
268SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
269TIME COUNT TOTAL
270 0.000001 1 0.000000
271 1.000000 9 4.500000
272SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
273TIME COUNT TOTAL
274 0.000001 1 0.000000
275 1.000000 2 1.000000
276SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
277TIME COUNT TOTAL
278 1.000000 7 3.500000
279SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
280TIME COUNT TOTAL
281SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
282TIME COUNT TOTAL
283SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
284TIME COUNT TOTAL
285------------------Test body begin--------------------
286BEGIN;
287INSERT INTO t1 (b) VALUES (1), (2);
288UPDATE t1 SET b = 1;
289ROLLBACK;
290------------------Test body end----------------------
291SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
292TIME COUNT TOTAL
293 0.000001 1 0.000000
294 1.000000 4 2.000000
295SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
296TIME COUNT TOTAL
297 0.000001 1 0.000000
298 1.000000 2 1.000000
299SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
300TIME COUNT TOTAL
301 1.000000 2 1.000000
302
303============================
304= Test for implicit commit
305============================
306
307SET autocommit = 0;
308SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
309TIME COUNT TOTAL
310SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
311TIME COUNT TOTAL
312SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
313TIME COUNT TOTAL
314------------------Test body begin--------------------
315INSERT INTO t1 (b) VALUES (1), (2);
316CREATE INDEX ab ON t1 (a, b);
317------------------Test body end----------------------
318SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
319TIME COUNT TOTAL
320 0.000001 1 0.000000
321 1.000000 2 1.000000
322SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
323TIME COUNT TOTAL
324 0.000001 1 0.000000
325SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
326TIME COUNT TOTAL
327 1.000000 2 1.000000
328SET autocommit = 1;
329
330============================
331= Test for trigger
332============================
333
334CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
335b INT UNSIGNED NOT NULL DEFAULT 0);
336SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
337TIME COUNT TOTAL
338SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
339TIME COUNT TOTAL
340SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
341TIME COUNT TOTAL
342------------------Test body begin--------------------
343CREATE TRIGGER test_trigger BEFORE INSERT ON t1
344FOR EACH ROW
345BEGIN
346INSERT INTO t2 SET b = NEW.b;
347INSERT INTO t2 SET b = NEW.b;
348END;
349|
350------------------Test body end----------------------
351SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
352TIME COUNT TOTAL
353 0.000001 1 0.000000
354 1.000000 1 0.500000
355SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
356TIME COUNT TOTAL
357 0.000001 1 0.000000
358SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
359TIME COUNT TOTAL
360 1.000000 1 0.500000
361SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
362TIME COUNT TOTAL
363SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
364TIME COUNT TOTAL
365SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
366TIME COUNT TOTAL
367------------------Test body begin--------------------
368INSERT INTO t1 (b) VALUES (1), (2);
369------------------Test body end----------------------
370SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
371TIME COUNT TOTAL
372 0.000001 1 0.000000
373 1.000000 5 2.500000
374SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
375TIME COUNT TOTAL
376 0.000001 1 0.000000
377SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
378TIME COUNT TOTAL
379 1.000000 5 2.500000
380DROP TABLE t1;
381DROP TABLE t2;
382SET default_storage_engine=InnoDB;
383SET default_storage_engine=MyISAM;
384
385==========================
386= Test for Innodb engine
387==========================
388
389============================
390= Test for simple DML and DDL
391============================
392
393SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
394TIME COUNT TOTAL
395SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
396TIME COUNT TOTAL
397SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
398TIME COUNT TOTAL
399------------------Test body begin--------------------
400CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
401b INT UNSIGNED NOT NULL DEFAULT 0);
402INSERT INTO t1 (b) VALUES (1), (2);
403UPDATE t1 SET b = 1;
404SELECT * FROM t1;
405a b
4061 1
4072 1
408DELETE FROM t1;
409ALTER TABLE t1 ADD COLUMN (c INT);
410DROP TABLE t1;
411------------------Test body end----------------------
412SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
413TIME COUNT TOTAL
414 0.000001 1 0.000000
415 1.000000 7 3.500000
416SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
417TIME COUNT TOTAL
418 0.000001 1 0.000000
419 1.000000 1 0.500000
420SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
421TIME COUNT TOTAL
422 1.000000 6 3.000000
423CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
424b INT(10) UNSIGNED NOT NULL DEFAULT 0);
425
426============================
427= Test for SELECT+DML
428============================
429
430SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
431TIME COUNT TOTAL
432SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
433TIME COUNT TOTAL
434SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
435TIME COUNT TOTAL
436------------------Test body begin--------------------
437SELECT DML_FUNC();
438DML_FUNC()
4391
440------------------Test body end----------------------
441SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
442TIME COUNT TOTAL
443 0.000001 1 0.000000
444 1.000000 3 1.500000
445SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
446TIME COUNT TOTAL
447 0.000001 1 0.000000
448SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
449TIME COUNT TOTAL
450 1.000000 3 1.500000
451
452============================
453= Test for nested calls
454============================
455
456SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
457TIME COUNT TOTAL
458SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
459TIME COUNT TOTAL
460SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
461TIME COUNT TOTAL
462------------------Test body begin--------------------
463CALL NESTED_PROC();
464DML_FUNC()
4651
466------------------Test body end----------------------
467SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
468TIME COUNT TOTAL
469 0.000001 1 0.000000
470 1.000000 4 2.000000
471SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
472TIME COUNT TOTAL
473 0.000001 1 0.000000
474SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
475TIME COUNT TOTAL
476 1.000000 4 2.000000
477
478============================
479= Test for 'PREPARE'
480============================
481
482SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
483TIME COUNT TOTAL
484SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
485TIME COUNT TOTAL
486SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
487TIME COUNT TOTAL
488------------------Test body begin--------------------
489PREPARE rw_stmt FROM 'DELETE FROM t1';
490PREPARE ro_stmt FROM 'SELECT * FROM t1';
491PREPARE select_dml_stmt FROM 'SELECT DML_FUNC()';
492PREPARE nested_call_stmt FROM 'CALL NESTED_PROC()';
493------------------Test body end----------------------
494SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
495TIME COUNT TOTAL
496 0.000001 1 0.000000
497 1.000000 4 2.000000
498SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
499TIME COUNT TOTAL
500 0.000001 1 0.000000
501 1.000000 4 2.000000
502SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
503TIME COUNT TOTAL
504
505============================
506= Test for 'EXECUTE'
507============================
508
509SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
510TIME COUNT TOTAL
511SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
512TIME COUNT TOTAL
513SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
514TIME COUNT TOTAL
515------------------Test body begin--------------------
516EXECUTE rw_stmt;
517EXECUTE ro_stmt;
518a b
519EXECUTE select_dml_stmt;
520DML_FUNC()
5211
522EXECUTE nested_call_stmt;
523DML_FUNC()
5241
525------------------Test body end----------------------
526SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
527TIME COUNT TOTAL
528 0.000001 1 0.000000
529 1.000000 9 4.500000
530SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
531TIME COUNT TOTAL
532 0.000001 1 0.000000
533 1.000000 1 0.500000
534SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
535TIME COUNT TOTAL
536 1.000000 8 4.000000
537
538============================
539= Test for 'DEALLOCATE PREPARE'
540============================
541
542SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
543TIME COUNT TOTAL
544SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
545TIME COUNT TOTAL
546SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
547TIME COUNT TOTAL
548------------------Test body begin--------------------
549DEALLOCATE PREPARE rw_stmt;
550DEALLOCATE PREPARE ro_stmt;
551DEALLOCATE PREPARE select_dml_stmt;
552DEALLOCATE PREPARE nested_call_stmt;
553------------------Test body end----------------------
554SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
555TIME COUNT TOTAL
556 0.000001 1 0.000000
557 1.000000 4 2.000000
558SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
559TIME COUNT TOTAL
560 0.000001 1 0.000000
561 1.000000 4 2.000000
562SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
563TIME COUNT TOTAL
564
565============================
566= Test for transactions
567============================
568
569SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
570TIME COUNT TOTAL
571SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
572TIME COUNT TOTAL
573SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
574TIME COUNT TOTAL
575------------------Test body begin--------------------
576BEGIN;
577SELECT * FROM t1;
578a b
5795 1
5806 1
5817 1
5828 1
583COMMIT;
584------------------Test body end----------------------
585SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
586TIME COUNT TOTAL
587 0.000001 1 0.000000
588 1.000000 3 1.500000
589SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
590TIME COUNT TOTAL
591 0.000001 1 0.000000
592 1.000000 3 1.500000
593SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
594TIME COUNT TOTAL
595SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
596TIME COUNT TOTAL
597SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
598TIME COUNT TOTAL
599SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
600TIME COUNT TOTAL
601------------------Test body begin--------------------
602BEGIN;
603INSERT INTO t1 (b) VALUES (1), (2);
604UPDATE t1 SET b = 1;
605SELECT * FROM t1;
606a b
6075 1
6086 1
6097 1
6108 1
6119 1
61210 1
613DELETE FROM t1;
614COMMIT;
615------------------Test body end----------------------
616SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
617TIME COUNT TOTAL
618 0.000001 1 0.000000
619 1.000000 6 3.000000
620SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
621TIME COUNT TOTAL
622 0.000001 1 0.000000
623 1.000000 3 1.500000
624SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
625TIME COUNT TOTAL
626 1.000000 3 1.500000
627SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
628TIME COUNT TOTAL
629SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
630TIME COUNT TOTAL
631SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
632TIME COUNT TOTAL
633------------------Test body begin--------------------
634BEGIN;
635SELECT DML_FUNC();
636DML_FUNC()
6371
638CALL NESTED_PROC();
639DML_FUNC()
6401
641COMMIT;
642------------------Test body end----------------------
643SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
644TIME COUNT TOTAL
645 0.000001 1 0.000000
646 1.000000 9 4.500000
647SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
648TIME COUNT TOTAL
649 0.000001 1 0.000000
650 1.000000 2 1.000000
651SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
652TIME COUNT TOTAL
653 1.000000 7 3.500000
654SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
655TIME COUNT TOTAL
656SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
657TIME COUNT TOTAL
658SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
659TIME COUNT TOTAL
660------------------Test body begin--------------------
661BEGIN;
662INSERT INTO t1 (b) VALUES (1), (2);
663UPDATE t1 SET b = 1;
664ROLLBACK;
665Warnings:
666Warning 1196 Some non-transactional changed tables couldn't be rolled back
667------------------Test body end----------------------
668SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
669TIME COUNT TOTAL
670 0.000001 1 0.000000
671 1.000000 5 2.500000
672SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
673TIME COUNT TOTAL
674 0.000001 1 0.000000
675 1.000000 3 1.500000
676SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
677TIME COUNT TOTAL
678 1.000000 2 1.000000
679
680============================
681= Test for implicit commit
682============================
683
684SET autocommit = 0;
685SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
686TIME COUNT TOTAL
687SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
688TIME COUNT TOTAL
689SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
690TIME COUNT TOTAL
691------------------Test body begin--------------------
692INSERT INTO t1 (b) VALUES (1), (2);
693CREATE INDEX ab ON t1 (a, b);
694------------------Test body end----------------------
695SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
696TIME COUNT TOTAL
697 0.000001 1 0.000000
698 1.000000 2 1.000000
699SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
700TIME COUNT TOTAL
701 0.000001 1 0.000000
702SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
703TIME COUNT TOTAL
704 1.000000 2 1.000000
705SET autocommit = 1;
706
707============================
708= Test for trigger
709============================
710
711CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
712b INT UNSIGNED NOT NULL DEFAULT 0);
713SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
714TIME COUNT TOTAL
715SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
716TIME COUNT TOTAL
717SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
718TIME COUNT TOTAL
719------------------Test body begin--------------------
720CREATE TRIGGER test_trigger BEFORE INSERT ON t1
721FOR EACH ROW
722BEGIN
723INSERT INTO t2 SET b = NEW.b;
724INSERT INTO t2 SET b = NEW.b;
725END;
726|
727------------------Test body end----------------------
728SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
729TIME COUNT TOTAL
730 0.000001 1 0.000000
731 1.000000 1 0.500000
732SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
733TIME COUNT TOTAL
734 0.000001 1 0.000000
735SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
736TIME COUNT TOTAL
737 1.000000 1 0.500000
738SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
739TIME COUNT TOTAL
740SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
741TIME COUNT TOTAL
742SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
743TIME COUNT TOTAL
744------------------Test body begin--------------------
745INSERT INTO t1 (b) VALUES (1), (2);
746------------------Test body end----------------------
747SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME WHERE count != 0;
748TIME COUNT TOTAL
749 0.000001 1 0.000000
750 1.000000 5 2.500000
751SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ WHERE count != 0;
752TIME COUNT TOTAL
753 0.000001 1 0.000000
754SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE WHERE count != 0;
755TIME COUNT TOTAL
756 1.000000 5 2.500000
757DROP TABLE t1;
758DROP TABLE t2;
759SET default_storage_engine=default;
760DROP FUNCTION DML_FUNC;
761DROP PROCEDURE NESTED_PROC;
0762
=== added file 'plugin/query_response_time/tests/mtr/query_response_time-rw.test'
--- plugin/query_response_time/tests/mtr/query_response_time-rw.test 1970-01-01 00:00:00 +0000
+++ plugin/query_response_time/tests/mtr/query_response_time-rw.test 2014-11-06 00:11:26 +0000
@@ -0,0 +1,201 @@
1--source include/have_query_response_time_plugin.inc
2--source include/have_debug.inc
3
4# The file with expected results fits only to a run without
5# ps-protocol/sp-protocol/cursor-protocol/view-protocol.
6if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
7 + $VIEW_PROTOCOL > 0`)
8{
9 --skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled
10}
11
12--delimiter |
13
14CREATE FUNCTION DML_FUNC()
15RETURNS INT
16BEGIN
17 INSERT INTO t1 (b) VALUES (1), (2);
18 UPDATE t1 SET b = 1;
19 RETURN 1;
20END|
21
22CREATE PROCEDURE NESTED_PROC()
23BEGIN
24 SELECT DML_FUNC();
25END|
26
27--delimiter ;
28
29--let $i=2
30
31while ($i)
32{
33
34 SET default_storage_engine=InnoDB;
35 if ($i==1)
36 {
37 SET default_storage_engine=MyISAM;
38 }
39
40 if ($i==0)
41 {
42 --echo
43 --echo ==========================
44 --echo = Test for MyISAM engine
45 --echo ==========================
46 --echo
47 }
48 if ($i==1)
49 {
50 --echo
51 --echo ==========================
52 --echo = Test for Innodb engine
53 --echo ==========================
54 }
55
56 --echo
57 --echo ============================
58 --echo = Test for simple DML and DDL
59 --echo ============================
60 --echo
61 --source query_response_time-rw-begin.inc
62 CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
63 b INT UNSIGNED NOT NULL DEFAULT 0);
64 INSERT INTO t1 (b) VALUES (1), (2);
65 UPDATE t1 SET b = 1;
66 SELECT * FROM t1;
67 DELETE FROM t1;
68 ALTER TABLE t1 ADD COLUMN (c INT);
69 DROP TABLE t1;
70 --source query_response_time-rw-end.inc
71
72 CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
73 b INT(10) UNSIGNED NOT NULL DEFAULT 0);
74 --echo
75 --echo ============================
76 --echo = Test for SELECT+DML
77 --echo ============================
78 --echo
79 --source query_response_time-rw-begin.inc
80 SELECT DML_FUNC();
81 --source query_response_time-rw-end.inc
82
83 --echo
84 --echo ============================
85 --echo = Test for nested calls
86 --echo ============================
87 --echo
88 --source query_response_time-rw-begin.inc
89 CALL NESTED_PROC();
90 --source query_response_time-rw-end.inc
91
92 --echo
93 --echo ============================
94 --echo = Test for 'PREPARE'
95 --echo ============================
96 --echo
97 --source query_response_time-rw-begin.inc
98 PREPARE rw_stmt FROM 'DELETE FROM t1';
99 PREPARE ro_stmt FROM 'SELECT * FROM t1';
100 PREPARE select_dml_stmt FROM 'SELECT DML_FUNC()';
101 PREPARE nested_call_stmt FROM 'CALL NESTED_PROC()';
102 --source query_response_time-rw-end.inc
103
104 --echo
105 --echo ============================
106 --echo = Test for 'EXECUTE'
107 --echo ============================
108 --echo
109 --source query_response_time-rw-begin.inc
110 EXECUTE rw_stmt;
111 EXECUTE ro_stmt;
112 EXECUTE select_dml_stmt;
113 EXECUTE nested_call_stmt;
114 --source query_response_time-rw-end.inc
115
116 --echo
117 --echo ============================
118 --echo = Test for 'DEALLOCATE PREPARE'
119 --echo ============================
120 --echo
121 --source query_response_time-rw-begin.inc
122 DEALLOCATE PREPARE rw_stmt;
123 DEALLOCATE PREPARE ro_stmt;
124 DEALLOCATE PREPARE select_dml_stmt;
125 DEALLOCATE PREPARE nested_call_stmt;
126 --source query_response_time-rw-end.inc
127
128 --echo
129 --echo ============================
130 --echo = Test for transactions
131 --echo ============================
132 --echo
133 --source query_response_time-rw-begin.inc
134 BEGIN;
135 SELECT * FROM t1;
136 COMMIT;
137 --source query_response_time-rw-end.inc
138 --source query_response_time-rw-begin.inc
139 BEGIN;
140 INSERT INTO t1 (b) VALUES (1), (2);
141 UPDATE t1 SET b = 1;
142 SELECT * FROM t1;
143 DELETE FROM t1;
144 COMMIT;
145 --source query_response_time-rw-end.inc
146 --source query_response_time-rw-begin.inc
147 BEGIN;
148 SELECT DML_FUNC();
149 CALL NESTED_PROC();
150 COMMIT;
151 --source query_response_time-rw-end.inc
152 --source query_response_time-rw-begin.inc
153 BEGIN;
154 INSERT INTO t1 (b) VALUES (1), (2);
155 UPDATE t1 SET b = 1;
156 ROLLBACK;
157 --source query_response_time-rw-end.inc
158
159 --echo
160 --echo ============================
161 --echo = Test for implicit commit
162 --echo ============================
163 --echo
164 SET autocommit = 0;
165 --source query_response_time-rw-begin.inc
166 INSERT INTO t1 (b) VALUES (1), (2);
167 CREATE INDEX ab ON t1 (a, b);
168 --source query_response_time-rw-end.inc
169 SET autocommit = 1;
170
171 --echo
172 --echo ============================
173 --echo = Test for trigger
174 --echo ============================
175 --echo
176 CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
177 b INT UNSIGNED NOT NULL DEFAULT 0);
178 --source query_response_time-rw-begin.inc
179 --delimiter |
180 CREATE TRIGGER test_trigger BEFORE INSERT ON t1
181 FOR EACH ROW
182 BEGIN
183 INSERT INTO t2 SET b = NEW.b;
184 INSERT INTO t2 SET b = NEW.b;
185 END;
186 |
187 --delimiter ;
188 --source query_response_time-rw-end.inc
189 --source query_response_time-rw-begin.inc
190 INSERT INTO t1 (b) VALUES (1), (2);
191 --source query_response_time-rw-end.inc
192
193 DROP TABLE t1;
194 DROP TABLE t2;
195
196 --dec $i
197}
198
199SET default_storage_engine=default;
200DROP FUNCTION DML_FUNC;
201DROP PROCEDURE NESTED_PROC;
0202
=== modified file 'sql/mdl.cc'
--- sql/mdl.cc 2014-05-12 14:47:30 +0000
+++ sql/mdl.cc 2014-11-06 00:11:26 +0000
@@ -2098,6 +2098,9 @@
2098 is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE));2098 is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE));
2099 DBUG_ASSERT(mdl_request->ticket == NULL);2099 DBUG_ASSERT(mdl_request->ticket == NULL);
21002100
2101 if (mdl_request->type >= MDL_SHARED_WRITE)
2102 get_owner()->is_write= true;
2103
2101 /* Don't take chances in production. */2104 /* Don't take chances in production. */
2102 mdl_request->ticket= NULL;2105 mdl_request->ticket= NULL;
2103 mysql_mutex_assert_not_owner(&LOCK_open);2106 mysql_mutex_assert_not_owner(&LOCK_open);
21042107
=== modified file 'sql/mdl.h'
--- sql/mdl.h 2014-05-12 14:47:30 +0000
+++ sql/mdl.h 2014-11-06 00:11:26 +0000
@@ -63,6 +63,7 @@
63class MDL_context_owner63class MDL_context_owner
64{64{
65public:65public:
66 MDL_context_owner() : is_write(false) {}
66 virtual ~MDL_context_owner() {}67 virtual ~MDL_context_owner() {}
6768
68 /**69 /**
@@ -121,6 +122,7 @@
121 */122 */
122 virtual bool notify_shared_lock(MDL_context_owner *in_use,123 virtual bool notify_shared_lock(MDL_context_owner *in_use,
123 bool needs_thr_lock_abort) = 0;124 bool needs_thr_lock_abort) = 0;
125 bool is_write;
124};126};
125127
126/**128/**
127129
=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc 2014-10-17 14:36:56 +0000
+++ sql/sql_parse.cc 2014-11-06 00:11:26 +0000
@@ -1867,6 +1867,8 @@
1867 thd->get_stmt_da()->sql_errno() : 0,1867 thd->get_stmt_da()->sql_errno() : 0,
1868 command_name[command].str);1868 command_name[command].str);
18691869
1870 thd->is_write = false;
1871
1870 log_slow_statement(thd);1872 log_slow_statement(thd);
18711873
1872 THD_STAGE_INFO(thd, stage_cleaning_up);1874 THD_STAGE_INFO(thd, stage_cleaning_up);

Subscribers

People subscribed via source and target branches