pg_stat_statements — отслеживание статистики планирования и выполнения SQL
Эта страница переведена при помощи нейросети GigaChat.
Модуль pg_stat_statements предоставляет средства для отслеживания статистики планирования и выполнения всех SQL-операторов, выполняемых сервером.
Этот модуль должен быть загружен путем добавления pg_stat_statements к shared_preload_libraries в postgresql.conf, поскольку он требует дополнительной общей памяти. Это означает, что для добавления или удаления модуля требуется перезапуск сервера. Кроме того, чтобы модуль был активен, необходимо включить расчет идентификатора запроса, что делается автоматически, если compute_query_id установлен на auto или on, или загружается любой сторонний модуль, который рассчитывает идентификаторы запросов.
Когда pg_stat_statements активен, он отслеживает статистику во всех базах данных сервера. Для доступа и управления этими статистическими данными модуль предоставляет представления pg_stat_statements и pg_stat_statements_info, а также утилитарные функции pg_stat_statements_reset и pg_stat_statements. Они не доступны глобально, но могут быть включены для конкретной базы данных с помощью CREATE EXTENSION pg_stat_statements.
Представление pg_stat_statements
Статистика, собранная модулем, доступна через представление с именем pg_stat_statements. Это представление содержит одну строку для каждой уникальной комбинации идентификатора базы данных, идентификатора пользователя, идентификатора запроса и того, является ли это верхнеуровневым оператором или нет (до максимального количества различных операторов, которые может отслеживать модуль). Столбцы представления показаны в таблице ниже.
Столбцы pg_stat_statements:
| Тип столбца | Описание |
|---|---|
userid oid (ссылается наpg_authid.oid) | OID пользователя, который выполнил оператор |
dbid oid (ссылается на pg_database.oid) | OID базы данных, в которой был выполнен оператор |
toplevel bool | Истина, если запрос был выполнен как верхний уровень оператора (всегда истинно, если pg_stat_statements.track установлен на top) |
queryid bigint | Хеш-код для идентификации идентичных нормализованных запросов |
query text | Текст представительного утверждения |
plans bigint | Количество раз, когда утверждение было запланировано (если pg_stat_statements.track_planning включен, иначе ноль) |
total_plan_time double precision | Общее время, затраченное на планирование оператора, в миллисекундах (если pg_stat_statements.track_planning включен, иначе ноль) |
min_plan_time double precision | Минимальное время, потраченное на планирование оператора, в миллисекундах. Это поле будет равно нулю, если pg_stat_statements.track_planning отключено, или если счетчик был сброшен функцией pg_stat_statements_reset с параметром minmax_only, установленным в true, и никогда не планировалось после этого |
max_plan_time double precision | Максимальное время, потраченное на планирование оператора, в миллисекундах. Это поле будет равно нулю, если pg_stat_statements.track_planning отключено, или если счетчик был сброшен функцией pg_stat_statements_reset с параметром minmax_only, установленным в true, и никогда не планировалось после этого |
mean_plan_time double precision | Среднее время, затраченное на планирование оператора, в миллисекундах (если pg_stat_statements.track_planning включен, иначе ноль) |
stddev_plan_time double precision | Стандартное отклонение населения времени, затраченного на планирование утверждения, в миллисекундах (если pg_stat_statements.track_planning включено, иначе ноль) |
calls bigint | Количество раз, когда было выполнено утверждение |
total_exec_time double precision | Общее время выполнения оператора, в миллисекундах |
min_exec_time double precision | Минимальное время выполнения оператора, в миллисекундах. Это поле будет равно нулю до первого выполнения данного оператора после сброса, произведенного функцией pg_stat_statements_reset, при которой параметр minmax_only установлен в значение true |
max_exec_time double precision | Максимальное время выполнения оператора, в миллисекундах. Это поле будет равно нулю до первого выполнения данного оператора после сброса, произведенного функцией pg_stat_statements_reset, при которой параметр minmax_only установлен в значение true |
mean_exec_time double precision | Среднее время выполнения оператора в миллисекундах |
stddev_exec_time double precision | Стандартное отклонение населения времени, затраченного на выполнение оператора, в миллисекундах |
rows bigint | Общее количество строк, извлеченных или затронутых оператором |
shared_blks_hit bigint | Общее количество попаданий в кеш общих блоков оператором |
shared_blks_read bigint | Общее количество общих блоков, прочитанных оператором |
shared_blks_dirtied bigint | Общее количество общих блоков, поврежденных оператором |
shared_blks_written bigint | Общее количество общих блоков, записанных оператором |
local_blks_hit bigint | Общее количество попаданий локального блока кеша оператором |
local_blks_read bigint | Общее количество локальных блоков, прочитанных оператором |
local_blks_dirtied bigint | Общее количество загрязненных локальных блоков оператором |
local_blks_written bigint | Общее количество локальных блоков, записанных оператором |
temp_blks_read bigint | Общее количество временных блоков, прочитанных оператором |
temp_blks_written bigint | Общее количество временных блоков, записанных оператором |
shared_blk_read_time double precision | Общее время, потраченное оператором на чтение общих блоков, в миллисекундах (если включена опция track_io_timing, иначе ноль) |
shared_blk_write_time double precision | Общее время, потраченное оператором на запись общих блоков, в миллисекундах (если включена опция track_io_timing, иначе ноль) |
local_blk_read_time double precision | Общее время, потраченное оператором на чтение локальных блоков, в миллисекундах (если включена опция track_io_timing, иначе ноль) |
local_blk_write_time double precision | Общее время, потраченное оператором на запись локальных блоков, в миллисекундах (если включена опция track_io_timing, иначе ноль) |
temp_blk_read_time double precision | Общее время, которое оператор потратил на чтение временных файловых блоков, в миллисекундах (если track_io_timing включен, иначе ноль) |
temp_blk_write_time double precision | Общее время, которое оператор затратил на запись временных файловых блоков, в миллисекундах (если track_io_timing включен, иначе ноль) |
wal_records bigint | Общее количество записей WAL, сгенерированных оператором |
wal_fpi bigint | Общее количество полных изображений страниц WAL, сгенерированных оператором |
wal_bytes numeric | Общее количество WAL, сгенерированное оператором в байтах |
jit_functions bigint | Общее количество функций, скомпилированных JIT-оператором |
jit_generation_time double precision | Общее время, затраченное оператором на генерацию кода JIT, в миллисекундах |
jit_inlining_count bigint | Количество раз, когда функции были встроены |
jit_inlining_time double precision | Общее время, затраченное оператором на встраивание функций, в миллисекундах |
jit_optimization_count bigint | Количество раз, когда оператор был оптимизирован |
jit_optimization_time double precision | Общее время, затраченное оператором на оптимизацию, в миллисекундах |
jit_emission_count bigint | Количество раз, когда код был сгенерирован |
jit_emission_time double precision | Общее время, затраченное оператором на генерацию кода, в миллисекундах |
jit_deform_count bigint | Общее число функций деформации кортежей, компилированных методом JIT для данного оператора |
jit_deform_time double precision | Общее время, затраченное оператором на JIT-компиляцию функций деформации кортежей, в миллисекундах |
stats_since timestamp with time zone | Время начала сбора статистики по данному оператору |
minmax_stats_since timestamp with time zone | Время начала сбора минимальной/максимальной статистики по этому оператору (поля min_plan_time, max_plan_time, min_exec_time и max_exec_time) |
По соображениям безопасности только суперпользователи и роли с привилегиями роли pg_read_all_stats имеют право видеть текст SQL и queryid запросов, выполненных другими пользователями. Однако другие пользователи могут просматривать статистику, если представление было установлено в их базе данных.
Планируемые запросы (то есть, SELECT, INSERT, UPDATE, DELETE и MERGE) и служебные команды объединяются в единую запись pg_stat_statements всякий раз, когда они имеют одинаковую структуру запросов согласно внутреннему расчету хеша. Обычно два запроса считаются одинаковыми для этой цели, если они семантически эквивалентны за исключением значений литеральных констант, встречающихся в запросе. Команды утилиты (то есть все остальные команды) сравниваются строго на основе их текстовых строк запросов.
Следующие сведения о замене констант и queryid применяются только при включении compute_query_id. Если используется внешний модуль для вычисления queryid, то следует обратиться к его документации за подробностями.
Когда значение константы было проигнорировано с целью сопоставления запроса другим запросам, константа заменяется символом параметра, таким как $1, в отображении pg_stat_statements. Остальной текст запроса – это первый запрос, который имел конкретное хеш-значение queryid, связанное со входом pg_stat_statements.
Запросы, к которым может применяться нормализация, можно наблюдать со значениями констант в pg_stat_statements, особенно когда наблюдается высокая частота освобождения записей. Чтобы уменьшить вероятность возникновения такой ситуации, рассмотрите возможность увеличения значения pg_stat_statements.max. В представлении pg_stat_statements_info приводится статистика об освобождении записей.
В некоторых случаях запросы с явно разными текстами могут быть объединены в одну запись pg_stat_statements. Обычно это происходит только для семантически эквивалентных запросов, но существует небольшой шанс того, что хеш-коллизии приведут к объединению несвязанных запросов в одну запись. Это невозможно для запросов, принадлежащих разным пользователям или базам данных.
Поскольку значение хеша queryid вычисляется на основе представления запроса после анализа синтаксиса, возможно и обратное: запросы с идентичными текстами могут отображаться как отдельные записи, если они имеют разные значения из-за таких факторов, как различные настройки search_path.
Пользователи pg_stat_statements могут пожелать использовать queryid (возможно, в сочетании с dbid и userid) в качестве более стабильного и надежного идентификатора для каждой записи, чем текст запроса. Однако важно понимать, что существуют лишь ограниченные гарантии стабильности значения хеша queryid. Поскольку идентификатор получен из дерева пост-анализа синтаксического разбора, его значение является функцией, среди прочего, внутренних идентификаторов объектов, появляющихся в этом представлении. Это имеет некоторые неожиданные последствия. Например, pg_stat_statements будет считать два идентичных на первый взгляд запроса различными, если они ссылаются на таблицу, которая была удалена и воссоздана между выполнениями двух запросов. Процесс хеширования также чувствителен к различиям в архитектуре машины и другим аспектам платформы. Кроме того, нельзя предполагать, что queryid будет стабильным при переходе от одной основной версии к другой PostgreSQL .
В качестве общего правила можно предположить, что значения queryid стабильны и сравнимы только до тех пор, пока версия основного сервера и детали метаданных каталога остаются точно такими же. Можно ожидать, что две серверы, участвующие в репликации на основе физического воспроизведения WAL, будут иметь одинаковые значения queryid для одного и того же запроса. Однако схемы логической репликации не обещают поддерживать копии идентичными во всех соответствующих деталях, поэтому queryid не будет полезным идентификатором для накопления затрат по набору логических копий. Если сомневаетесь, рекомендуется прямое тестирование.
Символы параметров, используемые для замены констант в представительных текстах запросов, начинаются с следующего числа после самого высокого параметра $n в исходном тексте запроса или $1, если его не было. Стоит отметить, что в некоторых случаях могут быть скрытые символы параметров, которые влияют на эту нумерацию. Например, PL / pgSQL использует скрытые параметры символов для вставки значений локальных переменных функции в запросы, так что оператор PL / pgSQL , такой как SELECT i + 1 INTO j, имел бы представительный текст вроде SELECT i + $2.
Текст, представляющий запрос, сохраняется во внешнем файле диска и не потребляют общую память. Поэтому даже очень длинные тексты запросов могут храниться успешно. Однако, если накапливается много длинных текстов запросов, внешний файл может стать неприемлемо большим. В качестве метода восстановления, если это произойдет, pg_stat_statements может решить отбросить тексты запросов, при этом все существующие записи в представлении pg_stat_statements будут показывать пустые поля query, хотя статистика, связанная с каждым queryid, сохраняется. Если это произойдет, рассмотрите возможность уменьшения pg_stat_statements.max, чтобы предотвратить повторение.
plans и calls не всегда должны совпадать, потому что планирование и статистические данные выполнения обновляются на соответствующих конечных этапах и только для успешных операций. Например, если оператор успешно спланирован, но терпит неудачу во время фазы выполнения, будут обновлены только его статистические данные планирования. Если планирование пропускается из-за использования кешированного плана, то будут обновляться только его статистические данные выполнения.
Представление pg_stat_statements_info
Статистика самого модуля pg_stat_statements отслеживается и предоставляется через представление с именем pg_stat_statements_info. Оно содержит только одну строку. Его столбцы показаны в таблице ниже.
Столбцы pg_stat_statements_info:
| Тип столбца | Описание |
|---|---|
dealloc bigint | Общее количество раз, когда записи о наименее выполняемых операторах были освобождены из-за того, что было замечено больше различных операторов, чем pg_stat_statements |
stats_reset timestamp with time zone | Время, когда все статистики в представлении pg_stat_statements были сброшены последний раз |
Функции
pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint, minmax_only boolean) returns timestamp with time zone
: pg_stat_statements_reset отбрасывает статистику, собранную до сих пор pg_stat_statements, соответствующую указанному userid, dbid и queryid. Если какой-либо из параметров не указан, для каждого из них используется значение по умолчанию 0 (недействительно), а статистика, соответствующая другим параметрам, будет сброшена. Если ни один параметр не указан или все указанные параметры являются 0 (недействительными), это приведет к сбросу всей статистики. Если вся статистика во представлении pg_stat_statements была отброшена, она также сбросит статистику во представлении pg_stat_statements_info. Когда minmax_only установлено в true, будут сброшены только значения минимального и максимального времени планирования и выполнения (то есть поля min_plan_time, max_plan_time, min_exec_time и max_exec_time). Значение параметра minmax_only по умолчанию — false. Время последнего выполненного сброса мин/макс отображается в поле minmax_stats_since представления pg_stat_statements. Эта функция возвращает время сброса. Это время сохраняется в поле stats_reset представления pg_stat_statements_info или в поле minmax_stats_since представления pg_stat_statements, если соответствующий сброс был фактически выполнен. По умолчанию эта функция может выполняться только суперпользователями. Доступ можно предоставить другим пользователям с помощью GRANT.
pg_stat_statements(showtext boolean) returns setof record
: Представление pg_stat_statements определено в терминах функции с тем же именем pg_stat_statements. Клиенты могут вызывать функцию pg_stat_statements непосредственно, а указав showtext := false, можно опустить текст запроса (т.е. аргумент OUT, соответствующий столбцу query представления, вернет нулевые значения). Эта функция предназначена для поддержки внешних инструментов, которые могут захотеть избежать накладных расходов при повторяющемся извлечении текстов запросов неопределенной длины. Такие инструменты могут вместо этого кешировать первый текст запроса, наблюдаемый для каждой записи, поскольку это все, что делает сам pg_stat_statements, а затем извлекать тексты запросов по мере необходимости. Поскольку сервер хранит тексты запросов в файле, этот подход может уменьшить физический ввод-вывод для повторного анализа данных pg_stat_statements.
Параметры конфигурации
pg_stat_statements.max (integer)
: pg_stat_statements.max – это максимальное количество операторов, отслеживаемых модулем (т.е. максимальное количество строк в представлении pg_stat_statements). Если наблюдается больше различных операторов, чем указано выше, информация о наименее выполняемых операторах удаляется. Количество раз, когда такая информация была отброшена, можно увидеть в представлении pg_stat_statements_info. Значение по умолчанию равно 5000. Этот параметр может быть установлен только при запуске сервера.
pg_stat_statements.track (enum)
: pg_stat_statements.track контролирует, какие операторы учитываются модулем. Укажите top, чтобы отслеживать основные операторы (те, которые непосредственно выдаются клиентами), all, чтобы также отслеживать вложенные операторы (такие как операторы, вызываемые внутри функций), или none, чтобы отключить сбор статистики операторов. Значение по умолчанию – top. Только суперпользователи могут изменить этот параметр.
pg_stat_statements.track_utility (boolean)
: pg_stat_statements.track_utility управляет тем, отслеживаются ли утилиты командами модуля. Утилитные команды - все те, кроме SELECT, INSERT, UPDATE, DELETE и MERGE. Значение по умолчанию - on. Только суперпользователи могут изменить этот параметр.
pg_stat_statements.track_planning (boolean)
: pg_stat_statements.track_planning определяет, отслеживаются ли операции планирования и их продолжительность модулем. Включение этого параметра может привести к заметному снижению производительности, особенно когда операторы с идентичной структурой запроса выполняются многими одновременными соединениями, которые конкурируют за обновление небольшого количества записей pg_stat_statements. Значение по умолчанию равно off. Только суперпользователи могут изменить этот параметр.
pg_stat_statements.save (boolean)
: pg_stat_statements.save указывает, следует ли сохранять статистику операторов при выключении сервера. Если установлено значение off, то статистика не сохраняется при завершении работы и не загружается при запуске сервера. Значение по умолчанию равно on. Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера.
Модуль требует дополнительной общей памяти, пропорциональной pg_stat_statements.max. Обратите внимание, что эта память потребляется всякий раз, когда модуль загружен, даже если pg_stat_statements.track установлен на none.
Эти параметры должны быть установлены в postgresql.conf. Типичное использование может выглядеть так:
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all
Пример вывода
bench=# SELECT pg_stat_statements_reset();
$ pgbench -i bench
$ pgbench -c10 -t300 bench
bench=# \x
bench=# 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;
-[ RECORD 1 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls | 3000
total_exec_time | 25565.855387
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 3000
total_exec_time | 20756.669379
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_exec_time | 291.865911
rows | 100000
hit_percent | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 3000
total_exec_time | 271.232977
rows | 3000
hit_percent | 98.8454011741682975
-[ RECORD 5 ]---+--------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_exec_time | 160.588563
rows | 0
hit_percent | 100.0000000000000000
bench=# 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';
bench=# 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;
-[ RECORD 1 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 3000
total_exec_time | 20756.669379
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_exec_time | 291.865911
rows | 100000
hit_percent | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 3000
total_exec_time | 271.232977
rows | 3000
hit_percent | 98.8454011741682975
-[ RECORD 4 ]---+--------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_exec_time | 160.588563
rows | 0
hit_percent | 100.0000000000000000
-[ RECORD 5 ]---+--------------------------------------------------------------------
query | vacuum analyze pgbench_accounts
calls | 1
total_exec_time | 136.448116
rows | 0
hit_percent | 99.9201915403032721
bench=# SELECT pg_stat_statements_reset(0,0,0);
bench=# 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;
-[ RECORD 1 ]---+-----------------------------------------------------------------------------
query | SELECT pg_stat_statements_reset(0,0,0)
calls | 1
total_exec_time | 0.189497
rows | 1
hit_percent |
-[ RECORD 2 ]---+-----------------------------------------------------------------------------
query | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit / +
| nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
| FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
calls | 0
total_exec_time | 0
rows | 0
hit_percent |