innodb_kill_idle_transaction.patch

   1 # name       : innodb_kill_idle_transaction.patch
   2 # introduced : 5.5.15
   3 # maintainer : Yasufumi
   4 #
   5 #!!! notice !!!
   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
  10 @@ -634,6 +634,12 @@
  11  */
  12  void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
  13                       const void *ha_data);
  14 +
  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
  19 +
  20  #ifdef __cplusplus
  21  }
  22  #endif
  23 --- a/include/mysql/plugin_audit.h.pp
  24 +++ b/include/mysql/plugin_audit.h.pp
  25 @@ -196,6 +196,9 @@
  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,
  28                       const void *ha_data);
  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
  33  {
  34    unsigned int event_subclass;
  35 --- a/include/mysql/plugin_auth.h.pp
  36 +++ b/include/mysql/plugin_auth.h.pp
  37 @@ -196,6 +196,9 @@
  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,
  40                       const void *ha_data);
  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
  46  {
  47 --- a/include/mysql/plugin_ftparser.h.pp
  48 +++ b/include/mysql/plugin_ftparser.h.pp
  49 @@ -149,6 +149,9 @@
  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,
  52                       const void *ha_data);
  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
  57  {
  58    MYSQL_FTPARSER_SIMPLE_MODE= 0,
  59 --- a/sql/sql_class.cc
  60 +++ b/sql/sql_class.cc
  61 @@ -713,6 +713,42 @@
  62    return buffer;
  63  }
  64
  65 +/* extend for kill session of idle transaction from engine */
  66 +extern "C"
  67 +int thd_command(const THD* thd)
  68 +{
  69 +  return (int) thd->command;
  70 +}
  71 +
  72 +extern "C"
  73 +long long thd_start_time(const THD* thd)
  74 +{
  75 +  return (long long) thd->start_time;
  76 +}
  77 +
  78 +extern "C"
  79 +void thd_kill(ulong id)
  80 +{
  81 +  THD *tmp;
  82 +  mysql_mutex_lock(&LOCK_thread_count);
  83 +  I_List_iterator<THD> it(threads);
  84 +  while ((tmp=it++))
  85 +  {
  86 +    if (tmp->command == COM_DAEMON)
  87 +      continue;
  88 +    if (tmp->thread_id == id)
  89 +    {
  90 +      mysql_mutex_lock(&tmp->LOCK_thd_data);
  91 +      break;
  92 +    }
  93 +  }
  94 +  mysql_mutex_unlock(&LOCK_thread_count);
  95 +  if (tmp)
  96 +  {
  97 +    tmp->awake(THD::KILL_CONNECTION);
  98 +    mysql_mutex_unlock(&tmp->LOCK_thd_data);
  99 +  }
 100 +}
 101
 102  /**
 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 @@
 107
 108         innobase_commit_concurrency_init_default();
 109
 110 +#ifndef EXTENDED_FOR_KILLIDLE
 111 +       srv_kill_idle_transaction = 0;
 112 +#endif
 113 +
 114  #ifdef HAVE_PSI_INTERFACE
 115         /* Register keys with MySQL performance schema */
 116         if (PSI_server) {
 117 @@ -11694,6 +11698,57 @@
 118         return(false);
 119  }
 120
 121 +/***********************************************************************
 122 +functions for kill session of idle transaction */
 123 +extern "C"
 124 +ibool
 125 +innobase_thd_is_idle(
 126 +/*=================*/
 127 +       const void*     thd)    /*!< in: thread handle (THD*) */
 128 +{
 129 +#ifdef EXTENDED_FOR_KILLIDLE
 130 +       return(thd_command((const THD*) thd) == COM_SLEEP);
 131 +#else
 132 +       return(FALSE);
 133 +#endif
 134 +}
 135 +
 136 +extern "C"
 137 +ib_int64_t
 138 +innobase_thd_get_start_time(
 139 +/*========================*/
 140 +       const void*     thd)    /*!< in: thread handle (THD*) */
 141 +{
 142 +#ifdef EXTENDED_FOR_KILLIDLE
 143 +       return((ib_int64_t)thd_start_time((const THD*) thd));
 144 +#else
 145 +       return(0); /*dummy value*/
 146 +#endif
 147 +}
 148 +
 149 +extern "C"
 150 +void
 151 +innobase_thd_kill(
 152 +/*==============*/
 153 +       ulong   thd_id)
 154 +{
 155 +#ifdef EXTENDED_FOR_KILLIDLE
 156 +       thd_kill(thd_id);
 157 +#else
 158 +       return;
 159 +#endif
 160 +}
 161 +
 162 +extern "C"
 163 +ulong
 164 +innobase_thd_get_thread_id(
 165 +/*=======================*/
 166 +       const void*     thd)
 167 +{
 168 +       return(thd_get_thread_id((const THD*) thd));
 169 +}
 170 +
 171 +
 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);
 178
 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.",
 183 +#else
 184 +  "No effect for this build.",
 185 +#endif
 186 +  NULL, NULL, 0, 0, LONG_MAX, 0);
 187 +
 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
 201 @@ -299,6 +299,7 @@
 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;
 206
 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
 211 @@ -594,6 +594,8 @@
 212                                         replication has processed */
 213         const char*     mysql_relay_log_file_name;
 214         ib_int64_t      mysql_relay_log_pos;
 215 +       time_t          idle_start;
 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
 222 @@ -87,6 +87,12 @@
 223  #include "mysql/plugin.h"
 224  #include "mysql/service_thd_wait.h"
 225
 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);
 231 +
 232  /* prototypes for new functions added to ha_innodb.cc */
 233  ibool  innobase_get_slow_log();
 234
 235 @@ -97,6 +103,9 @@
 236  /* The following is the maximum allowed duration of a lock wait. */
 237  UNIV_INTERN ulint      srv_fatal_semaphore_wait_threshold = 600;
 238
 239 +/**/
 240 +UNIV_INTERN lint       srv_kill_idle_transaction = 0;
 241 +
 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 @@
 246                 old_sema = sema;
 247         }
 248
 249 +       if (srv_kill_idle_transaction && trx_sys) {
 250 +               trx_t*  trx;
 251 +               time_t  now;
 252 +rescan_idle:
 253 +               now = time(NULL);
 254 +               mutex_enter(&kernel_mutex);
 255 +               trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
 256 +               while (trx) {
 257 +                       if (trx->conc_state == TRX_ACTIVE
 258 +                           && trx->mysql_thd
 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);
 262 +
 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);
 271 +                                       goto rescan_idle;
 272 +                               }
 273 +                       }
 274 +                       trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);
 275 +               }
 276 +               mutex_exit(&kernel_mutex);
 277 +       }
 278 +
 279         /* Flush stderr so that a database user gets the output
 280         to possible MySQL error file */
 281
 282 --- a/storage/innobase/trx/trx0trx.c
 283 +++ b/storage/innobase/trx/trx0trx.c
 284 @@ -143,6 +143,9 @@
 285         trx->mysql_relay_log_file_name = "";
 286         trx->mysql_relay_log_pos = 0;
 287
 288 +       trx->idle_start = 0;
 289 +       trx->last_stmt_start = 0;
 290 +
 291         mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO);
 292
 293         trx->rseg = NULL;
 294 --- /dev/null
 295 +++ b/mysql-test/r/percona_innodb_kill_idle_trx.result
 296 @@ -0,0 +1,41 @@
 297 +DROP TABLE IF EXISTS t1;
 298 +SET autocommit=0;
 299 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 300 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
 301 +Variable_name  Value
 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';
 308 +Variable_name  Value
 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
 313 +BEGIN;
 314 +INSERT INTO t1 VALUES (1),(2),(3);
 315 +COMMIT;
 316 +SELECT * FROM t1;
 317 +a
 318 +1
 319 +2
 320 +3
 321 +BEGIN;
 322 +INSERT INTO t1 VALUES (4),(5),(6);
 323 +SELECT * FROM t1;
 324 +ERROR HY000: MySQL server has gone away
 325 +SELECT * FROM t1;
 326 +a
 327 +1
 328 +2
 329 +3
 330 +DROP TABLE t1;
 331 +SET GLOBAL innodb_kill_idle_transaction=0;
 332 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
 333 +Variable_name  Value
 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
 338 --- /dev/null
 339 +++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result
 340 @@ -0,0 +1,45 @@
 341 +DROP TABLE IF EXISTS t1;
 342 +SET autocommit=0;
 343 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 344 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
 345 +Variable_name  Value
 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';
 352 +Variable_name  Value
 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
 357 +BEGIN;
 358 +INSERT INTO t1 VALUES (1),(2),(3);
 359 +COMMIT;
 360 +SELECT * FROM t1;
 361 +a
 362 +1
 363 +2
 364 +3
 365 +### Locking rows. Lock should be released when idle trx is killed.
 366 +BEGIN;
 367 +SELECT * FROM t1 FOR UPDATE;
 368 +a
 369 +1
 370 +2
 371 +3
 372 +UPDATE t1 set a=4;
 373 +SELECT * FROM t1;
 374 +a
 375 +4
 376 +4
 377 +4
 378 +DROP TABLE t1;
 379 +SET GLOBAL innodb_kill_idle_transaction=0;
 380 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
 381 +Variable_name  Value
 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
 386 --- /dev/null
 387 +++ b/mysql-test/t/percona_innodb_kill_idle_trx.test
 388 @@ -0,0 +1,28 @@
 389 +--source include/have_innodb.inc
 390 +--disable_warnings
 391 +DROP TABLE IF EXISTS t1;
 392 +--enable_warnings
 393 +
 394 +SET autocommit=0;
 395 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 396 +
 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
 400 +
 401 +BEGIN;
 402 +INSERT INTO t1 VALUES (1),(2),(3);
 403 +COMMIT;
 404 +SELECT * FROM t1;
 405 +
 406 +BEGIN;
 407 +INSERT INTO t1 VALUES (4),(5),(6);
 408 +sleep 3;
 409 +
 410 +--enable_reconnect
 411 +--error 2006 --error CR_SERVER_GONE_ERROR
 412 +SELECT * FROM t1;
 413 +SELECT * FROM t1;
 414 +DROP TABLE t1;
 415 +SET GLOBAL innodb_kill_idle_transaction=0;
 416 +--source include/percona_innodb_kill_idle_trx_show.inc
 417 --- /dev/null
 418 +++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test
 419 @@ -0,0 +1,31 @@
 420 +--source include/have_innodb.inc
 421 +--disable_warnings
 422 +DROP TABLE IF EXISTS t1;
 423 +--enable_warnings
 424 +
 425 +SET autocommit=0;
 426 +CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 427 +
 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
 431 +
 432 +connect (conn1,localhost,root,,);
 433 +connection conn1;
 434 +
 435 +BEGIN;
 436 +INSERT INTO t1 VALUES (1),(2),(3);
 437 +COMMIT;
 438 +SELECT * FROM t1;
 439 +
 440 +--echo ### Locking rows. Lock should be released when idle trx is killed.
 441 +BEGIN;
 442 +SELECT * FROM t1 FOR UPDATE;
 443 +
 444 +connection default;
 445 +UPDATE t1 set a=4;
 446 +
 447 +SELECT * FROM t1;
 448 +DROP TABLE t1;
 449 +SET GLOBAL innodb_kill_idle_transaction=0;
 450 +--source include/percona_innodb_kill_idle_trx_show.inc
 451 --- /dev/null
 452 +++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc
 453 @@ -0,0 +1,2 @@
 454 +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
 455 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';