High MySQL CPU usage after a DDOS attack

Solution 1:

maybe just reboot the whole vm..

things you could check before:

• is the memory full and the system already in swap ( free -m ) , you could try drop caches sysctl -w vm.drop_caches=3 , and then try to lower swappines or even swapoff -a , since once the system is in swap , there is a high disk load , and all your latency goes up
• check if somebody still has access to your site ( e.g. exploits , remote code, sql injection )
• you might still have many open tcp connections , try Cutter
• if your database tables got heavily fragmented or just too large , you might investigate with mytop
• if cutter / reboot / restart does not help , ty to investigate with mysql slow logs if something drains your performance

Solution 2:

The most likely explanation is that the DDoS resulted in some of the tables growing massively bigger as a consequence of the DoS. Check your table sizes for large unexpected growth since your most recent backup.

What probably happened was that you had poorly written queries operating on those tables and doing full table scans or something similarly inefficient. As those tables got big, the performance of those poorly written queries tanked. Install mytop and look at what queries are running and eating all the CPU - the chances are there are only 1 or 2 showing repeatedly. Fix those, or see what tables they are using and prune the data in the tables, and you should be back to normal operation.

I hope this helps and saves you upwork costs. ;-)