Estimating the progress of queries on MySQL

I've been doing a lot of batch updates on one of my databases at home recently. show processlist says something like this:

mysql> show processlist;
+-------+------+---------------+--------------+---------+-------+----------+------------------------------------------+
| Id    | User | Host          | db           | Command | Time  | State    | Info                                     |
+-------+------+---------------+--------------+---------+-------+----------+------------------------------------------|
| 18354 | root | maui:37403    | smtp_servers | Query   | 57234 | Updating | update ips_218 set reverse_lookup = null |
| 22286 | root | maui:37348    | smtp_servers | Query   | 38103 | Updating | update ips_80 set reverse_lookup = null, |
| 22851 | root | maui:54982    | smtp_servers | Query   | 34091 | Updating | update ips_19 set reverse_lookup = null, | 
| 23351 | root | molokai:58232 | smtp_servers | Sleep   |    57 |          | NULL                                     |
| 23496 | root | maui:40923    | smtp_servers | Query   | 29973 | Updating | update ips_62 set reverse_lookup = null, |
| 23906 | root | maui:38068    | smtp_servers | Query   | 26794 | Updating | update ips_83 set reverse_lookup = null, |
| 25675 | root | maui:56438    | smtp_servers | Query   | 12505 | Updating | update ips_82 set reverse_lookup = null, |
| 25846 | root | maui:41334    | smtp_servers | Query   | 10948 | Updating | update ips_90 set reverse_lookup = null, |
| 26437 | root | maui:41139    | smtp_servers | Query   |  6211 | Updating | update ips_66 set reverse_lookup = null, |
| 26773 | root | maui:32885    | smtp_servers | Query   |  3526 | Updating | update ips_76 set reverse_lookup = null, |
| 27073 | root | maui:42607    | smtp_servers | Query   |  1148 | Updating | update ips_11 set reverse_lookup = null, |
| 27202 | root | molokai:50688 | smtp_servers | Query   |     0 | NULL     | show processlist                         |
| 27203 | root | molokai:50689 | smtp_servers | Sleep   |     2 |          | NULL                                     |
+-------+------+---------------+--------------+---------+-------+----------+------------------------------------------+
14 rows in set (0.20 sec)

Now, wouldn't it be nice if MySQL provided some extra information about the progress of those queries? Like for example the number of rows which have been updated so far, or an estimate of how long the query has left to run? I'm ok with such queries not being very accurate, but I assume the storage engine has to have some idea of how many rows are in the table and how many it has touched already.

Perhaps something like this already exists and I haven't noticed? I'm using innodb if that matters.

Update: it seems like innodb can answer this question for me:

mysql> show engine innodb status \G;
...
---TRANSACTION 0 40056, ACTIVE 39794 sec, process no 22984, OS thread id 3020733328 waiting in InnoDB queue
mysql tables in use 1, locked 1
6672 lock struct(s), heap size 748864, undo log entries 909825
MySQL thread id 22851, query id 351217 maui 192.168.1.93 root Updating
update ips_19 set reverse_lookup = null, reverse = null, reverse_extracted
...

That doesn't give you an estimate of percentage complete though. I assume there is a 1:1 correlation between undo row entries and rows altered by the query?

Tags for this post: mysql()

Comment on this post

Syndicated 2008-05-07 08:26:00 (Updated 2008-05-07 00:03:34) from stillhq.com : Mikal, a geek from Canberra living in Silicon Valley