CODEWITHSUNDEEP
mysqlprocesslong-integer
user87843
user87843

Reputation: 91

How to tell if a MySQL process is stuck?

I have a long-running process in MySQL. It has been running for a week. There is one other connection, to a replication master, but I have halted slave processing so there's effectively nothing else going on.

How can I tell if this process is still working? I knew it would take a long time which is why I put it on its own database instance, but this is longer than I anticipated. Obviously, if it is still doing work, I don't want to kill it. If it is zombied, then I don't know how to get the work done that it's supposed to be doing.

It's in the "Sending data" state. The table is an InnoDB one but without any FK references that are used by the query. The InnoDB status shows no errors or locks since the query started.

Any thoughts are appreciated.

Upvotes: 9

Views: 20649

Answers (4)

Ty Smith
Ty Smith

Reputation: 2594

For better performance on a database that size, you may want to look at a document based database such as mongoDB. It will take more hard drive space to store the database, but depending on your current schema, you may get much better performance.

Upvotes: 0

Donnie
Donnie

Reputation: 46903

Given what you've said, it's not stuck. However, the is absolutely no guarantee that it will actually finish in anything resembling a reasonable amount of time. Adding indicies will almost certainly help, and depending on the type of query refactoring it into a series of queries that use temp tables could possibly give you a huge performance boost. I wouldn't suggest waiting around for it to maybe finish.

Upvotes: 1

tpdi
tpdi

Reputation: 35141

You need to kill it and come up with better indices.

I did a job for a guy. Had a table with about 35 million rows. His batch process, like yours, had been running a week, with no end in sight. I added some indexes, made some changes to the order and methods of his batch process, and got the whole thing down to about two and a half hours. On a slower machine.

Upvotes: 4

dkretz
dkretz

Reputation: 37645

Try "SHOW PROCESSLIST" to see what's active.

Of course if you kill it, it may then want to take just as much time rolling it back.

Upvotes: 8

Related Questions