Тщательно оптимизируйте базу данных mysql под высокую нагрузку в Linux.

При этом количество онлайн-заходов продолжает расти. Когда сервер с памятью 1G явно сильно перегружен, он даже каждый день крашится или сервер время от времени зависает. Эта проблема беспокоила меня уже больше половины. в месяц MySQL использует очень масштабируемый алгоритм, поэтому обычно его можно запускать с меньшим объемом памяти или предоставить MySQL больше памяти для повышения производительности.

После установки MySQL файлы конфигурации должны находиться в каталоге /usr/local/mysql/share/mysql. Существует несколько файлов конфигурации, включая my-huge.cnf, my-medium.cnf, my-large.cnf, my-small.cnf. Веб-сайты с разными потоками трафика и серверными средами с разными конфигурациями, конечно, потребуют разных файлов конфигурации.

В обычных обстоятельствах файл конфигурации my-medium.cnf может удовлетворить большинство наших потребностей; обычно мы копируем файл конфигурации в /etc/my.cnf, и нам нужно только изменить этот файл конфигурации, используя переменные mysqladmin Extended-status –u. root –p может видеть текущие параметры. Есть три наиболее важных параметра конфигурации, а именно key_buffer_size, query_cache_size, table_cache .

key_buffer_size работает только для таблиц MyISAM,

key_buffer_size указывает размер индексного буфера, который определяет скорость обработки индекса, особенно скорость чтения индекса. Обычно мы устанавливаем значение 16M. На самом деле этого числа недостаточно для сайтов немного большего размера. Проверив значения статуса Key_read_requests и Key_reads, вы можете узнать, является ли настройка key_buffer_size разумной. Соотношение key_reads/key_read_requests должно быть как можно меньшим, не менее 1:100, лучше 1:1000 (вышеуказанное значение статуса можно получить с помощью SHOW STATUS LIKE 'key_read%'). Или, если вы установили phpmyadmin, вы можете увидеть его через статус работы сервера. Автор рекомендует использовать phpmyadmin для управления mysql. Следующие значения статуса представляют собой пример анализа, который я получил с помощью phpmyadmin:

Этот сервер работает уже 20 дней
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
Соотношение близко к 1:8000, состояние здоровья очень хорошее.

Другой способ оценить key_buffer_size — сложить пространство, занимаемое индексами каждой таблицы в базе данных вашего веб-сайта. Возьмите этот сервер в качестве примера: сумма индексов нескольких больших таблиц составляет примерно 125 МБ. Это число будет увеличиваться по мере увеличения размера таблицы. стать больше.

Начиная с версии 4.0.1, MySQL предоставляет механизм буферизации запросов. Используя буферизацию запросов, MySQL сохраняет оператор SELECT и результаты запроса в буфере. В дальнейшем для того же оператора SELECT (с учетом регистра) результаты будут считываться непосредственно из буфера. Согласно руководству пользователя MySQL, использование буферизации запросов может повысить эффективность до 238%.

Настраивая следующие параметры, вы можете узнать, является ли параметр query_cache_size разумным.
Вставки Qcache
Qcache хиты
Qcache lowmem чернослив
Бесплатные блоки Qcache
Общее количество блоков Qcache

Если значение Qcache_lowmem_prunes очень велико, это указывает на то, что буферизации часто недостаточно. В то же время, если значение Qcache_hits очень велико, это указывает на то, что буфер запроса используется очень часто. необходимо увеличить. Если значение Qcache_hits мало, это означает, что частота повторения запросов очень низкая. В этом случае использование буферизации запросов повлияет на эффективность, поэтому вы можете отказаться от использования буферизации запросов. Кроме того, добавление SQL_NO_CACHE к инструкции SELECT может четко указывать на то, что буфер запроса не используется.
Qcache_free_blocks, если значение очень велико, это указывает на то, что в буфере много фрагментов. query_cache_type указывает, следует ли использовать буферизацию запросов.

Я установил:
query_cache_size = 32M
query_cache_type= 1

Получите следующее значение статуса:
Запросы Qcache в кеше 12737 указывают количество элементов, кэшированных в данный момент.
Qcache вставляет 20649006
Qcache достигает 79060095 Кажется, частота повторных запросов довольно высока.
Qcache lowmem сокращает 617913 Часто бывает, что кэша недостаточно.
Qcache не кэшируется 189896
Свободная память Qcache 18573912 Текущее оставшееся пространство кэша
Свободных блоков Qcache 5328 Это число кажется немного большим и фрагментированным.
Всего блоков Qcache 30953
Если памяти позволяет 32М, стоит добавить еще.

table_cache указывает размер кэша таблиц. Всякий раз, когда MySQL обращается к таблице, если в буфере таблицы есть место, таблица открывается и помещается в нее, обеспечивая более быстрый доступ к содержимому таблицы. Проверив значения статуса Open_tables и Opened_tables в пиковое время, вы можете решить, нужно ли вам увеличивать значение table_cache. Если вы обнаружите, что open_tables равно table_cache, а open_tables растет, то вам необходимо увеличить значение table_cache (вышеуказанное значение статуса можно получить с помощью SHOW STATUS LIKE 'Open%tables'). Обратите внимание, что table_cache нельзя установить большое значение вслепую. Если установлено слишком высокое значение, это может привести к недостаточности файловых дескрипторов, что приведет к нестабильной производительности или сбою соединения.

Для машин с 1 ГБ памяти рекомендуемое значение — 128–256.

Настройки автора
table_cache = 256

Получите следующий статус:
Открытые столы 256
Открытые столы 9046
Хотя open_tables уже равен table_cache, относительно времени работы сервера он работает уже 20 дней и значение open_tables тоже очень низкое. Поэтому увеличение значения table_cache будет малополезным. Если указанное выше значение появляется после 6 часов работы, вам следует рассмотреть возможность увеличения table_cache.

Если вам не нужно записывать двоичные журналы, отключите эту функцию. Обратите внимание, что после ее отключения вы не сможете восстановить данные до возникновения проблемы. Бинарный журнал содержит все операторы, обновляющие данные, и их. Цель — восстановить базу данных. Используйте ее, чтобы максимально восстановить данные до конечного состояния. Кроме того, если вы делаете синхронную репликацию (Репликацию), вам также необходимо использовать двоичный журнал для передачи изменений.

log_bin указывает файл журнала. Если имя файла не указано, MySQL сам сгенерирует имя файла по умолчанию. MySQL автоматически добавит числовой индекс после имени файла и будет заново создавать новый двоичный файл при каждом запуске службы. Кроме того, используйте log-bin-index, чтобы указать индексный файл; используйте binlog-do-db, чтобы указать базу данных для записи; используйте binlog-ignore-db, чтобы указать базу данных, которая не будет записываться. Примечание. binlog-do-db и binlog-ignore-db указывают только одну базу данных одновременно. Для указания нескольких баз данных требуется несколько операторов. Более того, MySQL изменит все имена баз данных на строчные. При указании базы данных необходимо использовать все имена в нижнем регистре, иначе это не будет работать.

Чтобы отключить эту функцию, просто добавьте перед ней знак #.
#log-bin

Включить журнал медленных запросов (журнал медленных запросов)

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

Чтобы включить журналы медленных запросов, вам необходимо установить параметры log_slow_queries, long_query_times и log-queries-not-using-indexes.

log_slow_queries указывает файлы журналов. Если имя файла не указано, MySQL самостоятельно сгенерирует имя файла по умолчанию. long_query_times указывает порог медленного запроса, значение по умолчанию — 10 секунд. log-queries-not-using-indexes — это параметр, появившийся после версии 4.1.0, который указывает, что запросы, не использующие индексы, протоколируются. Автор устанавливает long_query_time=10

Настройки автора:

sort_buffer_size = 1M
max_connections=120
wait_timeout = 120
back_log=100
read_buffer_size = 1M
thread_cache=32
интерактивный_таймаут=120
thread_concurrency = 4

Описание параметра:

back_log
Количество соединений, которые должен иметь MySQL. Это работает, когда основной поток MySQL получает много запросов на соединение за короткий период времени, а затем основному потоку требуется некоторое время (хотя и кратковременное), чтобы проверить наличие соединений и запустить новый поток. Значение back_log указывает, сколько запросов может быть сохранено в стеке за короткий период времени, прежде чем MySQL временно перестанет отвечать на новые запросы. Только если вы ожидаете много подключений за короткий период времени, вам нужно его увеличить, другими словами, это значение представляет собой размер очереди прослушивания входящих TCP/IP-соединений. Ваша операционная система имеет собственное ограничение на размер очереди. На странице руководства системного вызова Unix Listen(2) должно быть больше подробностей. Проверьте документацию вашей ОС, чтобы узнать максимальное значение этой переменной. Попытка установить back_log выше предела вашей операционной системы не даст никакого эффекта.
max_connections
Максимальное количество одновременных подключений — 120. Если оно превысит это значение, оно автоматически восстановится и проблемы будут автоматически решены.

thread_cache
Я не смог найти каких-либо конкретных инструкций, но после установки значения 32 за 20 дней было создано более 400 потоков, тогда как раньше за один день создавались тысячи потоков, так что это все еще полезно.

thread_concurrency
#Установите номер вашего процессора x2, например, если процессор только один, то thread_concurrency=2
#Есть 2 процессора, значит thread_concurrency=4

пропустить-innodb
#Удалить поддержку innodb