2

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?
  • 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. – Wilson Hauck May 6 '20 at 21:23
  • 1
    I created a dropbox folder with the information requested. dropbox.com/sh/190cw2lpfh86utg/AADfHlM54VSjAvfdZggIqITLa?dl=0 – mhweb May 8 '20 at 0:41
  • 1
    I also shared an image with the CPU usage now, and how it used too look before. – mhweb May 8 '20 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. – Wilson Hauck May 8 '20 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 May 8 '20 at 13:39
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.

  • 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 May 6 '20 at 0:45
  • 1
    The data is about 105mb. – mhweb May 6 '20 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 May 6 '20 at 13:52
  • 1
    Any reason why the new server doesn't have any configuration? Is this the default behavior? – mhweb May 6 '20 at 13:53
  • 1
    I'm going to give your suggestions a try. Thanks for taking the time. – mhweb May 6 '20 at 17:06
0

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 May 8 '20 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 May 8 '20 at 19:50
  • I will be available tomorrow by Skype. – Wilson Hauck May 8 '20 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. – Wilson Hauck May 16 '20 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 May 16 '20 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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