I have a VPS server with CentOS 7 where I host a few websites for my clients. Recently I noticed my server is getting overloaded. I used Linux: top command to check resource usage, from there I find out that MySQL is taking most of my CPU resources. Now can you tell me which website or the database is causing the problem?
I assume you are already connected with ssh. From there you need to connect with MySQL
mysql -u root -p
Then type the following command
SHOW PROCESSLIST;
or
SHOW FULL PROCESSLIST;
This should give you the queries that are running at that moment. Check, is there a higher number of queries than normal?
You can also use
SHOW OPEN TABLES;
This will show you what’s in the table cache, but this is not going to tell you what is using up most of the resources. It will show you the most active databases/tables.