maria-developers team mailing list archive

[Marko Mäkelä <marko.makela@xxxxxxxxxxx>]

Hi Sergei,

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
> on
> > 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
aborted-but-not-yet-rolled-back transactions.

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
-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation