Замедление из-за пробок каждый день около 12:00

Я проверил логи медленных запросов и получил всего 4 запроса за 2 часа, и все они были похожи на этот:

«ВЫБРАТЬ HEX(uhash) КАК uhash, vehid, IF(deleted = 0 AND follow_price_drop = 1, 1, 0) AS follow_price_drop, электронная почта, удалено 
       ОТ wp_ product_favorite_count КАК cfc 
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ wp_ product_favorite_user AS cfu ON cfc. product_favorite_user_uhash = cfu.uhash
       ГДЕ cfc.updated > '2021-09-23 12:49:02' ИЛИ ​​cfu.updated > '2021-09-23 12:49:02'"

Я проверил top и htop, и я часто получаю 100 загрузок процессора на всех 6 ядрах процессора.

Большая часть использования ЦП исходит от mysqld, поэтому я зарегистрировал базу данных:

https://pastebin.com/BBv7ngW5

iostat -xm 5 3 дал мне:

avg-cpu: %user %nice %system %iowait %steal %idle
          11,34 0,01 1,80 1,13 0,08 85,65

Устройство: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 39,75 720,61 79,81 192,29 0,99 3,57 34,30 0,02 0,09 0,19 0,04 0,09 2,53

^[[A^[[A^[[Aavg-cpu: %user %nice %system %iowait %steal %idle
          84,15 0,00 6,16 0,05 0,03 9,61

Устройство: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 0,80 31,00 14,40 19,80 0,65 0,20 50,95 0,02 0,73 0,93 0,58 0,43 1,48

^[[A^[[Bavg-cpu: %user %nice %system %iowait %steal %idle
          84,54 0,00 4,95 0,10 0,05 10,36

Устройство: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 0,00 2,40 22,60 1,60 1,77 0,02 151,40 0,02 1,02 1,04 0,75 0,64 1,56

улимит -а

размер основного файла (блоки, -c) 0
размер сегмента данных (кбайт, -d) не ограничен
приоритет планирования (-e) 0
размер файла (блоки, -f) не ограничен
ожидающие сигналы (-i) 128341
максимальная заблокированная память (кбайт, -l) 64
максимальный размер памяти (кбайт, -m) не ограничен
открыть файлы (-n) 1024
размер канала (512 байт, -p) 8
Очереди сообщений POSIX (байты, -q) 819200
приоритет реального времени (-r) 0
размер стека (кбайт, -с) 10240
время процессора (секунды, -t) не ограничено
максимальное количество пользовательских процессов (-u) 128341
виртуальная память (кбайт, -v) не ограничена
блокировка файлов (-x) не ограничена

Я проверил общий журнал запросов после проверки журнала медленных запросов и был удивлен, что получил так много запросов. При обычном трафике я получил: 136235 запросов, большинство из которых являются SELECT-запросами через 10 минут. А при большом трафике у меня получилось: 195650 запросов за 10 минут. Сомневаюсь, что 195650 посетителей, но звонки почему-то внутри general_log. В журнале slow_query_log было всего 4 запроса, и они не выглядели как неоптимизированные запросы. Есть ли что-то еще, на что я должен обратить внимание, или этого достаточно, чтобы предположить, что это из-за трафика, и нам следует обновить сервер?

top выглядит примерно так, я не смог его вовремя заснять, но когда он достиг 95%+ процессора, экран выглядел так:

топ - 13:04:51 вверх 1140 дней, 19:59, 2 пользователя, средняя загрузка: 26,57, 16,21, 8,92
Задания: 429 всего, 12 бегущих, 421 спящих, 0 остановленных, 0 зомби
Процессор (ы): 91,3% us, 1,6% sy, 0,0% ni, 65,7% id, 3,1% wa, 0,0% hi, 0,2% si, 0,1% st
Память: 32877280k всего, 31367584k использовано, 1509696k свободно, 3960824k буферов
Swap: всего 0k, использовано 0k, свободно 0k, кэшировано 3980580k

  PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND                                                 
14576 mysql 20 0 12,9 г 8,5 г 8424 S 951,6 27,2 18841:47 mysqld                                                  
 6032 martind 20 0 510 м 65 м 9160 S 61,4 0,2 2:49,40 php-fpm                                                  
 7329 мартинд 20 0 498 м 63 м 5556 R 57,6 0,2 0:47,15 php-fpm                                                  
 7321 мартинд 20 0 487м 52м 5532 R 46.1 0.2 0:45.18 php-fpm                                                  
 7160 мартинд 20 0 488м 52м 5540 R 44.1 0.2 1:02.67 php-fpm                                                  
 6031 martind 20 0 511 м 67 м 8076 S 42,2 0,2 ​​2:50,87 php-fpm                                                  
 6696 martind 20 0 498 м 63 м 5700 S 38,4 0,2 1:36,38 php-fpm                                                  
 7283 мартинд 20 0 494м 59м 5268 S 34.5 0.2 0:46.19 php-fpm                                                  
 7314 мартинд 20 0 490м 55м 5536 R 33.0 0.2 0:44.22 php-fpm                                                  
 7330 мартинд 20 0 496 м 60 м 5436 R 26,4 0,2 0: 46,82 php-fpm                                                  
 7305 мартинд 20 0 494 м 58 м 5572 R 25,4 0,2 0: 48,85 php-fpm                                                  
 6706 martind 20 0 507 м 62 м 8060 S 13,7 0,2 1:40,55 php-fpm                                                  
 7276 мартинд 20 0 498м 63м 5264 S 7.7 0.2 0:49.89 php-fpm                                                  
17464 redis 20 0 4328m 2.3g 888 R 7.7 7.3 7827:30 сервер redis                                             
 6402 martind 20 0 511 м 67 м 8056 S 5,8 0,2 2:15,21 php-fpm                                                  
 6405 martind 20 0 512 м 69 м 9204 S 5,8 0,2 2:14,32 php-fpm                                                  
 6703 martind 20 0 513 м 67 м 8056 S 5,8 0,2 1:39,40 php-fpm                                                  
 6705 martind 20 0 513 м 68 м 9040 S 5,8 0,2 1:36,18 php-fpm                                                  
 7303 мартинд 20 0 493м 57м 6556 S 5.8 0.2 0:47.04 php-fpm                                                  
 7304 мартинд 20 0 494 м 59 м 5264 S 5,8 0,2 0: 48,70 php-fpm                                                  
 7323 мартинд 20 0 511м 67м 7772 S 5.8 0.2 0:45.53 php-fpm                                                  
24515 nginx 20 0 123 м 66 м 2452 S 5,8 0,2 7231:17 nginx                                                    
 6039 martind 20 0 507 м 63 м 8200 S 3,8 0,2 2:48,39 php-fpm                                                  
 6400 martind 20 0 511 м 68 м 8204 S 3,8 0,2 2:13,54 php-fpm                                                  
 6401 martind 20 0 510 м 66 м 9052 S 3,8 0,2 2:13,36 php-fpm                                                  
 6404 martind 20 0 512 м 68 м 9048 S 3,8 0,2 2:12,75 php-fpm 

Поэтому, поскольку существует так много запросов SQL, когда они имеют тенденцию сильно замедляться, я думаю, что это вызвано высоким трафиком. Я проверил cronjobs (cronjobs wordpress и php cronjobs), и, кажется, ничего не запускается, когда он замедляется, может быть процесс rsync, работающий в то же время, но процесс rsync работает все время, поэтому я сомневаюсь, что это вызвано этим. Я могу что-нибудь проверить?

Анализ ГЛОБАЛЬНОГО СОСТОЯНИЯ и ПЕРЕМЕННЫХ:

Наблюдения:

• Версия: 10.4.12-МарияДБ
• 32 ГБ оперативной памяти
• Время безотказной работы = 19д 23:11:43
• Похоже, вы используете и MyISAM, и InnoDB.
• 240 запросов в секунду

Более важные вопросы:

Изменять long_query_time к 1 так что вы можете поймать больше запросов в slowlog. (У вас есть 10 секунд; это, вероятно, объясняет, почему вы нашли только 4 запроса.) Есть несколько признаков того, что некоторые запросы выполняются неэффективно. Вот как найти такие запросы: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Почему вы используете MyISAM? Значения сбивают с толку - это как если бы вы [пере]построили индекс для большой таблицы MyISAM, но ничего больше не сделали. В большинстве случаев лучше использовать InnoDB.

innodb_buffer_pool_size вероятно, можно увеличить, чтобы улучшить скорость запросов InnoDB.

Будьте осторожны в отношении общий_лог -- он довольно быстро заполняет диск.

«Кэш запросов» работает неэффективно. Я рекомендую полностью отключить его: query_cache_type = выкл. и query_cache_size = 0.

Max_used_connections нажмите 152, что указывает на то, что одновременно подключено много пользователей. (Это не означает, что одновременно выполнялось 152 запроса.)

Подробности и другие наблюдения:

Преобразование из MyISAM в InnoDB ( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0,35% -- Процент использования key_buffer. Верхняя отметка. -- Уменьшите key_buffer_size (теперь 134217728), чтобы избежать ненужного использования памяти.

((key_buffer_size/0,20 + innodb_buffer_pool_size/0,70)) = ((128M/0,20 + 8192M/0,70))/32768M = 37,7% -- Большая часть доступной оперативной памяти должна быть доступна для кэширования. -- http://mysql.rjweb.org/doc.php/память

(общий_журнал) = общий_журнал = ВКЛ. -- Журнал (ФАЙЛ или ТАБЛИЦА) всех выполненных запросов. -- Выключите general_log (сейчас включен), когда он не используется. Этот журнал может очень быстро заполнить диск.

( innodb_buffer_pool_size ) = 8 192 / 32768M = 25,0% -- % оперативной памяти, используемой для InnoDB buffer_pool -- Установите около 70% доступной оперативной памяти. (Слишком низкое значение менее эффективно; слишком высокое значение может привести к обмену.)

((key_buffer_size/0,20 + innodb_buffer_pool_size/0,70)) = ((128M/0,20 + 8192M/0,70))/32768M = 37,7% -- (метрика для оценки использования оперативной памяти)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1024 * 4 = 4096 -- Объем работы по очистке страниц каждую секунду. -- "InnoDB: page_cleaner: запланированный цикл занял 1000 мс..." можно исправить, уменьшив lru_scan_depth: рассмотрим 1000 / innodb_page_cleaners (сейчас 4). Также проверьте обмен.

(innodb_lru_scan_depth) = 1024 -- "InnoDB: page_cleaner: запланированный цикл занял 1000 мс..." можно исправить, уменьшив lru_scan_depth

(innodb_io_capacity) = 200 -- При сбросе используйте это количество операций ввода-вывода. -- Чтение может быть вялым или скачкообразным.

( Innodb_log_writes ) = 43 856 157 / 1725103 = 25 /сек

(Innodb_os_log_writing/(Uptime/3600)/innodb_log_files_in_group/innodb_log_file_size) = 137 804 939 264/(1725103/3600)/2/48M = 2,86 -- Соотношение -- (см. протокол)

(Время работы / 60 * innodb_log_file_size / Innodb_os_log_writing) = 1 725 103 / 60 * 48M / 137804939264 = 10,5 -- Минуты между ротациями журналов InnoDB. Начиная с 5.6.8, это можно изменить динамически; не забудьте также изменить my.cnf. -- (Рекомендация 60 минут между ротациями несколько произвольна.) Отрегулируйте innodb_log_file_size (теперь 50331648). (Невозможно изменить в AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- Как InnoDB должен запрашивать у ОС запись блоков. Предложите O_DIRECT или O_ALL_DIRECT (Percona), чтобы избежать двойной буферизации. (По крайней мере, для Unix.) См. chrischandler для предостережений относительно O_ALL_DIRECT.

( default_tmp_storage_engine ) = default_tmp_storage_engine =

(innodb_flush_neighbors) = 1 -- Незначительная оптимизация при записи блоков на диск. -- Используйте 0 для дисков SSD; 1 для жесткого диска.

(innodb_io_capacity) = 200 -- Количество операций ввода-вывода в секунду на диске . 100 для медленных дисков; 200 для вращающихся дисков; 1000-2000 для твердотельных накопителей; умножьте на коэффициент RAID.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Обычно должен быть включен. -- Есть случаи, когда OFF лучше. См. также innodb_adaptive_hash_index_parts (теперь 8) (после 5.7.9) и innodb_adaptive_hash_index_partitions (MariaDB и Percona). ON был причастен к редким сбоям (ошибка 73890). 10.5.0 решил по умолчанию ВЫКЛ.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = ВЫКЛ. -- Регистрировать ли все взаимоблокировки. -- Если вас мучают взаимоблокировки, включите это.Предупреждение: Если у вас много взаимоблокировок, это может привести к записи большого количества данных на диск.

(сервер_набора_символов) = сервер_набора_символов = latin1 -- Проблемы с кодировкой можно решить, установив для character_set_server (теперь latin1) значение utf8mb4. Это будущий дефолт.

( локальный_файл ) = локальный_файл = ON -- local_infile (сейчас ВКЛ) = ВКЛ - потенциальная проблема безопасности

( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0,35% -- Процент использования key_buffer . Верхняя отметка. -- Уменьшите key_buffer_size (теперь 134217728), чтобы избежать ненужного использования памяти.

( Key_writes / Key_write_requests ) = 19 978 377 / 40284646 = 49,6% -- эффективность key_buffer для записи -- Если у вас достаточно оперативной памяти, стоит увеличить key_buffer_size (сейчас 134217728).

( query_cache_size ) = 524 288 = 0,5 МБ -- Размер КК -- Слишком маленький = бесполезен. Слишком большой = слишком много накладных расходов. Рекомендуется либо 0, либо не более 50M.

( Qcache_lowmem_prunes ) = 125 234 412 / 1725103 = 73 /сек -- Не хватило места в QC -- увеличить query_cache_size (теперь 524288)

(Qcache_lowmem_prunes/Qcache_inserts) = 125 234 412/146211296 = 85,7% -- Removal Ratio (частота необходимости обрезки из-за нехватки памяти)

( Qcache_not_cached ) = 78 413 835 / 1725103 = 45 /сек -- Попытка SQL_CACHE была проигнорирована -- Переосмыслить кэширование; настроить qcache

(Qcache_hits/Qcache_inserts) = 37 201 050/146211296 = 0,254 -- Соотношение попаданий и вставок -- высокое -- это хорошо -- Рассмотрите возможность отключения кеша запросов.

(Qcache_hits / (Qcache_hits + Com_select)) = 37 201 050 / (37201050 + 282029692) = 11,7% -- Соотношение попаданий -- SELECT, которые использовали QC -- Рассмотрите возможность отключения кеша запросов.

(Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached)) = 37 201 050 / (37201050 + 146211296 + 78413835) = 14,2% -- Частота попаданий в кэш запросов -- Наверное, лучше отключить QC.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (524288 - 78344) / 82 / 16384 = 0,332 -- query_alloc_block_size против формулы -- Настроить query_alloc_block_size (теперь 16384)

( Created_tmp_tables ) = 96 501 765 / 1725103 = 56 /сек -- Частота создания "временных" таблиц как часть сложных SELECT.

( Created_tmp_disk_tables ) = 23 539 653 / 1725103 = 14 /сек -- Частота создания диск «временные» таблицы как часть сложных SELECT -- увеличить tmp_table_size (теперь 16777216) и max_heap_table_size (теперь 16777216). Проверьте правила для временных таблиц, когда вместо MyISAM используется MEMORY. Возможно, незначительные изменения схемы или запроса помогут избежать использования MyISAM. Улучшенные индексы и переформулировка запросов, скорее всего, помогут.

( Created_tmp_disk_tables / Вопросы ) = 23 539 653 / 414140316 = 5,7% -- Процент запросов, которым требовалась таблица tmp на диске. -- Улучшенные индексы/Без BLOB/и т.д.

( Select_full_join / Com_select ) = 30 333 225 / 282029692 = 10,8% -- % выборок, которые являются безиндексными соединениями -- Добавьте подходящие индексы к таблицам, используемым в JOIN.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /сек -- записей/сек -- 50 записей в секунду + сброс журнала, вероятно, превысят емкость записи ввода-вывода для жестких дисков. Если у вас SSD, то этот показатель, вероятно, подходит.

( binlog_format ) = binlog_format = СМЕШАННЫЙ -- ЗАЯВЛЕНИЕ/РЯД/СМЕШАННЫЙ. -- ROW предпочитают 5,7 (10,3)

(длинное_время_запроса) = 10 -- Cutoff (Seconds) для определения "медленного" запроса. -- Предложить 2

( Max_used_connections / max_connections ) = 152 / 151 = 100,7% -- Пиковый % подключений -- увеличьте max_connections (теперь 151) и/или уменьшите wait_timeout (теперь 28800). Или ускорить запросы.

( Соединения ) = 11 987 448 / 1725103 = 6,9 /сек -- Соединения -- Увеличить wait_timeout (сейчас 28800); использовать пул?

( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26 -- Ошибки подключения, отличные от max_connections. -- Для получения дополнительной информации см. SHOW GLOBAL STATUS LIKE 'Connection_errors%'

Аномально маленький:

Created_tmp_files = 0,094/час
innodb_spin_wait_delay = 4

Аномально большой:

Aria_pagecache_writes = 34 /сек
Ария_транзакция_log_syncs = 25 641
Com_show_warnings = 40/час
Connection_errors_internal = 0,054/час
Handler_read_key = 85109/сек
Handler_tmp_update = 839/сек
Innodb_buffer_pool_read_requests = 675158/сек
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) = 100,0%
Innodb_rows_updated = 356/сек.
performance_schema_max_cond_classes = 90

Нестандартные строки:

Innodb_have_punch_hole = ВЫКЛ.
aria_recover_options = РЕЗЕРВНОЕ КОПИРОВАНИЕ,БЫСТРОЕ
отключить_on_expired_password = ВЫКЛ.
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = ТАБЛИЦА
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = ПО УМОЛЧАНИЮ
оптимизатор_трассировка = включено = выключено