Есть сайт, сделан на Вордпрессе.
Проблема : приблизительно 1-3 раза в неделю, в бизнес-время( после 10:30 и до 16:00) сайт начинает долго открываться, выдавать через раз 502 ошибки, причем количество отказов быстро возрастает

Все это сопровождается тем, что mysql при этом резко начинает потреблять процессорный ресурс( с обычных 50-90% по top до 300 - 600%).
Нагрузка в бизнес-время: около 1500 запросов к MySQL в секунду
Хостится все это на нашем собственном сервере, сервер - виртуальная машина (гипервизор Proxmox) , 24 ядра и 28 ГБ ОЗУ(это сейчас, когда проблема проявилась первые разы, было 16 ядер и 16 ГБ ОЗУ).

Софт: Операционка Debian 8, nginx 1.6.2 в связке с php-fpm( версия php 5.6.14), mysql 5.5.44

Еще симптоматика:
При возрастающем потреблении процессорного ресурса в processlist видно от 100 до 200 запросов в состоянии waiting_for_query_cache_lock
Висит каждый из них в таком состоянии недолго, но если все оставить как есть, то количество запросов с таким состоянием увеличивается, потребление процессорного ресурса процессом mysqld возрастает,
и в syslog сыпяться ошибки типа BUG: soft lockup - CPU#10 stuck for 22s [php-fpm:20108]
Сайт превращается в однгу сполшную 502 ошибку, сервер может даже не отвечать на команды в консоли

Зато если на 30-60 секунд сайт отключить от базы данных, то запросы завершаются, и вновь подключенный сайт к БД спокойно может работать от нескольких часов до пары суток, пока вновь не произойдет такая ситуация

Как я понимаю, при возрастающем потреблении процессорного ресурса демоном mysql этого ресурса не хватает процессам php-fpm, которые в свою очередь вовремя не отдают ответ nginx, и возникает 502 ошибка
Чем меньше ресурса СPU для php-fpm - тем больше запросов к nginx возвращаются с ошибкой

Что делалось:
Во-первых, мы попробовали отказаться совсем от кэша запросов и вынесли каталог с временными таблицами(/dev/shm) в ОЗУ, но при отключенном кэше mysql сразу же жрет процессор до 600-1000%,
processlist показывает несколько сотен выолняющихся одновременно запросов с разным состоянием, время выполнения до 2 сек.,
сайт по сути не работает
Кэш вернули, поигрались с размером(от 32 до 96 МБ), на проблему это никак не повлияло, увеличили query_cache_min_res_unit с дефолтных 4 Кбайт до 8, чтобы избежать разбиения больших результатов запросов на большое количество блоков в кэше
Практически никак не повлияло.
Уменьшили wait_timeout до 2 сек., чтобы отсечь долго выполняющиеся запросы(хотя их и так не было ни в processlist, ни в slow-log)
Сайт продолжил работать как ни в чем не бывало - в вечернее, ночное время все страницы открывались без проблем, днем преиодически возникали описанные выше ситуации


my.cnf
автор
#
# The MySQL database server configuration file.
#
# You can copy this 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 = /dev/shm
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
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 256M
max_allowed_packet = 256M
wait_timeout = 2
thread_stack = 192K
thread_cache_size = 256
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 2046
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
#query_cache_limit = 10M
#query_cache_size = 128M
#
# * 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
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 20
log_queries_not_using_indexes

# * 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!
innodb_buffer_pool_size = 8192M
innodb_flush_log_at_trx_commit = 2
thread_cache_size = 16
#
# * 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


#skip-name-resolve

query_cache_limit = 10M
#query_cache_size = 128M
query_cache_size = 64M
query_cache_type = 1
query_cache_min_res_unit = 8192

max_heap_table_size=128M
tmp_table_size=128M


key_buffer_size=128M
key_cache_division_limit=70


[mysqldump]
quick
quote-names
max_allowed_packet = 256M


Какие еще будут идеи?
П.С. Поменять переписать сайт на другом движке не предлагать - это довольно очевидное решение, но у него слишком много издержек.

ScareCrow,

У нас же запись запросов без использования индексов и так велась, это видно из конфига.

по сути, у нас там постоянно только одна группа запросов, к стандартной вордпрессовской таблице termmeta, в которой у нас одна строка, а вордпресс периодически (раз в несколько секунд) делает запрос типа
SELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (933) ORDER BY meta_id ASC

при этом индекс для поля term_id присутствует, просто такого значения term_id=933 нет и никогда не было в таблице(и соответственно, в индексе), из-за этого, как я понимаю, запрос попадает в slow-log

Но опять же, повторюсь: подобных запросов(без индексов) в логе где-то один за несколько секунд(при порядка 1500 общего числа запросов в секунду), и время выполнения его либо миллисекунды либо доли миллисекунд.

Вы думаете, в этом может быть одна из причин проблемы с производительностью?

Вот результаты прогона mysqltuner

>> MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net>;
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Performing tests on 127.0.0.1:3306
[OK] Logged in using credentials passed on the command line
[!!] failed to execute: SHOW SLAVE STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW SLAVE HOSTS
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 5.5.44-0+deb8u1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 772M (Tables: 48)
[--] Data in MyISAM tables: 260K (Tables: 6)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE TRIM(USER) = '' OR USER IS NULL
[!!] FAIL Execute SQL / return code: 256
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE CAST(password as Binary) = PASSWORD(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as Binary) = PASSWORD(UPPER(LEFT(User, 1)) + SUBSTRING(User, 2, LENGTH(User)))
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE HOST='%'
[!!] FAIL Execute SQL / return code: 256
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 1h 24m 45s (286M q [1K qps], 1M conn, TX: 1224G, RX: 34G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 28.5G
[--] Max MySQL memory : 13.7G
[--] Other process memory: 36.7G
[--] Total buffers: 8.3G global + 2.7M per thread (2046 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.4G (33.09% of installed RAM)
[OK] Maximum possible memory usage: 13.7G (48.02% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (33K/286M)
[OK] Highest usage of available connections: 20% (424/2046)
[OK] Aborted connections: 0.26% (4370/1682146)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 4M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 80% (1M on disk / 2M total)
[!!] Table cache hit rate: 2% (400 open / 15K opened)
[OK] Open file limit used: 0% (5/10K)
[OK] Table locks acquired immediately: 99% (51M immediate / 51M locks)
[OK] Binlog cache memory access: 99.73% ( 453472 Memory / 454700 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/23.0K
[OK] Read Key buffer hit rate: 100.0% (46M cached / 1 reads)
[OK] Write Key buffer hit rate: 100.0% (28M cached / 0 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 8.0G/772.7M
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 8.30% (43494 used/ 524287 total)
[OK] InnoDB Read buffer efficiency: 100.00% (15712886119 hits/ 15712924640 total)
[!!] InnoDB Write Log efficiency: 44.04% (834843 hits/ 1895459 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1060616 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (10230) variable
should be greater than table_open_cache ( 400)
Variables to adjust:
long_query_time (<= 10)
query_cache_type (=0)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
table_open_cache (> 400)
innodb_buffer_pool_instances(=8)

В архиве приложил кусок slow-log на интервале около 1,5 часов

ScareCrowавтор[!!] Temporary tables created on disk: 80% (1M on disk / 2M total)
скорее всего вот это и есть.

Я писал выше, что мы сам каталог вынесли в RAM на уровне операционной системы.
Я еще раньше обращал внимание на это(высокий процент временных таблиц, создаваемых на диске),
мы увеличили tmp_table_size с 32 до 128M и max_heap_table_size с 32 до 128M, но на процент соотношение временных таблиц в памяти/на диске это не повлияло никак, я подозреваю что проблема в том, что у Wordpress поля, в котором храниться собственно контент сайта, типа text, и временные таблицы, которые создаются с таки же типом соответствующего поля, сразу попадают на диск
Именно поэтому мы вынесли их в ОЗУ на уровне операционной системы.

ScareCrowавтор[--] Binary logging is enabled (GTID MODE: OFF)
у вас репликация? если нет то отключайте - тормозит же.

Репликация - да, есть (это сейчас вынужденное явление, у нас после наступления этих проблем появился резервный сервер, на который реплицируется вся инфа, и опробуются изменения в конфигурации)

ScareCrowавтор[!!] InnoDB Write Log efficiency: 44.04% (834843 hits/ 1895459 total)
тоже добавить.

А тут можно подробнее - какой именно параметр править и насколько?

ScareCrowавторtable_open_cache (> 400)


сделать.

А что можете по этому поводу сказать
авторRead this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C

Нет ли описываемых проблем в версии 5.5.44, не в курсе ?
Спасибо за ответы.

ScareCrowпопробуйте перейти на Pecona Server последней версии.

А что скажете по поводу мысли доапгрейдить mysql до версии 5.7?
С Percona пока что не знаком, хотелось бы как-то обкатать ее, прежде чем переносить на нее критичные для компании сервисы.

Пока не дошли до применения всех рекомендаций
(прежде всего, изменение innodb_log_file_size, table_open_cache, skip-resolve-name и ограничения
числа одновременно выполняющихся php-fpm)

Сделали 2 вещи
innodb_buffer_pool_size = 1024M вместо 8192(8ГБ дали исходя из размера всех таблиц БД + запас с учетом роста)
Но учитывая
автор InnoDB buffer pool / data size: 8.0G/772.7M
решили забрать у буфера неиспользуемую память, чтобы поменять
thread_cache_size = 16
на 128
Вчера картинка была такая
| Threads_cached | 0 |
| Threads_connected | 37 |
| Threads_created | 80631 |
| Threads_running | 8 |

Через после реконфига и перезапуска Mysql такая:
| Threads_cached | 96 |
| Threads_connected | 126 |
| Threads_created | 349 |
| Threads_running | 99 |

Cпустя сутки после перезапуска:
| Threads_cached | 111 |
| Threads_connected | 18 |
| Threads_created | 349 |
| Threads_running | 3 |

Пока наблюдаем

ScareCrowпомогло?

Пока сложно сказать.
С момента внесения изменений описываемых проблем не наблюдалось
Если брать пятницу(23.06), то обратили внимание, что пик потребления процессора в пятницу(24.06) был на 30% ниже( в процентах от максимального ресурса процессора), чем в остальные будние дни на неделе, составил 40%(статистика бралась с гипервизора, данные о пиках беруться на 3-часовом интервале каждых суток).

Но сегодня, 25.06, аналогичный пик составил 64%, что весьма нетипично для субботы(обычно в выходные дни нагрузка на процессор существенно ниже).

При этом кол-во запросов за сутки не сильно отличалось от стандартных(в пятницу - как для буднего дня в субботу - как для выходного).

Короче еще несколько дней на следующей неделе будем наблюдать, потом делать выводы.