pg_store_plans - Модуль для отслеживания статистики плана выполнения операторов SQL
Модуль pg_store_plans предоставляет средства для отслеживания статистики плана выполнения всех операторов SQL, выполняемых сервером.
Модуль необходимо загрузить, добавив pg_store_plans в параметр shared_preload_libraries файла qhb.conf, поскольку для этого требуется дополнительная общая память. Это означает, что для добавления или удаления модуля требуется перезагрузка сервера. pg_store_plans требует, чтобы значение переменной GUC Compute_query_id было on или auto. Если установлено значение no, pg_store_plans автоматически отключается.
Установка
Модуль для отслеживания статистики плана выполнения операторов SQL для QHB поставляется в виде пакета qhb-1.5.1-pg-store-plans.
Подключите репозиторий пакетов QHB и установите пакет расширения для выбранной платформы со страницы загрузки.
Представление pg_store_plans
Статистика, собранная модулем, доступна через системное представление pg_store_plans. Это представление содержит по одной строке для каждого отдельного набора идентификаторов базы данных, идентификаторов пользователей и идентификаторов запросов. Столбцы представления описаны в таблице 1.
Таблица 1. Столбцы pg_store_plans
| Название | Тип | Ссылка | Описание |
|---|---|---|---|
| userid | oid | pg_authid.oid | OID пользователя, выполнившего оператор |
| dbid | oid | pg_database.oid | OID базы данных, в которой был выполнен оператор |
| queryid | bigint | Идентификатор запроса, сгенерированный ядром. Если для параметра calculate_query_id установлено значение "нет", pg_store_plan автоматически отключается. Его можно использовать в качестве ключа соединения с pg_stat_statements. | |
| planid | bigint | Хеш-код плана, вычисленный из нормализованного представления плана. | |
| plan | text | Текст репрезентативного плана. Формат задается параметром конфигурации pg_store_plans.plan_format. | |
| calls | bigint | Количество выполненных раз | |
| total_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, иначе ноль) | |
| first_call | timestamp with time zone | Временная метка для последнего вызова запроса с использованием этого плана. | |
| last_call | timestamp with time zone | Отметка времени для последнего вызова запроса с использованием этого плана. |
Это представление, а также функции pg_store_plans_reset и pg_store_plans
и другие вспомогательные функции доступны только в базах данных, в которых
pg_store_plans установлен с помощью CREATE EXTENSION. Однако статистика отслеживается
по всем базам данных сервера всякий раз, когда модуль pg_store_plans загружается
на сервер, независимо от наличия представления.
Из соображений безопасности пользователям, не являющимся суперпользователями, не разрешается просматривать представление плана, queryid или planid для запросов, выполняемых другими пользователями.
queryid вычисляется для идентификации исходного запроса аналогично pg_stat_statements, но по другому алгоритму. План рассчитывается аналогично. Два плана считаются одинаковыми, если они кажутся эквивалентными, за исключением значений буквальных констант или флуктуирующих значений, таких как затраты или измеренное время.
Для QHB v.1.5.1 или более поздней версии вы можете найти соответствующий запрос для записи pg_store_plans в pg_stat_statements, присоединившись с помощью queryid, как показано ниже.
SELECT s.query, p.plan FROM pg_store_plans p JOIN pg_stat_statements s USING (queryid);
Идентификатор плана рассчитывается без учета меняющихся свойств планов. С другой стороны, в представлении pg_store_plans.plan отображаются самые последние значения этих изменяющихся свойств.
pg_store_plans и pg_stat_statements обслуживают свои записи по отдельности, поэтому существует определенный неизбежный шанс, особенно для записей с низкой частотой выполнения, что корреспондент не будет найден.
Представление pg_store_plans_info
Статистика самого модуля pg_store_plans отслеживается и становится доступной через представление с именем pg_store_plans_info. Это представление содержит только одну строку. Столбцы представления показаны в таблице 2.
Таблица 2. Столбцы pg_store_plans_info
| Имя | Тип | Описание |
|---|---|---|
| Dealloc | bigint | Общее количество раз, когда записи pg_store_plans о наименее выполняемых инструкциях были освобождены из-за того, что наблюдалось больше отдельных инструкций, чем в pg_store_plans.max. |
| stats_reset | timestamp with time zone | Время последнего сброса всей статистики в представлении pg_store_plans. |
Функции
pg_store_plans_reset() возвращает void
**pg_store_plans_reset** отбрасывает всю статистику, собранную **pg_store_plans**.
По умолчанию эту функцию могут выполнять только суперпользователи.
pg_store_plans(showtext boolean) возвращает набор записей
Представление **pg_store_plans** определяется с помощью функции, также
называемой **pg_store_plans**.
pg_store_plans_info() возвращает запись
Представление **pg_store_plans_info** определяется в терминах функции,
также называемой **pg_store_plans_info**.
pg_store_hash_query(текст запроса) возвращает oid
Эта функция вычисляет хеш-значение текста запроса. Тот же алгоритм используется
для вычисления идентификатора запроса в **pg_store_plans**, поэтому эту функцию
можно использовать для соединения с **pg_store_plans**.
pg_store_plans_textplan(текст запроса) возвращает текст
Эта функция генерирует обычное текстовое представление из необработанного
представления плана в **pg_store_plans**, которое отображается там, когда
**pg_store_plans.plan_formats = 'raw'**. Поскольку текст плана результатов
генерируется из представления json, он может немного отличаться от того,
что вы получите непосредственно из команды **EXPLAIN**.
pg_store_plans_jsonplan(текст запроса) возвращает текст
Эта функция преобразует «краткий формат json-плана» или «необработанный формат»
в обычный формат json. Короткий формат json — это внутренний формат плана в
**pg_store_plans**, который отображается там, когда **pg_store_plans.plan_formats = 'raw'**.
pg_store_plans_xmlplan(текст запроса) возвращает текст
Эта функция создает XML-представление из необработанного представления плана
в **pg_store_plans**, которое отображается там, когда **pg_store_plans.plan_formats = 'raw'**.
pg_store_plans_yamlplan(текст запроса) возвращает текст
Эта функция генерирует представление YAML из необработанного представления
плана в **pg_store_plans**, которое отображается там, когда **pg_store_plans.plan_formats = 'raw'**.
Параметры конфигурации
pg_store_plans.max (integer)
**pg_store_plans.max** — это максимальное количество планов, отслеживаемых модулем
(т. е. максимальное количество строк в представлении **pg_store_plans**). Если
наблюдается больше различных планов, информация о наименее выполненном плане
отбрасывается. Значение по умолчанию — 1000. Этот параметр можно задать только
при запуске сервера.
pg_store_plans.track (enum)
Аналогично **pg_stat_statements**, **pg_store_plans.track** контролирует, какие
операторы учитываются модулем. Укажите **top**, чтобы отслеживать операторы верхнего
уровня (выданные непосредственно клиентами), **all**, чтобы также отслеживать вложенные операторы
(например, операторы, вызываемые внутри функций, за исключением некоторых команд,
см. ниже), или **none**, чтобы отключить сбор статистики операторов. Значение по
умолчанию — **top**. Когда все указано, команды, выполняемые в командах **CREATE EXTENSION**
и **ALTER EXTENSION**, по-прежнему игнорируются. Укажите **verbose**, чтобы отслеживать
все команды, в том числе исключенные всеми. Только суперпользователи могут изменить этот параметр.
pg_store_plans.max_plan_length (integer)
**pg_store_plans.max_plan_length** — максимальная длина планов в необработанном
(сокращенном формате JSON) для хранения в байтах. Текст плана усекается по длине,
если он длиннее этого значения. Значение по умолчанию — 5000. Этот параметр можно
задать только при запуске сервера.
pg_store_plans.plan_storage (integer)
**pg_store_plans.plan_storage** указывает, как хранятся тексты планов во время работы
сервера. Если он установлен в файл, тексты плана сохраняются во временном файле, как
это делает **pg_stat_statements**. shmem означает хранить тексты плана в памяти. Значение
по умолчанию — **file**.
pg_store_plans.plan_format (enum)
**pg_store_plans.plan_format** управляет форматом планов в **pg_store_plans**. **text**
является значением по умолчанию и для отображения в обычном текстовом представлении,
json, xml и yaml для отображения в соответствующем формате. raw для получения внутреннего
представления, которое можно передать функциям **pg_store_plans_\*plan**.
pg_store_plans.min_duration (integer)
**pg_store_plans.min_duration** — это минимальное время выполнения оператора в
миллисекундах, при котором план оператора регистрируется. Установка этого параметра
равным нулю (по умолчанию) регистрирует все планы. Только суперпользователи могут
изменить эту настройку.
pg_store_plans.log_analyze (boolean)
**pg_store_plans.log_analyze** приводит к включению в план выходных данных **EXPLAIN ANALYZE**,
а не только выходных данных **EXPLAIN**. Этот параметр отключен по умолчанию.
pg_store_plans.log_buffers (boolean)
**pg_store_plans.log_buffers** приводит к включению в план вывода **EXPLAIN**
(**ANALYZE**, **BUFFERS**), а не только вывода **EXPLAIN**. Этот параметр отключен по умолчанию.
pg_store_plans.log_timing (boolean)
Установка для **pg_store_plans.log_timing** значения **false** отключает запись
фактического времени. Накладные расходы на повторное чтение системных часов могут
значительно замедлить выполнение запроса в некоторых системах, поэтому может быть
полезно установить для этого параметра значение **FALSE**, когда требуется только
фактическое количество строк, а не точное время выполнения для каждого узла выполнения.
Время выполнения всего оператора всегда измеряется, когда **pg_store_plans.log_analyze**
имеет значение **TRUE**. По умолчанию оно равно **TRUE**.
pg_store_plans.log_triggers (boolean)
**pg_store_plans.log_triggers** заставляет статистику выполнения триггера включаться
в записываемые планы. Этот параметр не действует, если не включен параметр **pg_store_plans.log_analyze**.
pg_store_plans.verbose (boolean)
**pg_store_plans.verbose** приводит к включению в план вывода **EXPLAIN VERBOSE**,
а не только вывода **EXPLAIN**. Этот параметр отключен по умолчанию.
pg_store_plans.save (boolean)
**pg_store_plans.save** указывает, сохранять ли статистику плана при выключении
сервера. Если он выключен, то статистика не сохраняется при завершении работы и
не перезагружается при запуске сервера. Значение по умолчанию включено. Этот
параметр можно задать только в файле ***qhb.conf*** или в командной строке сервера.
Настройки plan_storage
pg_store_plans требует дополнительной общей памяти, пропорциональной pg_store_plans.max. Когда для pg_store_plans.plan_storage задано значение shmem, он требует дополнительной дополнительной общей памяти для хранения текстов планов в объеме, равном произведению максимального количества планов для хранения (pg_store_plans.max) и максимальной длины отдельного плана (pg_store_plans.max_plan_length). Если для pg_store_plans.plan_storage установлено значение file, тексты планов записываются во временный файл, как это делает pg_stat_statements. Если pg_store_plans.max недостаточно велик для хранения всех планов, pg_store_plans освобождает место для новых планов, удаляя часть записей. После нескольких циклов удаления pg_store_plans запускает сборку мусора для временного файла, что может быть тяжело для определенных рабочих нагрузок. Вы можете увидеть, как часто эта очистка происходит в pg_store_plans_info.dealloc.
Если pg_store_plans.max достаточно велик, чтобы сборка мусора не происходила, file рекомендуется использовать как pg_store_plans.plan_storage.
Эти параметры должны быть установлены в qhb.conf. Пример настройки следующий:
# qhb.conf
shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
pg_store_plans.max = 10000
pg_store_plans.track = all
Пример вывода
(qhb.conf has following settings)
shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
pg_store_plans.log_analyze = true
pg_store_plans.log_timing = false
bench=# SELECT pg_store_plans_reset();
$ pgbench -i bench
$ pgbench -c10 -t1000 bench
bench=# \x
bench=# SELECT s.query, p.plan,
p.calls as "plan calls", s.calls as "stmt calls",
p.total_time / p.calls as "time/call", p.first_call, p.last_call
FROM pg_stat_statements s
JOIN pg_store_plans p USING (queryid) WHERE p.calls < s.calls
ORDER BY query ASC, "time/call" DESC;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
plan | Update on pgbench_tellers (cost=0.00..7.88 rows=0 width=0) +
| -> Seq Scan on pgbench_tellers (cost=0.00..7.88 rows=1 width=10) +
| Filter: (tid = 1)
plan calls | 396
stmt calls | 10000
time/call | 16.15434492676767
first_call | 2021-11-25 15:11:38.258838+09
last_call | 2021-11-25 15:11:40.170291+09
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
plan | Update on pgbench_tellers (cost=0.14..8.15 rows=0 width=0) +
| -> Index Scan using pgbench_tellers_pkey on pgbench_tellers (cost=0.14..8.15 rows=1 width=10) +
| Index Cond: (tid = 8) +
plan calls | 9604
stmt calls | 10000
time/call | 10.287281695439345
first_call | 2021-11-25 15:11:40.161556+09
last_call | 2021-11-25 15:12:09.957773+09
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | select s.query, p.plan, p.calls as "plan calls", s.calls as "stmt calls", p.total_time / p.calls as "time/call", p.first_call, p.last_call from pg_stat_statements s join pg_store_plans p using (queryid) where p.calls < s.calls order by query asc, "time/call" desc
plan | Sort (cost=309.71..313.88 rows=1667 width=104) +
| Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC +
| -> Merge Join (cost=119.66..220.50 rows=1667 width=104) +
| Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid) +
| Join Filter: (pg_store_plans.calls < pg_stat_statements.calls) +
| -> Sort (cost=59.83..62.33 rows=1000 width=48) +
| Sort Key: pg_stat_statements.queryid +
| -> Function Scan on pg_stat_statements (cost=0.00..10.00 rows=1000 width=48) +
| -> Sort (cost=59.83..62.33 rows=1000 width=72) +
| Sort Key: pg_store_plans.queryid +
| -> Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=72) +
plan calls | 3
stmt calls | 4
time/call | 16.387161
first_call | 2021-11-25 15:20:57.978082+09
last_call | 2021-11-25 15:23:48.631993+09
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | select s.query, p.plan, p.calls as "plan calls", s.calls as "stmt calls", p.total_time / p.calls as "time/call", p.first_call, p.last_call from pg_stat_statements s join pg_store_plans p using (queryid) where p.calls < s.calls order by query asc, "time/call" desc
plan | Sort (cost=309.71..313.88 rows=1667 width=104) +
| Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC +
| Sort Method: quicksort Memory: 26kB +
| -> Merge Join (cost=119.66..220.50 rows=1667 width=104) +
| Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid) +
| Join Filter: (pg_store_plans.calls < pg_stat_statements.calls) +
| Rows Removed by Join Filter: 7 +
| -> Sort (cost=59.83..62.33 rows=1000 width=48) +
| Sort Key: pg_stat_statements.queryid +
| Sort Method: quicksort Memory: 27kB +
| -> Function Scan on pg_stat_statements (cost=0.00..10.00 rows=1000 width=48) +
| -> Sort (cost=59.83..62.33 rows=1000 width=72) +
| Sort Key: pg_store_plans.queryid +
| Sort Method: quicksort Memory: 30kB +
| -> Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=72) +
plan calls | 1
stmt calls | 4
time/call | 4.46928
first_call | 2021-11-25 15:12:27.142535+09
last_call | 2021-11-25 15:12:27.142536+09
qhb=#