оригинал:MySQL Performance Tuning Tips for the Shopping Season
автор:Shree Nair
Перевод: Нет помех моему полету

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

Хэллоуин давно прошел, и пришло время сосредоточиться на предстоящем праздничном сезоне. Сначала День Благодарения, затем Черная пятница и Киберпонедельник и, наконец, Рождество/Неделя подарков (начиная с Дня подарков 26 декабря и заканчивая кануном Нового года 31 декабря в течение шести или более дней. Этот термин был изобретен в розничной торговле около середине 2000-х в попытке расширить свои продажи в День подарков) до кульминации покупок. Для владельцев бизнеса это время года знаменуется долгожданной фиксацией прибыли в конце года. Для некоторых администраторов баз данных это вызывает страх, беспокойство и даже бессонные ночи, когда они пытаются вернуть систему в оперативный режим.

К счастью, это не так. Внося некоторые упреждающие корректировки в переменные производительности MySQL, вы можете оградить свой сервер базы данных от повышенного спроса, связанного с сезоном покупок.

Совет №1: Определите максимальное количество подключений к MySQL

Для максимального количества подключений к MySQL лучше всего отправлять 5 запросов на веб-сервер за раз. Некоторые из 5 запросов к веб-серверу будут касаться таких ресурсов, как таблицы стилей CSS, изображения и сценарии. Получение точного соотношения запросов MySQL к веб-серверу может быть затруднено из-за таких причин, как кэширование браузера; чтобы получить точное число, вам необходимо проанализировать файлы журнала веб-сервера. Например, к файлу журнала Apache «access_log» можно получить доступ вручную или черезAnalogилиWebalizerи другие утилиты для доступа к лог-файлам.

Когда у вас будет точная оценка вашего конкретного использования, умножьте это соотношение на максимальное количество подключений к вашему веб-серверу. Например, если веб-сервер настроен на обслуживание до 256 клиентов, а отношение запросов MySQL к веб-запросам составляет 1/8, лучше всего установить максимальное количество подключений к базе данных равным 32. Также подумайте о том, чтобы оставить запас прочности, умножьте это число на 2, чтобы получить окончательную сумму. Попытайтесь сопоставить максимальное количество подключений к базе данных с ограничением клиентов веб-сервера, только если инфраструктура поддерживает это. В большинстве случаев лучше оставаться около 32.

Просмотр соединений MySQL в Monyog

В базе данных MySQL максимальное количество одновременных подключений к MySQL хранится в глобальной переменной max_connections. Monyog сообщает о переменной «max_connections» как о «максимально разрешенном» показателе в текущей группе мониторинга соединений. Он также делит это число на количество открытых соединений, чтобы получить процент использования соединения:

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

Совет № 2: Выделите достаточно памяти для временных таблиц

В некоторых случаях сервер создает внутренние временные таблицы при обработке оператора. Временные таблицы используются для внутренних операций, таких как GROUP BY и отдельные, а также некоторые запросы ORDER BY и подзапросы в предложениях UNION и FROM (производные таблицы). Это мемтаблицы, созданные в памяти. Максимальный размер временной таблицы в памяти определяется меньшим из значений tmp_table_size и max_heap_table_size. Если размер временной таблицы превышает этот порог, она преобразуется в таблицу InnoDB или MyISAM на диске. Кроме того, если запрос включает столбцы BLOB или TEXT, которые не могут быть сохранены в таблице в памяти, временная таблица всегда указывает непосредственно на диск.

Это преобразование является дорогостоящим, поэтому рассмотрите возможность увеличения размера переменных max_heap_table_size и tmp_table_size, чтобы уменьшить количество временных таблиц, создаваемых на диске. Имейте в виду, что для этого потребуется много памяти, поскольку размер временной таблицы в памяти основан на «наихудшем случае». Например, таблицы в памяти всегда используют столбцы фиксированной длины, поэтому используйте VARCHAR(255) для символьных столбцов. Это может привести к тому, что временные таблицы в памяти будут намного больше, чем вы думаете, — на самом деле, во много раз больше, чем общий размер таблицы поиска! При увеличении размера переменных max_heap_table_size и tmp_table_size обязательно следите за использованием памяти сервером, поскольку временные таблицы в памяти могут увеличить риск нехватки памяти сервера.

Обычно рекомендуются значения от 32M до 64M, начните с этих двух переменных и настройте по мере необходимости.

Мониторинг временных таблиц в Monyog

Мониторинг временных таблиц является одним из многих предопределенных мониторинга Monyog. Он предоставляет некоторые показатели, используемые временными таблицами, в том числе:

максимально допустимый: отображает значение серверной переменной tmp_table_size, определяющей максимальный размер временной таблицы, создаваемой в памяти. Вместе с max_heap_table_size это значение определяет максимальный размер временной таблицы, которую можно создать в памяти. Если временная таблица в памяти превышает этот размер, она сохраняется на диске.
Максимальный размер таблицы памяти: отображает значение серверной переменной max_heap_table_size, которая определяет максимальный размер явно созданной таблицы механизма хранения MEMORY.
Общее количество созданных временных таблиц: отображает значение серверной переменной created_tmp_tables, которая определяет количество временных таблиц, созданных в памяти.
Временная таблица, созданная на диске: отображает значение серверной переменной created_tmp_disk_tables, которая определяет количество временных таблиц, созданных на диске. Если это значение велико, вам следует рассмотреть возможность увеличения значений tmp_table_size и max_heap_table_size, чтобы увеличить количество создаваемых в памяти временных таблиц и, таким образом, уменьшить количество временных таблиц, создаваемых на диске.
Диск: Общее соотношение: рассчитано на основе created_tmp_disk_tables, разделенных на created_tmp_tables. Процент временных таблиц, созданных на диске из-за недостаточного размера tmp_table_size или max_heap_table_size. Monyog отображает это число в виде индикатора выполнения и процента, чтобы быстро определить, сколько диска используется для временных таблиц, а не памяти.

Графики тенденций доступны для общего количества созданных таблиц, соотношения таблиц, созданных на диске, к общему количеству на диске. Это позволяет нам увидеть, как они развивались с течением времени:

Совет № 3: увеличьте размер кеша потоков

Поток диспетчера соединений обрабатывает запросы клиентов на подключение к сетевому интерфейсу, который прослушивает сервер. Поток диспетчера соединений связывает каждое клиентское соединение с выделенным для него потоком, который обрабатывает аутентификацию и всю обработку запросов для этого соединения. Таким образом, между потоками и подключенными в данный момент клиентами существует соотношение один к одному. Очень важно убедиться, что кэш потока достаточно велик для размещения всех входящих запросов.

MySQL предоставляет ряд серверных переменных, связанных с потоками подключения:

Размер кэша потока определяется системной переменной thread_cache_size. Значение по умолчанию — 0 (без кэширования), что приведет к созданию потока для каждого нового соединения, которое необходимо обрабатывать при разрыве соединения. Если вы ожидаете, что сервер будет получать сотни запросов на подключение в секунду, значение thread_cache_size должно быть достаточно высоким, чтобы большинство новых подключений могли использовать поток кэша. Значение max_connections может быть установлено при запуске сервера или во время выполнения.

Также следует отслеживать количество потоков в кеше (Threads_cached) и количество созданных потоков, так как потоки не могут быть получены из кеша (Threads_created). Что касается последнего, если Threads_created продолжает увеличиваться более чем на несколько потоков в минуту, рассмотрите возможность увеличения значения thread_cache_size.

Используйте команду MySQL show status, чтобы отобразить переменную MySQL и информацию о состоянии. Вот несколько примеров:

SHOW GLOBAL STATUS LIKE '%Threads_connected%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_connected | 2     |

+-------------------+-------+
SHOW GLOBAL STATUS LIKE '%Threads_running%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| Threads_running | 1     |

+-----------------+-------+

Мониторинг кеша потоков Monyog

Monyog предоставляет экран для мониторинга кешей потоков, называемых «потоками». Переменные сервера, связанные с потоками MySQL, сопоставляются со следующими метриками Monyog:

thread_cache_size: количество потоков, которые могут быть кэшированы.
Threads_cached: количество потоков в кеше.
Threads_created: потоки, созданные для обработки соединений.

На экране Monyog Threads также отображается метрика Thread Cache Hit Ratio. Это показатель частоты попаданий в кэш потоков. Если значение низкое, следует рассмотреть возможность увеличения кеша потока. Значение отображается в процентах в строке состояния, чем ближе оно к 100%, тем лучше.

Каждую метрику можно настроить для выдачи предупреждения и/или критического оповещения, если значение этих метрик равно или превышает указанное значение.

Другие связанные переменные сервера

В дополнение к вышеперечисленным показателям необходимо контролировать следующее:

Размер буферного пула InnoDB: Размер буферного пула InnoDB играет решающую роль в базах данных MySQL, использующих InnoDB. Пул буферов кэширует как данные, так и индексы. Его значение должно быть как можно больше, чтобы база данных использовала память, а не жесткий диск для операций чтения.
размер временной таблицы: MySQL использует меньшее из значений max_heap_table_size и tmp_table_size для ограничения размера временных таблиц в памяти. Наличие большого значения может помочь уменьшить количество временных таблиц, создаваемых на диске, но также увеличивает риск нехватки памяти сервера, поскольку этот показатель применяется для каждого клиента. Обычно рекомендуются значения от 32M до 64M, начните с этих двух переменных и настройте по мере необходимости.
Размер буфера журнала InnoDB: Каждый раз, когда MySQL записывает в файл журнала, он использует значительные системные ресурсы, которые можно использовать для обработки данных о продажах. Следовательно, имеет смысл только установить большее значение размера буфера журнала InnoDB. Таким образом, сервер записывает на диск меньше при больших транзакциях, сводя к минимуму эти трудоемкие операции. 64M — хорошая отправная точка для этой переменной.

в заключении

Хотя даже самые крупные корпоративные веб-сайты могут страдать от простоев, это особенно критично для малого и среднего бизнеса, занимающегося онлайн-продажами. в соответствии сНедавний отчет о расследованииСогласно отчету, одна минута простоя обходится предприятиям в среднем примерно в 5000 долларов. Не позволяйте вашему бизнесу быть частью этой статистики (потери из-за простоя). Перед праздничной суматохой заранее настройте сервер(ы) базы данных MySQL и пожинайте плоды!