1

I'm managing a server with medium to high traffic.

It serves a wordpress website.

It seems that it use a lot of CPU (over 300% at some points) and always stack around 15% of memory usage.

It is a MySQL 5.7 server with InnoDB engine under nginx and php7-fpm.

Server has 16GB RAM

I tried to tune with mysqltuner but that doesn't help

Here is my mysqld.cnf

[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     = 64M
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        = 192M
query_cache_type        = 1
innodb_buffer_pool_size = 10G

#
# * 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/lib/mysql/Ubuntu-1604-xenial-64-minimal-slow.log
slow_query_log_file     = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
#long_query_time = 5
#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
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Let me know what to tune on the above

Kind Regards

Update

The problem was solved but not on the current server. I created an image of the VPS and requested another virtual machine. Copied that on the new server and the problem solved. I can only imagine that there was a faulty hard drive on the initial machine.

I accepted @Rick James answer as correct because after debugging mysql slow query log, as he suggested, I found that all the slow queries reported didn't make any sense.

  • Downvote? Why? Isn't my question legit or something? – fat_mike May 17 '18 at 15:52
  • Additional information request, please. Post on pastebin.com or here. complete (not edited) my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) complete MySQLTuner.com report if readily available - 7 or more days uptime is helpful D) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck May 18 '18 at 20:50
  • 1
    @WilsonHauck Thank you for the comment. I'll collect the information and edit the answer the next few days because we restarted MySQL few hours ago – fat_mike May 19 '18 at 1:51
  • For the first cut, 1 full business day or more will be enough to get the most significant tuning items uncovered. As a matter of fact, if you only post thru C) Mysqltuner repot after at least 1 full business day, we can get the most significant details on the table. – Wilson Hauck May 19 '18 at 5:15
  • Rather than adding your own Answer OR adding a Comment with your links to data, Edit your Question with a line for the Link Content with a date, such as "my.cnf as of May 19" and then paste your link, please. – Wilson Hauck May 19 '18 at 13:09
1

There are inefficiencies in the Query cache.

query_cache_size        = 192M

Change that to 50M.

That is one of the few cases where tuning can help with "high CPU". Usually you need to check the indexes and query formulation. So...

Find a couple slow queries, present to us SHOW CREATE TABLE for the table(s) involved, and EXPLAIN SELECT .... Then we can continue this discussion.

Oh, good; I see that the slowlog is already on, with a useless value for long_query_time; change it to 1. After a day, use pt-query-digest (or mysqldumpslow -s t) to find the 'worst' queries.

  • All the slow queries reported don't make any sense. Shouldn't be slow. See my updated question and why this is the correct answer to MY question. – fat_mike Sep 9 '18 at 17:46

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.