Образовательный проект «SnakeProject» Михаила Козлова

⇒ FreeBSD and Nix ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

SQL\T-SQL

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

Найти и остановить выполнение проблемных запросов в PostgreSQL

Найти и остановить выполнение проблемных запросов в PostgreSQL

В статье приведены примеры поиска блокировок и проблемных запросов

1. Найдите pid

PostgreSQL:
- создает один процесс на соединение
- идентифицирует каждый процесс с помощью идентификатора процесса ОС pid

Чтоб отменить запрос, нужно знать pid для соединения, на котором он запущен
Один из способов узнать информацию о запросах — представление pg_stat_activity

Например, попробуйте этот запрос:
SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;

substr ограничивает отображаемый текст SQL до 100 символов
backend_type избегает отображения фоновых серверных процессов
backend_start сначала показывает самые долго работающие соединения

Теперь у вас есть pid, можете остановить нужный запрос

2. Завершить или отменить процесс

PostgreSQL имеет две функции остановки запросов

«Жесткая» функция — pg_terminate_backend:
SELECT pg_terminate_backend(pid);

«Мягкая» функция — pg_cancel_backend:
SELECT pg_cancel_backend(pid);

Есть два отличия, которые делают pg_terminate_backend «жестче».

Во-первых, pg_terminate_backend полностью останавливает процесс, заставляя соединение закрываться.
Это откатывает любую открытую транзакцию в соединении, освобождая все блокировки, которые оно удерживает.

Напротив, pg_cancel_backend прерывает только выполняющийся запрос, оставляя соединение открытым.
Текущая транзакция или точка сохранения прерывается.
Таким образом, если соединение использует точки сохранения, оно все еще может держать окружающую транзакцию открытой с ожидающими изменениями данных и блокировками.

Во-вторых, pg_terminate_backend применяется немедленно*, в то время как pg_cancel_backend может быть отложен в определенных точках жизненного цикла процесса бэкэнда.
Поэтому иногда вы можете запустить pg_cancel_backend и увидеть, что некоторое время ничего не происходит.
В частности, это может произойти, когда процесс считывает входные данные от клиента, например входящий запрос.
Процесс бэкэнда откладывает обработку отмены до тех пор, пока все входные данные не будут прочитаны, поскольку в противном случае соединение не может оставаться открытым и функциональным.

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

Обнаружение надоедливых выполняющихся запросов

Если вы хотите остановить более одного запроса, может быть обременительно запускать pg_terminate_backend() для них по одному.
Вы можете использовать SQL для поиска плохих запросов и генерировать операторы terminate, чтобы с легкостью избавиться от них всех.
Вот несколько примеров.

Запросы, блокирующие определенный процесс
Если вы выполняете ALTER TABLE и обнаруживаете, что он заблокирован, ожидая блокировки таблицы, вы можете завершить соединения, удерживающие блокировки этой таблицы.
Это позволит ALTER TABLE продолжить работу.

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

Вы можете найти заблокированный pid ALTER TABLE с помощью запроса к pg_stat_activity следующим образом:
SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
ORDER BY backend_start;

При наличии заблокированного pid вы можете использовать этот запрос с pg_blocking_pids для генерации SQL-кода для завершения блокирующих процессов:
SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(blockedpid));

Чтобы выполнить этот запрос, замените blockedpid на pid заблокированного процесса. Затем скопируйте и вставьте выходные строки и запустите их.

Запросы к определенной таблице

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

Этот запрос сгенерирует SQL для завершения всех запущенных запросов, которые выглядят будто они используют определенную таблицу с именем auth_user:
SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE query LIKE '%auth_user%'
  AND pid != pg_backend_pid();

Чтобы использовать этот запрос, измените имя сопоставленной таблицы в LIKE '%auth_user%' перед его запуском.
Затем скопируйте и вставьте выходные строки и запустите их.

Сопоставление запросов с LIKE немного грубовато, так как есть вероятность ложных срабатываний, но это просто.
Сравнение с pg_backend_pid необходимо, чтобы избежать сопоставления текущего соединения.

Подключения открыты дольше N секунд

Отфильтровать подключения, которые открыты дольше N секунд. Это довольно тупой молоток, но вы можете попробовать его в экстренных случаях.
SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
      AND pid != pg_backend_pid()
      AND backend_start < NOW() - '20 seconds'::interval;

По необходимости измените «20 seconds».

Комментарии пользователей