Огромное количество веб-приложений используют СУБД MySQL как основное хранилище данных. Очень часто основной проблемой в производительности становится именно взаимодействие с базой данных. Попробуем разобраться, как последовательно изолировать и решить проблему скорости веб-приложения со стороны MySQL. Основная задача: изложить системный подход, а не набор деталей (которых легко наберётся на приличную книгу).

Диагностика

Это первый и, возможно, самый важный этап оптимизации производительности.

Во-первых, нужно понять, что проблема именно в MySQL. Для этого можно использовать стандартный набор диагностических утилит Linux (или другой ОС). Самый простой способ: понаблюдать за выводом top на сервере в процессе проблемной нагрузки. Здесь нужно сделать уточнение: нагрузки на MySQL условно делятся на два типа – зависимые от ввода-вывода (диска) и зависимые от CPU. Обнаружить ваш тип нагрузки просто: ввод-вывод это iowait в top, CPU это высокие значения user и system в графиках использования CPU.

Если сервер приложений и сервер БД это одна машина, то мы увидим процесс mysqld висящим в самом верху и пожирающим значительное процессорное время. Если сервер БД отдельный, то можно собрать статистику запросов через slow_query_log с отсечкой времени выполнения запроса 0 секунд (то есть запись всех запросов). Далее проанализировать лог и посмотреть на абсолютное суммарное время выполнения запросов (в секундах). Если это время близко к диапазону дат в выборке лога, значит MySQL вносит значительный вклад отклик системы.

Далее, нужно уточнить конкретные запросы, на которые тратится большинство времени. Здесь опять нам поможет анализ slow_query_log. Анализировать лучше всего с помощью pt-query-digest из Percona Toolkit. С параметрами по умолчанию мы получим 20 самых затратных запросов с аналитикой по каждому из них. Если у вас установлен Percona Server, то можно получить дополнительные детали с помощью опции log_slow_verbosity = full.

Самый удобный вариант: первый по сумме времени выполнения запрос занимает большую долю нагрузки (>20%). Понятно, что нужно оптимизировать этот запрос.

Более сложная ситуация: топовый запрос занимает небольшую часть времени (<10%). Здесь нужно проводить более сложный анализ связи медленных запросов между собой. Возможно, удастся выявить группу связанных запросов, которые в сумме дают большую долю нагрузки.

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

Пути оптимизации

Основной принцип, который поможет нам в этом непростом процессе: действовать от простого к сложному. На данный момент у нас есть набор запросов, которые вносят наибольший вклад во время отклика системы.

Первый шаг: смотрим непосредственно на эти запросы – находим примеры и выполняем их. Желательно использовать реальный набор данных или близкий к этому. Запросы можно выполнять как в консоли mysql, так и в Workbench (будет удобнее). При тестировании запроса нужно помнить в влиянии query_cache (кеша запросов), поэтому добавляем SQL_NO_CACHE ко всем выборкам (SELECT). Если самые нагруженные запросы не являются выборками (SELECTами), ситуация усложняется. Но pt-query-digest подскажет эквивалентный SELECT-запрос для возможности отладки.

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

Сложные объединения большого количества таблиц (особенно с LEFT JOIN).
Объединения без индексов.
Избыточные группировки и сортировки (GROUP BY, ORDER BY).
Выборка лишних данных (SELECT * FROM).
Реализация поиска без индексов (например LIKE '%string').
Использование функций, блокирующих кеширование (если оно используется). Например: NOW(), CURDATE().
Выборка избыточных записей (забыли поставить LIMIT).
Использование большого количества мелких запросов вместо одного более сложного. Например, выборка объектов по ID по-одному, вместо выборки всех сразу.
Использование неоптимальных типов данных (например TEXT вместо INT).

Второй шаг: оптимизируем сам запрос, если это возможно. Сначала нужно посмотреть, можно ли оптимизировать схему БД (таблицы, использованные в запросе). Самый очевидный шаг: проверить, можно ли модифицировать или создать новые индексы. Кроме того, небольшое ускорение может дать оптимизация типов данных.

Если изменения схемы не дают нужного эффекта, нужно переписывать сам запрос. Здесь всё также индивидуально. Например, если запрос слишком сложный для выполнения, можно попробовать разбить его на несколько более простых. Если наоборот, запрос точечный, но имеет большую частоту выполнения, можно сделать более крупный запрос, но один.

Для оптимизации самого запроса нужно определить конкретное место в коде приложения, где он формируется. В случае использования SQL напрямую в коде это несложно. Нужно искать по шаблону характерной части запроса. Чтобы окончательно удостовериться в правильности нахождения запроса, можно поставить комментарии в текст запроса – полный текст запроса будет в примерах в выводе pt-query-digest.

Если вы используете какой-либо ORM, то вы уже не имеете полного контроля над выходящим SQL-кодом и поиск источника запроса будет уже сложнее – можно попробовать аналитически вычислить модули и функции, где может генерироваться ваш запрос.

Третий шаг: запускаем повторный мониторинг нагрузки MySQL (slow_query_log с нулевой отсечкой, всех запросов). Для достаточной достоверности желательно собирать запросы в течение нескольких часов (до суток). Этот период должен совпадать с моментом максимальной нагрузки на вашу систему. Внимание: при сборе логов запросов контролируйте его объём, он может расти очень быстро до огромных размеров!

Проводим анализ лога запросов с помощью pt-query-digest и смотрим: как изменилась картина в топовых запросах.

Если причина проблемы устранена, то мы не увидим старого запроса в топе самых нагруженных запросов. Также важно удостовериться, что наш(и) запросы-заменители также не входят в топ запросов (или по крайней мере не вверху списка). В таком случае мы увидим новые запросы в списке самых медленных. Если заметна следующая проблема в виде отдельного запроса или группы запросов, можно провести еще одну итерацию оптимизации. А если запроса-лидера уже нет, значит нужно переносить усилия по оптимизации в другую область (общие настройки системы, увеличение ресурсов сервера, тюнинг MySQL).

Оптимизация настроек (тюнинг) MySQL

Процесс настройки СУБД может быть совсем не простым и продолжительным по времени. Эти системы обладают высокой сложностью и большим количеством настроек. Множество настроек СУБД MySQL влияют на производительность, поэтому начинающие разработчики и администраторы думают, что настройками можно решить любую проблему. На самом деле, оптимальные настройки позволяют всего лишь максимально эффективно использовать ресурсы сервера. То есть, настройки – необходимое, но не достаточное условие высокой производительности. Оптимизация SQL-запросов в большинстве случаев может дать ускорение отклика в 10-100 раз, тогда как настройки в 2-4 раза.

Мы не будем углубляться в подробности и рассматривать все полезные настройки, для этого существуют специальные книги и вебинары (рекомендуем обратиться к видео от компании Percona и книге «High Performance MySQL»).

Приведём основные принципы разумного тюнинга MySQL.

Учитывайте при настройке особенности движков данных, которые вы используете, часто они требуют различные параметры для оптимизации.
При настройке изменяйте по одному параметру за раз.
Измеряйте все изменения – не верьте на слово рекомендациям.
Заранее узнайте соотношение чтения/записи в вашей БД, это будет важно при настройке.
При изменении настроек пытайтесь разобраться, не будет ли проблем с перерасходованием ресурсов (например, оперативной памяти при раздувании буферов).
Копируя настройки из рекомендаций применяйте корректировки на масштаб системы роль вашего сервера (выделенный MySQL-сервер или full-stack).

Главное – не стоит увлекаться бесконечным процессом оптимизации настроек MySQL. Чуда не произойдёт, если все основные параметры уже настроены.

Резюме

Надеемся, что изложенные идеи по оптимизации MySQL будут полезны и помогут избежать типичных ошибок. В этом процессе важно соблюдать баланс между усилиями по оптимизации и получаемым эффектом. Если вы дошли до стадии, когда эффект стремится к нулю, а усилия требуются титанические, нужно остановиться и пересмотреть принцип работы системы – возможно сменить саму технологию хранения и извлечения данных.

Профессиональные услуги по оптимизации MySQL в ваших приложениях заказывайте у нас.

Экспертное ускорение сайтов

Ускорение сайтов
Цена от 69 900 Р