2

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.

2

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";

6
  • 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. – JGeer Jun 26 '17 at 12:05
  • Ah OK, that's weird. Did you try to restart the mysql daemon also? sudo service mysql restart, or /etc/init.d/mysqld restart? – Niels Jun 26 '17 at 12:12
  • Yes, I restarted the entire VPS, including mysql. It looks like the performance of the DB is decrease. – JGeer Jun 26 '17 at 12:30
  • See my updated question. – JGeer Jun 26 '17 at 12:47
  • 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. – Niels Jun 26 '17 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.