F.45. pg_stat_statements
Модуль pg_stat_statements
предоставляет возможность отслеживать статистику выполнения сервером всех операторов SQL.
Этот модуль нужно загружать, добавив pg_stat_statements
в shared_preload_libraries в файле postgresql.conf
, так как ему требуется дополнительная разделяемая память. Это значит, что для загрузки или выгрузки модуля необходимо перезапустить сервер.
Когда модуль pg_stat_statements
загружается, он отслеживает статистику по всем базам данных на сервере. Для получения и обработки этой статистики этот модуль предоставляет представление pg_stat_statements
и вспомогательные функции pg_stat_statements_reset
и pg_stat_statements
. Эти объекты не доступны глобально, но их можно установить в определённой базе данных, выполнив команду CREATE EXTENSION pg_stat_statements
.
F.45.1. Представление pg_stat_statements
Статистика, собираемая модулем, выдаётся через представление с именем pg_stat_statements
. Это представление содержит отдельные строки для каждой комбинации идентификатора базы данных, идентификатора пользователя и идентификатора запроса (но в количестве, не превышающем максимальное число различных операторов, которые может отслеживать модуль). Столбцы представления показаны в Таблице F.33.
Таблица F.33. Столбцы pg_stat_statements
Имя | Тип | Ссылки | Описание |
---|---|---|---|
userid | oid |
| OID пользователя, выполнявшего оператор |
dbid | oid |
| OID базы данных, в которой выполнялся оператор |
queryid | bigint | Внутренний хеш-код, вычисленный по дереву разбора оператора | |
query | text | Текст, представляющий оператор | |
calls | bigint | Число выполнений | |
total_time | double precision | Общее время, затраченное на оператор, в миллисекундах | |
min_time | double precision | Минимальное время, затраченное на оператор, в миллисекундах | |
max_time | double precision | Максимальное время, затраченное на оператор, в миллисекундах | |
mean_time | double precision | Среднее время, затраченное на оператор, в миллисекундах | |
stddev_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 | Общее число временных блоков, записанных данным оператором | |
blk_read_time | double precision | Общее время, затраченное оператором на чтение блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае) | |
blk_write_time | double precision | Общее время, затраченное оператором на запись блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае) |
По соображениям безопасности только суперпользователям и членам роли pg_read_all_stats
разрешено видеть текст SQL и queryid
запросов, выполняемых другими пользователями. Однако другие пользователи могут видеть статистику, если это представление установлено в их базу данных.
Планируемые запросы (то есть SELECT
, INSERT
, UPDATE
и DELETE
) объединяются в одну запись в pg_stat_statements
, когда они имеют идентичные структуры запросов согласно внутреннему вычисленному хешу. Обычно два запроса будут считаться равными при таком сравнении, если они семантически равнозначны, не считая значений констант, фигурирующих в запросе. Однако служебные команды (то есть все другие команды) сравниваются строго по текстовым строкам запросов.
Когда значение константы игнорируется в целях сравнения запроса с другими запросами, эта константа заменяется в выводе pg_stat_statements
обозначением параметра, например, $1
. В остальном этот вывод содержит текст первого запроса, хеш которого равнялся значению queryid
, связанному с записью в pg_stat_statements
.
В некоторых случаях запросы с визуально различными текстами могут быть объединены в одну запись pg_stat_statements
. Обычно это происходит только для семантически равнозначных запросов, но есть небольшая вероятность, что из-за наложений хеша несвязанные запросы могут оказаться объединёнными в одной записи. (Однако это невозможно для запросов, принадлежащих разным пользователям баз данных.)
Так как значение хеша queryid
вычисляется по представлениям запроса на стадии после разбора, возможна и обратная ситуация: запросы с одинаковым текстом могут оказаться в разных записях, если они получили различные представления по разным причинам, например, из-за изменения search_path
.
Потребители статистики pg_stat_statements
могут пожелать использовать в качестве более стабильного и надёжного идентификатора для каждой записи не текст запроса, а queryid
(возможно, в сочетании с dbid
и userid
). Однако важно понимать, что стабильность значения хеша queryid
гарантируется с ограничениями. Так как этот идентификатор получается из дерева запроса после анализа, его значение будет, помимо прочего, зависеть от внутренних идентификаторов объектов, фигурирующих в этом представлении. С этим связано несколько неинтуитивных следствий. Например, pg_stat_statements
будет считать два одинаково выглядящих запроса разными, если они обращаются к таблице, которая была удалена, а затем воссоздана между этими запросами. Результат хеширования также чувствителен к различиям в машинной архитектуре и другим особенностям платформы. Более того, не стоит рассчитывать на то, что queryid
будет оставаться неизменным при обновлении основных версий Postgres Pro.
Как правило, значения 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
окажутся значения NULL, хотя статистика, связанная с каждым queryid
будет сохранена. Если это происходит и мешает анализу, возможно, стоит уменьшить pg_stat_statements.max
для предотвращения таких ситуаций.
F.45.2. Функции
-
pg_stat_statements_reset() returns void
Функция
pg_stat_statements_reset
очищает всю статистику, собранную к этому времени модулемpg_stat_statements
. По умолчанию эту функцию могут выполнять только суперпользователи.-
pg_stat_statements(showtext boolean) returns setof record
Представление
pg_stat_statements
реализовано на базе функции, которая тоже называетсяpg_stat_statements
. Клиенты могут вызывать функциюpg_stat_statements
непосредственно, и могут указатьshowtext := false
и получить результат без текста запроса (то есть, выходной аргумент (OUT
), соответствующий столбцу представленияquery
, будет содержать NULL). Эта возможность предназначена для поддержки внешних инструментов, для которых желательно избежать издержек, связанных с получением текстов запросов неопределённой длины. Такие инструменты могут кешировать текст первого запроса, который они получат самостоятельно, как это и делаетpg_stat_statements
, а затем запрашивать тексты запросов только при необходимости. Так как сервер сохраняет тексты запросов в файле, этот подход сокращает объём физического ввода/вывода, порождаемого при постоянном обращении к даннымpg_stat_statements
.
F.45.3. Параметры конфигурации
pg_stat_statements.max
(integer
)Параметр
pg_stat_statements.max
задаёт максимальное число операторов, отслеживаемых модулем (то есть, максимальное число строк в представленииpg_stat_statements
). Когда на обработку поступает больше, чем заданное число различных операторов, информация о редко выполняемых операторах отбрасывается. Значение по умолчанию — 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
. Значение по умолчанию —on
(вкл.). Изменить этот параметр могут только суперпользователи.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' pg_stat_statements.max = 10000 pg_stat_statements.track = all
F.45.4. Пример вывода
bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_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_time DESC LIMIT 5; -[ RECORD 1 ]--------------------------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; calls | 3000 total_time | 9609.00100000002 rows | 2836 hit_percent | 99.9778970000200936 -[ RECORD 2 ]--------------------------------------------------------------------- query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; calls | 3000 total_time | 8015.156 rows | 2990 hit_percent | 99.9731126579631345 -[ RECORD 3 ]--------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 total_time | 310.624 rows | 100000 hit_percent | 0.30395136778115501520 -[ RECORD 4 ]--------------------------------------------------------------------- query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; calls | 3000 total_time | 271.741999999997 rows | 3000 hit_percent | 93.7968855088209426 -[ RECORD 5 ]--------------------------------------------------------------------- query | alter table pgbench_accounts add primary key (aid) calls | 1 total_time | 81.42 rows | 0 hit_percent | 34.4947735191637631
F.45.5. Авторы
Такахиро Итагаки <itagaki.takahiro@oss.ntt.co.jp>
. Нормализацию запросов добавил Питер Гейган <peter@2ndquadrant.com>
.