Hi Sergei,

On Mon, Apr 17, 2017 at 4:23 PM, Sergei Golubchik <serg@mariadb.org> 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