maria-developers team mailing list archive
Mailing list archive
Re: How to report progress for InnoDB background tasks (esp. at startup/shutdown)?
On Mon, Apr 17, 2017 at 4:23 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
> Hi, Marko!
> On Apr 07, Marko Mäkelä wrote:
> > Hi all,
> > MariaDB Server features a nice-looking interface for reporting progress
> > long-running SQL operations:
> > https://mariadb.com/kb/en/mariadb/progress-reporting/
> > MySQL 5.7 implemented something similar for reporting progress for ALTER
> > TABLE…ALGORITHM=INPLACE. I should reimplement that for MariaDB using the
> > existing MariaDB infrastructure.
> > I wonder if the same interface could be used for tracking progress on
> > periodic background tasks that might not be directly related to the
> > execution of any SQL statements. InnoDB has a number of background tasks,
> > such as:
> > * purging unneeded history (removing undo log records and delete-marked
> > index records)
> > * rolling back recovered transactions (mainly, if the server was killed)
> > * index defragmentation, encryption key rotation
> > * redo log checkpoints (flush all dirty pages up to the checkpoint LSN;
> > most interesting at shutdown)
> Yes and no. InnoDB background threads can report the progress. They'll
> need a THD for that. Luckily, purge threads already have it in 10.2.
> The progress will be shown in SHOW PROCESSLIST, but won't be sent
> to a client in special progress report packets.
> I suspect it should be fairly easy to start using progress reporting in
> purge threads. And then you could see whether it makes sense to enable
> it for more background threads.
OK, that sounds like a reasonable medium-term plan.
> > Related to this, I wonder if it is possible or feasible to change the
> > SQL layer logic so that at shutdown, it will not issue ROLLBACK (as
> > part of KILL QUERY) to the currently active connections, but let the
> > subsequent startup do the rollback in the background. This should
> > allow faster shutdowns and restarts.
> I don't see why not. From the user point of view an aborted (with a
> shutdown or a disconnect) transaction should always be rolled back. But
> how exactly it's implemented is up to us, as long as the user visible
> behavior stays the same.
The observable behaviour would differ when it comes to locking. If the
aborted transactions are left behind by shutdown, then the subsequent
startup would initiate the rollback in a background thread. Until that
rollback is completed, user transactions can observe lock conflicts for
those records for which modifications were already persisted by the
I guess that this could be part of a new innodb_fast_shutdown=3 option. But
that would not be a high-priority task.
Marko Mäkelä, Lead Developer InnoDB