Здесь описывается процедура, предназначенная для предохранения базы данных высоконагруженной системы от перегрузки.
После того, как ваши запросы оптимизированы, по идее у вас не должно возникать ситуаций, когда
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-ах, и последующая — в выявляемых в процессе эксплуатации системы.
Автор: dankey
