Модуль pg_stat_statements

Модуль pg_stat_statements предоставляет средства для отслеживания статистики планирования и выполнения всех операторов SQL, выполняемых сервером.

Модуль необходимо загрузить, добавив pg_stat_statements в shared_preload_libraries в файле postgresql.conf, поскольку для этого требуется дополнительная общая память. Это означает, что для добавления или удаления модуля необходим перезапуск сервера. Кроме того, для того, чтобы модуль был активен, необходимо включить вычисление идентификатора запроса, что выполняется автоматически, если для параметра calculate_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.

1. Представление (View) pg_stat_statements

Статистика, собранная модулем, доступна через представление с именем pg_stat_statements. Это представление содержит по одной строке для каждой отдельной комбинации идентификатора базы данных, идентификатора пользователя, идентификатора запроса и того, является ли это оператором верхнего уровня или нет (до максимального количества отдельных операторов, которые может отслеживать модуль). Столбцы представления показаны в Таблице 1 .

Таблица 1. Столбцы 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 запись. Обычно это происходит только для семантически эквивалентных запросов, но существует небольшая вероятность коллизии хэшей, что приведет к объединению несвязанных запросов в одну запись. (Однако этого не может произойти для запросов, принадлежащих разным пользователям или базам данных.)

Поскольку 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 не всегда должны совпадать, поскольку статистика планирования и выполнения обновляется на соответствующей конечной фазе и только для успешных операций. Например, если оператор успешно спланирован, но на этапе выполнения произошел сбой, будет обновлена ​​только его статистика планирования. Если планирование пропущено из-за использования кэшированного плана, будет обновлена ​​только статистика его выполнения.

2. Вид pg_stat_statements_info

Статистика pg_stat_statements самого модуля отслеживается и становится доступной через представление с именем pg_stat_statements_info. Это представление содержит только одну строку. Столбцы представления показаны в Таблице 2 .

Таблица 2. pg_stat_statements_info

3. Функции

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

pg_stat_statements_reset отбрасывает статистику, собранную до сих пор, в соответствии 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.

4. Параметры конфигурации

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, , , и . Значение по умолчанию равно . Только суперпользователи могут изменить этот параметр.INSERTUPDATEDELETEMERGEon

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

5. Пример вывода

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     |