BackendlyLab
PostgreSQL • Backend • DevOps

Canceling statement due to statement timeout in PostgreSQL

Коротко и быстро

Ошибка canceling statement due to statement timeout значит, что SQL-запрос не успел выполниться за время, установленное в параметре (конфигурация PostgreSQL) «statement_timeout» (учитывается полное время запроса, включая ожидание освобождения блокировок). Есть два способа решить эту проблему — увеличить лимит или оптимизировать запрос, который не выполнился. Лучше всего идти в сторону проверки и оптимизации запроса, т.к. неэффективный запрос может выполняться слишком долго или создавать дополнительную нагрузку на базу.

Теперь разберём ошибку canceling statement due to statement timeout подробнее

По умолчанию в PostgreSQL время выполнения не ограничено. Но можно установить лимит задав параметр statement_timeout. Надо ли ограничивать время выполнения? Допустив ошибку в запросе можно получить аналог бесконечного цикла и запрос будет продолжать своё выполнение пока не будет отменён, в этом случае лимит будет полезен. Также, с помощью лимита можно следить, чтобы в системе не было запросов, занимающих много времени, даже если один из запросов начинает долго работать, могут появиться другие запросы, ожидающие выполнение первого, и так ожидание может нарастать и увеличивать время выполнения остальных.

Пример ошибки:

ERROR: canceling statement due to statement timeout
CONTEXT: while executing query

Основные причины возникновения ошибки

  • Сложные запросы. Запросы с подзапросами, множеством JOIN, и агрегатных функций могут выполняться слишком долго.
  • Блокировки. Если другая транзакция удерживает блокировку на нужных строках или таблице, запрос будет ждать её освобождения. В statement_timeout также входит время ожидания, поэтому может возникнуть ситуация, когда запрос будет отменён ещё до начала фактического выполнения.
  • Отсутствие индексов. Без индексов PostgreSQL вынужден выполнять Sequential Scan (полное сканирование), а это в большинстве случаев значительно замедляет запрос.
  • Неправильные индексы. Добавление индексов не является панацеей для ускорения работы запросов. Неправильно выбранные индексы могут не использоваться оптимизатором или приводить к неэффективному плану выполнения.
  • Высокая нагрузка на сервер. При пиковой нагрузке ресурсы CPU и памяти могут быть исчерпаны, что замедляет выполнение запросов.
  • Некорректные настройки. Иногда для работы приложения действительно требуется выполнить запрос, который длится больше чем значение statement_timeout.

Диагностика проблемы

Для начала нужно узнать какой запрос вызвал ошибку:

1. Настроить логирование медленных запросов. Для этого нужно установить значение в параметре log_min_duration_statement (например 1000мс — 1 секунда). В логах будет текст запроса и время выполнения.

2. Отслеживание активных запросов.

SQL покажет все запросы, выполняющиеся дольше 5 минут:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle'
  AND query IS NOT NULL
ORDER BY duration DESC;

3. Определить в тестовой среде приложения время выполнения запросов.

4. Определив проблемный запрос нужно его исследовать с помощью EXPLAIN ANALYZE. Он покажет фактический план который PostgreSQL использовал при выполнении, и время выполнения каждой операции.

Исследовать план выполнения запроса:

EXPLAIN ANALYZE
SELECT * FROM table_name
    CROSS JOIN table_name2
    INNER JOIN table_name3 USING (name)
ORDER BY sort DESC;

Как избавиться от ошибки

1. Правка значения statement_timeout

Самое простое, что можно сделать это повысить лимит. Но это стоит делать только если есть понимание, что приложению действительно требуется больше времени на определённые запросы, или как срочное решение ситуации когда приложение из-за этой ошибки не работает. Не повышайте statement_timeout только потому что это просто.

Изменение лимита:

SHOW statement_timeout; -- посмотреть текущий statement_timeout

SET statement_timeout = '30s'; -- установить значение для сессии 30 секунд (можно указать значение в мс — 30000)

ALTER DATABASE mydb SET statement_timeout = '30s'; -- установить значение для БД

2. Оптимизация запросов

Ускорение запросов это тема для отдельной статьи, а может и нескольких. Тут я могу привести только несколько общих рекомендаций

  • Вложенные подзапросы в большинстве случаев стоят дороже чем JOIN, лучше использовать их
  • В PostgreSQL есть матвью, их можно использовать для того, чтобы заранее собрать данные из нескольких таблиц или результаты вычислений
  • Добавить индексы на поля по которым часто осуществляется поиск и JOIN. Например, если часто используется поиск только активной сущности (select * from t_entity where id_category = 999 and active = 1), стоит добавить индекс не только по id_category, но и составной (id_category, active)
  • Если запрос выдаёт большое количество результатов, то стоит его ограничить и отдавать результаты по частям с помощью LIMIT и OFFSET или других видов пагинации.
  • Работа с таблицей в которой огромное количество записей тоже занимает много времени. В PostgreSQL можно разбить таблицы на partition. Например, для таблицы сохраняющей историю изменений можно добавить партиционирование по дате изменения и это упростит выполнение запроса получающего последние изменения.

canceling statement due to statement timeout возникает когда PostgreSQL принудительно останавливает запрос, превысивший лимит, установленный в параметре statement_timeout. Чаще всего эта ошибка сигнализирует о необходимости проверить план выполнения запроса и состояние базы. Простое увеличение лимита редко является правильным решением.

Похожие статьи