<<toc>>
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##
in ##[[show-processlist|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## (within current stage).
* The client receives progress messages which it can display to the user to
indicate how long the command will take.
We have separate progress reporting for stages because different stages take
different amounts of time.
== Supported commands
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##
See also: MWL#202 "Extend progress reporting"
== Enabling and disabling progress reporting
##mysqld## (the MariaDB server) automatically sends progress report messages to clients that support the new protocol. They are sent every
max(##global.progress_report_time## , ##progress_report_time##) seconds (by default 5). You can disable the sending of progress report messages to the client by setting either the local variable (affects only the current connection) or the global variable (affects all connections) to ##0##.
If the extra column in ##SHOW PROCESSLIST## gives you a compatibility problem,
you can disable it by starting ##mysqld## with the <<code>>--old<</code>> flag.
== Clients which support progress reporting
* The [[mysql-command-line-client|##mysql## command line client]], starting
from version 15.0 (comes with MariaDB 5.3).
* The ##mytop## that comes with MariaDB 5.3 has a ##'%'## column which shows
the progress.
== Progress reporting in the mysql command line client
Progress reporting is enabled by default in the new ##mysql## client. You can
disable it with <<code>>--disable-progress-reports<</code>>. It is automatically disabled in
batch mode.
When enabled, for every supported command you get a progress report like:
<<code>>
MariaDB [test]> alter table my_mail engine=maria;
Stage: 1 of 2 'copy to tmp table' 5.37% of stage done
<</code>>
This is updated every ##progress_report_time## seconds (the default is 5). If the global ##progress_report_time## is higher, this will be used. You can also disable error reporting by setting the variable to ##0##.
== How to add support for progress reporting to a MySQL client
You need to use the MariaDB 5.3 client library. You can check that the library
supports progress reporting by doing:
<<code lang=c inline=false>>
#ifdef CLIENT_PROGRESS
<</code>>
To enable progress reporting to the client you need to add
##CLIENT_PROGRESS## to the ##connect_flag## in ##mysql_real_connect()##:
<<code lang=c inline=false>>
mysql_real_connect(mysql, host, user, password,
database, opt_mysql_port, opt_mysql_unix_port,
connect_flag | CLIENT_PROGRESS);
<</code>>
Then you need to provide a callback function for progress reports:
<<code lang=c inline=false>>
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);
<</code>>
The above ##report_progress## function will be called for each
progress message.
This is the implementation used by ##mysql.cc##:
<<code lang=c inline=false>>
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;
}
<</code>>
If you want only one number for the total progress, you can calculate it with:
<<code lang=c inline=false>>
double total_progress=
((stage -1) / (double) max_stage * 100.00 + progress / max_stage);
<</code>>
<<style class="greenbox">>
**Note:** ##proc_info## is totally independent of stage. You can have many
different ##proc_info## values within a stage. The idea behind ##proc_info## is
to give the user more information about what the server is doing.
<</style>>
== How to add support for progress reporting to a storage engine
The functions to use for progress reporting are:
<<code lang=c inline=false>>
void thd_progress_init(MYSQL_THD thd, unsigned int max_stage);
<</code>>
Initialize progress reporting with stages. This is mainly used for
commands that are totally executed within 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 storage engine will have.
<<code lang=c inline=false>>
void thd_progress_report(MYSQL_THD thd, unsigned long long progress,
unsigned long long max_progress);
<</code>>
The above is used for reporting progress.
* ##progress## is how much of the file/rows/keys you have gone through.
* ##max_progress## is the max number of rows you will go through.
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 INDEX##.
<<code lang=c inline=false>>
void thd_progress_next_stage(MYSQL_THD thd);
<</code>>
To go to the next stage in a multi-stage process initiated by
##thd_progress_init()##:
<<code lang=c inline=false>>
void thd_progress_end(MYSQL_THD thd);
<</code>>
End progress reporting; Sets 'Progress' back to 0 in ##SHOW PROCESSLIST##.
<<code lang=c inline=false>>
const char *thd_proc_info(thd, 'stage name');
<</code>>
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 its original value when you are done
processing.
<<style class="greenbox">>
**Note:** ##thd_proc_info()## is totally independent of stage. You can have
many different ##proc_info## values within a stage to give the user more
information about what is going on.
<</style>>
== Examples to look at in the 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 packets
The progress packet is sent as an error packet with error number ##65535##.
It contains the following data (in addition to the error header):
<<style class="darkheader-nospace-borders">>
|= Option |= Number of bytes |= Other info
| 1 | 1 | Number of strings. For future |
| Stage | 1 | Stage from 1 - Max_stage |
| Max_stage | 1 | Max number of stages |
| Progress | 3 | Progress in ##% * 1000## |
| Status_length | 1-2 | Packet length of string in ##net_field_length()## format |
| Status | Status_length | Status / Stage name|
<</style>>
== Future plans
MWL#202 "Extend progress reporting" contains the future plans for this feature.
== See Also:
* [[what-is-mariadb-53|What is MariaDB 5.3]]
