4

I ran a MySQL query from command line using the following:

nohup mysql --user=root --password=XXXXXXXXX database < report.sql > results.tab

This query is extensive (5000 lines) and running a complex search over 90,000,000 rows from 150 tables.

This query has been running for over 24 hours and has not yet finished.

Is there a way I can run status check on this running query?

reefine
  • 239
  • 5
  • 11

2 Answers2

4

Well, you can log into a mysql console and give out the SHOW PROCESSLIST; command. That will show you what queries are running, but you won't get an estimate, when will those queries end.

If you run the queries with an EXPLAIN EXTENDED prefix beforehand, that will show you cardinality and other relevant details about the query. That could help you, but since the query is running, you can use another approach, explained in detail at mysqlperformanceblog.

Excerpt:

The answer was simple, because there was nothing else running on the server. That means that SHOW GLOBAL STATUS gave a rough idea of what the query was actually doing. (If there had been a lot of activity on the server, I wouldn’t have been able to say with confidence that SHOW GLOBAL STATUS showed what that one query was doing; activity from other queries would have been mixed in there too. It would be great to be able to choose another thread and watch only its status, but MySQL doesn’t currently let you do that.)

The solution was to measure how fast the query was scanning rows in the table scan of the fact table. This is shown by the Handler_read_rnd_next status variable. Here’s an easy way to watch it (innotop is another handy way):

mysqladmin extended -r -i 10 | grep Handler_read_rnd_next
-- ignore the first line of output...
| Handler_read_rnd_next             | 429224      |

So the server was reading roughly 43K rows per second, and there were 150 million rows in the table. A little math later, and you get 3488 seconds to completion, or a little less than an hour. And indeed the query completed in about 55 minutes.

This is the simplest case, and there are more complicated ones to consider, but hopefully this gives you an idea how you can tackle this problem in different situations.

Jauzsika
  • 623
  • 1
  • 4
  • 10
  • 3
    `SHOW PROCESSLIST;` will also give you the [current state of the query](http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html). Some of these states can tell you how far through the query the MySQL server is. The state `optimizing` means you have barely started, `executing` and `Copying to tmp table` mean you are partway through and `Writing to net` and `Sending data` mean you are almost finished. – Ladadadada Jan 05 '12 at 12:47
  • 429224 is about 430K, not 43K – Joel Coel Dec 27 '14 at 23:48
0

It seems that it is not an easy task to estimate the progress of a running query. You can have a look at this post.

Other options I can emphasize:

  1. You can use a system tool like top to make sure it is still running (consuming CPU).
  2. It is recommended you divide your complex query to multiple queries if possible. In this case, you can simply write a script to execute multiple queries and echo some information between every two consecutive executions. This way you can know the job progress.
Community
  • 1
Khaled
  • 35,688
  • 8
  • 69
  • 98