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

Это руководство поможет вам обнаружить их на ранней стадии, остановить их или настроить способ автоматической обработки.

Что такое длительный запрос?

Длительный запрос в MySQL — это запрос, выполнение которого занимает необычно много времени.
Конкретная продолжительность, которая классифицирует запрос как «длительный», может варьироваться в зависимости от стандартов производительности вашего приложения. Как правило, если запрос выполняется дольше обычного и начинает замедлять работу базы данных, он считается длительным.

Основные причины длительных запросов

Причины длительного выполнения запросов могут быть разными:

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

• Ситуации с большой нагрузкой — когда сервер обрабатывает большой объем запросов или одновременно обрабатывает несколько сложных запросов, доступные ресурсы (например, ЦП и память) сильно истощаются. Эта конкуренция за ресурсы может задержать выполнение запросов, что приведет к увеличению времени их выполнения, особенно в периоды пикового использования.

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

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

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

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

Чтобы эффективно управлять длительными запросами, сначала необходимо их идентифицировать. Вот несколько методов:

1. Использование ПОКАЗАТЬ СПИСОК ПРОЦЕССОВ

ПОКАЗАТЬ СПИСОК ПРОЦЕССОВ; Команда — это быстрый способ получить снимок всех активных запросов, выполняемых на вашем сервере. Эта команда отображает каждый запрос вместе с несколькими фрагментами ключевой информации, включая продолжительность выполнения каждого запроса. Те запросы с высоким значением «Время», скорее всего, являются вашими долго выполняющимися запросами. Вот как вы можете использовать эту команду:

ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ;

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

2. Журнал медленных запросов

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

Чтобы запустить журнал медленных запросов, вам необходимо настроить несколько параметров в файле конфигурации MySQL (my.cnf или my.ini):

• Slow_query_log — установите значение 1, чтобы включить журнал.
• Slow_query_log_file — укажите путь к файлу, в котором вы хотите сохранить журнал.
• Long_query_time — установите минимальное время выполнения (в секундах), при котором запрос подлежит регистрации. Например, установка значения «2» будет регистрировать любые запросы, выполнение которых занимает более двух секунд.

3. Схема производительности

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

Убедитесь, что эта функция включена в вашей конфигурации MySQL, добавив следующую строку:

[mysqld]
Performance_schema = ВКЛ

После активации вы сможете просматривать различные таблицы Performance Schema для анализа производительности ваших запросов. Например, если вы хотите точно определить долго выполняющиеся запросы, возможно, вам захочется просмотреть таблицу events_statements_history_long. Вот как вы можете запросить его:

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS «Продолжительность (секунды)»
ИЗ Performance_schema.events_statements_history_long
ГДЕ TIMER_WAIT > 10000000000;

Этот запрос поможет вам найти любые запросы, которые выполняются более 10 секунд. Он дает вам такую ​​информацию, как текст SQL и продолжительность выполнения каждого запроса.

Ручное уничтожение длительных запросов

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

Вы можете узнать идентификатор процесса, выполнив команду SHOW PROCESSLIST, которая отображает все текущие запущенные процессы и их соответствующие идентификаторы. Просмотрите список на наличие запросов, которые имеют высокое значение «Время», которое указывает, как долго они выполняются.

После того как вы определили проблемный запрос и записали его идентификатор процесса, вы можете завершить его с помощью команды KILL:

KILL [идентификатор процесса];

Замените [идентификатор процесса] фактическим номером из вывода SHOW PROCESSLIST.

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

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

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

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

1. Включите планировщик событий.

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

SET GLOBAL event_scheduler = ON;

2. Создайте событие для уничтожения долго выполняющихся запросов.

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

`СОЗДАТЬ СОБЫТИЕ kill_long_running_queries
ПО РАСПИСАНИЮ КАЖДУЮ 1 МИНУТУ — указывает, как часто запускается событие
ДЕЛАТЬ
НАЧИНАТЬ
ОБЪЯВИТЬ выполненное INT DEFAULT FALSE;
ОБЪЯВИТЬ proc_id INT; -- Переменная для хранения идентификатора процесса каждого запроса
ОБЪЯВИТЬ КУРСОР cur1 ДЛЯ ВЫБОР ИДЕНТИФИКАТОРА ИЗ Information_schema.processlist
ГДЕ Команда = «Запрос» И Время > 60; -- Измените «60» на пороговое значение в секундах
ОБЪЯВИТЬ ОБРАБОТЧИК ПРОДОЛЖЕНИЯ ДЛЯ НЕ НАЙДЕННОГО SET Done = TRUE;

ОТКРЫТЬ Cur1;

read_loop: LOOP
FETCH cur1 INTO proc_id;
ЕСЛИ сделано, ТО
ПОКИНУТЬ read_loop;
КОНЕЦ ЕСЛИ;
УБИТЬ proc_id; -- Убивает процесс, указанный в proc_id
КОНЕЦ ЦИКЛА;

ЗАКРЫТЬ Cur1;
КОНЕЦ;`

3. Установите максимальное время выполнения запроса в MySQL.

Контроль максимального времени выполнения запроса помогает предотвратить перегрузку базы данных слишком длительными запросами. Это делается с использованием системной переменной max_execution_time в MySQL 5.7.8 и более поздних версиях путем установки общесистемного ограничения времени выполнения для всех запросов SELECT только для чтения:

SET GLOBAL max_execution_time = 2000;

Это устанавливает предел в 2000 миллисекунд (2 секунды)

Помните, что этот параметр не применяется к хранимым процедурам, функциям или триггерам и сбрасывается до значения по умолчанию при перезапуске сервера, если он не добавлен в ваш файл конфигурации MySQL:

[mysqld]
max_execution_time = 2000

4. Установите максимальное время выполнения запроса в MariaDB.

MariaDB, будучи ответвлением от MySQL, предлагает аналогичный, но отличный подход к управлению временем выполнения запросов. Начиная с MariaDB 10.1.1, для этой цели можно использовать системную переменную max_statement_time:

SET GLOBAL max_statement_time = 2;

Это ограничивает время выполнения всех запросов до 2 секунд.

Для постоянной настройки посредством перезапуска сервера добавьте эту строку в файл конфигурации MariaDB:

[mysqld]
max_statement_time = 2

Выявляйте и устраняйте длительные запросы с помощью Query Analytics от Releem

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

Благодаря Releem нет необходимости вручную просматривать выходные данные PROCESSLIST или просматривать журнал медленных запросов, чтобы выявить неэффективные запросы. Инструмент имеет интуитивно понятную панель управления, которая позволяет легко сортировать и выявлять запросы, которые задерживаются или отнимают слишком много времени. Эта немедленная информация поможет вам быстро выявить и устранить узкие места.