Slow SQL DB after running query

Question

After running a specific query in my DB, the entire DB is very very slow.

I can see a huge decrease in performance on my 1.9.3.3 store.

I did run the following query, which did not complete:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `index_event`;
SET FOREIGN_KEY_CHECKS=1;

I got the error; Cannot truncate a table referenced in a foreign key constraint

After that, the DB is very slow. Load on pages on backend and frontend +2 sec.

What causes this issue and how can I solve this?

UPDATE: When enabling Product Flat Data index, I get the error that there was a error with indexing. This worked perfectly before.

Answer 1

The easiest way would be to kill the query. Get the running queries using:

show processlist;

Find your query there and terminate it (the query number is display in the first column):

kill QUERY "QUERY NUMBER";

• JGeer: Thanks! Well that is the problem, there is not a specific query running right now. I already restarted my VPS, to check if that kill the problem. But that did not help also. The only process that is running is the mysqld process itself.
• Niels: Ah OK, that's weird. Did you try to restart the mysql daemon also? sudo service mysql restart, or /etc/init.d/mysqld restart?
• JGeer: Yes, I restarted the entire VPS, including mysql. It looks like the performance of the DB is decrease.
• JGeer: See my updated question.
• Niels: Could you check if there is anything in var/locks/? If so, could you delete the files and try the reindexing process again? Might be possible that the index was stopped while it was running.