pg_store_plans
Описание
Модуль 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.2-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 | Идентификатор запроса, сгенерированный ядром. Если для параметра compute_query_id установлено значение no, pg_store_plan автоматически отключается. Его можно использовать в качестве ключа соединения с pg_stat_statements. |
| planid bigint | Хеш-код плана, вычисленный из нормализованного представления плана. |
| plan text | Текст репрезентативного плана. Формат задается параметром конфигурации pg_store_plans.plan_format. |
| 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 | Общее количество временных блоков, записанных оператором, использующим план. |
| shared_blk_read_time double precision | Общее время, затраченное оператором, использующим план, на чтение разделяемых блоков, в миллисекундах (если установлен параметр track_io_timing, в противном случае ноль) |
| shared_blk_write_time double precision | Общее время, затраченное оператором, использующим план, на запись разделяемых блоков, в миллисекундах (если установлен параметр track_io_timing, в противном случае ноль) |
| local_blk_read_time double precision | Общее время, затраченное оператором, использующим план, на чтение локальных блоков, в миллисекундах (если установлен параметр track_io_timing, в противном случае ноль) |
| local_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, но по другому алгоритму. plan рассчитывается схожим образом. Два плана считаются одинаковыми, если они кажутся равнозначными, за исключением значений литеральных констант или изменяющихся значений, например, затрат или измеренного времени.
Для QHB 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
pg_store_plans_reset () → void
Сбрасывает всю статистику, собранную pg_store_plans. По умолчанию эту функцию могут выполнять только суперпользователи.
pg_store_plans ( показать_текст boolean ) → setof record
Представление pg_store_plans определяется через функцию, тоже называемую pg_store_plans.
pg_store_plans_info () → record
Представление pg_store_plans_info определяется через функцию, тоже называемую pg_store_plans_info.
pg_store_plans_hash_query ( запрос text ) → oid
Эта функция вычисляет хеш-значение текста запроса. Тот же алгоритм используется для вычисления столбца queryid в представлении pg_store_plans, поэтому эту функцию можно использовать для соединения с pg_store_plans.
pg_store_plans_textplan ( запрос text ) → text
Эта функция генерирует обычное текстовое представление из необработанного
представления plan в pg_store_plans, которое отображается там, когда
pg_store_plans.plan_formats = 'raw'. Поскольку результирующий текст плана
генерируется из представления json, он может немного отличаться от того, что вы
получите непосредственно из команды EXPLAIN.
pg_store_plans_jsonplan ( запрос text ) → text
Эта функция разворачивает «план в кратком формате json» или «необработанный формат» в обычный формат json. Краткий формат json — это внутренний формат plan в pg_store_plans, который отображается там, когда pg_store_plans.plan_formats = 'raw'.
pg_store_plans_xmlplan ( запрос text ) → text
Эта функция генерирует XML-представление из необработанного представления plan в pg_store_plans, которое отображается там, когда pg_store_plans.plan_formats = 'raw'.
pg_store_plans_yamlplan ( запрос text ) → text
Эта функция генерирует YAML-представление из необработанного представления plan в pg_store_plans, которое отображается там, когда pg_store_plans.plan_formats = 'raw'.
Параметры конфигурации
pg_store_plans.max (integer)
Это максимальное количество планов, отслеживаемых модулем (т. е. максимальное
количество строк в представлении pg_store_plans). Если различных планов
наблюдается больше указанного значения, информация о наименее выполненном плане
отбрасывается. Значение по умолчанию — 1000. Этот параметр можно задать только
при запуске сервера.
pg_store_plans.track (enum)
Аналогично pg_stat_statements, pg_store_plans.track управляет тем,
какие операторы учитываются модулем. Укажите top, чтобы отслеживать операторы
верхнего уровня (выполненные непосредственно клиентами), all, чтобы также
отслеживать вложенные операторы (например, операторы, вызываемые внутри функций,
за исключением некоторых команд; см. ниже), или none, чтобы отключить сбор
статистики по операторам. Значение по умолчанию — top. При значении all
команды, выполняемые в командах CREATE EXTENSION и ALTER EXTENSION,
по-прежнему игнорируются. Укажите verbose, чтобы отслеживать все команды, в том
числе исключенные all. Только суперпользователи могут изменить этот параметр.
pg_store_plans.max_plan_length (integer)
Это максимальная длина планов в необработанном формате (сокращенном формате JSON)
для хранения в байтах. Если текст плана длиннее этого значения, он усекается по
длине. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске
сервера.
pg_store_plans.plan_storage (integer)
Этот параметр определяет, как хранятся тексты планов во время работы сервера.
При значении file тексты плана сохраняются во временном файле, как это делается
в pg_stat_statements. shmem означает хранение текстов плана в памяти.
Значение по умолчанию — file. Более подробную информацию см. ниже.
pg_store_plans.plan_format (enum)
Этот параметр управляет форматом полей plan в pg_store_plans. text
является значением по умолчанию и задает отображение в обычном текстовом
представлении, json, xml и yaml задают отображение в соответствующем формате.
При значении raw генерируется внутреннее представление, которое можно передать
функциям pg_store_plans_*plan.
pg_store_plans.min_duration (integer)
Это минимальное время выполнения оператора в миллисекундах, при котором его план
протоколируется. При значении ноль (по умолчанию) протоколируются все планы.
Только суперпользователи могут изменить этот параметр.
pg_store_plans.log_analyze (boolean)
С этим параметром в plan включаются не просто выходные данные EXPLAIN, а
выходные данные EXPLAIN ANALYZE. По умолчанию этот параметр отключен.
pg_store_plans.log_buffers (boolean)
С этим параметром в plan включаются не просто выходные данные EXPLAIN, а
выходные данные EXPLAIN (ANALYZE, BUFFERS). По умолчанию этот параметр отключен.
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_analyze.
pg_store_plans.log_verbose (boolean)
С этим параметром в plan включаются не просто выходные данные EXPLAIN, а
выходные данные EXPLAIN VERBOSE. По умолчанию этот параметр отключен.
pg_store_plans.save (boolean)
Этот параметр определяет, сохранять ли статистику плана при выключении сервера.
Если он выключен, то статистика не сохраняется при завершении работы и не
загружается повторно при запуске сервера. Значение по умолчанию — on (включен).
Этот параметр можно задать только в файле 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 достаточно велико, чтобы сбор мусора не происходил, рекомендуется установить в pg_store_plans.plan_storage значение file.
Эти параметры следует установить в 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 имеет следующие настройки)
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=#