Progress of SHOW PROCESSLIST in MySQL 5.5

Sheeri

Edited to add – this is in Percona’s patched 5.5, not in Oracle’s 5.5.

We have only recently started using MySQL 5.5 here at Mozilla, and while there are features I have known about for a while, I usually hear about them early on and file them as “oh yeah, that is coming later!”. Like how right now we are learning about all the neat stuff in MySQL 5.6. Well, I am sure I knew about the addition of the Rows_read and Rows_sent fields to the PROCESSLIST information, but it was only today that I really am using it.

I am doing an export of a large table, and I did a SHOW PROCESSLIST to see:

Id: 651130
User: export
Host: localhost
db: puppet_dashboard
Command: Query
Time: 1246
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `resource_statuses`
Rows_sent: 33642001
Rows_examined: 0
Rows_read: 33642002

Well, I quickly popped on to the slave and did a COUNT(*), which gave me 122692530, so now I have a quick-and-dirty progress meter going:

mysql> select now(),rows_sent/122692530*100 as pct from information_schema.processlist where id=651130; do sleep(60); select now(),rows_sent/122692530*100 as pct from information_schema.processlist where id=651130; do sleep(60); select now(),rows_sent/122692530*100 as pct from information_schema.processlist where id=651130; [etc. repeated several times]
+---------------------+---------+
| now() | pct |
+---------------------+---------+
| 2012-06-07 06:12:12 | 29.4174 |
+---------------------+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (1 min 0.01 sec)

+---------------------+---------+
| now() | pct |
+---------------------+---------+
| 2012-06-07 06:13:12 | 30.7194 |
+---------------------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (59.99 sec)

+---------------------+---------+
| now() | pct |
+---------------------+---------+
| 2012-06-07 06:14:12 | 31.9814 |
+---------------------+---------+
1 row in set (0.00 sec)

I am sure the wizards out there will point out they have known this for a while, or that there is a tool that shows progress for you, but I love stumbling across stuff like this!