Использование модуля pg_stat_statements в postgresql
pg_stat_statements
https://habr.com/ru/articles/488968/
Модуль pg_stat_statements предоставляет возможность отслеживать статистику планирования и выполнения сервером всех операторов SQL.
Для функционирования этого модуля должны вычисляться идентификаторы запросов, что происходит автоматически, когда для параметра compute_query_id задаётся значение auto или on или же загружается сторонний модуль, вычисляющий идентификаторы запросов.
Для получения и обработки статистики модуль pg_stat_statements предоставляет представления pg_stat_statements и pg_stat_statements_info и вспомогательные функции pg_stat_statements_reset и pg_stat_statements
Представление содержит запросы для каждой комбинации идентификатора базы данных, пользователя, идентификатора запроса и уровня запроса.
Столбы представления перечислены в таблице на официальном сайте: https://postgrespro.ru/docs/postgresql/16/pgstatstatements
Время затраченное на планирование вычисляется если включен параметр pg_stat_statements.track_planning
По соображениям безопасности только суперпользователям и ролям с правами роли pg_read_all_stats разрешено видеть текст SQL и queryid запросов, выполняемых другими пользователями.
Планируемые запросы (то есть SELECT, INSERT, UPDATE, DELETE, MERGE) и служебные команды объединяются в одну запись в pg_stat_statements, когда они имеют идентичные структуры запросов согласно внутреннему вычисленному хешу. Обычно два запроса будут считаться равными при таком сравнении, если они семантически равнозначны, не считая значений констант, фигурирующих в запросе.
Функция pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) - очищает всю статистику, вместе с очисткой всей статистики в pg_stat_statements также сбрасывается статистика в pg_stat_statements_info
Параметры конфигурации
pg_stat_statements.max - задаёт максимальное число операторов, отслеживаемых модулем, если этого значения не хватит, строки будут отбрасываться
pg_stat_statements.track - определяет, какие операторы будут отслеживаться модулем
pg_stat_statements.track_utility - определяет, будет ли этот модуль отслеживать служебные команды
pg_stat_statements.track_planning - определяет, будет ли этот модуль отслеживать операции планирования и их длительность. Его включение может привести к заметному снижению производительности
pg_stat_statements.save - определяет, должна ли статистика операторов сохраняться после перезагрузки сервера
Чтобы включить pg_stat_statements необходимо добавить в файл postgresql.conf строки
#Модулю требуется дополнительная разделяемая память
shared_preload_libraries = 'pg_stat_statements'
...
...
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all
#Перезапустить сервис
/etc/init.d/postgresql restart
Для доступа к данным необходимо создать представление
CREATE EXTENSION pg_stat_statements;
#Просмотр модулей
\dx
#Структура представления
\d pg_stat_statements
#Просмотр доступности pg_stat_statements
show shared_preload_libraries;
#Сброс всей статистики
SELECT pg_stat_statements_reset();
Примеры использования
SELECT pg_stat_statements_reset();
pgbench -i
pgbench -c10 -t300 bench
\x
sudo -u postgres pgbench -p 9999 -i megabase
sudo -u postgres pgbench -p 9999 -c10 -t300 megabase
SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
query | calls | total_exec_time | rows | hit_percent
---------------------------------------------------------------------+-------+--------------------+--------+----------------------
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 | 3000 | 9651.954327000009 | 3000 | 99.9979010117123546
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 | 3000 | 7672.551326000011 | 3000 | 99.9967476501772531
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 | 3000 | 174.43100099999975 | 3000 | 98.7667608412933315
copy pgbench_accounts from stdin with (freeze on) | 1 | 141.20719 | 100000 | 100.0000000000000000
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 3000 | 83.16917799999992 | 3000 | 100.0000000000000000
(5 rows)
SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';
pg_stat_statements_reset
--------------------------
(1 row)
SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
query | calls | total_exec_time | rows | hit_percent
---------------------------------------------------------------------+-------+--------------------+--------+----------------------
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 | 3000 | 7672.551326000011 | 3000 | 99.9967476501772531
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 | 3000 | 174.43100099999975 | 3000 | 98.7667608412933315
copy pgbench_accounts from stdin with (freeze on) | 1 | 141.20719 | 100000 | 100.0000000000000000
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 3000 | 83.16917799999992 | 3000 | 100.0000000000000000
alter table pgbench_accounts add primary key (aid) | 1 | 65.904901 | 0 | 100.0000000000000000
(5 rows)
SELECT pg_stat_statements_reset(0,0,0);
pg_stat_statements_reset
--------------------------
(1 row)
SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
Онаружение медленных запросов
Самый простой способ найти наиболее интересные запросы — отсортировать вывод pg_stat_statements по total_time:
SELECT * FROM pg_stat_statements ORDER BY total_plan_time DESC;
Обычно поля blk_read_time и blk_write_time пусты, если не включен track_io_timing. Данные показатели дают возможность измерять количество времени, которое определенный тип запроса тратит на ввод-вывод
Процесс pg_stat_statements может быть прерван из за слишко маленько размера track_activity_query_size
Для большинства приложений 1024 байта абсолютно достаточно
Посмотреть текущий размер можно так:
SHOW track_activity_query_size;
20 запросов, занимающих много времени
SELECT substring(query, 1, 50) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
На реальной нагруженной БД pg_stat_statements быстро «забивается». Его нужно тюнить, регулярно сбрасывать