1

Our nginx-mysql server has been running smoothly for months (even years) . All of the sudden mysql consume around 700-2000% CPU , very little RAM. even after mysql restart, the usage goes up right off. Nginx CPU/RAM usage is minimal (since the traffic is just at 20% of peak). Turning off nginx solved it proving some queries were having problem. The question is how to pinpoint the troublemaker one. any command or query to do so (from bash, nginx, log etc..)?

FYI: I did optimize and repair all the tables but did not work. The problem was solved when I truncate 2 "middle size" table of 2M rows. Those 2 tables used to behave normally with 20M+ rows, and they have good indexes... I did purge/truncate the large tables from time to time.

Edit 1: mysql-slow.log did help a little but not right to the point

4
  • Are you using MyISAM or InnoDB as the engine? Commented Dec 27, 2017 at 6:50
  • @TeroKilkanen : I think it is MyISAM. most of the tables are myIsam Commented Dec 27, 2017 at 12:57
  • You should convert to InnoDB, in general it always outperforms MyISAM. Commented Dec 28, 2017 at 1:03
  • Turn on the slowlog. When you find some slow queries, let's discuss them. Possibly the solution is as simple as adding an index.
    – Rick James
    Commented Jan 5, 2018 at 2:07

1 Answer 1

1

You should use SHOW FULL PROCESSLIST in MySQL console to display all running queries.

0

Your Answer

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

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