High CPU consumption MySQL

Question

I have a wordpress news portal with easyengine + wp + redis and come to days with a performance problem, my mysql is consuming a lot of cpu usage I have more than 500k posts someone to help me solve this problem?

print cpu https://prnt.sc/jgkpwy

server details Ubuntu 16.04 - minimal - RAID1 Harddisk 256 GB SSD 2.5" Harddisk 256 GB SSD 2.5" RAM 32 GB CPU Intel Xeon E3-1225v3

mysql configuration my.cnf

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port  = 3306
socket  = /var/run/mysqld/mysqld.sock

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

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[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
lc_messages = en_US
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
#
max_connections  = 300
connect_timeout  = 5
wait_timeout  = 600
max_allowed_packet = 16M
thread_cache_size       = 500
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size  = 32M
max_heap_table_size = 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size  = 128M
#open-files-limit = 2000
table_open_cache = 600
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit  = 2M
query_cache_size  = 256M
query_cache_strip_comments =1
# for more write intensive setups, set to DEMAND or OFF
query_cache_type  = 1
#
# * 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 logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings  = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# 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
#report_host  = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
#log_bin   = /var/log/mysql/mariadb-bin
#log_bin_index  = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog  = 1
#expire_logs_days = 10
#max_binlog_size         = 100M
# slaves
#relay_log  = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode  = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * 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!
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size = 50M
innodb_buffer_pool_size = 15G
innodb_log_buffer_size = 16M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 15
#
# * 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

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer  = 16M

#
# * 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/

Answer 1

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

sort_buffer_size=4M  # REMOVE or LEAD with # for default
read_buffer_size=2M  # REMOVE or LEAD with # for default
read_rnd_buffer_size=1M  # REMOVE or LEAD with # for default
innodb_buffer_pool_instances=8  # from 15 will be adequate
thread_cache_size=100  # from 500 see V8 refman CAP at 100 to avoid OOM
innodb_lru_search_depth=128  # from 1024 to be = innodb_buffer_pool_instances
innodb_purge_threads=4  # from likely 1 to support DELETE cleanup
max_write_lock_count=16  # from HUGE # to conserve CPU cycles
query_cache_min_res_unit=512  # from 4096 to support more QC results

Keep us posted on progress, please.

If you would post text results of ulimit -a and SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; additional helpful Suggestions could be provided.

Answer 2

Suggestions for your my.cnf-ini [mysqld] section
AFTER using SET GLOBAL variable_name=value; to ensure correct and tolerated limits let run for a minute before moving to the next variable, check error log always before next SET GLOBAL to ensure no harm has been done, THEN when you are at the end of the list EDIT your my.cnf-ini with the successful variable values, shutdown/restart.

innodb_buffer_pool_size=18*1024*1024*1024  # from 15G for growth
innodb_io_capacity=1000  # from 400 for additional IOPS capacity
read_rnd_buffer_size=128*1024  # from 256K to reduce handler_read_rnd_next
read_buffer_size=256*1024  # from 128K to reduce handler_read_rnd
tmp_table_size=128*1024*1024  # from 32M to expand tmp RAM tbls
max_heap_table_size=128*1024*1024  # from 32M to reduce created_tmp_disk_tables
innodb_lru_scan_depth=128  # from 1024 to reduce CPU use
query_cache_size=50*1024*1024  # from 256M to reduce QC overhead on CPU
query_cache_limit=512*1024  # from 2M for max query result to be stored
updatable_views_with_limit=0  # from YES to reduce handler_external_lock

UVWL may have to stay on YES, see refman.

I would like to observe your testing and implementation via SKYPE & teamviewer, please. Today, if possible (early).

Additional my.cnf-ini [mysqld] changes that may NOT be dynamically set and tested include:

thread_concurrency=30  # from 10 to allow more concurrency
expire_logs_days=5  # from 0 for limited historical logs
key_cache_age_threshold=64800  # from 300 seconds discard to RD again
key_cache_division_limit=50  # for HOT and WARM usage boundary
key_cache_block_size=16384  # from 1024 to age out larger size  

Additional analysis will find more opportunities.

Answer 3

All of the slow queries you mentioned involve wp_postmeta. WP has a very inefficient schema for that table. I outline several fixes for it in here . If there are still issues after improving that schema, we can discuss further steps.

You cannot tune your way out of a high CPU problem. However, there is one setting that is too high:

query_cache_size = 256M -- Change to 50M

For the sake of readability, here is one of the slow queries:

SELECT  wp_posts.ID
    FROM  wp_posts
    LEFT JOIN  wp_term_relationships
          ON ( wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN  wp_postmeta  ON ( wp_posts.ID = wp_postmeta.post_id )
    INNER JOIN  wp_postmeta AS mt1  ON ( om16_posts.ID = mt1.post_id )
    WHERE  1=1
      AND  ( wp_term_relationships.term_taxonomy_id IN (19,37, 38,72) )
      AND  ( wp_postmeta.meta_key = '_thumbnail_id'
              AND  ( mt1.meta_key = 'exibir_menu'
                      AND  mt1.meta_value = '1' ) 
           )
      AND  wp_posts.post_type = 'post'
      AND  ((wp_posts.post_status = 'publish'))
    GROUP BY  wp_posts.ID
    ORDER BY  wp_posts.post_date DESC
    LIMIT  0, 7 

Please use -s t when using mysqldump.

Addenda

Given this:

CREATE TABLE `om16_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `idx_meta_key` (`meta_key`(50)),
  KEY `meta_key` (`meta_key`(191))

This may give you the most benefit with the least effort:

ALTER TABLE om16_postmeta
    DROP PRIMARY KEY,
    DROP INDEX post_id,
    ADD PRIMARY KEY(post_id, meta_key),  -- much better for queries
    ADD INDEX(meta_id),         -- sufficient for AUTO_INCREMENT
    DROP INDEX idx_meta_key,    -- prefix indexing unnecessary an inefficient
    DROP INDEX meta_key,
    ADD INDEX(meta_key);

Caution: It will take some time to perform the ALTER; the table will be blocked.

If that ALTER leads to "Duplicate key", apparently you have the same "key" multiple times for a given "post". Either

• There is a mistake in entering the data. In this case the data should be cleaned up. Or...
• You deliberate have a repeated meta_key. In this case, change the PK line to ADD PRIMARY KEY(post_id, meta_key, meta_id), .