MySQL Query Killer — предохранитель от перегрузки СУБД

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

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

Потому хорошим «сторожем работоспособности» будет умный «Query killer»,
который будет отслеживать подозрительные ситуации и освобождать базу данных.

Этот киллер допускает ситуацию, когда БД выполняет пару тяжелых запросов.
Но когда он видит, что начинает появляться много долгих запросов — то начинает принмать меры

Назначение

Query killer предназначен для:
1. Отслеживание ситуаций, когда:
1.1. Некоторые запросы делают lock-и, блокирующие другие запросы
1.2. Большое количеслых запросов, запущенных в короткий промежуток времени блокируют друг друга
2. Логировния таких ситуаций
3. Разрешения таких ситуаций путем:
3.1. Для ситуаций, когда один запрос блокирует другие — убить источник блокировок: убить самый долгий запрос
3.2. Для ситуаций, когда запросы блокируют друг друга — попытаться освободить базу данных: убить ряд запросов, которые длинее, чем некоторый нижний порог

Так, даже если когда-то ваши оптимизированные процедуры по генерации кеша или рабочие запросы снова начнут глючить — Qeury-Killer не допустит, чтобы из-за регенерации кеша загнулась БД, а просто убьет регенирацию и зарепортит лог ошибок.
(Регенерация кэша должна предусматривать возможность своей гибели, и генерироваться во временную таблицу, которая позже переименовывается в целевую.)

Логика — псевдокод

Ниже приведен псевдокод для Query-killer

-- CONSTANTS:
SET @alert_query_duration:=10;
SET @alert_queries_max_count:=6;

SET @dangerous_query_duration:=15;
SET @dangerous_queries_max_count:=30;
SET @dangerous_queries_affect_duration_bottom:=5;

-- Check long queries:
SELECT @alert_queries_count:=COUNT(0) FROM information_schema.processlist
WHERE COMMAND != 'Sleep' AND TIME>@alert_query_duration
ORDER BY TIME DESC;

IF (@alert_queries_count >= @alert_queries_max_count) THEN

    1. Report to error_log:
        module = Query killer
        error = Maximum amount of long queries reached
        context = List of all long queries (which longer than @alert_query_duration)

    2. Take actions:

    -- Check if there is a lot of dangerous queries already 
    -- (which may cause each other locks):
    SELECT @dangerous_queries_count:=COUNT(0) FROM information_schema.processlist
    WHERE COMMAND != 'Sleep' AND TIME>@dangerous_query_duration
    ORDER BY TIME DESC;

    IF (@dangerous_queries_count >= @dangerous_queries_max_count) THEN

        -- KILL dangerous queries and queries, which probably might be already affected by them
        -- (so kill all queryes, where TIME>@dangerous_queries_affect_duration_bottom)
        ...

    ELSE

        -- KILL one query (the longes one) which probably locks all others
        ...

    END

END

Примеры ситуаций

Ниже приведены примеры опасных ситуаций:

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

 97669965      root       localhost        gtf       289  Query CREATE TABLE gtf.cache_vw_...
  99057101      root localhost:33092        gtf       284  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i
  99057467      root localhost:51863        gtf       276  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i
  99057499      root localhost:51868        gtf       275  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i
  99057840      root localhost:33164        gtf       267  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i
  99057907      root localhost:54313        gtf       266  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i
  99057987      root localhost:59942        gtf       264  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i
  99059528      root localhost:52062        gtf       229  Query UPDATE media INNER JOIN image_file USING(media_id) INNER JOIN i

Здесь первый запрос на создание cache-таблицы лочит обновление таблицы media

2. Большое количеслых запросов, запущенных в короткий промежуток времени и блокируют друг друга

Это могут быть запросы, использующие Using temporary; Using filesort

+------+-------------+-------+--------+----------------------------------+----------------------------------+---------+-----------------+------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys                    | key                              | key_len | ref             | rows | Extra                                        |
+------+-------------+-------+--------+----------------------------------+----------------------------------+---------+-----------------+------+----------------------------------------------+
|    1 | SIMPLE      | vw    | ref    | fk_cache_vw_video_website1 | fk_cache_vw_video_website1 | 4       | const           | 8643 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | mtc   | eq_ref | PRIMARY,content_id               | PRIMARY                          | 4       | gtf.vw.media_id |    1 | Using where                                  |
+------+-------------+-------+--------+----------------------------------+----------------------------------+---------+-----------------+------+----------------------------------------------+

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

Скрипт полезен в системах, где применяется методология разработки не ставящая целью «преждевременную» оптимизацию всех возможных участков системы,
а где первичная оптимизация производится в очевидных bottleneck-ах, и последующая — в выявляемых в процессе эксплуатации системы.