Собрать медленные запросы к базе данных MySQL Ubuntu

Часто нужно оптимизировать запросы к БД...

Для этого можно собрать медленные запросы к базе данных MySQL Ubuntu

Все настройки обычно хранятся (тут описано внизу статьи)

Параметры сбора медленных запросов:

1) long_query_time - продолжительность запроса в секундах.

• 10 - настолько высока, что почти бесполезна;
• 2 - компромисс;
• 0,5 - и другие фракции;
• 0 - захватывает все; это может забить место на диске достаточно быстро, но может быть очень полезно.

По умолчанию административные инструкции не регистрируются, равно как и запросы, которые не используют индексы для поиска. Это поведение можно изменить с помощью log_slow_admin_statements и log_queries_not_using_indexes, как описано ниже.

По умолчанию журнал медленных запросов отключен (понятно для чего? - чтобы не "грузить" систему).

Включаем лог!!!: Чтобы явно указать начальное состояние журнала медленных запросов, используйте --slow_query_log[={0|1}]. Без аргумента или с аргументом, равным 1, --slow_query_log включает журнал. При аргументе 0 эта опция отключает журнал.

Чтобы указать имя файла журнала, используйте --slow_query_log_file=file_name.

Если вы не укажете имя для файла журнала медленных запросов, имя по умолчанию будет host_name-slow.log. Сервер создает файл в каталоге данных, если не указано абсолютное имя пути для указания другого каталога.

Сервер записывает меньше информации в журнал медленных запросов, если вы используете --log-short-format опцию.

Примеры и как это делать

## Сбор медленных запросов в MySQL на Ubuntu

Существует несколько способов:

1. Включение логгирования медленных запросов:

* Редактирование конфигурационного файла:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

* Найдите секцию `[mysqld]` и добавьте следующие строки:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 2

* `slow_query_log = 1` - включает логгирование медленных запросов.
* `slow_query_log_file = /var/log/mysql/slow_query.log` - устанавливает путь к файлу логов.
* `long_query_time = 2` - устанавливает порог времени для медленных запросов (в секундах).

* Перезапуск MySQL:

sudo systemctl restart mysql

* Просмотр лог-файла:

sudo cat /var/log/mysql/slow_query.log

2. Использование инструментов мониторинга:

* `mysqladmin`:

sudo mysqladmin --host=localhost --user=root --password=your_password variables | grep slow_query_log

* Показывает состояние логгирования медленных запросов.

* `mysql`:

mysql --host=localhost --user=root --password=your_password

* В консоли MySQL можно использовать команду `SHOW STATUS LIKE 'Slow_queries'`;
* Также можно посмотреть информацию о последних запросах с помощью команды `SHOW PROCESSLIST;`

3. Использование инструментов аналитики:

* `Percona Monitoring and Management (PMM)`: Платформа для мониторинга и управления MySQL.
* `MySQL Workbench`: Инструмент для работы с MySQL, включает в себя анализатор медленных запросов.
* `MySQL Enterprise Monitor (MEM)`: Инструмент для мониторинга и оптимизации MySQL, который включает в себя функции аналитики медленных запросов.

Дополнительные советы:

* Используйте `EXPLAIN` для анализа плана выполнения медленных запросов.
* Профилируйте запросы: Используйте инструмент `profiler` для получения подробной информации о времени выполнения разных частей запроса.
* Оптимизируйте свои запросы: Используйте индексы, уменьшайте количество данных, которые обрабатываются запросом.
* Обновите MySQL: Новейшие версии MySQL часто включают в себя улучшения в области производительности.

Важно:

* Не устанавливайте слишком низкий порог `long_query_time`: Это может привести к избыточному количеству записей в логе медленных запросов.
* Регулярно анализируйте лог-файл: Это поможет вам определить медленные запросы и оптимизировать их работу.

Помните, что сбор и анализ медленных запросов - это важная часть оптимизации производительности MySQL.

Тонкие настройки 

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

Чтобы включить медленные административные инструкции в журнал медленных запросов, включите log_slow_admin_statements системную переменную. Административные инструкции включают в себя ALTER TABLE, ANALYZE TABLE CHECK TABLE, CREATE INDEX DROP INDEX, OPTIMIZE TABLE REPAIR TABLE,,,,,,,,,,,,,, и,,.

А это нужно почти всегда

Чтобы включить запросы, которые не используют индексы для поиска строк, в инструкции, записываемые в журнал медленных запросов, включите log_queries_not_using_indexes системную переменную. (Даже при включенной этой переменной сервер не регистрирует запросы, которые не выиграли бы от наличия индекса из-за того, что в таблице меньше двух строк.)

В некоторых системах уже есть подготовленные данные параметры, их нужно только раскомментировать, например в Hestia :


# Enable the slow query log to see queries with especially long duration
slow_query_log = 1 
#slow_query_log_file = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_verbosity = query_plan,explain
#log-queries-not-using-indexes
#min_examined_row_limit = 1000

Именно, slow_query_log при значении 1 - включает запись лога!

long_query_time = 10 - соберет все, что более 10 секунд выполняется, поэтому постепенно можно его уменьшать.

Скорость регистрации медленных запросов

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

По умолчанию эта переменная равна 0, что означает отсутствие ограничения. Положительные значения (1 и более) накладывают поминутное ограничение на ведение журнала запросов, которые не используют индексы. Если равен = 1 - это открывает 60-секундное окно, в течение которого сервер регистрирует запросы до заданного предела, затем подавляет дополнительные запросы. Если по окончании работы окна есть подавленные запросы, сервер регистрирует сводку, в которой указывается, сколько их было, и общее время, затраченное на них. Следующее 60-секундное окно начинается, когда сервер регистрирует следующий запрос, который не использует индексы.

Последовательность обработки параметров

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

1. Запрос должен либо не быть административным заявлением, либо log_slow_admin_statements должен быть включен.
2. Запрос должен был занять не менее long_query_time секунд или log_queries_not_using_indexes должен быть включен, и запрос не использовал индексы для поиска строк.
3. В запросе должно быть проверено не менее min_examined_row_limit строк.
4. Запрос не должен быть подавлен в соответствии с log_throttle_queries_not_using_indexes настройкой.

!! Сервер не регистрирует запросы, обрабатываемые кэшем запросов.

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

Когда включен журнал медленных запросов, сервер записывает выходные данные в любые пункты назначения, указанные log_output системной переменной. Если вы включаете журнал, сервер открывает файл журнала и записывает в него сообщения о запуске. Однако дальнейшее протоколирование запросов в файл не происходит, если не выбрано FILE место назначения журнала. Если адресатом является NONE, сервер не записывает запросы, даже если включен журнал медленных запросов. Установка имени файла журнала не влияет на ведение журнала, если FILE не выбрано в качестве места назначения вывода.

Если журнал медленных запросов включен и FILE выбран в качестве места назначения вывода, каждому оператору, записываемому в журнал, предшествует строка, начинающаяся с # символа и содержащая эти поля (причем все поля в одной строке):

1. Query_time: duration

        Время выполнения инструкции в секундах.

2. Lock_time: duration

        Время получения блокировок в секундах.

3. Rows_sent: N

        Количество строк, отправленных клиенту.

4. Rows_examined:

        Количество строк, проверенных серверным уровнем (не считая какой-либо обработки, встроенной в механизмы хранения).

Каждому оператору, записанному в файл журнала медленных запросов, предшествует SET оператор, который включает временную метку, указывающую, когда был зарегистрирован оператор slow (что происходит после завершения выполнения оператора).