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

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

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

Таблица 10. Столбцы 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; иначе ноль)
max_plan_time double precision	Максимальное время, затраченное на планирование этого оператора, в миллисекундах (если включен параметр pg_stat_statements.track_planning; иначе ноль)
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	Минимальное время, затраченное на выполнение этого оператора, в миллисекундах
max_exec_time double precision	Максимальное время, затраченное на выполнение этого оператора, в миллисекундах
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	Общее количество временных блоков, записанных этим оператором
blk_read_time double precision	Общее время, затраченное оператором на чтение блоков файлов данных, в миллисекундах (если включен параметр track_io_timing; иначе ноль)
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	Общее время, затраченное этим оператором на выдачу кода, в миллисекундах

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

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

Примечание
Приведенные ниже сведения о замене констант и queryid применимы, только когда включен параметр compute_query_id. Если же вы используете для вычисления queryid внешний модуль, обратитесь за информацией к его документации.

Когда значение константы игнорируется в целях сравнения запроса с другими запросами, эта константа заменяется в выводе pg_stat_statements символом параметра, например, $1. В остальном этот вывод содержит текст первого запроса с определенным значением хеша queryid, связанным с записью в pg_stat_statements.

Запросы, для которых может быть применена нормализация, могут отображаться в pg_stat_statements со значениями констант, особенно при высокой интенсивности уничтожения записей. Чтобы снизить вероятность уничтожения записей, стоит увеличить значение pg_stat_statements.max. Представление pg_stat_statements_info, рассматриваемое ниже, в параграфе Представление pg_stat_statements_info, предоставляет статистику об уничтожении записей.

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

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

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

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

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

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

Показатели plans и calls не всегда должны совпадать, потому что статистика планирования и выполнения обновляется в конце соответствующей фазы и только для успешно завершившихся операций. Например, если оператор был успешно запланирован, но во время фазы выполнения произошла ошибка, изменится только статистика планирования. Если же планирование пропускается по причине использования кешированного плана, изменится только статистика выполнения.

---

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

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

Таблица 11. Столбцы pg_stat_statements_info

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

---

Функции

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

Функция pg_stat_statements_reset сбрасывает всю статистику, собранную к этому времени модулем pg_stat_statements для заданного пользователя (userid), базы данных (dbid) и запроса (queryid). В случае отсутствия одного из параметров для него по умолчанию используется значение 0(неприменимо), и очищается статистика, соответствующая другим параметрам. Если никакой параметр не задан или все параметры имеют значение 0(неприменимо), очищается вся статистика. Если сбрасывается вся статистика в представлении pg_stat_statements, вместе с ней также очищается статистика в представлении pg_stat_statements_info. По умолчанию эту функцию могут выполнять только суперпользователи. Другим пользователям можно дать к ней доступ с помощью команды 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.

---

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

pg_stat_statements.max (integer)
Этот параметр задает максимальное количество операторов, отслеживаемых модулем (т. е. максимальное количество строк в представлении pg_stat_statements). Если различных операторов выявляется больше заданного числа, информация о наиболее редко выполняемых операторах сбрасывается. В представлении pg_stat_statements_info можно увидеть, сколько раз это происходило. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.

pg_stat_statements.track (enum)
Этот параметр определяет, какие операторы будут подсчитываться модулем. Со значением top отслеживаются операторы верхнего уровня (те, которые выполняются непосредственно клиентами), со значением all также отслеживаются вложенные операторы (например, операторы, вызываемые внутри функций), а со значением none сбор статистики по операторам выключается. Значение по умолчанию — top. Изменять этот параметр могут только суперпользователи.

pg_stat_statements.track_utility (boolean)
Этот параметр определяет, будет ли модуль отслеживать служебные команды. Служебными командами являются все команды, отличные от SELECT, INSERT, UPDATE, DELETE и MERGE. Значение по умолчанию — on (включен). Изменять этот параметр могут только суперпользователи.

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

pg_stat_statements.save (boolean)
Этот параметр указывает, должна ли статистика операторов сохраняться после остановок сервера. При значении off (выключен) статистика не сохраняется при остановке сервера и не перезагружается при его запуске. Значение по умолчанию — on (включен). Этот параметр можно задать только в файле qhb.conf или в командной строке сервера.

Этому модулю требуется дополнительная разделяемая память в объеме, пропорциональном pg_stat_statements.max. Обратите внимание, что эта память будет занята при загрузке модуля, даже если в pg_stat_statements.track установлено none.

Эти параметры следует задавать в qhb.conf. Обычно использование может выглядеть так:

# qhb.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     |

---

Авторы

Такахиро Итагаки (Takahiro Itagaki), itagaki.takahiro@oss.ntt.co.jp. Нормализацию запросов добавил Питер Гейган (Peter Geoghegan), peter@2ndquadrant.com.