MySQL sleep запросы

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

Давайте освежим память перечнем того, как все работает.

  1. пользователь открывает страницу Вашего сайта
  2. на сервер приходит запрос к Apache
  3. Apache посылает запрос PHP
  4. PHP подключается к MySQL
  5. PHP направляет SQL-запрос в MySQL
  6. MySQL выполняет запрос и отдает результат в PHP
  7. PHP закрывает соединение с MySQL
  8. PHP формирует контент и отдает его в Apache
  9. Apache посылает ответ в браузер

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

А может это быть потому, что PHP скрипт не завершает свою работу, и обычно причина:

  • банальная нехватка памяти (память можно увеличить в PHP-конфиге)
  • неправильная логика работы скрипта в результате которой получился безконечный цикл
  • php-процесс просто убили в консоле

Собственно все, исправляйте одну из этих ошибок и SLEEP-запросов не будет.

Подробнее

А теперь подробнее, для тех кто не поверил и хочет разобраться подробнее:

mysql> show full processlist;
+-----+------------+-----------+------------+---------+------+-------+-----------------------+
| Id  | User       | Host      | db         | Command | Time | State | Info                  |
+-----+------------+-----------+------------+---------+------+-------+-----------------------+
| 127 | putin      | localhost | mydb       | Sleep   |  895 |       | NULL                  |
| 130 | root       | localhost | mydb       | Query   |    0 | NULL  | show full processlist |
+-----+------------+-----------+------------+---------+------+-------+-----------------------+

итак, мы видим зависший (уснувший) MySQL-процесс, и в довесок к нему мы можем увидеть зависший Apache-процесс

# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp        0      0 127.0.0.1:80            127.0.0.1:47733         ESTABLISHED 27420/apache2
...

а давайте убьем Apache-процесс, ведь это должно отразиться и на MySQL:

# service apache2 restart

готово, теперь смотрим на MySQL-процессы:

mysql> show full processlist;
+-----+------------+-----------+------------+---------+------+-------+-----------------------+
| Id  | User       | Host      | db         | Command | Time | State | Info                  |
+-----+------------+-----------+------------+---------+------+-------+-----------------------+
| 130 | root       | localhost | mydb       | Query   |    0 | NULL  | show full processlist |
+-----+------------+-----------+------------+---------+------+-------+-----------------------+

и видим, что спящего процесса нет, а это значит, что убив Apache-процесс, у нас убился PHP-процесс, который закрыл соединение с MySQL.

Что такое SLEEP запрос MySQL

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

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

Что делать, чтобы SLEEP запросов не было

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

  • interactive_timeout - Сколько ждать активности от интерактивного клиента перед тем как разорвать соединение.
  • wait_timeout - Сколько ждать активности от любого клиента перед тем как разорвать соединение. Если клиент интерактивный и значение interactive_timeout отличается от wait_timeout, то будет использовано значение interactive_timeout.

Указать параметры можно следующими способами:

1. Указать параметры в файле my.cnf (не забудьте затем перезапустить mysql):

[mysqld]
interactive_timeout=180
wait_timeout=180

2. Если Вы не хотите перезапускать mysql, тогда в mysql-клиенте выполните две команды:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

3. Сделать, чтобы клиент (например PHP) указал данные параметры для текущей сессии:

<?php
mysql_connect('127.0.0.1:3351', 'root', '');
mysql_select_db('test');
mysql_query('set wait_timeout=1');
$result = mysql_query( 'SELECT * FROM table');

p.s. Есть и другие интересные параметры:

mysql> show variables like '%timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    | - кол-во секунд, прежде чем mysql ответит: Bad handshake
| interactive_timeout        | 28800 |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| wait_timeout               | 28800 |
+----------------------------+-------+

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

SHOW VARIABLES WHERE Variable_name='connect_timeout'

или так:

SELECT @@GLOBAL.connect_timeout; -- 10 сек
SELECT @@connect_timeout; -- 10 сек

Timeout

Давайте поиграемся с временем выполнения и прерыванием.

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

SELECT @@GLOBAL.MAX_EXECUTION_TIME, @@SESSION.MAX_EXECUTION_TIME;

Оба значения указывают время в миллисекундах (1000 мс = 1 секунда).

Давайте дле текущей сессии установим время выполнения 3 секунды:

SET SESSION MAX_EXECUTION_TIME=3000;

Теперь проверим, будет ли наш запрос выполняться более 3 секунд, для этого воспользуемся функцией SLEEP:

SELECT SLEEP(4);

Ура, запрос работал всего 3 секунды, но при завершении вернул значение 1 - это значит он был прерван. Если указать значение 2 секунды, то такой запрос возвратит 0 - что значит, что запрос выполнен полностью.

Кто-то скажет, как же так, почему запрос выполнился, а не прервался с ошибкой? Все дело в том, что функция SLEEP знает про значение MAX_EXECUTION_TIME и не будет работать, больше указанного времени. А вот реальный запрос работает иначе:

SELECT 'hi' FROM t1 WHERE SLEEP(1);

ОЧЕНЬ ВАЖНО: такой запрос будет делать паузу 1 секунда при чтении каждой строки из таблицы t1 (это значит, сколько строк в таблице, столько секунд и будет выполняться запрос).

Например, если в таблице t1 имеется 4 строки, то такой запрос будет работать 4 секунды и завершится с ошибкой:

[HY000][3024] Query execution was interrupted, maximum statement execution time exceeded

ведь выше мы задали максимальное время выполнения 3 секунды: MAX_EXECUTION_TIME=3000

p.s. функция benchmark так же как SLEEP и не выполняется больше MAX_EXECUTION_TIME

select benchmark(9999999999, md5('when will it end?'));

PROCESSLIST

Следующие две команды эквивалентны:

SHOW FULL PROCESSLIST;

SELECT * FROM information_schema.processlist;

Так же результат можно представить из списка процессов, любым удобным для Вас способом. Есть некоторые базовые примеры.

Показать количество подключений для каждого пользователя:

SELECT 'USER', COUNT(*) FROM information_schema.processlist GROUP BY 'USER';

Показать количество подключений по каждому хосту:

SELECT 'HOST', COUNT(*) FROM information_schema.processlist GROUP BY 'HOST';

Показать активности пользователя root:

SELECT * FROM information_schema.processlist WHERE 'USER' = 'root';

Показать процессы подобные указанному:

SELECT * FROM information_schema.processlist WHERE 'INFO' LIKE 'SELECT %';

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

SELECT 'DB', AVG('TIME') FROM information_schema.processlist GROUP BY 'DB';

Slow log

Лог медленных запросов -  способ, помогающий понять, какие SQL-запросы мы должны оптимизировать в приложении. 

Данный механизм по-умолчанию выключен и настроен на запись в файл при выполнении запроса более 10 секунд:

SELECT @@GLOBAL.slow_query_log; -- 0 - выключен
SELECT @@GLOBAL.log_output; -- FILE
SELECT @@GLOBAL.long_query_time; -- 10
или так:
SELECT @@slow_query_log; -- 0 - выключен
SELECT @@log_output; -- FILE
SELECT @@long_query_time; -- 10

Давайте включим + т.к. грепать лог-файл неудобно, пропросим MySQL писать в таблицу и писать только в том случае, если запрос выполняется дольше 1 секунды: 

SET global slow_query_log = 1;
SET global log_output = 'table';
SET global long_query_time = 1;

Теперь ждем, пока приложение сделает медленные запросы (или делаем их самостоятельно - см. выше как) и смотрим логи, например таким запросом:

SELECT * FROM mysql.slow_log WHERE query_time > 1 ORDER BY slow_log.start_time DESC;

Из интересного:

  • sql_text - SQL-запрос, который долго выполнялся
  • query_time - указано в секундах, например значение 00:00:02.368703 означает 2 секунды и 368 миллисекунд
  • user_host - означает, какой пользователь и с какого сервера подключался
  • rows_examined - сколько строк было исследовано, чтобы вернуть результат
  • rows_sent - сколько строк было отправлено клиенту
  • lock_time - время, которое запрос провел в ожидании получения блокировки, необходимой для его выполнения. Например, UPDATES запросам нужна блокировка записи.

Удачки в разработке!

Источники: 1 - 2 - 3 - 4 - 5

31.01.2014 13:57