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

F.40. pg_stat_statements — отслеживание статистики планирования и выполнения SQL
Назад	Наверх	Приложение F. Дополнительные поставляемые модули и расширения	Начало	Далее

---

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

F.40.1. Представление pg_stat_statements

F.40.2. Представление pg_stat_statements_info

F.40.3. Функции

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

F.40.5. Пример вывода

F.40.6. Авторы

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

F.40.1. Представление pg_stat_statements #

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

Таблица F.21. pg_stat_statements Колонки

Тип столбца Описание
userid oid (ссылается на pg_authid.oid) OID пользователя, выполнившего оператор
dbid oid (ссылается на pg_database.oid) OID базы данных, в которой был выполнен оператор
toplevel bool True, если запрос был выполнен как самостоятельный оператор (всегда true, если 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 Количество выполнений оператора: {Number of times the statement was executed
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 Общее количество временных блоков, записанных оператором
время_чтения_общих_блоков double precision Общее время, затраченное оператором на чтение общих блоков, в миллисекундах (если track_io_timing включен, иначе ноль)
время_записи_общих_блоков double precision Общее время, затраченное оператором на запись общих блоков, в миллисекундах (если track_io_timing включен, иначе ноль)
время_чтения_локального_блока double precision Общее время, затраченное оператором на чтение локальных блоков, в миллисекундах (если track_io_timing включен, иначе ноль)
время_записи_локального_блока 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-компиляцию функций деформации кортежей, в миллисекундах
статистика_с timestamp with time zone Время начала сбора статистики для этого оператора
minmax_stats_since timestamp with time zone Время начала сбора статистики min/max для этого оператора (поля 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, обсуждаемое ниже в Раздел F.40.2, предоставляет статистику об освобождении записей.

В некоторых случаях запросы с явно разным текстом могут быть объединены в одну запись pg_stat_statements. Обычно это происходит только для семантически эквивалентных запросов, но существует небольшая вероятность коллизий хешей, из-за которых несвязанные запросы могут быть объединены в одну запись. (Однако это не может произойти для запросов, принадлежащих разным пользователям или базам данных).

Так как значение хеша queryid вычисляется на основе представления запросов после анализа, обратное также возможно: запросы с идентичными текстами могут появляться как отдельные записи, если они имеют различные значения в результате факторов, таких как разные настройки search_path.

Потребители pg_stat_statements могут захотеть использовать queryid (возможно, в сочетании с dbid и userid) в качестве более стабильного и надежного идентификатора для каждой записи, чем ее текст запроса. Однако важно понимать, что существуют только ограниченные гарантии стабильности хеш-значения queryid. Поскольку идентификатор производится из дерева после анализа разбора, его значение является функцией, среди прочего, внутренних идентификаторов объектов, появляющихся в этом представлении. Это имеет некоторые контринтуитивные последствия. Например, pg_stat_statements будет считать два, на первый взгляд, идентичных запроса разными, если они ссылается на таблицу, которая была удалена и воссоздана между выполнением двух запросов. Процесс хеширования также чувствителен к различиям в архитектуре машины и другим аспектам платформы. Кроме того, нельзя считать, что queryid будет стабильным в разных основных версиях Tantor BE.

Два сервера, участвующие в репликации на основе физического воспроизведения WAL, могут иметь идентичные значения queryid для одного и того же запроса. Однако схемы логической репликации не обещают сохранять реплики идентичными во всех соответствующих деталях, поэтому queryid не будет полезным идентификатором для накопления затрат по набору логических реплик. В случае сомнений рекомендуется прямое тестирование.

В общем, можно предположить, что значения queryid стабильны между минорными версиями Tantor BE, при условии, что экземпляры работают на одной и той же архитектуре машины и детали метаданных каталога совпадают. Совместимость будет нарушена между минорными версиями только в крайнем случае.

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

F.40.2. Представление pg_stat_statements_info #

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

Таблица F.22. pg_stat_statements_info Columns

Тип столбца Описание
dealloc bigint Общее количество раз, когда записи pg_stat_statements о наименее выполняемых запросах были освобождены, потому что было обнаружено больше различных запросов, чем pg_stat_statements.max
stats_reset timestamp with time zone Время, когда все статистические данные в представлении pg_stat_statements были последний раз сброшены.

F.40.3. Функции #

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. Время последнего выполненного сброса min/max отображается в поле 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 представления, будет содержать значения null). Эта функция предназначена для поддержки внешних инструментов, которым может потребоваться избежать издержек на повторное получение текстов запросов неопределенной длины. Такие инструменты могут вместо этого кешировать первый обнаруженный текст запроса для каждой записи, поскольку это делает и сам pg_stat_statements, и затем получать тексты запросов только по мере необходимости. Поскольку сервер хранит тексты запросов в файле, такой подход может снизить физический ввод-вывод при повторном изучении данных pg_stat_statements.

F.40.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, INSERT, UPDATE, DELETE и MERGE. Значение по умолчанию - on. Изменить эту настройку может только суперпользователь.

pg_stat_statements.track_planning (boolean)

pg_stat_statements.track_planning управляет отслеживанием операций планирования и их продолжительности модулем. Включение этого параметра может привести к заметному снижению производительности, особенно при выполнении операторов с идентичной структурой запроса множеством параллельных соединений, которые конкурируют за обновление небольшого количества записей pg_stat_statements. Значение по умолчанию - off. Изменять этот параметр могут только суперпользователи.

pg_stat_statements.sample_rate (real)

pg_stat_statements.sample_rate заставляет pg_stat_statements отслеживать только часть операторов в каждой сессии. Значение по умолчанию — 1, что означает отслеживание всех запросов. Установка этого параметра в 0 отключает отслеживание выборочных операторов, то же самое, что и установка pg_stat_statements.track в none. В случае вложенных операторов либо все будут отслеживаться, либо ни один. Изменять этот параметр могут только суперпользователи.

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

F.40.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     |

F.40.6. Авторы #

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>. Нормализация запроса добавлена Питером Геогеганом <peter@2ndquadrant.com>.

---

Назад	Наверх	Далее
F.39. pg_stat_kcache — статистика о реальных операциях чтения и записи	Начало	F.41. pgstattuple — получить статистику на уровне кортежей