| 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?
Update: my imperical observation is that the undo rows are not 100% correlated to the number of rows your query changed. Its correlated to the number of rows that were changed kinda near your query. For example, if you're doing an update, then the number is good enough to trust. However, if you're doing a select, then the number seems to be the number of rows someone else changed while your select was running (i.e. how many old versions needed to be kept around because of your select transaction).
Also, Jeremy Cole to the rescue!
Tags for this post: mysql(
)
posted at: 04:19 | path: /mysql | permanent link to this entry
There are 2 comments on this post, and 7 comments which didn't survive moderation. 0 were blocked by trained gerbils. Click here to see them.