0

I have a mysql instance running on my windows dev box that is using 100% disk i/o with no active query.

SHOW FULL PROCESSLIST;

Process List

SHOW ENGINE INNODB MUTEX;

Mutex List

If I refresh the mutex list the count in the last row keeps increasing.

How I got to this point is I ran a complicated query on a set of large tables with a number of inner joins. I let the query run all night and in the morning decided to kill it as I needed to do other things. But even after query was killed the disk I/O was still 100%. I rebooted and still same result. It is like the query is still running.

What is going on here? Is mysql simply recovering temporary tables from the query I previously ran? Do I have any other option other than to let it continue to run?

Improve this question
9
  • I think you're right that it's cleaning up lots of temporary data from the query.
    – Barmar
    Commented Nov 7 at 22:30
  • InnoDB does a number of things in the background even when you are not running queries. It merges the change buffer into indexes, it flushes changed pages from the buffer pool to disk, it purges old row versions from the undo buffer, etc. This work often persists after a reboot. It sounds like you need to upgrade your disk system to something more high-performance, given your database demands.
    – Bill Karwin
    Commented Nov 7 at 22:44
  • Also I would be suspicious of any query that takes all night to run, even on a large database. That makes me think you have not optimized the query enough with the proper indexes. No way to know for sure given the information you have provided (i.e. no query, no EXPLAIN for the query, no table definitions). The query optimization is a subject for a different Stack Overflow question.
    – Bill Karwin
    Commented Nov 7 at 22:46
  • @BillKarwin Yes, well this database is a copy of the 14GB production database that I copied to my dev box to do testing. The queries that I was running were designed to prune the tables back to something reasonable. So they were all of the form DELETE n FROM note AS n LEFT JOIN ...LEFT JOIN ...WHERE l.created_ts < @cutoff; They all worked fine except for the one I had to kill.
    – AQuirky
    Commented Nov 7 at 23:14
  • So your dev box undoubtedly has a totally different type of storage, right?
    – Bill Karwin
    Commented Nov 8 at 1:16

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.