Текущие активности в PostgreSQL

Введение

Что в этой статье будет рассмотрено:

• Вы можете посмотреть на текущие активности сервера PostgreSQL с помощью представления  pg_stat_activity .
• Чтобы завершить один из обслуживающих процессов нужно использовать функцию pg_terminate_backend(<pid>).
• Вы можете, с помощью функции pg_blocking_pids(<pid>), посмотреть кого ожидает процесс с этим pid.

Все эти действия можно выполнить с помощью инструментов командной строки операционной системы:

• посмотреть процессы с помощью команды ps;
• завершить процесс с помощью команды kill -9 <pid>.

Но операционная система не сможет определить чем занят процесс postgress, поэтому ps не будет столь-же информативен, как представление pg_stat_activity.

Как вы помните при работе сервера PostgreSQL работает 1 главный процесс, который запускает остальные и следит за ними. При падении одного из процессов postgres, например когда мы его завершили командой kill -9 <pid> , главный процесс postgres может решить что в базе данных случилась ошибка и перезапустит весь кластер. Поэтому завершать процессы лучше средствами PostgreSQL, с помощью функции pg_terminate_backend(<pid>).

Практика

Создадим таблицу и вставим в неё одно значение, равное 42:

postgres@s-pg13:~$ psql -d admin_monitoring
Timing is on.
psql (13.3)
Type "help" for help.

postgres@admin_monitoring=# CREATE TABLE t(n integer);
CREATE TABLE
Time: 2,281 ms

postgres@admin_monitoring=# INSERT INTO t VALUES(42);
INSERT 0 1
Time: 0,713 ms

Затем заблокируем эту таблицу выполняя в ней транзакцию:

postgres@admin_monitoring=# BEGIN;
BEGIN
Time: 0,076 ms

postgres@admin_monitoring=# UPDATE t SET n = n + 1;
UPDATE 1
Time: 0,422 ms

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

postgres@s-pg13:~$ psql -d admin_monitoring
Timing is on.
psql (13.3)
Type "help" for help.

postgres@admin_monitoring=# UPDATE t SET n = n + 2;

Второй сеанс при этом завис!

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

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' \gx
-[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------------------------------------
pid              | 15360
query            | UPDATE t SET n = n + 1;
state            | idle in transaction
wait_event       | ClientRead
wait_event_type  | Client
pg_blocking_pids | {}
-[ RECORD 2 ]----+------------------------------------------------------------------------------------------------------------------------------------------
pid              | 15396
query            | UPDATE t SET n = n + 2;
state            | active
wait_event       | transactionid
wait_event_type  | Lock
pg_blocking_pids | {15360}
-[ RECORD 3 ]----+------------------------------------------------------------------------------------------------------------------------------------------
pid              | 15412
query            | SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend'
state            | active
wait_event       |
wait_event_type  |
pg_blocking_pids | {}
Time: 5,000 ms

В запросе мы получали следующую информацию:

• pid — идентификаторе процесса;
• query — текст запроса;
• state — состояние процесса;
• wait_event — чего этот процесс ожидает;
• wait_event_type — тип ожидания;
• pg_blocking_pids(pid) — с помощью этой функции, посмотрим pid процесса которого ожидает этот процесс.

Состояние idle in transaction означает, что сеанс начал транзакцию, но в настоящее время ничего не делает, а транзакция осталась незавершенной. Это может стать проблемой, если ситуация возникает систематически, например, из-за некорректной реализации приложения или из-за ошибок в драйвере — поскольку открытый сеанс расходует оперативную память.

В конфигурации сервера есть параметр:

• idle_in_transaction_session_timeout — принудительно завершает сеансы, в которых транзакция простаивает больше указанного времени.

Теперь завершим зависший процесс вручную:

postgres@postgres=# SELECT pg_terminate_backend(15360);
 pg_terminate_backend
----------------------
 t
(1 row)
Time: 0,300 ms

После этого второй терминал должен отвиснуть и запрос выполнится:

postgres@admin_monitoring=# UPDATE t SET n = n + 2;
UPDATE 1
Time: 204915,650 ms (03:24,916)

Дальше посмотрим некоторую информацию по всем процессам (фоновым и обслуживающим) которые есть в этом представлении:

postgres@postgres=# SELECT pid, backend_type, backend_start, state FROM pg_stat_activity;
  pid  |         backend_type         |         backend_start         | state
-------+------------------------------+-------------------------------+--------
 29397 | logical replication launcher | 2021-06-25 15:32:38.013169+03 |
 29395 | autovacuum launcher          | 2021-06-25 15:32:38.014063+03 |
 15396 | client backend               | 2021-06-28 16:46:15.1184+03   | idle
 15412 | client backend               | 2021-06-28 16:47:10.278381+03 | active
 29393 | background writer            | 2021-06-25 15:32:38.014382+03 |
 29392 | checkpointer                 | 2021-06-25 15:32:38.014499+03 |
 29394 | walwriter                    | 2021-06-25 15:32:38.014213+03 |
(7 rows)
Time: 0,651 ms

Сравним с выводом команды операционной системы ps:

postgres@postgres=# \q

postgres@s-pg13:~$ ps -o pid,command --ppid `head -n 1 $PGDATA/postmaster.pid`
  PID COMMAND
15396 postgres: postgres admin_monitoring [local] idle
29392 postgres: checkpointer
29393 postgres: background writer
29394 postgres: walwriter
29395 postgres: autovacuum launcher
29396 postgres: stats collector
29397 postgres: logical replication launcher

Можно заметить, что в представлении pg_stat_activity нет процесса stats collector.

Если понравилась статья, подпишись на мой канал в  VK  или  Telegram .