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. :)
That rocks. I’ve wanted that for a long, long time…
That’s a great patch. You’re the man.
Your permalinks from Planet MySQL are busted (at least in my feed reader).
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).
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
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.
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.
Wow….. I soooooooo want that!
I sense backport time! I’d totally recompile my MySQL binaries again for that :)
To be clear .. this is server-side and requires a server restart with the new code, correct?
Kishore Balakrishnan’s Blog » Blog Archive » The solution to MySQL Copying to tmp table
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.
Hmmm — a year and a half later, and this still hasn’t found its way into the actual MySQL releases. Sad.
doesnt work for me, percent always null and progress row gives random values, like 100 then 12092 then 200 etc
Is there any way to see the progress of an ALTER TABLE statement in MySQL? - Programmers Goodies
Hi, any idea why this feature is gone on current versions of MySQL ?
We’re running very long queries, some take even days.
This feature would help our users from going crazy :)