Bug #75298 Purge thread should check purge_sys->state after every batch
Submitted: 23 Dec 2014 15:29 Modified: 5 Jan 2015 17:48
Reporter: Inaam Rana (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[23 Dec 2014 15:29] Inaam Rana
Description:
purge_sys->state is set to PURGE_STATE_STOP to stop the purge when exporting a tablespace. This flag is checked by the the purge thread in srv_purge_coordinator_suspend(). The purge thread goes to this function after finishing a full sweep of purge in srv_do_purge(). If we have a large history list, a full purge can take quite a while.

How to repeat:
Have a large history list. Let the purge thread start purging. Do "FLUSH TABLE <table_name> FOR EXPORT" for some table and see it getting stuck waiting for purge to finish.

Suggested fix:
Check for purge_sys->state in srv_do_purge():

diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index ac1a945..302bee9 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -2675,7 +2675,9 @@ srv_do_purge(
 
                *n_total_purged += n_pages_purged;
 
-       } while (!srv_purge_should_exit(n_pages_purged) && n_pages_purged > 0);
+       } while (!srv_purge_should_exit(n_pages_purged)
+                 && n_pages_purged > 0
+                 && purge_sys->state == PURGE_STATE_RUN);
 
        return(rseg_history_len);
 }
[24 Dec 2014 6:42] MySQL Verification Team
To initially create a large history list length one can just update a row many times:

##session 1
drop table if exists t1;
create table t1(a int)engine=innodb;
drop procedure if exists p1;
delimiter $
create procedure p1(n int unsigned)
begin
  repeat
   start transaction;update t1 set a=a+1;
   commit;set n:=n-1;
  until n = 0 end repeat;
end $
delimiter ;

insert into t1 values (1);
start transaction;
select * from t1;
##wait here...

##session2
call p1(10000000);
[24 Dec 2014 7:07] MySQL Verification Team
Hello Inaam,

Thank you for the report and contribution.
Observed this behavior with 5.6.22.

Thank you Shane for the test case to create a large history list.

Thanks,
Umesh
[5 Jan 2015 17:48] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.23, 5.7.6 release, and here's the changelog entry:

A tablespace export operation set the purge state to "PURGE_STATE_STOP"
but the purge thread did not check the purge state until the current purge
operation was completed. In the case of a large history list, the
tablespace export operation was delayed, waiting for the current purge
operation to finish. The purge state is now checked with every purge
batch.
[12 Feb 2015 14:41] Laurynas Biveinis
$ git show -s f9a1df8
commit f9a1df899b724d26d7997a49e6403bbe90024bf3
Author: Yasufumi Kinoshita <yasufumi.kinoshita@oracle.com>
Date:   Tue Dec 30 12:31:08 2014 +0900

    Bug#20266847 : PURGE THREAD SHOULD CHECK PURGE_SYS->STATE AFTER EVERY BATCH
    
    purge_sys->state is set to PURGE_STATE_STOP to stop the purge when exporting
    a tablespace. This flag is checked by the the purge thread in
    srv_purge_coordinator_suspend(). The purge thread goes to this function after
    finishing a full sweep of purge in srv_do_purge(). If we have a large history
    list, a full purge can take quite a while.
    
    Approved by Sunny in IM