Solving MySQL 100% CPU

in #linux5 years ago (edited)

Background

I'm facing this issue with my cloud server having high load for a few Wordpress sites:

  • The mysqld.bin process is constantly taking up > 150% CPU, and
  • Available memory is constantly low, with about 90,000 KiB Mem left.

Considering my server capacity is relatively huge (2 vCPU, 14GB RAM and premium SSD on Azure) this the load doesn't make sense at all.

Cause

After researching for a while, I realized the issue caused by MySQL has no memory to write to. With the Buffer Usage above 95%

Solution

Increase SWAP space for the MySQL and other processes on Azure server.

To create a swap file in the directory that’s defined by the ResourceDisk.MountPoint parameter, you can update the /etc/waagent.conf file by setting the following three parameters:

ResourceDisk.Format=y
ResourceDisk.EnableSwap=y
ResourceDisk.SwapSizeMB=4096

Source

After that, restart the waagent service.

For Ubuntu:
sudo service walinuxagent restart

For CoreOS, please execute the command,
sudo systemctl restart waagent

For other linux distributions, please execute the command,
sudo service waagent restart

If the changes not applied, try restart the server
sudo shutdown -r now

Source

Results:

The InnoDB Buffer usage is healthy now at about 52%.
Screenshot 2019-05-30 at 5.57.31 PM.png

And the server load is at 1.69 now.
Screenshot 2019-05-30 at 5.41.03 PM.png