Распространенные узкие места производительности MySQL и способы их устранения

Когда MySQL замедляется, первый симптом редко бывает "база данных медленная". Обычно это страница оформления заказа, которая зависает, очередь, которая перестает обрабатываться, панель мониторинга, которая выходит из строя по тайм-ауту, или API, которому внезапно требуется три секунды для запроса, который раньше выполнялся за 80 мс.

Самый быстрый способ потратить время впустую — настраивать случайные параметры до того, как вы узнаете, где находится ожидание. Начните с простого вопроса: ждет ли MySQL выполнения запроса, блокировок, памяти, диска, ЦП, сети или слишком большого количества соединений? Исправление зависит от ответа.

1. Медленные запросы

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

Выявление медленных запросов

Журнал медленных запросов MySQL — бесценный инструмент для выявления запросов, выполнение которых занимает больше указанного порога. Вы можете включить и настроить этот журнал в файле конфигурации my.cnf (или my.ini).

Пример конфигурации my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

В этом примере:

slow_query_log = 1: Включает журнал медленных запросов.
slow_query_log_file: Указывает путь к файлу журнала.
long_query_time = 2: Устанавливает порог в 2 секунды. Запросы, выполняющиеся дольше этого времени, будут регистрироваться.
log_queries_not_using_indexes = 1: Регистрирует запросы, которые не используют индексы, что часто является основным кандидатом на оптимизацию.

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

Оптимизация медленных запросов

После выявления медленных запросов можно применить несколько стратегий:

Индексирование: Убедитесь, что созданы соответствующие индексы для столбцов, используемых в предложениях WHERE, JOIN, ORDER BY и GROUP BY. Используйте EXPLAIN для анализа планов выполнения запросов и выявления отсутствующих индексов.
- Пример: Если запрос часто фильтрует по user_id в большой таблице orders, индекс на orders(user_id) может кардинально повысить производительность.
```
CREATE INDEX idx_user_id ON orders (user_id);
```
Переписывание запросов: Иногда запрос можно переписать для повышения эффективности. Это может включать упрощение соединений, избегание SELECT * или более разумное использование подзапросов.
- Пример: Замена коррелированного подзапроса на JOIN может обеспечить лучшую производительность.
Проектирование схемы базы данных: Проверка схемы базы данных на предмет проблем нормализации или возможностей денормализации (с осторожностью) также может помочь.

2. Неэффективное индексирование

Хотя индексирование является ключом к производительности запросов, плохо спроектированные или избыточные индексы также могут стать узким местом. Индексы потребляют дисковое пространство и добавляют накладные расходы на операции записи (INSERT, UPDATE, DELETE).

Выявление проблем с индексированием

Анализ плана EXPLAIN: Всегда используйте EXPLAIN до и после внесения изменений в индексы. Ищите полное сканирование таблиц (type: ALL) для больших таблиц или количество проверенных строк, которое намного превышает количество возвращенных строк.
```
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
```
Неиспользуемые индексы: В MySQL 5.6+ есть функция отслеживания использования индексов. Вы можете проверить performance_schema.table_io_waits_summary_by_index_usage, чтобы выявить индексы, которые никогда или редко используются.
Избыточные индексы: Индексы, которые охватывают одни и те же столбцы или являются префиксами других индексов, могут быть избыточными.

3. Буферный пул и конфигурация памяти

Буферный пул InnoDB — это критическая область памяти, где InnoDB кэширует страницы данных и индексов. Недостаточный размер буферного пула может привести к чрезмерному дисковому вводу-выводу, что значительно замедляет операции.

Настройка буферного пула InnoDB

Параметр innodb_buffer_pool_size является одним из наиболее важных параметров для производительности InnoDB.

Рекомендация: Для выделенных серверов баз данных установка innodb_buffer_pool_size на 50-75% доступной оперативной памяти является распространенной отправной точкой. Некоторые системы могут работать с более высокими значениями, но только если операционная система не использует подкачку и память соединений находится под контролем.

Пример конфигурации my.cnf:

[mysqld]
innodb_buffer_pool_size = 8G

Это устанавливает буферный пул размером 8 гигабайт.

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

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Показатель попаданий можно рассчитать как (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

Другие настройки памяти

innodb_log_file_size: Влияет на производительность записи и время восстановления. Файлы большего размера могут улучшить пропускную способность записи, но увеличивают время восстановления после сбоя.
innodb_flush_log_at_trx_commit: Управляет долговечностью по сравнению с производительностью. Установка значения 1 (по умолчанию) обеспечивает полное соответствие ACID, но может быть медленнее. Установка значения 0 или 2 может повысить производительность за счет некоторых гарантий долговечности.

4. Проблемы блокировок и параллелизм

Блокировки необходимы для согласованности данных, но могут стать узким местом, если ими не управлять должным образом. Чрезмерная блокировка может привести к конкуренции запросов, тайм-аутам и взаимоблокировкам.

Выявление проблем с блокировками

SHOW ENGINE INNODB STATUS: Эта команда предоставляет подробную информацию о внутреннем состоянии InnoDB, включая активные транзакции, удерживаемые блокировки и ожидания блокировок.
Таблицы блокировок Performance Schema: В MySQL 8.0 используйте таблицы Performance Schema, такие как data_locks и data_lock_waits. В более старых версиях информация о блокировках была доступна через таблицы information_schema.
Инструменты мониторинга: Инструменты мониторинга производительности часто могут выделить высокое время ожидания блокировок или взаимоблокировки.

Решение проблем с блокировками

Оптимизация запросов, вызывающих блокировки: Более короткие и эффективные запросы сокращают время удержания блокировок.
Управление транзакциями: Делайте транзакции как можно короче. Избегайте длительных операций в рамках транзакций, требующих обширной блокировки.
Гранулярность блокировок: InnoDB использует блокировку на уровне строк для большинства операций, что в целом хорошо для параллелизма. Однако важно понимать, как ваши запросы могут привести к блокировкам таблиц (например, ALTER TABLE без онлайн DDL).
Обнаружение и разрешение взаимоблокировок: В MySQL есть детектор взаимоблокировок. При обнаружении взаимоблокировки InnoDB обычно откатывает одну из участвующих транзакций, позволяя другой продолжить. Проанализируйте информацию о взаимоблокировке из SHOW ENGINE INNODB STATUS, чтобы понять причину и скорректировать логику приложения или порядок запросов.

5. Конкуренция за ресурсы (ЦП, диск, сеть)

Даже при оптимизированных запросах и правильной конфигурации недостаточные аппаратные ресурсы или конкуренция за эти ресурсы могут ограничить производительность.

Выявление узких мест ресурсов

Использование ЦП: Высокая загрузка ЦП процессом mysqld может указывать на неэффективные запросы, интенсивную сортировку или недостаточную вычислительную мощность.
Дисковый ввод-вывод: Высокая активность чтения/записи на диск, особенно при низком показателе попаданий в буферный пул, указывает на то, что узким местом является дисковый ввод-вывод. Ищите высокое время iowait в системах Linux.
Пропускная способность сети: Чрезмерный сетевой трафик может возникать при передаче больших наборов результатов или большом количестве клиентских соединений.

Устранение узких мест ресурсов

Модернизация оборудования: Иногда самым простым решением является добавление ЦП, ОЗУ или более быстрого хранилища. Относитесь к этому как к исправлению только после того, как вы убедитесь, что рабочая нагрузка разумна; оборудование может скрыть плохой запрос, но редко заставляет его исчезнуть.
Оптимизация запросов: Уменьшите объем обрабатываемых и передаваемых данных, что косвенно снижает нагрузку на ЦП, диск и сеть.
Пул соединений: Внедрите пул соединений в вашем приложении, чтобы уменьшить накладные расходы на установление новых соединений и эффективно управлять количеством активных соединений.
Реплики для чтения: Для рабочих нагрузок с интенсивным чтением рассмотрите возможность настройки реплик для чтения, чтобы распределить нагрузку чтения с основного сервера.

Порядок триажа, который работает под давлением

Когда происходит инцидент, не начинайте с полного проекта настройки. Сначала получите быструю картину.

Проверьте активные запросы:

SHOW FULL PROCESSLIST;

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

Проверьте состояние InnoDB:

SHOW ENGINE INNODB STATUS\G

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

Проверьте, насыщен ли сервер:

top
vmstat 1
iostat -xz 1
ss -s

Высокий ЦП с низким вводом-выводом обычно указывает на дорогостоящее выполнение запросов, сортировку, синтаксический анализ или слишком высокий параллелизм. Высокий iowait указывает на хранилище. Активность подкачки — это красный флаг; MySQL под давлением подкачки часто ведет себя непредсказуемо.

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

Штормы соединений

Распространенным узким местом MySQL является не один плохой запрос, а слишком много соединений приложений, выполняющих небольшие объемы работы. Если каждый веб-воркер открывает свое собственное соединение и приложение внезапно масштабируется, MySQL может тратить слишком много времени на планирование сессий и выделение памяти на каждое соединение.

Симптомы включают:

Резкий рост Threads_connected.
Threads_running остается высоким.
Ошибки приложения, такие как Too many connections.
Рост ЦП без одного очевидного медленного запроса.

Полезные проверки:

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Исправление часто находится на уровне приложения: используйте пул соединений, установите разумные лимиты пула и сделайте тайм-ауты явными. Увеличение max_connections может выиграть время, но также может привести к более сильному падению сервера, если каждое соединение использует память для соединений, сортировок и временных таблиц.

Временные таблицы и сортировки

Запросы с GROUP BY, ORDER BY, DISTINCT или большими соединениями могут создавать временные таблицы. Некоторые временные таблицы остаются в памяти. Более крупные вытесняются на диск. Временные таблицы на диске не обязательно являются катастрофой, но внезапное увеличение часто объясняет скачки задержки.

Проверьте:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Затем проверьте планы запросов. Если EXPLAIN показывает Using temporary и Using filesort, спросите, может ли индекс поддерживать фильтр и сортировку вместе. Например:

SELECT customer_id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Индекс на (status, created_at) может уменьшить как фильтрацию, так и работу по сортировке. Увеличение tmp_table_size может помочь в некоторых случаях, но это риск на сессию. Если много сессий одновременно выделяют большие временные таблицы, память быстро заканчивается.

Задержка репликации как симптом производительности

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

Проверьте статус реплики с помощью инструментов, подходящих для вашей версии MySQL:

SHOW REPLICA STATUS\G

В более старых версиях используется:

SHOW SLAVE STATUS\G

Задержка может возникать из-за медленных SQL-запросов на реплике, больших транзакций на основной, недостаточного аппаратного обеспечения реплики, заданий по обслуживанию построчного обновления или сетевых проблем. Исправление может заключаться в настройке запросов, разбиении больших операций записи на более мелкие части, улучшении ресурсов реплики или изменении маршрутизации свежих чтений.

Что изменить в первую очередь

Отдавайте предпочтение исправлениям, которые уменьшают объем работы:

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

Будьте более осторожны с исправлениями, которые только увеличивают емкость:

Увеличение max_connections.
Глобальное увеличение буферов сортировки и соединений.
Увеличение лимитов временных таблиц.
Добавление реплик без исправления запроса, который им вредит.

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