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?