Progress Reporting in MariaDB 5.3

There are many new features in MariaDB 5.3. I’m looking forward to many of them, but one of the ones I’m most excited about is Progress Reporting.

It’s a fact of life in the database world that some commands take longer to run than others. Commands like ALTER TABLE, LOAD DATA INFILE, and adding and dropping an index simply take time to run, depending (of course) on your data and schema. I always have hated having to wait for those commands to run with no indication of how much progress has been made or how much is left to do. All of that changes with the upcoming release of MariaDB 5.3.

In MariaDB 5.3 there is a new “Progress” column in the output of SHOW PROCESSLIST (this can be turned off) which shows the progress, and there are three new columns (STAGE, MAX_STAGE, and PROGRESS_DONE) in INFORMATION_SCHEMA.PROCESSLIST which allow you to see which process stage you are in, the number of stages, and how much of the current stage is completed. Additionally, the mysqld server sends progress reports to clients at a specified interval (the default is every 5 seconds, 0 will disable it). In the MariaDB 5.3 mysql command-line client, these progress reports look like this:

MariaDB [test]> alter table my_mail engine=maria;
Stage: 1 of 2 'copy to tmp table'  5.37% of stage done

Currently, the following commands can send progress report messages to the client:

• ALTER TABLE
• ADD INDEX
• DROP INDEX
• LOAD DATA INFILE (not LOAD DATA LOCAL INFILE, as in that case we don’t know the size of the file).

Some Aria storage engine operations also support progress messages:

• CHECK TABLE
• REPAIR TABLE
• ANALYZE TABLE
• OPTIMIZE TABLE

Third-party clients and storage engines can easily add support for receiving and sending (respectively) these progress reports. Details are on the Progress Reporting page in the AskMonty Knowledgebase. One application which has been updated to support progress reports (in addition to the MariaDB mysql command-line client) is the ‘mytop‘ program, which will be included in source and binary packages of MariaDB 5.3.

Progress reporting doesn’t speed up ALTER TABLE or any of the other supported commands, but at least I now have some visibility into them, and that is “a very good thing”.