Question

Help me trubleshooting Mysql High CPU

Hello Guys,

I’m running few websites on my droplet (8G, 4vcpu) and one of my websites causing extremely high CPU usage (it has only a few simultaneaus visitor). MySql is guilty, accoring to htop. When I turn off this website, everything goes back to normal. I have tried everything to identify the issue, but it comes back time-to-time (sometimes it is good for weeks, then the high CPU load comes back. High = 100% always, according to the DO draphs.)

I have tried SHOW PROCESSLIST; but I cant see too much connections, or slow queries (basically the site is fast, most of the times, but not always!) i have tried to play with my.cnf, one of my friends fine-tuned it, then it was good for few weeks, then since a few days the graph touched the ceiling again.

I’m using cloudflare and a bit suspicious that It I see very high bandwidth usage and high Traffic (users, crawler bots, threats included) (High = compared to my other sites which has much more traffic (measured by Google Analytics). I cant see uncommon number in the number of threats. I’ve tried to turn on the Cloudflare “im under attack” mode, but the cpu usage is the same.

Any suggestions ? should I copy my.cnf here ? I have a feeling that I will find the answer somewhere else.

Thank you guys for the help !!!


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Hi there @arvaizsolt,

This an interesting case. Have you tried to check your Apache/Nginx access logs and see if there are any strange requests there?

You could use this script to use this script here to summarize your access log.

Another thing that you could try doing is to install caching for this specific site, that way it would reduce the number of queries that it generates to MySQL.

Also when you run SHOW PROCESSLIST; how many queries on average do you see and for how long are they running for? If there are some big selects, what I could suggest is adding indexes in order to optimize the queries.

Regarding the MySQL optimization, you could try running the MySQL tunner script and see if it suggests any optimizations, you can take a look at this answer here for more information:

https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

Another thing you could do is move this 1 website to its own Droplet so that it does not affect the other sites, and then you could install Varnish for example in order to improve the performance of the site:

https://www.digitalocean.com/community/tutorials/how-to-configure-varnish-cache-4-0-with-ssl-termination-on-ubuntu-14-04

Let me know how it goes! Regards, Bobby

Hey @arvaizsolt

Generally speaking the things that cost MySQL CPU cycles are internal functions. Things such as sorting, filtering, math, etc. It has always been the case in my experience that high CPU is down to SQL queries and almost always sorting and grouping. The simplest thing you can do is to capture your query-load during these spikes of CPU usage. You should look to turn on the Slow Logging and set the Long Query Time to 0s to grab all queries. Once you’ve got a good sample of the queries then use the pt-query-digest tool to process and rank your queries. This is going to provide you with a good idea of what’s going on and which queries are taking your resources.

In summary;

  • It’s likely poorly indexed SQL that’s causing the problem
  • Turn on Slow Query Logging
  • Run pt-query-digest to generate slow query report
  • Tune SQL
  • Review impact on Server resources

Good luck.

Andrew

Thanks ! I’m going to try out these optimization tricks. Moreover I will try to monitor Crawlers somehow.