...

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

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

...

Запрос к pg_stat_

...

activity покажет, какие запросы работают в БД в конкретный момент времени (при обращении к pg_stat_

...

activity):

SELECTselect query,
       calls,
       total_time,
       mean_time,
       max_time
FROM * from pg_stat_statements
ORDERactivity BYwhere total_time DESC
LIMIT 100;

Что делает этот запрос?

• Показывает 100 самых долгих запросов по суммарному времени выполнения (total_time).
• mean_time – среднее время выполнения.
• max_time – самое долгое выполнение.
• calls – сколько раз запрос выполнялся.

Способ 2: Мониторинг текущих долгих запросов (pg_stat_activity)

Если pg_stat_statements не включен, можно посмотреть активные запросы, которые выполняются прямо сейчас:

SELECT pid,
       age(clock_timestamp(), query_start) AS duration,
       state,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 100;

Этот запрос показывает:

• Запросы, которые прямо сейчас выполняются дольше всего.
• duration – время выполнения запроса.
• state – статус запроса (например, active или idle in transaction).

...

state <> 'idle'

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

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

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

2.1 Настройка

Чтобы включить pg_stat_statements,

...

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

CREATE EXTENSION IF NOT EXISTS 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.

1.3 PWR-отчёт

В PostgresPro, коммерческом форке PostgreSQL, есть специальный инструмент, который
можно считать аналогом AWR-отчёта Oracle — pgpro_pwr.
Подробнее информацию по настройке см. в документации вендора.Данную информацию необходимо будет выгрузить в отдельный файл (.CSV), и передать на анализ
в Отдел сопровождения и внедрения IQStore: support@iqstore.ru