pg_stat_statements
Модуль 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. Это представление содержит по одной строке для каждой отдельной комбинации ID базы данных, ID пользователя, ID запроса и указания, относится ли данный оператор к верхнему уровню или нет (в количестве, не превышающем максимальное число отдельных операторов, которые может отслеживать модуль). Столбцы представления показаны в Таблице 21.
Таблица 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; иначе 0 | ||
| total_plan_time double precision | ||
| Общее время, затраченное на планирование этого оператора, в миллисекундах (если включен параметр pg_stat_statements.track_planning; иначе 0) | ||
| min_plan_time double precision | ||
| Минимальное время, затраченное на планирование этого оператора, в миллисекундах (если включен параметр pg_stat_statements.track_planning; иначе 0) | ||
| max_plan_time double precision | ||
| Максимальное время, затраченное на планирование этого оператора, в миллисекундах (если включен параметр pg_stat_statements.track_planning; иначе 0) | ||
| mean_plan_time double precision | ||
| Среднее время, затраченное на планирование этого оператора, в миллисекундах (если включен параметр pg_stat_statements.track_planning; иначе 0) | ||
| stddev_plan_time double precision | ||
| Стандартное отклонение времени, затраченного на планирование этого оператора, в миллисекундах (если включен параметр pg_stat_statements.track_planning; иначе 0) | ||
| 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; иначе 0) | ||
| blk_write_time double precision | ||
| Общее время, затраченное оператором на запись блоков, в миллисекундах (если включен track_io_timing; иначе 0) | ||
| wal_records bigint | ||
| Общее количество записей WAL, сгенерированных этим оператором | ||
| wal_fpi bigint | ||
| Общее количество образов полных страниц WAL, сгенерированных этим оператором | ||
| wal_bytes numeric | ||
| Общий объем WAL, сгенерированный этим оператором, в байтах | ||
Из соображений безопасности только суперпользователям и членам роли pg_read_all_stats разрешено видеть текст SQL и queryid запросов, выполняемых другими пользователями. Однако другие пользователи могут видеть статистику, если это представление было установлено в их базу данных.
Планируемые запросы (то есть SELECT, INSERT, UPDATE и DELETE) объединяются
в одну запись в pg_stat_statements, когда они имеют идентичные структуры
запросов согласно внутреннему вычислению хеша. Обычно в таком случае два запроса
буду считаться равными, если они семантически равнозначны, не считая значений
литеральных констант, находящихся в запросе. Однако служебные команды (то есть
все остальные команды) сравниваются строго по текстовым строкам запросов.
Примечание
Приведенные ниже сведения о замене констант и queryid применимы, только когда включен параметр compute_query_id. Если же вы используете для вычисления queryid внешний модуль, обратитесь за информацией к его документации.
Когда значение константы игнорируется в целях сравнения запроса с другими запросами, эта константа заменяется в выводе 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 будет оставаться неизменным при обновлении основных версий QHB.
На практике выходит, что значения 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, чтобы предотвратить повторение таких ситуаций.
Показатели plans и calls не всегда должны совпадать, потому что статистика планирования и выполнения обновляется в конце соответствующей фазы и только при успешном завершении операции. Например, если оператор был успешно запланирован, но во время фазы выполнения произошла ошибка, изменится только статистика планирования. Если же планирование пропускается по причине использования кэшированного плана, изменится только статистика выполнения.
Представление pg_stat_statements_info
Статистика самого модуля pg_stat_statements отслеживается и выдается через представление с именем pg_stat_statements_info. Это представление содержит только одну строку. Столбцы представления показаны в Таблице 22.
Таблица 22. Столбцы 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. Значение по умолчанию — 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.