Progress reporting

MariaDB 5.3 supports progress reporting for some long running commands.

What is progress reporting

Progress reporting means that:

• There is a new column Progress last in SHOW PROCESSLIST that shows how much total progress is done (0-100 %)
• INFORMATION_SCHEMA.PROCESSLIST has tree new columns that allows you to see in which process stage we are and how much is done of that stage:
  • STAGE
  • MAX_STAGE
  • PROGRESS_DONE (within current stage).
• The client gets progress messages that it can display to the user to indicate how long the command will take.

We have separated progress reporting into stages as different stages takes different time.

Supported commands

Currently the commands that sends client progress messages are:

• ALTER TABLE
• ADD INDEX
• DROP INDEX
• LOAD DATA INFILE (not LOAD DATA LOCAL INFILE, as in this 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

Clients that supports progress reporting

• mysql command line client. mysql command line client 15.0 (comes with MariaDB 5.3).
• mytop which comes with MariaDB 5.3 has a '%' column that shows the progress.

How to get a MySQL client to support progress reporting

You need to use the MariaDB 5.3 client library. You can check that the library supports progress reporting by doing:

#if CLIENT_PROGRESS

To enable progress reporting to the client, you need to add CLIENT_PROGRESS to the connect_flag in mysql_real_connect():

mysql_real_connect(mysql, host, user, password,
                   database, opt_mysql_port, opt_mysql_unix_port,
                   connect_flag | CLIENT_PROGRESS);

Then you need provide a callback function for progress reports:

static void report_progress(const MYSQL *mysql, uint stage, uint max_stage,
                            double progress, const char *proc_info,
                            uint proc_info_length);

mysql_options(&mysql, MYSQL_PROGRESS_CALLBACK, (void*) report_progress);

The above report_progress function will be called for each progress message.

Here is the implementation used by mysql.cc:

uint last_progress_report_length;

static void report_progress(const MYSQL *mysql, uint stage, uint max_stage,
                            double progress, const char *proc_info,
                            uint proc_info_length)
{
  uint length= printf("Stage: %d of %d '%.*s' %6.3g%% of stage done",
                      stage, max_stage, proc_info_length, proc_info, 
                      progress);
  if (length < last_progress_report_length)
    printf("%*s", last_progress_report_length - length, "");
  putc('\r', stdout);
  fflush(stdout);
  last_progress_report_length= length;
}

If you want only one number for the total progress, you can calculate it with:

double total_progress=
 ((stage -1) / (double) max_stage * 100.00 + progress / max_stage);

How to get a storage engine to support progress reporting

The functions to use for progress reporting are:

void thd_progress_init(MYSQL_THD thd, unsigned int max_stage);

Initialize progress reporting with stages. This is mainly to be used for a commands that are totally executed in the engine like CHECK TABLE. You should not use this for operations that could be called by for example ALTER TABLE as this has already called the function.

max_stage is the number of stages your will have.

void thd_progress_report(MYSQL_THD thd, unsigned long long progress,
                         unsigned long long max_progress);

The above is used for reporting progress.

progress is how much of the file/rows/keys you have gone trough.
max_progress is the max number of rows you will go trough.

You can call this with varying numbers, but normally the ratio progress/max_progress should be increasing.

This function can be called even if you are not using stages, for example when enabling keys as part of ALTER TABLE or add ADD INDEX.

void thd_progress_next_stage(MYSQL_THD thd);

To go to next stage in a multi stage process initiated by thd_progress_init().:

void thd_progress_end(MYSQL_THD thd);

End progress reporting; Sets 'Progress' back '0' for SHOW PROCESSLIST.

const char *thd_proc_info(thd, 'stage name');

This sets the name of the current status/stage that is displayed in SHOW PROCESSLIST and in the client. It's recommended that you call this between stages and thus before thd_progress_report() and thd_progress_next_stage().

This functions returns the last used proc_info. It's recommended that you restore proc_info to it's original value when you are done processing.

Examples to look at in MariaDB source:

• client/mysql.cc for an example of how to use reporting.
• libmysql/client.c:cli_safe_read() to see how progress packets are handled in client
• sql/protocol.cc::net_send_progress_packet() for how progress packets are handled in server.

Format of progress packet

The progress packet is sent as an error packet with error number 65535.

It contains the following data: