Help me trubleshooting Mysql High CPU

Posted February 23, 2020 6.5k views

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 !!!

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.

Submit an Answer
3 answers

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:

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:

Let me know how it goes!

by Mitchell Anicas
Varnish Cache is a caching HTTP reverse proxy, or HTTP accelerator, which reduces the time it takes to serve content to a user. In this tutorial, we will cover how to use Varnish Cache 4.0 to improve the performance of your existing web server. We will also show you a way to add HTTPS support to Varnish, with Nginx performing the SSL termination.

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

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.