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


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

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

socket      = /var/run/mysqld/mysqld.sock
nice        = 0

# * 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
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        =
# * 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
# 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?
  • Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Commented May 6, 2020 at 21:23
  • 1
    I created a dropbox folder with the information requested. dropbox.com/sh/190cw2lpfh86utg/AADfHlM54VSjAvfdZggIqITLa?dl=0
    – mhweb
    Commented May 8, 2020 at 0:41
  • 1
    I also shared an image with the CPU usage now, and how it used too look before.
    – mhweb
    Commented May 8, 2020 at 0:54
  • Welcome to dba.stackexchange.com Your analysis is in process. I will try to post suggestions within 24 hours. What is your UTC zone? We are in UTC -6 hrs. Please view our profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning. Commented May 8, 2020 at 12:46
  • 1
    Thank you. I'm in Eastern Time Zone. I want to emphasise that I never experience the high CPU usage. When I created the new server, I created a copy of it, then in the original server, I tried to run optimize table command in mysql, and then CPU started acting up like now. I destroyed that server, and created another one from the backup, I didn't do any table optimization, and everything was fine. Weeks later, I tried running optimization using a plugin, and since high CPU usage when running the command manually.
    – mhweb
    Commented May 8, 2020 at 13:39

2 Answers 2


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.

  • 1
    Thanks for your reply. Out of curiosity, it was understood that the current server doesn't have any configuration and last block under "In the content below, I omitted some comments to make this post a little shorter." was from the Old server, right?
    – mhweb
    Commented May 6, 2020 at 0:45
  • 1
    The data is about 105mb.
    – mhweb
    Commented May 6, 2020 at 0:46
  • 1
    It'll grow over time, slowly by it'll grow (10 to 20mb per year). Should I still use the same value?
    – mhweb
    Commented May 6, 2020 at 13:52
  • 1
    Any reason why the new server doesn't have any configuration? Is this the default behavior?
    – mhweb
    Commented May 6, 2020 at 13:53
  • 1
    I'm going to give your suggestions a try. Thanks for taking the time.
    – mhweb
    Commented May 6, 2020 at 17:06

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.

  • I appreciate the time for suggesting these modifications. I understand about the swap, but I won't be able to do it, because DO guarantees hardware problems doing so on SSD servers. I'll schedule a maintenance to test the rest of the settings. Do I have to apply settings in this order? Or apply one and test and the other settings in any order? Thank you again.
    – mhweb
    Commented May 8, 2020 at 19:48
  • I don't know if you can answer this question, but why for weeks the server worked find, and then because trying to optimize the database the CPU starts acting up. Also, using your suggestions are we trying to mitigate or solve the problem?
    – mhweb
    Commented May 8, 2020 at 19:50
  • I will be available tomorrow by Skype. Commented May 8, 2020 at 21:47
  • @mhweb One more change needed in my.cnf [mysqld] section. internal_tmp_mem_storage_engine=MEMORY. For reference information refer to Question 267143 on this site. Will be corrected in next release 8.0.21, whenever it is made available. This is a covering workaround for 8.0.20. CPU will be lowered and performance improved. Commented May 16, 2020 at 19:57
  • Thanks for the follow up, but is the bug also included with version 8.0.19? I had the issue with version 8.0.19 and then updated to .20 and the issue continue. I have transferred the site to a new server and mysql is working fine now, but I know as soon as I run maintenance, the problem with return.
    – mhweb
    Commented May 16, 2020 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.