pg_stat_statements — отслеживание статистики планирования и выполнения SQL#

Модуль 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:

По соображениям безопасности только суперпользователи и роли с привилегиями роли pg_read_all_stats имеют право видеть текст SQL и queryid запросов, выполненных другими пользователями. Однако другие пользователи могут просматривать статистику, если представление было установлено в их базе данных.

Планируемые запросы (то есть, SELECT, INSERT, UPDATE, DELETE и MERGE) и служебные команды объединяются в единую запись pg_stat_statements всякий раз, когда они имеют одинаковую структуру запросов согласно внутреннему расчету хеша. Обычно два запроса считаются одинаковыми для этой цели, если они семантически эквивалентны за исключением значений литеральных констант, встречающихся в запросе. Команды утилиты (то есть все остальные команды) сравниваются строго на основе их текстовых строк запросов.

Когда значение константы было проигнорировано с целью сопоставления запроса другим запросам, константа заменяется символом параметра, таким как $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:

Функции#

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     |