WL#4170: Provide progress indication for long queries and operations
Affects: WorkLog-3.4 — Status: Un-Assigned — Priority: Medium
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.
<contents> 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 </contents> 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
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.