Відстеження активних транзакцій у реальному часі з pg_stat_activity

pg_stat_activity — це, по суті, вікно у реальному часі, яке допомагає зрозуміти, що відбувається у твоїй базі даних прямо зараз. У попередній лекції ми розглянули основи, тепер зануримось у детальну роботу з цим потужним інструментом.

Приклад базового запиту до pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Цей запит виведе всі активні підключення та поточні запити. Круто! Але даних буде забагато, і можна витратити вічність, гортаючи їх. Тому корисно відфільтрувати найважливішу інфу.

Основні поля в pg_stat_activity

Давай глянемо на ключові поля, які тобі знадобляться на додачу до вже відомих. query_start показує час початку виконання запиту — це критично для визначення довгих операцій. pid містить ідентифікатор процесу з'єднання — це треба для керування (наприклад, завершення) з'єднанням. state_change показує, коли було встановлено поточний стан підключення, що особливо корисно для аналізу довгоживучих проблемних станів.

Приклад вибірки активних процесів:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Як відстежити довгі запити?

Уяви, що ти адміністратор бази даних, і раптом навантаження на сервер злетіло до небес. Що робити? Спочатку треба зрозуміти, який запит з'їдає всі ресурси. Використаємо pg_stat_activity, щоб знайти такі «ненажерливі» запити.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Цей запит покаже всі запити, які виконуються більше 10 секунд. Підлаштуй значення інтервалу під свої потреби.

Завершення проблемних запитів

Давай розберемо, як позбутися запитів, які вже занадто довго виконуються і заважають базі працювати. Використовуй функцію pg_terminate_backend() для примусового завершення процесу.

Приклад завершення процесу з певним PID:

SELECT pg_terminate_backend(12345);

Де 12345 — це ідентифікатор процесу (поле pid) з pg_stat_activity.

Важливо: Завершення процесу може викликати rollback для некоректно завершеної транзакції, тому будь обережним.

Тепер, якщо треба автоматично завершити всі «завислі» процеси, наприклад idle-транзакції, можеш виконати наступний PL/pgSQL-блок. Оскільки ти вже вчив програмування, поняття циклу (loop) тобі знайоме — це конструкція, яка повторює виконання певних інструкцій, поки виконується задана умова або поки не закінчиться обробка набору даних:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Це динамічне рішення дозволяє виконувати очистку системи від проблемних транзакцій. Цикл FOR проходить по кожному запису з результату запиту і виконує операцію завершення процесу для кожного знайденого PID.

Скоро ми почнемо вивчати PL/pgSQL, зачекай, ще трохи залишилось :P

Фільтрація за станом транзакцій

Іноді ти не просто хочеш знайти активний запит, а й зрозуміти, які підключення знаходяться в особливому стані, наприклад idle чи idle in transaction. Це може допомогти тобі виявити потенційні проблеми до того, як вони стануть критичними.

Приклад запиту для виявлення транзакцій, що знаходяться в idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

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

Практичне застосування

Моніторинг довгих запитів на продакшені: ти можеш налаштувати регулярний моніторинг запитів, що перевищують певний поріг часу, щоб сповіщати про це через Slack, Telegram чи будь-який інший інструмент повідомлень. Це дозволить швидко реагувати на проблеми з продуктивністю.

Аналіз запитів під час інцидентів: якщо сервер починає гальмувати, перш за все дивись у pg_stat_activity, щоб знайти причину. Це має бути твоїм стандартним протоколом реагування на проблеми з продуктивністю.

Обслуговування бази даних: регулярний аналіз pg_stat_activity допоможе тобі відстежувати неефективні запити й оптимізувати їх (наприклад, додаючи індекси або переписуючи запити).

Коли справа доходить до моніторингу, помилки можуть виникати через неправильну фільтрацію або інтерпретацію даних. Наприклад, якщо ти фільтруєш за станом active, можеш пропустити запити, які знаходяться у стані idle in transaction, а вони теж можуть призвести до блокування ресурсів. Ще одна помилка — занадто агресивне завершення процесів, що може викликати небажані відкати транзакцій і втрату даних. Завжди аналізуй контекст перед тим, як приймати радикальні рішення.

Додаткові техніки моніторингу

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

Також корисно налаштувати автоматичне логування довгих запитів у файли журналів PostgreSQL, використовуючи параметри конфігурації log_min_duration_statement та log_statement. Це допоможе тобі аналізувати проблеми з продуктивністю постфактум і виявляти закономірності у поведінці додатків.