How to detect sudden bottleneck in mysql with 2000%CPU 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
You should use SHOW FULL PROCESSLIST in MySQL console to display all running queries.