Description:
If we have more than 1 recovered trx that needs to be rollbacked ( by background trx_recovery_rollback thread ) and trigger shutdown command with innodb_fast_shutdown=1, InnoDB Waits until the rollback of the current transaction being processed by background trx_recovery_rollback thread during shutdown before closing the trx_recovery_rollback thread.
In the worst case scenario where there is one huge recovered transaction, with innodb_fast_shutdown=1, shutdown waits for the rollback to complete impacting the total shutdown time.
How to repeat:
create database test;
use test;
create table t1(col1 int primary key auto_increment, col2 longblob, col3 longblob, col4 longblob, col5 longblob, col6 longblob);
insert into t1 values(null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536));
begin;
insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;
insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;
insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;
insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;
insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;
insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;insert into t1 select null, repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536), repeat(md5(rand()*100000),65536) from t1;
kill -9 mysqld process
start mysqld ( trx will be recovered and background thread will be doing rollback)
shutdown command (immediately)
Suggested fix:
The below commit tries to achieve - wait for completed rollback of recovered transactions ONLY in case of slow shutdown. However, it still completes the rollback of current trx and skip other trx if any.
https://github.com/mysql/mysql-server/commit/6a37a4076c7d4e4edc23019ccae76801bd7ab472.
It would be great if this can be further optimized to skip the rollback of current processing trx by background thread.
https://github.com/mysql/mysql-server/blob/mysql-8.3.0/storage/innobase/trx/trx0roll.cc#L7...
