0

I have a webserver hosting about 300 websites and sometimes my CPU usage gets close to 100% and when I use "top" in my CENTOS to check who is using all CPU, I discover it's mysql. I would like then to know what website is causing that high mysql CPU usage. With Apache I can know exactly which virtual domain is consuming my cpu, ram, bandwidth... but I have no idea how to do that with mysql. Any idea?

migrated from serverfault.com Feb 24 '15 at 7:29

This question came from our site for system and network administrators.

1

You could check your slow query log. If it's CPU intensive, it may be some heavy hitting queries that take a while to process. Check to make sure your slow query threshold is set to a reasonable amount of time and then look at your log.

When your server is in the middle of a CPU spike, log in and do show full processlist to look at the queries that are running at that moment. Is there a higher number of queries than normal? Is there a high number of slow queries than normal?

You could use show open tables which will show you what's in the table cache, but it doesn't necessarily tell you what is using up all the resources, but rather which databases/tables are the most active.

  • how do I check the slow query log? – Gil Feb 20 '15 at 18:17
  • Check the following three variables: slow_query_log, slow_query_log_file, and long_query_time. Look those up in the MySQL documentation and it'll tell you everything you need. – Safado Feb 20 '15 at 18:32

Your Answer

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