1 # name : innodb_kill_idle_transaction.patch
3 # maintainer : Yasufumi
6 # Any small change to this file in the main branch
7 # should be done or reviewed by the maintainer!
8 --- a/include/mysql/plugin.h
9 +++ b/include/mysql/plugin.h
12 void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
15 +int thd_command(const MYSQL_THD thd);
16 +long long thd_start_time(const MYSQL_THD thd);
17 +void thd_kill(unsigned long id);
18 +#define EXTENDED_FOR_KILLIDLE
23 --- a/include/mysql/plugin_audit.h.pp
24 +++ b/include/mysql/plugin_audit.h.pp
26 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
27 void thd_set_ha_data(void* thd, const struct handlerton *hton,
29 +int thd_command(const void* thd);
30 +long long thd_start_time(const void* thd);
31 +void thd_kill(unsigned long id);
32 struct mysql_event_general
34 unsigned int event_subclass;
35 --- a/include/mysql/plugin_auth.h.pp
36 +++ b/include/mysql/plugin_auth.h.pp
38 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
39 void thd_set_ha_data(void* thd, const struct handlerton *hton,
41 +int thd_command(const void* thd);
42 +long long thd_start_time(const void* thd);
43 +void thd_kill(unsigned long id);
44 #include <mysql/plugin_auth_common.h>
45 typedef struct st_plugin_vio_info
47 --- a/include/mysql/plugin_ftparser.h.pp
48 +++ b/include/mysql/plugin_ftparser.h.pp
50 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
51 void thd_set_ha_data(void* thd, const struct handlerton *hton,
53 +int thd_command(const void* thd);
54 +long long thd_start_time(const void* thd);
55 +void thd_kill(unsigned long id);
56 enum enum_ftparser_mode
58 MYSQL_FTPARSER_SIMPLE_MODE= 0,
59 --- a/sql/sql_class.cc
60 +++ b/sql/sql_class.cc
65 +/* extend for kill session of idle transaction from engine */
67 +int thd_command(const THD* thd)
69 + return (int) thd->command;
73 +long long thd_start_time(const THD* thd)
75 + return (long long) thd->start_time;
79 +void thd_kill(ulong id)
82 + mysql_mutex_lock(&LOCK_thread_count);
83 + I_List_iterator<THD> it(threads);
86 + if (tmp->command == COM_DAEMON)
88 + if (tmp->thread_id == id)
90 + mysql_mutex_lock(&tmp->LOCK_thd_data);
94 + mysql_mutex_unlock(&LOCK_thread_count);
97 + tmp->awake(THD::KILL_CONNECTION);
98 + mysql_mutex_unlock(&tmp->LOCK_thd_data);
103 Implementation of Drop_table_error_handler::handle_condition().
104 --- a/storage/innobase/handler/ha_innodb.cc
105 +++ b/storage/innobase/handler/ha_innodb.cc
106 @@ -2860,6 +2860,10 @@
108 innobase_commit_concurrency_init_default();
110 +#ifndef EXTENDED_FOR_KILLIDLE
111 + srv_kill_idle_transaction = 0;
114 #ifdef HAVE_PSI_INTERFACE
115 /* Register keys with MySQL performance schema */
117 @@ -11694,6 +11698,57 @@
121 +/***********************************************************************
122 +functions for kill session of idle transaction */
125 +innobase_thd_is_idle(
126 +/*=================*/
127 + const void* thd) /*!< in: thread handle (THD*) */
129 +#ifdef EXTENDED_FOR_KILLIDLE
130 + return(thd_command((const THD*) thd) == COM_SLEEP);
138 +innobase_thd_get_start_time(
139 +/*========================*/
140 + const void* thd) /*!< in: thread handle (THD*) */
142 +#ifdef EXTENDED_FOR_KILLIDLE
143 + return((ib_int64_t)thd_start_time((const THD*) thd));
145 + return(0); /*dummy value*/
155 +#ifdef EXTENDED_FOR_KILLIDLE
164 +innobase_thd_get_thread_id(
165 +/*=======================*/
168 + return(thd_get_thread_id((const THD*) thd));
172 static SHOW_VAR innodb_status_variables_export[]= {
173 {"Innodb", (char*) &show_innodb_vars, SHOW_FUNC},
174 {NullS, NullS, SHOW_LONG}
175 @@ -11985,6 +12040,15 @@
176 "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",
177 NULL, NULL, 500L, 1L, ~0L, 0);
179 +static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction,
180 + PLUGIN_VAR_RQCMDARG,
181 +#ifdef EXTENDED_FOR_KILLIDLE
182 + "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB.",
184 + "No effect for this build.",
186 + NULL, NULL, 0, 0, LONG_MAX, 0);
188 static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,
189 PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,
190 "Number of file I/O threads in InnoDB.",
191 @@ -12327,6 +12391,7 @@
192 MYSQL_SYSVAR(fast_checksum),
193 MYSQL_SYSVAR(commit_concurrency),
194 MYSQL_SYSVAR(concurrency_tickets),
195 + MYSQL_SYSVAR(kill_idle_transaction),
196 MYSQL_SYSVAR(data_file_path),
197 MYSQL_SYSVAR(doublewrite_file),
198 MYSQL_SYSVAR(data_home_dir),
199 --- a/storage/innobase/include/srv0srv.h
200 +++ b/storage/innobase/include/srv0srv.h
202 extern ulint srv_activity_count;
203 extern ulint srv_fatal_semaphore_wait_threshold;
204 extern ulint srv_dml_needed_delay;
205 +extern lint srv_kill_idle_transaction;
207 extern mutex_t* kernel_mutex_temp;/* mutex protecting the server, trx structs,
208 query threads, and lock table: we allocate
209 --- a/storage/innobase/include/trx0trx.h
210 +++ b/storage/innobase/include/trx0trx.h
212 replication has processed */
213 const char* mysql_relay_log_file_name;
214 ib_int64_t mysql_relay_log_pos;
216 + ib_int64_t last_stmt_start;
217 /*------------------------------*/
218 ulint n_mysql_tables_in_use; /* number of Innobase tables
219 used in the processing of the current
220 --- a/storage/innobase/srv/srv0srv.c
221 +++ b/storage/innobase/srv/srv0srv.c
223 #include "mysql/plugin.h"
224 #include "mysql/service_thd_wait.h"
226 +/* prototypes of new functions added to ha_innodb.cc for kill_idle_transaction */
227 +ibool innobase_thd_is_idle(const void* thd);
228 +ib_int64_t innobase_thd_get_start_time(const void* thd);
229 +void innobase_thd_kill(ulong thd_id);
230 +ulong innobase_thd_get_thread_id(const void* thd);
232 /* prototypes for new functions added to ha_innodb.cc */
233 ibool innobase_get_slow_log();
236 /* The following is the maximum allowed duration of a lock wait. */
237 UNIV_INTERN ulint srv_fatal_semaphore_wait_threshold = 600;
240 +UNIV_INTERN lint srv_kill_idle_transaction = 0;
242 /* How much data manipulation language (DML) statements need to be delayed,
243 in microseconds, in order to reduce the lagging of the purge thread. */
244 UNIV_INTERN ulint srv_dml_needed_delay = 0;
245 @@ -2837,6 +2846,36 @@
249 + if (srv_kill_idle_transaction && trx_sys) {
254 + mutex_enter(&kernel_mutex);
255 + trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
257 + if (trx->conc_state == TRX_ACTIVE
259 + && innobase_thd_is_idle(trx->mysql_thd)) {
260 + ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd);
261 + ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd);
263 + if (trx->last_stmt_start != start_time) {
264 + trx->idle_start = now;
265 + trx->last_stmt_start = start_time;
266 + } else if (difftime(now, trx->idle_start)
267 + > srv_kill_idle_transaction) {
268 + /* kill the session */
269 + mutex_exit(&kernel_mutex);
270 + innobase_thd_kill(thd_id);
274 + trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);
276 + mutex_exit(&kernel_mutex);
279 /* Flush stderr so that a database user gets the output
280 to possible MySQL error file */
282 --- a/storage/innobase/trx/trx0trx.c
283 +++ b/storage/innobase/trx/trx0trx.c
285 trx->mysql_relay_log_file_name = "";
286 trx->mysql_relay_log_pos = 0;
288 + trx->idle_start = 0;
289 + trx->last_stmt_start = 0;
291 mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO);
295 +++ b/mysql-test/r/percona_innodb_kill_idle_trx.result
297 +DROP TABLE IF EXISTS t1;
299 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
300 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
302 +innodb_kill_idle_transaction 0
303 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
304 +VARIABLE_NAME VARIABLE_VALUE
305 +INNODB_KILL_IDLE_TRANSACTION 0
306 +SET GLOBAL innodb_kill_idle_transaction=1;
307 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
309 +innodb_kill_idle_transaction 1
310 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
311 +VARIABLE_NAME VARIABLE_VALUE
312 +INNODB_KILL_IDLE_TRANSACTION 1
314 +INSERT INTO t1 VALUES (1),(2),(3);
322 +INSERT INTO t1 VALUES (4),(5),(6);
324 +ERROR HY000: MySQL server has gone away
331 +SET GLOBAL innodb_kill_idle_transaction=0;
332 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
334 +innodb_kill_idle_transaction 0
335 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
336 +VARIABLE_NAME VARIABLE_VALUE
337 +INNODB_KILL_IDLE_TRANSACTION 0
339 +++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result
341 +DROP TABLE IF EXISTS t1;
343 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
344 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
346 +innodb_kill_idle_transaction 0
347 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
348 +VARIABLE_NAME VARIABLE_VALUE
349 +INNODB_KILL_IDLE_TRANSACTION 0
350 +SET GLOBAL innodb_kill_idle_transaction=5;
351 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
353 +innodb_kill_idle_transaction 5
354 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
355 +VARIABLE_NAME VARIABLE_VALUE
356 +INNODB_KILL_IDLE_TRANSACTION 5
358 +INSERT INTO t1 VALUES (1),(2),(3);
365 +### Locking rows. Lock should be released when idle trx is killed.
367 +SELECT * FROM t1 FOR UPDATE;
379 +SET GLOBAL innodb_kill_idle_transaction=0;
380 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
382 +innodb_kill_idle_transaction 0
383 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
384 +VARIABLE_NAME VARIABLE_VALUE
385 +INNODB_KILL_IDLE_TRANSACTION 0
387 +++ b/mysql-test/t/percona_innodb_kill_idle_trx.test
389 +--source include/have_innodb.inc
391 +DROP TABLE IF EXISTS t1;
395 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
397 +--source include/percona_innodb_kill_idle_trx_show.inc
398 +SET GLOBAL innodb_kill_idle_transaction=1;
399 +--source include/percona_innodb_kill_idle_trx_show.inc
402 +INSERT INTO t1 VALUES (1),(2),(3);
407 +INSERT INTO t1 VALUES (4),(5),(6);
411 +--error 2006 --error CR_SERVER_GONE_ERROR
415 +SET GLOBAL innodb_kill_idle_transaction=0;
416 +--source include/percona_innodb_kill_idle_trx_show.inc
418 +++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test
420 +--source include/have_innodb.inc
422 +DROP TABLE IF EXISTS t1;
426 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
428 +--source include/percona_innodb_kill_idle_trx_show.inc
429 +SET GLOBAL innodb_kill_idle_transaction=5;
430 +--source include/percona_innodb_kill_idle_trx_show.inc
432 +connect (conn1,localhost,root,,);
436 +INSERT INTO t1 VALUES (1),(2),(3);
440 +--echo ### Locking rows. Lock should be released when idle trx is killed.
442 +SELECT * FROM t1 FOR UPDATE;
449 +SET GLOBAL innodb_kill_idle_transaction=0;
450 +--source include/percona_innodb_kill_idle_trx_show.inc
452 +++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc
454 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
455 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';