WL#4170: Provide progress indication for long queries and operations

This Worklog task is to make it possible to get progress indication for long
queries and operations.

We need to modify the server to indicate progress, and modify the command line
client to display progress bar(s) when in interactive mode.

1. What needs progress indication and how it could be done
1.1 ANALYZE TABLE
1.2 ALTER TABLE
1.3 LOAD DATA INFILE
1.4 mysqldump dumps
1.5 SELECT
2. How to pass progress information to client
2.1 Option#1: extend the client-server protocol
2.2 Option#2: use secondary connection
3. Progress indication in other DBMSes


1. What needs progress indication and how it could be done
==========================================================

For most of long operations it is possible to have some progress indication:

1.1 ANALYZE TABLE
-----------------
MyISAM's implementation runs through all indexes. We know #records*#indexes,
therefore it is possible to have myisam code update the counter, and then
some other part use it to infer percentage of work done.


1.2 ALTER TABLE
---------------
Long ALTER TABLE queries can be executed with one of the following strategies:

1. At SQL layer: create a new table with the desired DDL, copy data to the
  new table, drop the old table and rename new one as old.
2. Delegate the operation into the engine.

For #1, progress indication can be done at SQL layer, and 

  #records_copied / #total_records 

is a reasonably good indication of which fraction of work is done/remains.


1.3 LOAD DATA INFILE
--------------------
This one is harder as we dont know how many records are there in the infile.
If the infile is a regular file (not an e.g. pipe) then we could check its size
and provide progress indication of how many kilobytes of data was loaded.

If the total size of loaded file is not available, we could give the user
information about how many records has been loaded.


1.4 mysqldump dumps
-------------------
It would be nice if one could get an indication of where he is in loading a
mysqldump file. This could be achieved if mysqldump command put at the top
of the mysqldump file some information about how many tables are in the file
and how many (approximately) records each table has. 


1.5 SELECT
----------
(This is a lower-priority) One can get a rough estimate of the progress made by
looking at how many records have been enumerated from the first non-const
table of the first top-level join.


2. How to pass progress information to client
=============================================

We see two possible options:

2.1 Option#1: extend the client-server protocol
-----------------------------------------------

Extend the client-server protocol to support passing chunks of out-of-band data
from server to client. Extend the client to interpret those chunks and display
progress bars.

2.2 Option#2: use secondary connection
--------------------------------------
* Modify the server so that it is possible to issue some query in one
  connection and get information about progress made by the other connection.
  Perhaps the query could use information_schema interface.

* Modify the client so it starts a second connection and uses the query to get
  progress reports about the primary connection. The client could periodically
  poll the server or use one long-running query.

Note: There are other efforts to report progress-like information as server
tables, see information_schema.profiling and WL#4116 "Online Backup: Record
Online Backup Progress".


3. Progress indication in other DBMSes
======================================
We've discovered PostgreSQL has this:
> University of Toronto:
> http://queens.db.toronto.edu/project/conex/
>
> University of Wisconsin:
> http://pages.cs.wisc.edu/~gangluo/interface.pdf
> http://pages.cs.wisc.edu/~gangluo/workload_final.pdf


References
----------

BUG#26182 patch: process percentage of a process -- contributed from Jeremy Cole
http://jcole.us/blog/archives/2007/02/08/progress-in-mysql-process-list/
BUG#56262 REPAIR STATUS
WL#5234 Percentage of completion is missing (Backup or Restore)
WL#1227 SHOW PROCESSLIST: Add better output for slave processing LOAD DATA

dev-private email "Re: New (by AnuragShekhar): Percentage of completion is
missing (Backup or Restore) (5234)"
[ mysql intranet ] /secure/mailarchive/mail.php?folder=4&mail=32654