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.