# name : innodb_kill_idle_transaction.patch # introduced : 5.5.15 # maintainer : Yasufumi # #!!! notice !!! # Any small change to this file in the main branch # should be done or reviewed by the maintainer! --- a/include/mysql/plugin.h +++ b/include/mysql/plugin.h @@ -634,6 +634,12 @@ */ void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton, const void *ha_data); + +int thd_command(const MYSQL_THD thd); +long long thd_start_time(const MYSQL_THD thd); +void thd_kill(unsigned long id); +#define EXTENDED_FOR_KILLIDLE + #ifdef __cplusplus } #endif --- a/include/mysql/plugin_audit.h.pp +++ b/include/mysql/plugin_audit.h.pp @@ -196,6 +196,9 @@ void *thd_get_ha_data(const void* thd, const struct handlerton *hton); void thd_set_ha_data(void* thd, const struct handlerton *hton, const void *ha_data); +int thd_command(const void* thd); +long long thd_start_time(const void* thd); +void thd_kill(unsigned long id); struct mysql_event_general { unsigned int event_subclass; --- a/include/mysql/plugin_auth.h.pp +++ b/include/mysql/plugin_auth.h.pp @@ -196,6 +196,9 @@ void *thd_get_ha_data(const void* thd, const struct handlerton *hton); void thd_set_ha_data(void* thd, const struct handlerton *hton, const void *ha_data); +int thd_command(const void* thd); +long long thd_start_time(const void* thd); +void thd_kill(unsigned long id); #include typedef struct st_plugin_vio_info { --- a/include/mysql/plugin_ftparser.h.pp +++ b/include/mysql/plugin_ftparser.h.pp @@ -149,6 +149,9 @@ void *thd_get_ha_data(const void* thd, const struct handlerton *hton); void thd_set_ha_data(void* thd, const struct handlerton *hton, const void *ha_data); +int thd_command(const void* thd); +long long thd_start_time(const void* thd); +void thd_kill(unsigned long id); enum enum_ftparser_mode { MYSQL_FTPARSER_SIMPLE_MODE= 0, --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -713,6 +713,42 @@ return buffer; } +/* extend for kill session of idle transaction from engine */ +extern "C" +int thd_command(const THD* thd) +{ + return (int) thd->command; +} + +extern "C" +long long thd_start_time(const THD* thd) +{ + return (long long) thd->start_time; +} + +extern "C" +void thd_kill(ulong id) +{ + THD *tmp; + mysql_mutex_lock(&LOCK_thread_count); + I_List_iterator it(threads); + while ((tmp=it++)) + { + if (tmp->command == COM_DAEMON) + continue; + if (tmp->thread_id == id) + { + mysql_mutex_lock(&tmp->LOCK_thd_data); + break; + } + } + mysql_mutex_unlock(&LOCK_thread_count); + if (tmp) + { + tmp->awake(THD::KILL_CONNECTION); + mysql_mutex_unlock(&tmp->LOCK_thd_data); + } +} /** Implementation of Drop_table_error_handler::handle_condition(). --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -2860,6 +2860,10 @@ innobase_commit_concurrency_init_default(); +#ifndef EXTENDED_FOR_KILLIDLE + srv_kill_idle_transaction = 0; +#endif + #ifdef HAVE_PSI_INTERFACE /* Register keys with MySQL performance schema */ if (PSI_server) { @@ -11694,6 +11698,57 @@ return(false); } +/*********************************************************************** +functions for kill session of idle transaction */ +extern "C" +ibool +innobase_thd_is_idle( +/*=================*/ + const void* thd) /*!< in: thread handle (THD*) */ +{ +#ifdef EXTENDED_FOR_KILLIDLE + return(thd_command((const THD*) thd) == COM_SLEEP); +#else + return(FALSE); +#endif +} + +extern "C" +ib_int64_t +innobase_thd_get_start_time( +/*========================*/ + const void* thd) /*!< in: thread handle (THD*) */ +{ +#ifdef EXTENDED_FOR_KILLIDLE + return((ib_int64_t)thd_start_time((const THD*) thd)); +#else + return(0); /*dummy value*/ +#endif +} + +extern "C" +void +innobase_thd_kill( +/*==============*/ + ulong thd_id) +{ +#ifdef EXTENDED_FOR_KILLIDLE + thd_kill(thd_id); +#else + return; +#endif +} + +extern "C" +ulong +innobase_thd_get_thread_id( +/*=======================*/ + const void* thd) +{ + return(thd_get_thread_id((const THD*) thd)); +} + + static SHOW_VAR innodb_status_variables_export[]= { {"Innodb", (char*) &show_innodb_vars, SHOW_FUNC}, {NullS, NullS, SHOW_LONG} @@ -11985,6 +12040,15 @@ "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket", NULL, NULL, 500L, 1L, ~0L, 0); +static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction, + PLUGIN_VAR_RQCMDARG, +#ifdef EXTENDED_FOR_KILLIDLE + "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB.", +#else + "No effect for this build.", +#endif + NULL, NULL, 0, 0, LONG_MAX, 0); + static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads, PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR, "Number of file I/O threads in InnoDB.", @@ -12327,6 +12391,7 @@ MYSQL_SYSVAR(fast_checksum), MYSQL_SYSVAR(commit_concurrency), MYSQL_SYSVAR(concurrency_tickets), + MYSQL_SYSVAR(kill_idle_transaction), MYSQL_SYSVAR(data_file_path), MYSQL_SYSVAR(doublewrite_file), MYSQL_SYSVAR(data_home_dir), --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -299,6 +299,7 @@ extern ulint srv_activity_count; extern ulint srv_fatal_semaphore_wait_threshold; extern ulint srv_dml_needed_delay; +extern lint srv_kill_idle_transaction; extern mutex_t* kernel_mutex_temp;/* mutex protecting the server, trx structs, query threads, and lock table: we allocate --- a/storage/innobase/include/trx0trx.h +++ b/storage/innobase/include/trx0trx.h @@ -594,6 +594,8 @@ replication has processed */ const char* mysql_relay_log_file_name; ib_int64_t mysql_relay_log_pos; + time_t idle_start; + ib_int64_t last_stmt_start; /*------------------------------*/ ulint n_mysql_tables_in_use; /* number of Innobase tables used in the processing of the current --- a/storage/innobase/srv/srv0srv.c +++ b/storage/innobase/srv/srv0srv.c @@ -87,6 +87,12 @@ #include "mysql/plugin.h" #include "mysql/service_thd_wait.h" +/* prototypes of new functions added to ha_innodb.cc for kill_idle_transaction */ +ibool innobase_thd_is_idle(const void* thd); +ib_int64_t innobase_thd_get_start_time(const void* thd); +void innobase_thd_kill(ulong thd_id); +ulong innobase_thd_get_thread_id(const void* thd); + /* prototypes for new functions added to ha_innodb.cc */ ibool innobase_get_slow_log(); @@ -97,6 +103,9 @@ /* The following is the maximum allowed duration of a lock wait. */ UNIV_INTERN ulint srv_fatal_semaphore_wait_threshold = 600; +/**/ +UNIV_INTERN lint srv_kill_idle_transaction = 0; + /* How much data manipulation language (DML) statements need to be delayed, in microseconds, in order to reduce the lagging of the purge thread. */ UNIV_INTERN ulint srv_dml_needed_delay = 0; @@ -2837,6 +2846,36 @@ old_sema = sema; } + if (srv_kill_idle_transaction && trx_sys) { + trx_t* trx; + time_t now; +rescan_idle: + now = time(NULL); + mutex_enter(&kernel_mutex); + trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); + while (trx) { + if (trx->conc_state == TRX_ACTIVE + && trx->mysql_thd + && innobase_thd_is_idle(trx->mysql_thd)) { + ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); + ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); + + if (trx->last_stmt_start != start_time) { + trx->idle_start = now; + trx->last_stmt_start = start_time; + } else if (difftime(now, trx->idle_start) + > srv_kill_idle_transaction) { + /* kill the session */ + mutex_exit(&kernel_mutex); + innobase_thd_kill(thd_id); + goto rescan_idle; + } + } + trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); + } + mutex_exit(&kernel_mutex); + } + /* Flush stderr so that a database user gets the output to possible MySQL error file */ --- a/storage/innobase/trx/trx0trx.c +++ b/storage/innobase/trx/trx0trx.c @@ -143,6 +143,9 @@ trx->mysql_relay_log_file_name = ""; trx->mysql_relay_log_pos = 0; + trx->idle_start = 0; + trx->last_stmt_start = 0; + mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO); trx->rseg = NULL; --- /dev/null +++ b/mysql-test/r/percona_innodb_kill_idle_trx.result @@ -0,0 +1,41 @@ +DROP TABLE IF EXISTS t1; +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +Variable_name Value +innodb_kill_idle_transaction 0 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_KILL_IDLE_TRANSACTION 0 +SET GLOBAL innodb_kill_idle_transaction=1; +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +Variable_name Value +innodb_kill_idle_transaction 1 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_KILL_IDLE_TRANSACTION 1 +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +COMMIT; +SELECT * FROM t1; +a +1 +2 +3 +BEGIN; +INSERT INTO t1 VALUES (4),(5),(6); +SELECT * FROM t1; +ERROR HY000: MySQL server has gone away +SELECT * FROM t1; +a +1 +2 +3 +DROP TABLE t1; +SET GLOBAL innodb_kill_idle_transaction=0; +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +Variable_name Value +innodb_kill_idle_transaction 0 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_KILL_IDLE_TRANSACTION 0 --- /dev/null +++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result @@ -0,0 +1,45 @@ +DROP TABLE IF EXISTS t1; +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +Variable_name Value +innodb_kill_idle_transaction 0 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_KILL_IDLE_TRANSACTION 0 +SET GLOBAL innodb_kill_idle_transaction=5; +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +Variable_name Value +innodb_kill_idle_transaction 5 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_KILL_IDLE_TRANSACTION 5 +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +COMMIT; +SELECT * FROM t1; +a +1 +2 +3 +### Locking rows. Lock should be released when idle trx is killed. +BEGIN; +SELECT * FROM t1 FOR UPDATE; +a +1 +2 +3 +UPDATE t1 set a=4; +SELECT * FROM t1; +a +4 +4 +4 +DROP TABLE t1; +SET GLOBAL innodb_kill_idle_transaction=0; +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +Variable_name Value +innodb_kill_idle_transaction 0 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_KILL_IDLE_TRANSACTION 0 --- /dev/null +++ b/mysql-test/t/percona_innodb_kill_idle_trx.test @@ -0,0 +1,28 @@ +--source include/have_innodb.inc +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; + +--source include/percona_innodb_kill_idle_trx_show.inc +SET GLOBAL innodb_kill_idle_transaction=1; +--source include/percona_innodb_kill_idle_trx_show.inc + +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +COMMIT; +SELECT * FROM t1; + +BEGIN; +INSERT INTO t1 VALUES (4),(5),(6); +sleep 3; + +--enable_reconnect +--error 2006 --error CR_SERVER_GONE_ERROR +SELECT * FROM t1; +SELECT * FROM t1; +DROP TABLE t1; +SET GLOBAL innodb_kill_idle_transaction=0; +--source include/percona_innodb_kill_idle_trx_show.inc --- /dev/null +++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test @@ -0,0 +1,31 @@ +--source include/have_innodb.inc +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; + +--source include/percona_innodb_kill_idle_trx_show.inc +SET GLOBAL innodb_kill_idle_transaction=5; +--source include/percona_innodb_kill_idle_trx_show.inc + +connect (conn1,localhost,root,,); +connection conn1; + +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +COMMIT; +SELECT * FROM t1; + +--echo ### Locking rows. Lock should be released when idle trx is killed. +BEGIN; +SELECT * FROM t1 FOR UPDATE; + +connection default; +UPDATE t1 set a=4; + +SELECT * FROM t1; +DROP TABLE t1; +SET GLOBAL innodb_kill_idle_transaction=0; +--source include/percona_innodb_kill_idle_trx_show.inc --- /dev/null +++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc @@ -0,0 +1,2 @@ +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';