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_count¹), 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.

¹ 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. :)

15 thoughts on “Progress in MySQL Process List”

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

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.

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

Wow….. I soooooooo want that!

I sense backport time! I’d totally recompile my MySQL binaries again for that :)

Nice, dude.

To be clear .. this is server-side and requires a server restart with the new code, correct?

Dan

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.