minor changes to optimize innodb_kill_idle_transaction

[zhai weixiang]

If innodb_kill_idle_transaction is setting to a non-zero value , the monitor thread has to check the transaction list while holding trx_sys->mutex every one secound. This is reasonable if we set innodb_kill_idle_transaction to 1. But if innodb_kill_idle_transaction is larger than 1 , we can do some optimizations.

1. while scanning the trx list, pick out the oldest idle time ( that not need to be killed), called last_max_idle;
2.During next loop, if ++last_max_idle < last_max_idle, then we don't need to scan the trx list.

A simple patch (Not tested fully)
Index: srv/srv0srv.cc
===================================================================
--- srv/srv0srv.cc (revision 7085)
+++ srv/srv0srv.cc (working copy)
@@ -2068,6 +2068,7 @@
        /* the semaphore that is being waited for */
        const void* sema = NULL;
        const void* old_sema = NULL;
+ long long last_max_idle = 0;

        ut_ad(!srv_read_only_mode);

@@ -2140,10 +2141,13 @@
           old_sema = sema;
        }

- if (srv_kill_idle_transaction && trx_sys) {
+ if (srv_kill_idle_transaction
+ && ((++last_max_idle) >= srv_kill_idle_transaction)
+ && trx_sys) {
           trx_t* trx;
           time_t now;
 rescan_idle:
+ last_max_idle = 0;
           now = time(NULL);
           mutex_enter(&trx_sys->mutex);
           trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
@@ -2158,12 +2162,16 @@
                        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(&trx_sys->mutex);
- innobase_thd_kill(thd_id);
- goto rescan_idle;
+ } else {
+ longlong diff = difftime(now, trx->idle_start);
+ if (diff > srv_kill_idle_transaction) {
+ /* kill the session */
+ mutex_exit(&trx_sys->mutex);
+ innobase_thd_kill(thd_id);
+ goto rescan_idle;
+
+ } else
+ last_max_idle = ut_max(last_max_idle, diff);
                        }
                }
                trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);

[Valerii Kravchuk (valerii-kravchuk)]

Thank you for the patch contributed. It makes sense to consider this idea for all Percona Server versions.

[Laurynas Biveinis (laurynas-biveinis)]

     https://github.com/percona/percona-server/pull/1164
     https://github.com/percona/percona-server/pull/1165

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2450