Высокое потребление ресурсов процессора MySQL

Question

У меня есть новостной портал WordPress с easyengine + wp + redis, и у меня бывают проблемы с производительностью, мой MySQL потребляет много ресурсов процессора. У меня есть более 500 000 сообщений, кто-нибудь, чтобы помочь мне решить эту проблему?

распечатать процессор https://prnt.sc/jgkpwy

сведения о сервере Ubuntu 16.04 - минимальный - RAID1 Жесткий диск 256 ГБ SSD 2,5"Жесткий диск 256 ГБ SSD 2,5" ОЗУ 32 ГБ Процессор Intel Xeon E3-1225v3

MySQL конфигурация 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

Предложения для вашего раздела my.cnf-ini [mysqld] для рассмотрения

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

Держите нас в курсе, пожалуйста.

Если вы хотите опубликовать текстовые результаты ulimit -a и SHOW GLOBAL STATUS; и ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ; Дополнительные полезные предложения могут быть предоставлены.

Answer 2

Предложения для вашего раздела my.cnf-ini [mysqld]
ПОСЛЕ использования SET GLOBAL variable_name=value; чтобы убедиться в правильных и допустимых пределах, дайте поработать в течение минуты, прежде чем переходить к следующей переменной, всегда проверяйте журнал ошибок перед следующим SET GLOBAL, чтобы убедиться, что никакого вреда не было, ТО, когда вы находитесь в конце списка, отредактируйте my.cnf- Ини с успешными значениями переменных, выключение / перезапуск.

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, возможно, придется остаться на ДА, см. Refman.

Я хотел бы наблюдать за вашим тестированием и внедрением через SKYPE и teamviewer, пожалуйста. Сегодня, если возможно (рано).

Дополнительные изменения my.cnf-ini [mysqld], которые НЕ могут быть динамически установлены и протестированы, включают:

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  

Дополнительный анализ найдет больше возможностей.

Answer 3

Все медленные запросы, которые вы упомянули, включают wp_postmeta, У WP очень неэффективная схема для этой таблицы. Я наметил несколько исправлений для этого здесь. Если после улучшения этой схемы все еще есть проблемы, мы можем обсудить дальнейшие шаги.

Вы не можете отрегулировать выход из проблемы высокой загрузки процессора. Однако есть один параметр, который слишком высок:

query_cache_size = 256M -- Change to 50M

Для удобства чтения вот один из медленных запросов:

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 

Пожалуйста, используйте -s t когда используешь mysqldump,

Addenda

Учитывая это:

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))

Это может принести вам максимальную пользу при минимальных усилиях:

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);

Внимание: это займет некоторое время, чтобы выполнить ALTER; стол будет заблокирован.

Если это ALTER приводит к "Дублирующему ключу", очевидно, у вас есть один и тот же "ключ" несколько раз для данного "поста". Или

• Ошибка при вводе данных. В этом случае данные должны быть очищены. Или же...
• Вы намеренно имеете повторный meta_key. В этом случае измените строку PK на ADD PRIMARY KEY(post_id, meta_key, meta_id),,