Bug #76750 innodb History list length (trx_rseg_history_len) never reaches 0
Submitted: 20 Apr 2015 8:34
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.44 OS:Any
Assigned to: CPU Architecture:Any
Tags: history list length, trx_rseg_history_len

[20 Apr 2015 8:34] Shane Bester
Description:
Affects 5.5, 5.6, 5.7, 5.8

On a fresh installation, after some transactions, the history list length never seems to be able to reach zero again,  even after a forced purge and slow shutdown of innodb.

mysql> set global innodb_purge_run_now=1;
Query OK, 0 rows affected (0.00 sec)

mysql> do sleep(10);
Query OK, 0 rows affected (10.00 sec)

mysql> select name,subsystem,count from information_schema.innodb_metrics where name='trx_rseg_history_len';
+----------------------+-------------+-------+
| name                 | subsystem   | count |
+----------------------+-------------+-------+
| trx_rseg_history_len | transaction |     0 |
+----------------------+-------------+-------+
1 row in set (0.00 sec)

mysql> call p1(1000);
Query OK, 0 rows affected (2.17 sec)

mysql> set global innodb_purge_run_now=1;
Query OK, 0 rows affected (0.00 sec)

mysql> do sleep(10);
Query OK, 0 rows affected (10.00 sec)

mysql> select name,subsystem,count from information_schema.innodb_metrics where name='trx_rseg_history_len';
+----------------------+-------------+-------+
| name                 | subsystem   | count |
+----------------------+-------------+-------+
| trx_rseg_history_len | transaction |   664 |
+----------------------+-------------+-------+
1 row in set (0.00 sec)

AFTER RESTART of server,  the value 664 might go down, but never reaches 0 again.

How to repeat:
drop procedure if exists p1;
drop table if exists t1;
create table t1(a int)engine=innodb;
delimiter $
create procedure p1(p_n int)
begin
  repeat
   start transaction;
   insert into t1 values(1);
   delete from t1 where a=1;
   commit;  
   set p_n:=p_n-1;
  until p_n <=1 end repeat;
end $
delimiter ;
set global innodb_purge_run_now=1;
do sleep(10);
select name,subsystem,count from information_schema.innodb_metrics where name='trx_rseg_history_len';
call p1(1000);
set global innodb_purge_run_now=1;
do sleep(10);
select name,subsystem,count from information_schema.innodb_metrics where name='trx_rseg_history_len';

Suggested fix:
Marko says it should be investigated.
[22 Sep 2016 6:55] MySQL Verification Team
this seems somewhat better in current trunk 8.0.1. after a "slow shutdown" history list goes to 1 again.
[12 Feb 2018 15:46] zhai weixiang
I did some test to investigate this problem. It turns out that the undo log which is cached on undo list is not truncated and that's why history list never reaches zero.