Using progress reporting in the mysql client

One relatively unknown feature of MariaDB is the ability of the client to show progress reports for long commands.

How to do it…

Let's get started by following the ensuing steps:

1. There's nothing to configure as progress reporting is turned on by default and works with the ALTER TABLE, ADD INDEX, DROP INDEX, and LOAD DATA INFILE commands. It also works with the CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE commands when using the Aria storage engine. For example, if we needed to change a large table from using the MyISAM storage engine to the Aria storage engine, it might look similar to the following command:

   MariaDB [test]> ALTER TABLE my_big_table engine=aria;
   Stage: 1 of 2 'copy to tmp table'  29.26% of stage done
   

2. The progress report line will update every 5 seconds until the operation is complete.

How it works...

For the clients that support it, mysqld (the MariaDB server) sends progress report messages every 5 seconds. The mysql command-line client supports it, as does the mytop shell script included with MariaDB.

You can easily add support for progress messages on other clients by following the instructions at https://mariadb.com/kb/en/progress-reporting/. If our favorite client application does not support progress reporting, encourage the developers to add it!

There's more…

We can change the default 5 second update by setting the progress_report_time variable to a value greater than 5. Values ranging from 1 to 5 are ignored.

Disabling progress reporting

To disable progress reporting, set the progress_report_time variable to 0 or use the --disable-progress-reports option when launching the mysql client. Progress reporting is automatically disabled in batch mode.

Progress reporting in mytop

The mytop script included with MariaDB shows the progress of long running commands in the '%' column.