Progress in MySQL Process List
Today I had a sort of short epiphany regarding getting progress of running statements in MySQL. MySQL already keeps a running count of rows touched in most multi-row statements (called thd->row_count1), so I figured there must be a way to make use of it. It was trivial to expose row_count through SHOW PROCESSLIST. After that, it was fairly obvious that another variable could be added: row_count_expected. For certain statements (currently only ALTER TABLE) it is easy to estimate how many rows will be touched, so that number can be used to calculate a Progress_percent column.
The Progress_rows number indicates progress within a given step of executing the query. For instance, if you run a SELECT with a GROUP BY that can’t be done using an index, you will see two cycles of Progress_rows: once with a State of “Copying to tmp table” and once with “Sending data”.
I implemented this all in a small patch to MySQL 5.0 (and backported to MySQL 4.1) which produces the following output from SHOW FULL PROCESSLIST:
mysql> show full processlist \G
*************************** 1. row ***************************
Id: 1
User: jcole
Host: localhost
db: test
Command: Query
Time: 3
State: copy to tmp table
Info: alter table sclot__a type=myisam
Progress_rows: 44141
Progress_percent: 76.09
This was really way, way too easy. Hopefully it can be one with MySQL Community soon.
1 Note that currently thd->row_count is a 32-bit unsigned integer, so it will wrap at about 4.2 billion rows. Someone should really think about fixing this. 
February 8th, 2007 at 12:25:07
That rocks. I’ve wanted that for a long, long time…
February 9th, 2007 at 12:05:12
That’s a great patch. You’re the man.
Your permalinks from Planet MySQL are busted (at least in my feed reader).
February 10th, 2007 at 06:25:31
Something I’ve been waiting a very long time for. Hopefully it can make it into the community edition (but in a better manner than the SHOW HOST CACHE which they only implemented in the INFORMATION_SCHEMA).
February 10th, 2007 at 13:26:42
Great work, hope it will be added ASAP, but I’d love to see all this implemented in the INFORMATION_SCHEMA and not that “SHOW …” synthax
February 18th, 2007 at 17:27:54
Hi Jeremy,
I’ve been thinking about this a bit more and I think it would be better not to calculate the percentage. If you output rows_count and row_count_expected, anyone can calculate the percentage done themselves. But as it is, row_count_expected potentially loses some precision.
February 19th, 2007 at 20:10:27
Hi Baron,
I know your sentiment, and I know it’s losing precision in the calculation, but I think that’s OK. Really, the row_count_expected stuff is all an estimation, and may even reset many times within a given query. Since it’s an estimate, I don’t think losing precision on any conversions is really a problem, and the percentage view is more interesting for the human users of the system.
Regards,
Jeremy
February 20th, 2007 at 01:29:35
Wow….. I soooooooo want that!
I sense backport time! I’d totally recompile my MySQL binaries again for that
June 4th, 2007 at 11:04:18
Nice, dude.
December 13th, 2007 at 09:16:57
To be clear .. this is server-side and requires a server restart with the new code, correct?
Dan
December 16th, 2007 at 16:05:15
[...] update : Progress in MySQL Process List [...]
May 16th, 2008 at 01:59:16
Wow… I posted on my blog a few hours ago that I wish this existed already, and a comment led me here. It’s a real shame this patch hasn’t been rolled into production.
November 14th, 2008 at 00:05:46
Hmmm — a year and a half later, and this still hasn’t found its way into the actual MySQL releases. Sad.
March 9th, 2009 at 09:10:14
doesnt work for me, percent always null and progress row gives random values, like 100 then 12092 then 200 etc
mysql 5.0.32