2011-06-30

Progress reporting for ALTER TABLE

I just finished my last MariaDB 5.3 feature before we go beta:

Progress reporting for ALTER TABLE, LOAD DATA INFILE etc.

This is a feature that I think every MySQL user who has ever waited for an ALTER TABLE to finish has wanted for a long time! I know people who have written scripts to monitor the size of the result files to try to estimate how much an ALTER TABLE has progressed.

Progress reporting means that:
  • There is a new column Progress in SHOW PROCESSLIST which shows the total progress (0-100 %)
  • INFORMATION_SCHEMA.PROCESSLIST has three new columns which allow you to see in which process stage we are and how much of that stage is completed:
    • STAGE
    • MAX_STAGE
    • PROGRESS_DONE (within current stage).
  • The client receives out-of-band progress messages which it can display to the user to indicate how long the command will take!
Progress reporting is enabled by default in the new mysql client. When enabled, for every supported command you get a progress report like:

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

MariaDB 5.3 also includes a modified mytop which shows progress for the running commands.

This feature is now pending a review. It will be pushed into MariaDB 5.3 tomorrow.

You can find a full documentation of this feature in the knowledgebase.

5 comments:

NMMM.NU said...

hey, that's very cool ability,
TokuDB has something similar, but they do it also for UPDATE, DELETE and INSERT INTO SELECT FROM statements.

Monty said...

Adding it for UPDATE and DELETE is now easy when the infrastructure exists.

INSERT .. SELECT has the same problem as SELECT; For more than one table it's very hard to predict the total time.
(We plan of course to try to do that...)

Bret said...

Just a quick question. I am running MySql 5.5 on Windows and am wondering what the best way to switch to MariaDB is as there is no MariaDB 5.5? We are not leveraging any features unique to MySql 5.5.

Monty said...

To Bret:

If you are not using any of the new features or options, then you should just be able to remove MySQL and install MariaDB 5.2 or 5.3 over it.

Another option is to wait a few of weeks and install MariaDB 5.5 (We are working full time to get this out and we are close to having a beta ready)

Dale said...

This is exactly what I needed! Thank You! MySQL has been lacking in this for some time - does anyone know another option for UPDATE? It is the backend for all of my sites but I always run into this!

Dale