Выявляем проблемные запросы

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

Долгие запросы

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

1. Добавим в файл postgresql.conf параметр  log_min_duration_statement=время_в_мс. Нужно найти идеальный баланс: чем меньше пороговое значение, тем больше запросов мы сохраним и больше ресурсов потратим на их отслеживание.

2. Обновим конфигурацию командой SELECT pg_reload_conf(); или перезапустим сервер, чтобы применить настройки.

3. Посмотрим, как это работает при log_min_duration_statement=10000 (10 с): запустим два тривиальных запроса:

   SELECT pg_sleep(5);
   SELECT pg_sleep(12);

Первый запрос короткий, меньше 10 с. Для второго запроса сработает таймер, а информация запишется в лог-файл:

[2263] LOG: duration: 12007.490 ms 
rows: 1 
size: 6 bytes statement:
SELECT pg_sleep(12);

Такой результат мы получим при использовании настроек по умолчанию. Если понадобится развёрнутая информация о запросе, можно настроить логирование более точно. Список параметров — в документации.

Подозрительные запросы

Попробуем отслеживать статистику выполнения всех операторов SQL. Для этого подключим модуль pg_stat_statements:

1. В файле postgresql.conf укажем shared_preload_libraries ='pg_stat_statements'

2. Создадим расширение CREATE EXTENSION pg_stat_statements;

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

• query — текст запущенного запроса; 

• сalls — количество вызовов запроса с точностью до констант;

• total_exec_time — общее время, затраченное на все запросы; 

• mean_exec_time — среднее время выполнения запроса;

• stddev_exec_time — стандартное отклонение по времени;

• rows — общее число полученных или затронутых строк. 

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

• Время выполнения зависит от количества строк: запросы с небольшим числом строк выполняются быстро, с большим — медленно. Второй случай следует оптимизировать.

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

Избыточные запросы

Отсортируем нашу статистику по total_exec_time — суммарному времени выполнения всех итераций одного запроса:

SELECT *
FROM pg_stat_statements
ORDER BY  total_exec_time DESC;

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

• Не используется ли запрос для перестраховки. Например, разработчик приложения не очень хорошо разобрался в работе СУБД и на всякий случай пингует базу SELECT 1 перед каждым изменением таблицы или после него.

• Не вычисляет ли запрос одно и то же выражение на основе редко меняющихся данных. Результат повторяющихся вычислений SQL-запроса можно сохранить с помощью представления — теперь вам не придется повторять запрос, пока данные не изменятся.

Отсортируем запросы по загрузке ЦПУ (cpu_perc) — времени, потраченному на выполнение всех итераций одного запроса относительно суммарного времени выполнения всех запросов. Порядок запросов, конечно, не изменится, но сам параметр проиллюстрирует степень влияния такого запроса на общую производительность системы:

SELECT *
(100 * total_exec_time/sum(total_exec_time) OVER ()) AS cpu_perc
FROM pg_stat_statements
ORDER BY cpu_perc DESC;

Способ для любознательных

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

Наблюдаем за запросами в реальном времени

А теперь проверим, что запущено прямо сейчас. Не блокируется ли запрос, оптимально ли выполняется. 

Что сейчас запущено?

На этот вопрос ответит системное представление pg_stat_activity, содержащее одну строку для каждого активного бэкенда. 

SELECT pid, backend_type, state, query, wait_event_type, wait_event
FROM pg_stat_activity;

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

Не заблокирован ли запрос?

Чтобы увидеть заблокированные процессы, можно использовать встроенные в IDE инструменты или специальные программы, а можно самостоятельно написать запрос к системным представлениям. Нам понадобится представление pg_locks, чтобы видеть информацию по имеющимся блокировкам, и pg_stat_activity, чтобы быть в курсе актуальных процессов. Самая простая версия такого запроса имеет вид:

SELECT * FROM pg_locks #данные о блокировках
LEFT JOIN pg_stat_activity #последний активный запрос
ON pg_locks.pid = pg_stat_activity.pid;

Разобраться в таблице результатов, полученной после выполнения такого запроса, будет довольно сложно. Можно написать более грамотный запрос, и необязательно самому: по ключевым словам pg_locks monitoring находим запрос, который выдаст нам таблицу, явно отображающую, какой запрос кем блокируется. 

Выполняется ли запрос оптимально и без ошибок?

Ответить на этот вопрос поможет команда EXPLAIN.

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

Важно: EXPLAIN (без флага ANALYSE) не запускает сам запрос, а лишь описывает его выполнение на основе статистических данных, доступных планировщику.

Рассмотрим вывод команды EXPLAIN на примере запроса, считающего максимальную прибыль среди всех реализованных товаров.

EXPLAIN SELECT max(income.value - expense.value)
FROM income FULL JOIN expense ON income.id = expense.id
WHERE expense.value < income.value;

Результат запроса:

QUERY PLAN                               	 
---------------------------------------------------------------------------------
 Aggregate  (cost=67362.98..67362.99 rows=1 width=4)
   ->  Hash Join  (cost=15417.00..63304.62 rows=266667 width=8)
     	Hash Cond: (income.id = expense.id)
     	Join Filter: (expense.value < income.value)
     	->  Seq Scan on income  (cost=0.00..7213.00 rows=500000 width=8)
     	->  Hash  (cost=7213.00..7213.00 rows=500000 width=8)
           	->  Seq Scan on expense  (cost=0.00..7213.00 rows=500000 width=8)
(7 rows)

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

• auto_explain.log_min_duration (integer) — число миллисекунд, начиная с которого мы регистрируем план;

• auto_explain.log_analyze (boolean) — сохранять explain или explain analyze.

Чтобы разобраться в непростых результатах EXPLAIN для сложных запросов, можно воспользоваться визуализаторами плана запроса, которые легко ищутся по фразе “explain visualizer postgres”. Первым вам наверняка попадётся этот.

Перерисуем запрос в виде дерева. 

По данным планировщика, обе таблицы состоят из 500000 строк, которые мы будем просматривать последовательным сканированием. Объединим таблицы методом Hash Join.

Чтобы выжать из плана больше информации, используем флаг ANALYZE. В отличие от EXPLAIN, который описывает выполнение запроса, EXPLAIN + ANALYZE  запрос выполняет. Помните:

• Если вы не хотите изменять данные, а интересуетесь только подробностями выполнения запроса, оберните его в транзакцию и сделайте ROLLBACK.

• EXPLAIN выполняется практически моментально и не нагружает БД, а EXPLAIN ANALYSE может отнять время и ресурсы системы, чтобы выполнить SQL-запрос.

К предполагаемым параметрам cost, rows и width теперь добавятся данные реального выполнения: 

• times — сколько времени оператор затратил в этом узле плана;

• rows — сколько строк на самом деле обработал оператор;

• loops — сколько циклов обработки потребовалось.

В арсенале планировщика есть несколько методов сканирования и объединения таблиц. Чтобы понять, как планировщик выбирает метод в каждом случае, разберёмся с параметром cost в нашем плане. 

Стоимость описывается двумя числами: 

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

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

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

Может быть, запрос ускорится, если проиндексировать используемые таблицы? На самом деле в нашем примере результат не изменится: планировщик продолжит использовать последовательное сканирование вместо сканирования по индексу. Чтобы понять причину, посмотрим, как планировщик выбирает самый оптимальный метод на примере Join.

Метод Nested Loop не требует предварительной подготовки (первое число в cost равно 0) и имеет квадратичную сложность. У Merge Join затраты на подготовку зависят от количества строк в этом узле, сложность увеличивается линейно. Hash Join аналогичен Merge Join, но имеет больший коэффициент и требует больше времени на подготовку.

Поскольку мы выбираем самый оптимальный вариант, на каждом отрезке смотрим нижний график. Если предполагаемое количество строк меньше, чем N₁, смотрим Nested Loop, если больше N₂ — Hash Join, иначе — Merge Join. 

Выбор операции математически обоснован, но выбор оптимального варианта не гарантирован по двум причинам:

• Мы лишь предполагаем количество строк на основе статистики, которая может отставать от реальных данных.

• Фактическая стоимость операции может отличаться от теоретической. В случае с Hash Join функция линейная, но если выбрана неоптимальная хэш-функция или у нас множество повторяющихся идентификаторов, значение может достигнуть N^₂.

  

Изобразим пунктирными линиями предполагаемую планировщиком сложность операций, а сплошными — фактическую. Планировщик принимает решение на основе известной ему информации: если получает количество строк, меньшее N₁, то выбирает первый метод (на рисунке обозначен сиреневым), если больше — второй (изображён синим). Может случиться так, что сложность «синего» метода выше, но если количество строк окажется равным N₁ или больше, планировщик выберет именно его.

Вернёмся к нашему запросу. Нам казалось, что индексы ускорят выполнение за счёт использования сканирования по индексу, но планировщик посчитал иначе и продолжает использовать последовательное. Можем ли мы «заставить» его выбрать другой способ сканирования?

У каждого метода, известного планировщику, есть параметр конфигурации enable_<метод>. По умолчанию все параметры включены. Попробуем выключить последовательное сканирование.

SET enable_seqscan = off

На самом деле полностью отключить метод нельзя. Команда выше лишь добавляет 10 млрд условных единиц к стоимости метода.

Повторим наш запрос и рассмотрим полученные результаты, изображённые в виде дерева.

После отключения последовательного сканирования планировщику пришлось сканировать по индексу. Стоимость такого сканирования действительно была выше, чем у Seq Scan, поэтому индекс не использовался. Метод соединения тоже изменился — Merge Join вместо Hash Join.

«Изменение методов сканирования и объединения» звучит довольно абстрактно, поэтому рассмотрим более приземлённую величину — время выполнения. Оно уменьшилось в 2,5 раза! Мы нашли более удачный план, чем тот, что выбрал планировщик!

Злоупотреблять этим трюком не стоит, поскольку:

• Обычно планировщик выбирает лучший способ выполнения запроса и обыграть его не так-то просто.

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

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

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

• sr_plan позволяет сохранить для конкретного запроса конкретный план выполнения и использовать его для последующего выполнения аналогичных запросов;

• pg_hint_plan позволяет управлять планом выполнения запроса: считывает фразы-указания из комментариев внутри SQL-запроса. Например, указание использовать конкретный метод операции JOIN;

• AQO с помощью методов машинного обучения улучшает оценку количества строк, способствуя выбору наилучшего плана.

Мы научились анализировать планы запросов, но как поступить, если запрос выполняется уже долгое время? Отменить запрос или дождаться результата? Однозначного ответа нет, а план мы сохранить забыли. Но выход есть: модуль pg_query_state отображает результат команды EXPLAIN ANALYZE прямо во время выполнения запроса. Подробно о pg_query_state мы расскажем в отдельной статье, которую приурочим к релизу новой версии модуля. Следите за публикациями на хабе Postgres Professional.

На сколько процентов выполнен запрос?

Если запрос ничем не блокируется, но выполняется долгое время, возникает желание узнать — на сколько он уже выполнился, точно ли с ним всё в порядке. 

С системными запросами помогут динамические представления pg_stat_progress_* для команд  ANALYZE, CREATE INDEX, VACUUM, CLUSTER, Base Backup, COPY. Их содержимое будет зависеть от конкретной команды, но обязательно будет содержать pid и текст команды, на каком отношении и в какой фазе находится запрос, сколько блоков, строк или байтов уже обработано.

Отправим два запроса select * from pg_stat_progress_vacuum; с разницей в одну секунду. 

Мы видим, что увеличилось количество «мёртвых» строк и изменился номер отношения, на котором операция выполнялась. Значит, вакуум обработал одну таблицу и переключился на следующую. Если мы знаем общее количество «мертвых» строк, то можем понять, на сколько процентов выполнена операция.

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

Подводим итоги

Напоследок соберём всё, что узнали, в пошаговый план поиска и отлова долгих запросов:

1. Находим запросы – кандидаты на улучшение.

2. Убираем избыточные запросы, переносим несрочные.

3. Проверяем блокировки.

4. Изучаем планы долгих запросов.

5. При необходимости создаём или удаляем индексы, настраиваем параметры, подключаем полезные расширения.

6. Проверяем степень выполнения запроса.

7. Решаем, продолжить выполнение запроса или отменить его.

Буду рада, если наш опыт отлова неоптимальных запросов поможет и вам. Делитесь своими способами в комментариях!