Some issues with kill_idle_transaction implementation at InnoDB level

Bug #1166744 reported by Raghavendra D Prabhu
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.1
Won't Fix
Undecided
Unassigned
5.5
Won't Fix
High
Laurynas Biveinis
5.6
Fix Released
High
Laurynas Biveinis
5.7
Fix Released
High
Laurynas Biveinis

Bug Description

Looking at following:

   if (trx->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;
    }
   }

a) It exits the kernel_mutex before killing the thread.

    i)Is it not possible that thread state can change (from sleep to
running) after mutex is exited?

    ii)If it is not possible to kill thread with kernel_mutex is held,
then it is not better to scan the trx list fully and then kill them at
once since thd_kill holds mutexes as well.

b) Is

    if (trx->last_stmt_start != start_time) {
     trx->idle_start = now;
     trx->last_stmt_start = start_time;

    required since thd->start_time (used by innobase_thd_get_start_time)
means the time the thread was in that state (in this case IDLE), so
doesn't thd->start_time > srv_... should state if it was idle that long?

c) Another issue (not sure if it can be fixed), is that this
runs every second in error thread, is it possible to not skip the
idle code for every few iterations (so as to run it every 60
seconds or configurable time) since otherwise the overhead of
this can be high.

tags: added: xtradb
Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

@Raghavendra,

Also implementation of innobase_thd_kill (actually thd_kill in sql_class.cc) is copy-paste from sql_parse.cc. And it does scan the list of THD to find one with matching ID. But we know THD already (it is trx->mysql_thd).

and there is also bug #907719

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

So, is this a duplicate of https://bugs.launchpad.net/percona-server/+bug/907719 or something else is going to be implemented/changed in the meantime?

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Bug 907719 is about reimplementation of this feature, the current bug is about defects in the current implementation. Fixing the other bug would probably fix this one, but we'll probably fix the current implementation first, because we have a high priority bug 1179136.

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

Code is still the same in recent PS 5.5.x and 5.6.x (srv0srv.c or srv0srv.cc), so items b) and c) above remain it seems.

tags: added: kill-idle-trx
summary: - About kill_idle_transactions
+ Some issues with kill_idle_transaction implementation at InnoDB level
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.