Замедление из-за пробок каждый день около 12 часов дня?

Question

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

Наблюдения:

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

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

Измените 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.

Будьте осторожны general_log— диск заполняется довольно быстро.

«Кэш запросов» работает неэффективно. Рекомендую полностью его отключить: query_cache_type=offи 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/memory

( general_log ) = general_log = ON-- Журнал (ФАЙЛ или ТАБЛИЦА) всех выполненных запросов. -- Отключайте 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 ) = 1,024 * 4 = 4,096-- Объем работы для очистки страниц каждую секунду. -- "InnoDB: page_cleaner: 1000ms expected loop took ..." можно исправить, уменьшив lru_scan_depth: рассмотрите 1000 / innodb_page_cleaners (теперь 4). Также проверьте подкачку.

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

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

( Innodb_log_writes ) = 43,856,157 / 1725103 = 25 /sec

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

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 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 для HDD.

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

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

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

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

( local_infile ) = local_infile = 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.5MB-- Размер QC -- Слишком маленький = не очень полезен. Слишком большой = слишком много накладных расходов. Рекомендуется 0 или не более 50M.

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

( Qcache_lowmem_prunes/Qcache_inserts ) = 125,234,412/146211296 = 85.7%-- Коэффициент удаления (частота необходимости удаления из-за нехватки памяти)

( Qcache_not_cached ) = 78,413,835 / 1725103 = 45 /sec-- 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 /sec-- Частота создания «временных» таблиц как части сложных SELECT-ов.

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

( Created_tmp_disk_tables / Questions ) = 23,539,653 / 414140316 = 5.7%-- Процент запросов, которым требовалась таблица tmp на диске. -- Лучшие индексы / Никаких блобов / и т. д.

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

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

( binlog_format ) = binlog_format = MIXED-- ЗАЯВЛЕНИЕ/СТРОКА/СМЕШАННАЯ. -- СТРОКА предпочитается 5.7 (10.3)

( long_query_time ) = 10-- Отсечка (секунды) для определения «медленного» запроса. -- Предложить 2

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

( Connections ) = 11,987,448 / 1725103 = 6.9 /sec-- Подключения -- Увеличить 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. -- Для получения дополнительной информации см. ПОКАЗ ГЛОБАЛЬНОГО СОСТОЯНИЯ, КАК 'Connection_errors%'

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

Created_tmp_files = 0.094 /HR
innodb_spin_wait_delay = 4

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

Aria_pagecache_writes = 34 /sec
Aria_transaction_log_syncs = 25,641
Com_show_warnings = 40 /HR
Connection_errors_internal = 0.054 /HR
Handler_read_key = 85109 /sec
Handler_tmp_update = 839 /sec
Innodb_buffer_pool_read_requests = 675158 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Innodb_rows_updated = 356 /sec
performance_schema_max_cond_classes = 90

Ненормальные строки:

Innodb_have_punch_hole = OFF
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = TABLE
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off

Answer 1

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

Наблюдения:

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