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

15 thoughts on “Progress in MySQL Process List

  1. 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).

  2. 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.

  3. 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

  4. Kishore Balakrishnan’s Blog » Blog Archive » The solution to MySQL Copying to tmp table

  5. 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.

  6. doesnt work for me, percent always null and progress row gives random values, like 100 then 12092 then 200 etc

    mysql 5.0.32

  7. Is there any way to see the progress of an ALTER TABLE statement in MySQL? - Programmers Goodies

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

    Thanks.

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s