Медленные запросы в PostgreSQL
При высоких нагрузках, некорректно сформированных условиях выборки или просто медленной БД, иногда, происходит очень долгое выполнение или полное зависание запросов, в связи с этим, они начинают скапливаться в очередь, порождая лавинообразный эффект.
Обнаружить зависшие процессы позволяет несложный запрос, выполнить который можно с помощью любого клиента баз данных.
SELECT
pid, client_addr, usename, datname, state, waiting,
to_char(current_timestamp - state_change, 'SSSS.MS') AS runtime,
query
FROM
pg_stat_activity
WHERE
pid <> pg_backend_pid()
AND state = 'active'
AND state_change < current_timestamp - INTERVAL '3' SECOND
ORDER BY
runtime DESC;
Данный пример актуален для PostgreSQL 9.5 (у других версий могут быть небольшие различия в именовании столбцов), давайте проясним некоторые строки, по сути они не являются обязательными и служат исключительно для улучшения восприятия и фильтрации данных в результирующей выдаче:
-- вычисляет длительность выполнения запроса и форматирует его в простой читаемый вид 1.234 (сек)
to_char(current_timestamp - state_change, 'SSSS.MS') AS runtime,
-- исключает из выдачи текущий запрос
pid <> pg_backend_pid()
-- отображает только активные запросы
AND state = 'active'
-- отображает только запросы которые выполняются более 3-х секунд
AND state_change < current_timestamp - INTERVAL '3' SECOND
Если ваши запросы очень длинные и не отображаются полностью, но вам необходимо видеть их целиком, измените значение параметра track_activity_query_size в конфигурационном файле БД или параметрах RDS.
track_activity_query_size=16384
Данная настройка позволит логировать запросы длинной до 16KB, но имейте в виду, что данный параметр является статическим и вступит в силу, только после перезагрузки БД.
Отмена или уничтожение запросов
После обнаружения медленного запроса, можно отменить его используя pg_cancel_backend(pid) или уничтожить при помощи pg_terminate_backend(pid).
Пример запроса уничтожающего все запросы зависшие на более чем 1 минуту в указанной БД:
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
pid <> pg_backend_pid()
AND datname = 'ИМЯ_ВАШЕЙ_БД'
AND state = 'idle'
AND state_change < current_timestamp - INTERVAL '1' MINUTE;
Успешного обнаружения!