Percona Server moved to https://jira.percona.com/projects/PS

Merge lp:~longbow/percona-server/ps51_kill_idle_trx into lp:percona-server/5.1

  1. ps51_kill_idle_trx
  2. Merge into 5.1
Proposed by Valentine Gostev
Status: Rejected
Rejected by: Stewart Smith
Proposed branch: lp:~longbow/percona-server/ps51_kill_idle_trx
Merge into: lp:percona-server/5.1
Diff against target: 314 lines (+185/-30)
2 files modified
patches/innodb_kill_idle_transaction.patch (+184/-30)
patches/series (+1/-0)
To merge this branch: bzr merge lp:~longbow/percona-server/ps51_kill_idle_trx
Reviewer Review Type Date Requested Status
Stewart Smith (community) Needs Resubmitting
Oleg Tsarev (community) Approve
Review via email: mp+72222@code.launchpad.net

This proposal supersedes a proposal from 2011-08-19.

Description of the change

Added innodb_kill_idle_transaction patch and test.

To post a comment you must log in.
Revision history for this message
Stewart Smith (stewart) wrote : Posted in a previous version of this proposal

(as mentioned on IRC)

1) decrease sleep
2) create a single patch including tests?
3) on reconnect, do the select again to enuser content of table is correct
4) another test that took row locks (SELECT FOR UPDATE) may be useful to doubly ensure they're released properly

review: Needs Fixing
Revision history for this message
Oleg Tsarev (tsarev) wrote : Posted in a previous version of this proposal

1) Unnecessary error directive:

198 ++--error 2006 --error CR_SERVER_GONE_ERROR

Enough just a
198 ++--error CR_SERVER_GONE_ERROR

2) SHOW GLOBAL VARIABLES instead of SHOW VARIABLES
248 ++SHOW VARIABLES LIKE 'innodb_kill_idle_transaction';
249 ++SET GLOBAL innodb_kill_idle_transaction=5;

3) Use also SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES ORDER BY Variable_name (not only SHOW GLOBAL VARIABLES)

review: Needs Fixing
Revision history for this message
Oleg Tsarev (tsarev) wrote : Posted in a previous version of this proposal

Please merge two commits to single

review: Needs Fixing
Revision history for this message
Oleg Tsarev (tsarev) wrote : Posted in a previous version of this proposal

> Please merge two commits to single

Single commit more clear and simpler for search problems/analyse changes, than two.

Thank you.

Revision history for this message
Oleg Tsarev (tsarev) wrote : Posted in a previous version of this proposal

Where is Jenkins's build URL?

review: Needs Information
Revision history for this message
Oleg Tsarev (tsarev) wrote :

LGTM

review: Approve
Revision history for this message
Stewart Smith (stewart) wrote :

https://code.launchpad.net/~longbow/percona-server/ps55_kill_idle_trx/+merge/72361

needs same fix as ^ (i.e. there are bugs)

review: Needs Resubmitting
Revision history for this message
Stewart Smith (stewart) wrote :

This patch has been merged in other commits.

Unmerged revisions

279. By longbow <longbow@mil-nb>

Added tests for innodb_kill_idle_transaction.patch and added patch to series

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== renamed file 'innodb_kill_idle_transaction.patch' => 'patches/innodb_kill_idle_transaction.patch'
--- innodb_kill_idle_transaction.patch 2011-07-07 13:02:19 +0000
+++ patches/innodb_kill_idle_transaction.patch 2011-08-19 16:57:59 +0000
@@ -5,9 +5,8 @@
5#!!! notice !!!5#!!! notice !!!
6# Any small change to this file in the main branch6# Any small change to this file in the main branch
7# should be done or reviewed by the maintainer!7# should be done or reviewed by the maintainer!
8diff -ruN a/include/mysql/plugin.h b/include/mysql/plugin.h8--- a/include/mysql/plugin.h
9--- a/include/mysql/plugin.h 2011-07-07 19:38:24.650374103 +09009+++ b/include/mysql/plugin.h
10+++ b/include/mysql/plugin.h 2011-07-07 19:39:44.611713196 +0900
11@@ -845,6 +845,12 @@10@@ -845,6 +845,12 @@
12 */11 */
13 void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,12 void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
@@ -21,9 +20,8 @@
21 #ifdef __cplusplus20 #ifdef __cplusplus
22 }21 }
23 #endif22 #endif
24diff -ruN a/include/mysql/plugin.h.pp b/include/mysql/plugin.h.pp23--- a/include/mysql/plugin.h.pp
25--- a/include/mysql/plugin.h.pp 2011-07-07 19:38:24.423414787 +090024+++ b/include/mysql/plugin.h.pp
26+++ b/include/mysql/plugin.h.pp 2011-07-07 19:39:44.615372555 +0900
27@@ -150,3 +150,6 @@25@@ -150,3 +150,6 @@
28 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);26 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
29 void thd_set_ha_data(void* thd, const struct handlerton *hton,27 void thd_set_ha_data(void* thd, const struct handlerton *hton,
@@ -31,9 +29,8 @@
31+int thd_command(const void* thd);29+int thd_command(const void* thd);
32+long long thd_start_time(const void* thd);30+long long thd_start_time(const void* thd);
33+void thd_kill(void* thd);31+void thd_kill(void* thd);
34diff -ruN a/sql/sql_class.cc b/sql/sql_class.cc32--- a/sql/sql_class.cc
35--- a/sql/sql_class.cc 2011-07-07 19:38:24.999372326 +090033+++ b/sql/sql_class.cc
36+++ b/sql/sql_class.cc 2011-07-07 19:39:44.619370533 +0900
37@@ -468,6 +468,26 @@34@@ -468,6 +468,26 @@
38 return buffer;35 return buffer;
39 }36 }
@@ -61,10 +58,9 @@
61 58
62 /**59 /**
63 Implementation of Drop_table_error_handler::handle_error().60 Implementation of Drop_table_error_handler::handle_error().
64diff -ruN a/storage/innodb_plugin/handler/ha_innodb.cc b/storage/innodb_plugin/handler/ha_innodb.cc61--- a/storage/innodb_plugin/handler/ha_innodb.cc
65--- a/storage/innodb_plugin/handler/ha_innodb.cc 2011-07-07 19:38:26.162373407 +090062+++ b/storage/innodb_plugin/handler/ha_innodb.cc
66+++ b/storage/innodb_plugin/handler/ha_innodb.cc 2011-07-07 19:39:44.625370650 +090063@@ -2521,6 +2521,10 @@
67@@ -2511,6 +2511,10 @@
68 64
69 innobase_commit_concurrency_init_default();65 innobase_commit_concurrency_init_default();
70 66
@@ -75,7 +71,7 @@
75 /* Since we in this module access directly the fields of a trx71 /* Since we in this module access directly the fields of a trx
76 struct, and due to different headers and flags it might happen that72 struct, and due to different headers and flags it might happen that
77 mutex_t has a different size in this module and in InnoDB73 mutex_t has a different size in this module and in InnoDB
78@@ -11237,6 +11241,48 @@74@@ -11247,6 +11251,48 @@
79 return(false);75 return(false);
80 }76 }
81 77
@@ -124,7 +120,7 @@
124 static SHOW_VAR innodb_status_variables_export[]= {120 static SHOW_VAR innodb_status_variables_export[]= {
125 {"Innodb", (char*) &show_innodb_vars, SHOW_FUNC},121 {"Innodb", (char*) &show_innodb_vars, SHOW_FUNC},
126 {NullS, NullS, SHOW_LONG}122 {NullS, NullS, SHOW_LONG}
127@@ -11480,6 +11526,15 @@123@@ -11500,6 +11546,15 @@
128 "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",124 "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",
129 NULL, NULL, 500L, 1L, ~0L, 0);125 NULL, NULL, 500L, 1L, ~0L, 0);
130 126
@@ -140,7 +136,7 @@
140 static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,136 static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,
141 PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,137 PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,
142 "Number of file I/O threads in InnoDB.",138 "Number of file I/O threads in InnoDB.",
143@@ -11762,6 +11817,7 @@139@@ -11784,6 +11839,7 @@
144 MYSQL_SYSVAR(fast_checksum),140 MYSQL_SYSVAR(fast_checksum),
145 MYSQL_SYSVAR(commit_concurrency),141 MYSQL_SYSVAR(commit_concurrency),
146 MYSQL_SYSVAR(concurrency_tickets),142 MYSQL_SYSVAR(concurrency_tickets),
@@ -148,10 +144,9 @@
148 MYSQL_SYSVAR(data_file_path),144 MYSQL_SYSVAR(data_file_path),
149 MYSQL_SYSVAR(doublewrite_file),145 MYSQL_SYSVAR(doublewrite_file),
150 MYSQL_SYSVAR(data_home_dir),146 MYSQL_SYSVAR(data_home_dir),
151diff -ruN a/storage/innodb_plugin/include/srv0srv.h b/storage/innodb_plugin/include/srv0srv.h147--- a/storage/innodb_plugin/include/srv0srv.h
152--- a/storage/innodb_plugin/include/srv0srv.h 2011-07-07 19:38:26.128372970 +0900148+++ b/storage/innodb_plugin/include/srv0srv.h
153+++ b/storage/innodb_plugin/include/srv0srv.h 2011-07-07 19:39:44.646824971 +0900149@@ -284,6 +284,7 @@
154@@ -281,6 +281,7 @@
155 extern ulint srv_activity_count;150 extern ulint srv_activity_count;
156 extern ulint srv_fatal_semaphore_wait_threshold;151 extern ulint srv_fatal_semaphore_wait_threshold;
157 extern ulint srv_dml_needed_delay;152 extern ulint srv_dml_needed_delay;
@@ -159,9 +154,8 @@
159 154
160 extern mutex_t* kernel_mutex_temp;/* mutex protecting the server, trx structs,155 extern mutex_t* kernel_mutex_temp;/* mutex protecting the server, trx structs,
161 query threads, and lock table: we allocate156 query threads, and lock table: we allocate
162diff -ruN a/storage/innodb_plugin/include/trx0trx.h b/storage/innodb_plugin/include/trx0trx.h157--- a/storage/innodb_plugin/include/trx0trx.h
163--- a/storage/innodb_plugin/include/trx0trx.h 2011-07-07 19:38:25.705377859 +0900158+++ b/storage/innodb_plugin/include/trx0trx.h
164+++ b/storage/innodb_plugin/include/trx0trx.h 2011-07-07 19:39:44.650304314 +0900
165@@ -600,6 +600,8 @@159@@ -600,6 +600,8 @@
166 ulint mysql_process_no;/* since in Linux, 'top' reports160 ulint mysql_process_no;/* since in Linux, 'top' reports
167 process id's and not thread id's, we161 process id's and not thread id's, we
@@ -171,9 +165,8 @@
171 /*------------------------------*/165 /*------------------------------*/
172 ulint n_mysql_tables_in_use; /* number of Innobase tables166 ulint n_mysql_tables_in_use; /* number of Innobase tables
173 used in the processing of the current167 used in the processing of the current
174diff -ruN a/storage/innodb_plugin/srv/srv0srv.c b/storage/innodb_plugin/srv/srv0srv.c168--- a/storage/innodb_plugin/srv/srv0srv.c
175--- a/storage/innodb_plugin/srv/srv0srv.c 2011-07-07 19:38:26.147372081 +0900169+++ b/storage/innodb_plugin/srv/srv0srv.c
176+++ b/storage/innodb_plugin/srv/srv0srv.c 2011-07-07 19:39:44.658303810 +0900
177@@ -86,6 +86,11 @@170@@ -86,6 +86,11 @@
178 #include "trx0i_s.h"171 #include "trx0i_s.h"
179 #include "os0sync.h" /* for HAVE_ATOMIC_BUILTINS */172 #include "os0sync.h" /* for HAVE_ATOMIC_BUILTINS */
@@ -196,7 +189,7 @@
196 /* How much data manipulation language (DML) statements need to be delayed,189 /* How much data manipulation language (DML) statements need to be delayed,
197 in microseconds, in order to reduce the lagging of the purge thread. */190 in microseconds, in order to reduce the lagging of the purge thread. */
198 UNIV_INTERN ulint srv_dml_needed_delay = 0;191 UNIV_INTERN ulint srv_dml_needed_delay = 0;
199@@ -2549,6 +2557,36 @@192@@ -2554,6 +2562,36 @@
200 old_sema = sema;193 old_sema = sema;
201 }194 }
202 195
@@ -233,9 +226,8 @@
233 /* Flush stderr so that a database user gets the output226 /* Flush stderr so that a database user gets the output
234 to possible MySQL error file */227 to possible MySQL error file */
235 228
236diff -ruN a/storage/innodb_plugin/trx/trx0trx.c b/storage/innodb_plugin/trx/trx0trx.c229--- a/storage/innodb_plugin/trx/trx0trx.c
237--- a/storage/innodb_plugin/trx/trx0trx.c 2011-07-07 19:38:25.723372433 +0900230+++ b/storage/innodb_plugin/trx/trx0trx.c
238+++ b/storage/innodb_plugin/trx/trx0trx.c 2011-07-07 19:39:44.665304098 +0900
239@@ -137,6 +137,9 @@231@@ -137,6 +137,9 @@
240 trx->mysql_relay_log_file_name = "";232 trx->mysql_relay_log_file_name = "";
241 trx->mysql_relay_log_pos = 0;233 trx->mysql_relay_log_pos = 0;
@@ -246,3 +238,165 @@
246 mutex_create(&trx->undo_mutex, SYNC_TRX_UNDO);238 mutex_create(&trx->undo_mutex, SYNC_TRX_UNDO);
247 239
248 trx->rseg = NULL;240 trx->rseg = NULL;
241--- /dev/null
242+++ b/mysql-test/r/percona_innodb_kill_idle_trx.result
243@@ -0,0 +1,41 @@
244+DROP TABLE IF EXISTS t1;
245+SET autocommit=0;
246+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
247+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
248+Variable_name Value
249+innodb_kill_idle_transaction 0
250+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
251+VARIABLE_NAME VARIABLE_VALUE
252+INNODB_KILL_IDLE_TRANSACTION 0
253+SET GLOBAL innodb_kill_idle_transaction=1;
254+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
255+Variable_name Value
256+innodb_kill_idle_transaction 1
257+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
258+VARIABLE_NAME VARIABLE_VALUE
259+INNODB_KILL_IDLE_TRANSACTION 1
260+BEGIN;
261+INSERT INTO t1 VALUES (1),(2),(3);
262+COMMIT;
263+SELECT * FROM t1;
264+a
265+1
266+2
267+3
268+BEGIN;
269+INSERT INTO t1 VALUES (4),(5),(6);
270+SELECT * FROM t1;
271+ERROR HY000: MySQL server has gone away
272+SELECT * FROM t1;
273+a
274+1
275+2
276+3
277+DROP TABLE t1;
278+SET GLOBAL innodb_kill_idle_transaction=0;
279+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
280+Variable_name Value
281+innodb_kill_idle_transaction 0
282+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
283+VARIABLE_NAME VARIABLE_VALUE
284+INNODB_KILL_IDLE_TRANSACTION 0
285--- /dev/null
286+++ b/mysql-test/t/percona_innodb_kill_idle_trx.test
287@@ -0,0 +1,28 @@
288+--source include/have_innodb.inc
289+--disable_warnings
290+DROP TABLE IF EXISTS t1;
291+--enable_warnings
292+
293+SET autocommit=0;
294+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
295+
296+--source include/percona_innodb_kill_idle_trx_show.inc
297+SET GLOBAL innodb_kill_idle_transaction=1;
298+--source include/percona_innodb_kill_idle_trx_show.inc
299+
300+BEGIN;
301+INSERT INTO t1 VALUES (1),(2),(3);
302+COMMIT;
303+SELECT * FROM t1;
304+
305+BEGIN;
306+INSERT INTO t1 VALUES (4),(5),(6);
307+sleep 3;
308+
309+--enable_reconnect
310+--error 2006 --error CR_SERVER_GONE_ERROR
311+SELECT * FROM t1;
312+SELECT * FROM t1;
313+DROP TABLE t1;
314+SET GLOBAL innodb_kill_idle_transaction=0;
315+--source include/percona_innodb_kill_idle_trx_show.inc
316--- /dev/null
317+++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result
318@@ -0,0 +1,45 @@
319+DROP TABLE IF EXISTS t1;
320+SET autocommit=0;
321+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
322+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
323+Variable_name Value
324+innodb_kill_idle_transaction 0
325+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
326+VARIABLE_NAME VARIABLE_VALUE
327+INNODB_KILL_IDLE_TRANSACTION 0
328+SET GLOBAL innodb_kill_idle_transaction=5;
329+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
330+Variable_name Value
331+innodb_kill_idle_transaction 5
332+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
333+VARIABLE_NAME VARIABLE_VALUE
334+INNODB_KILL_IDLE_TRANSACTION 5
335+BEGIN;
336+INSERT INTO t1 VALUES (1),(2),(3);
337+COMMIT;
338+SELECT * FROM t1;
339+a
340+1
341+2
342+3
343+### Locking rows. Lock should be released when idle trx is killed.
344+BEGIN;
345+SELECT * FROM t1 FOR UPDATE;
346+a
347+1
348+2
349+3
350+UPDATE t1 set a=4;
351+SELECT * FROM t1;
352+a
353+4
354+4
355+4
356+DROP TABLE t1;
357+SET GLOBAL innodb_kill_idle_transaction=0;
358+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
359+Variable_name Value
360+innodb_kill_idle_transaction 0
361+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
362+VARIABLE_NAME VARIABLE_VALUE
363+INNODB_KILL_IDLE_TRANSACTION 0
364--- /dev/null
365+++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test
366@@ -0,0 +1,31 @@
367+--source include/have_innodb.inc
368+--disable_warnings
369+DROP TABLE IF EXISTS t1;
370+--enable_warnings
371+
372+SET autocommit=0;
373+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
374+
375+--source include/percona_innodb_kill_idle_trx_show.inc
376+SET GLOBAL innodb_kill_idle_transaction=5;
377+--source include/percona_innodb_kill_idle_trx_show.inc
378+
379+connect (conn1,localhost,root,,);
380+connection conn1;
381+
382+BEGIN;
383+INSERT INTO t1 VALUES (1),(2),(3);
384+COMMIT;
385+SELECT * FROM t1;
386+
387+--echo ### Locking rows. Lock should be released when idle trx is killed.
388+BEGIN;
389+SELECT * FROM t1 FOR UPDATE;
390+
391+connection default;
392+UPDATE t1 set a=4;
393+
394+SELECT * FROM t1;
395+DROP TABLE t1;
396+SET GLOBAL innodb_kill_idle_transaction=0;
397+--source include/percona_innodb_kill_idle_trx_show.inc
398--- /dev/null
399+++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc
400@@ -0,0 +1,2 @@
401+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
402+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
249403
=== modified file 'patches/series'
--- patches/series 2011-08-01 21:45:17 +0000
+++ patches/series 2011-08-19 16:57:59 +0000
@@ -60,3 +60,4 @@
60bug813587.patch60bug813587.patch
61innodb_bug47167_test_fix.patch61innodb_bug47167_test_fix.patch
62disable_query_cache_28249_test_sporadic_failure.patch62disable_query_cache_28249_test_sporadic_failure.patch
63innodb_kill_idle_transaction.patch

Subscribers

People subscribed via source and target branches