How to fix MySQL high CPU usage running on VPS

Question

I manage a WordPress site on virtual private server with 2 vCore, 80GB SSD, 4GB of memory, Ubuntu 18.04, Apache 2.4.29, and mysql community version 8.0.20.

When started the site on the server, everything worked OK, but right after running database maintenance (simple table optimization), I’m noticing high processor utilization up to 100% of the total capacity for several second, and then goes down, and the goes up again, and it’s constantly like that. After restarting the server, the high CPU usage continues.

Using the htop command, I can see that the problem is always a mysql process.

I tried going to mysql, and I used the “Show processlist” command everything looks normal, and no query is hanging. If I run the command again, some WordPress related queries will be there, but redoing the command again, the queries complete successfully.

However, running queries like saving, publishing post, querying a list of posts, deleting posts the trash takes a very long time. We’re talking 10 to 20 seconds, and the homepage now takes a long time too.

I use a cache plugin and CDN, you for people the site is OK, but something isn’t right.

I tried disabling all the plugins and theme, but the CPU continues to spike up and down.

During the testing period, I turned on the “Optimize Database after Deleting Revisions” which I used for years. Trying to delete 46 pingback links 17 minutes, and the query completed successfully.

The biggest thing to note here is that before the site was running on Ubuntu 16.04 and similar hardware specs with an older version of mysql and apache, and the processor never broke a sweat (never high CPU).

During the time I spent researching, I was thinking that high processor usage was a database problem, but now, I’m thinking that it could be a mysql configuration issue.

For instance, inside /etc/mysql folder, there are two folders and four files, including conf.d and mysql.conf.d folders and Debian.cnf, my.cnf.fallback, mysql.cnf files, and there’s a my.conf file but it’s a symlink pointing to /etc/alternatives/my.cnf which then points to /etc/mysql/mysql.cnf.

Inside the conf.d/mysql.cnf file, the content appears as followed:
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysql]

Inside the mysql.cnf, this is the content:

# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Finally, inside the mysql.conf.d the only thing I see a mysqld.cnf file with text:

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log

IMPORTANT: The below content is from the old server, not new server with the problem. I'm mentioning it below to make a point that the new server doesn't have a configure, and I'm wondering if I can use the below config to fix the issue.

After seeing these configurations, I decided to look into one of the old backups to see the mysql configuration file, and I saw this:

In the content below, I omitted some comments to make this post a little shorter.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB

These are my questions now:

1. The high CPU usage with mysql is because the new serve doesn't any configuration like the old server?
2. Did I check the mysql setting correctly? (I couldn't the /etc/my.cnf.)
3. If the problem is configuration, can the old mysqld.cnf be use in the new server? I mean would be compatible with new server running Ubuntu 18.04 and mysql 8?
4. Can someone provide other suggestion and/or solution to this mysql problem?

Answer 1

key_buffer_size implies MyISAM. Don't use for MyISAM unless you have an overwhelmingly good reason (and it is a near certainty that you don't in 2020).

query_cache almost always does more harm than good, which is why it has been completely removed in MySQL 8.0. Set:

query_cache_type = 0
query_cache_size = 0

1) High mysqld CPU usage is almost never caused by configuration. It is typically caused by bad queries and bad indexing.

Having said that - you don't seem to have any explicit setting for innodb_buffer_pool_size. This should probably be set to about 2GB on a non-dedicated server of that size. Unless your data is quite tiny. How big is your data?

2) Yes you did.

3) Mostly. Some options may be deprecated and removed. Options that are no longer understood by mysqld typically cause it to fail to start and have to be removed from the config.

4) Enable

slow_query_log = 1
long_query_time = 0

Capture 24 hours of the log. It will be big. Process it using mysqldumpslow or pt-query-digest. Identify the slow queries and index them better where possible, or rewrite them into a more performant form where necessary.

Answer 2

Consider enabling 6GB of swap space on the server, from today's 0 available.

At Linux command prompt, ulimit -n 20000 and press Enter to enable additional Open Files by the OS. Currently limited to 1024 per ulimit -a report. This is dynamically applied, no restart required. Will be available with next stop/start of MySQL.

To make this persistent across OS shutdown/restart, follow this guide and use 20000 where the example is for 500000. https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

htop2 indicates there are many mysqld PID's occupied for multiple minutes, usually Sleeping, Our General Log Analysis would enable determining why this pattern exists.

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=100  # from 9 to reduce threads_created count (expensive operation)
innodb_io_capacity=1900  # from 200 to use more of your SSD io capacity
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 97
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS of 1,744

Observations, com_create_table in 4 days indicates 325,788 tables created RPhr of 3,284 for what purpose? There were no dropped tables reported during the 4 days.

Select_scan RPhr of 482 indicates indexes are missing. Our FAQ page includes Q. How can I find JOINS or QUERIES not using indexes? To assist with analysis and resolution.

Other opportunities exist to improve performance on your site. When time permits, get in touch, please.