Перейти к основному содержимому
Версия: Последняя

Статистика топ-100 долгих запросов в БД PostgreSQL

1. Мониторинг в реальном времени через pg_stat_activity​

Рассмотрим представление для мониторинга текущих активных соединений и запросов через pg_stat_activity.

Запрос к pg_stat_activity покажет, какие запросы работают в БД в конкретный момент времени (при обращении к pg_stat_activity):

SELECT *
FROM pg_stat_activity
WHERE state <> 'idle';

Функционал полезен для диагностики текущих проблем с производительностью — можно в реальном времени увидеть долго выполняющиеся запросы. Однако он не покажет статистику по работе запросов, которые уже завершились, поэтому его нельзя рассматривать в качестве полноценного инструмента мониторинга.

2. Расширение для сбора статистики выполнения запросов pg_stat_statements​

  • Сохраняет информацию о частоте выполнения запросов, общем времени выполнения, времени ожидания и других параметрах.
  • Используется для анализа наиболее «тяжелых» запросов.

2.1. Настройка​

Чтобы включить pg_stat_statements, необходимо выполнить запрос:

CREATE EXTENSION pg_stat_statements;

А также необходимо в postgresql.conf задать настройки сбора статистики:

# Подключение библиотеки pg_stat_statements.
# Если в shared_preload_libraries уже указаны другие библиотеки,
# просто добавьте pg_stat_statements через запятую,
# например: 'pg_stat_statements,other_library'.
shared_preload_libraries = 'pg_stat_statements'

# Максимальное количество запросов, статистика которых будет храниться:
pg_stat_statements.max = 5000

# Типы запросов, которые будут отслеживаться (all, top или none):
pg_stat_statements.track = all

# Сохранять статистику между перезапусками:
pg_stat_statements.save = on

# Включать параметр queryid в статистику (только для PostgreSQL 14 и выше):
compute_query_id = on

2.2. Сбор статистики​

Чтобы собрать статистику, можно использовать следующие варианты запросов.

Сбор самых долгих запросов (по общему времени выполнения):

SELECT
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    max_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 100;

Сбор самых долгих запросов (по среднему времени выполнения):

SELECT
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    max_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 100;

Сбор самых долгих запросов (по максимальному времени выполнения):

SELECT
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    max_time,
    rows
FROM pg_stat_statements
ORDER BY max_time DESC
LIMIT 100;

Для анализа будут полезны все три вида статистики.

Обратите внимание: параметр queryid доступен к использованию только в версиях PostgreSQL 14 и выше. В случае использования версии PostgreSQL ниже 14 параметр queryid необходимо исключить из запроса.

После сбора статистики вы можете отправить информацию на анализ в Отдел сопровождения и внедрения: support@iqstore.ru.

2.3. PWR-отчёт​

В PostgresPro, коммерческом форке PostgreSQL, есть специальный инструмент, который можно считать аналогом AWR-отчёта Oracle — pgpro_pwr.

Подробнее информацию по настройке см. в документации вендора.

3. Чистка данных перед повторным сбором статистики​

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

Для сброса статистики выполните запрос:

SELECT pg_stat_reset();

После сброса статистики необходимо подождать сутки, а затем повторно собрать топ-100 долгих запросов.