MySQL Bugs: #26182: patch: process percentage of a process -- contributed from Jeremy Cole

Bug #26182	patch: process percentage of a process -- contributed from Jeremy Cole
Submitted:	8 Feb 2007 11:02
Reporter:	Chad MILLER	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	community

Description:
Description from http://jcole.us/blog/archives/2007/02/08/progress-in-mysql-process-list/

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

How to repeat:
.

Suggested fix:
Patch:
http://jcole.us/patches/mysql/5.0/progress_rows_percent.patch

As I commented on Jeremy's blog:

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

I would prefer to just have the raw data, not the percentage, exposed in the process list.

Any progress on this? This sounds like a good patch (and it looks simple in terms of changes).

A percentage would help I think to be shown regardless, because that's what you will end up calculating anyway to find out how much longer the operation has to go.

Is there a reason this has been stuck on Verified since 07?

Andrew A, it's a feature request.  When 5.1 is released, we'll have time for such things.

Yep understood.

Apologies if that comment sounded a bit abrupt - thanks :)

As I sit waiting an unknown time for a conversion to innodb to complete, this would be a very nice feature to have. Any progress in the last two years?

i closed bug 56262 since it's similar to this one

This fix would make it A LOT easier for me too. Right now i am waiting for a repair table; to finish, 1.3 billion records, and I have no idea whatsoever if it will finish in a minute or in three days. There is no way that I know off, that I can get an indication of progress. I'd love to see this feature end up in a mysql somewhere down the line.

Yep same here, billions of records and no clue how long it will take, how does this not affect more people???  Incredible something like this just sits around for nearly 6 years...

This looks fixed in MySQL 5.7 (w/ SYS 1.5)
https://github.com/MarkLeith/mysql-sys/issues/50

The sys.processlist now has a progress column to indicate the progress percentage.

This does require stage instrumentation to be enabled in performance schema.