CPU spiking - How I can find the culprit?

Well I can see that mysql is the problem here. It looks like you have multiple process of mysql running and one that constantly uses 40% of CPU.

I had this as well with WordPress on Ubuntu 14.04. When I upgraded to 16.04 the problem was fixed.

I also figure out that plugins like Jetpack do a lot of things at the same time on the background. They for example do a health check on the background that runs every minute. This is a pretty have check for WordPress and also for your server.

@bobbybouwmann Thanks for looking at my screenshots.

Sorry I should have mentioned I am running on Ubuntu 16.04.3 LTS and MySQL 5.7.21. So not sure how to find the rotten apple here.

Must be a plugin update then since I have not added any plugins to site around that time the CPU usage started climbing.

There's also been some reports on CPU performance taking a hit due to Meltdown and Spectre patches.

Edit: from Linode's blog

As a result of the mitigation put in place by the Linux kernel, there may be a small reduction in performance. The performance impact greatly depends on whether your workload is heavy on system calls and disk I/O.

https://linode.com/docs/security/meltdown-and-spectre/

Database performance is hit especially hard by the Meltdown and Spectre patches.

This would make sense however patches was implemented before I launched this server unless some other patches has been implemented without a reboot of my server. I have other servers which has been patched but not this one since it was launched after the patches on my other servers.

Gone from 40% to 240% in CPU. Rather odd.

Try using this useful script for determining CPU usuage:

https://askubuntu.com/questions/450045/show-cpu-usage-using-a-command

Check your web server

Tail your access log with tail -f /var/log/nginx/access_log and stick around for a bit, if lines are screaming past your screen that could mean someone is hammering the site, if that's the case, there is almost always a reason why (security vulnerability scanning, compromised plugin, etc), drop the -f and replace with -n 100 and find a pattern, remove/lock down whatever they're after.

Check your mysql slow query log

Enter SET GLOBAL slow_query_log = 'ON'; at the mysql prompt. DONT FORGET to turn slow_query_log back off when your done investigating, just change ON to OFF in that same query.

Then SET GLOBAL long_query_time = X; Where x is how many seconds, start low (1,2) and filter higher if you have too many results.

You can change where the log file goes if needs be. SET GLOBAL slow_query_log_file = '/var/lib/mysql/hostname-slow.log';

Misc

1. Make sure your server isn't accessible via IP (i.e. Networking Enabled); if it is; there is chance someone is hammering the sql server in attempt to brute force it.

2. If the server CPU is still spiking because of mysql and you can ssh in run SHOW FULL PROCESSLIST in mysql for near the same thing as the query log but you should get a full read out of any possible queries being ran.

3. If possible for your site use, something like https://wordpress.org/plugins/wp-super-cache/ will render the HTML via PHP and then keep a copy so any further requests don't need to hit the database each time.

@knox Nice guide!

Thanks to EVERYONE who has answered. All is very good and useful. If not for this problem then for any future issues.