Использование pg_stat_statements

Евгения Кузина

Обзор

Расширение pg_stat_statements позволяет отслеживать статистику планирования и выполнения SQL-запросов. Оно собирает статистику по всем базам данных. Для доступа к статистике расширение включает представления и функции.

Пакет, требуемый для установки pg_stat_statements, поставляется с ADP, и параметр shared_preload_libraries конфигурационного файла ADP уже содержит значение pg_stat_statements. Чтобы включить pg_stat_statements для текущей базы данных, достаточно выполнить команду CREATE EXTENSION:

CREATE EXTENSION pg_stat_statements;
ПРИМЕЧАНИЕ
Если расширение pg_stat_statements создано в базе данных template1, используемой в качестве шаблона БД по умолчанию, во всех вновь создаваемых базах данных будет установлено это расширение.

ADP использует версию 1.10 расширения pg_stat_statements. Чтобы это проверить, выполните следующий запрос:

SELECT extversion FROM pg_extension
    WHERE extname = 'pg_stat_statements';
 extversion
------------
1.10

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

Расширение включает два представления:

  • pg_stat_statements — содержит собранную статистику по базе данных;

  • pg_stat_statements_info — содержит статистику самого расширения.

pg_stat_statements

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

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

Поля представления pg_stat_statements
Название Тип Описание

userid

oid

OID пользователя, выполнившего запрос. Ссылается на поле oid системного каталога pg_authid

dbid

oid

OID базы данных, в которой был выполнен запрос. Ссылается на поле oid системного каталога pg_database

toplevel

bool

Определяет, является ли запрос запросом верхнего уровня. Всегда 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)

temp_blk_read_time

double precision

Общее время, затраченное запросом на чтение временных блоков файлов, в миллисекундах (если включен параметр track_io_timing, иначе 0)

temp_blk_write_time

double precision

Общее время, затраченное запросом на запись временных блоков файлов, в миллисекундах (если включен параметр track_io_timing, иначе 0)

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

Количество раз, когда функции были встроены (inlined)

jit_inlining_time

double precision

Общее время, затраченное запросом на встраивание функций, в миллисекундах

jit_optimization_count

bigint

Количество раз, когда запрос был оптимизирован

jit_optimization_time

double precision

Общее время, затраченное запросом на оптимизацию, в миллисекундах

jit_emission_count

bigint

Количество раз, когда код был сгенерирован (emitted)

jit_emission_time

double precision

Общее время, затраченное запросом на генерацию кода (emission), в миллисекундах

Представление pg_stat_statements позволяет получить информацию о наиболее долго выполняющихся запросах (столбец total_exec_time), о запросах, которые выполняются чаще всего (столбец calls), и о количестве возвращаемых ими строк (столбец rows).

Столбец stddev_exec_time содержит стандартное отклонение времени. Если это отклонение велико, то некоторые из запросов будут быстрыми, а некоторые — медленными, что может привести к ухудшению работы пользователей.

Соотношение значений столбцов shared_blks_read (количество физических чтений) и shared_blks_hit (количество попаданий в кеш) позволяет оценить производительность при кешировании.

Значения столбцов blk_read_time и blk_write_time содержат информацию о том, сколько времени запрос тратит на операции ввода/вывода, и дают представление о том, нет ли в системе проблем, связанных с этими операциями.

По соображениям безопасности только суперпользователи и пользователи с ролью pg_read_all_stats могут видеть текст SQL-запросов и queryid запросов, выполненных другими пользователями. Однако другие пользователи могут видеть статистику, если расширение pg_stat_statements установлено в их базе данных.

ПРИМЕЧАНИЕ
В ADP параметр compute_query_id установлен в значение auto, что позволяет pg_stat_statements автоматически включить вычисление идентификатора запроса в ядре. Если параметр compute_query_id отключен, приведенные ниже сведения могут быть некорректными.

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

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

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

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

pg_stat_statements_info

Представление pg_stat_statements_info содержит статистику расширения pg_stat_statements. Оно включает всего одну строку с двумя полями:

  • dealloc (bigint) — общее количество случаев, когда pg_stat_statements отбрасывал записи о редко выполняемых запросах, поскольку для обработки было получено больше уникальных запросов, чем указано в параметре конфигурации pg_stat_statements.max;

  • stats_reset (timestamp with time zone) — время, когда в последний раз были удалены все статистические данные в представлении pg_stat_statements.

Функции

pg_stat_statements_reset

Функция pg_stat_statements_reset обнуляет статистику, собранную к этому моменту расширением pg_stat_statements для указанных пользователя (<userid>), базы данных (<dbid>) и запроса (<queryid>). Ее синтаксис выглядит следующим образом:

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

Если какой-либо из параметров не указан, для него используется значение по умолчанию 0, а статистика, соответствующая другим параметрам, будет сброшена. Если ни один параметр не указан или все указанные параметры равны 0, вся статистика будет удалена.

По умолчанию только суперпользователи могут вызывать pg_stat_statements_reset. Если необходимо предоставить это право другим пользователям, используйте команду GRANT.

Следующий код удаляет статистику полностью:

SELECT pg_stat_statements_reset();

pg_stat_statements

Функция pg_stat_statements возвращает строки представления pg_stat_statements. Она имеет следующий синтаксис:

pg_stat_statements(<showtext> boolean) returns setof record

Параметр <showtext> позволяет опустить текст запроса (столбец query представления pg_stat_statements). Для этого необходимо передать false в качестве значения <showtext>. Эта функция предназначена для поддержки внешних инструментов, которым необходимо избежать издержек, связанных с многократным получением текстов запросов неопределенной длины. Поскольку сервер хранит тексты запросов в файле, такой подход сокращает объем физического ввода/вывода, возникающий при постоянном обращении к данным pg_stat_statements.

Следующий код отображает одну строку из представления pg_stat_statements без текста запроса:

SELECT pg_stat_statements(false) LIMIT 1;
 pg_stat_statements
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (10,5,t,6910407680078390346,,0,0,0,0,0,0,2,0.043220999999999996,0.020992,0.022229,0.021610499999999998,0.0006184999999999984,12,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)

Конфигурационные параметры

Конфигурационные параметры расширения 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

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

Вышеперечисленные конфигурационные параметры можно указать в поле postgresql.conf (см. Конфигурационные параметры). Например, в конце поля postgresql.conf можно добавить следующие строки:

compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Примеры

Чтобы протестировать расширение pg_stat_statements, используйте утилиту pgbench.

Создайте новую базу данных и установите расширение pg_stat_statements:

CREATE DATABASE test_db;

CREATE EXTENSION pg_stat_statements;

Запустите тесты pgbench:

$ pgbench -i test_db;

$ pgbench -c10 -t300 test_db;

Получение информации о запросах с наибольшим временем выполнения

Выведите 10 запросов с наибольшим временем выполнения:

SELECT queryid, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
  queryid             | calls |  total_exec_time
----------------------+-------+--------------------
  7383078919277633246 |  3000 |  20018.59277099997
  4073535880199856896 |  3000 | 16169.558812999983
  8862263855866986518 |    31 | 382.61634699999996
  3489562727129942967 |  2619 |  343.3107100000004
  8862263855866986518 |    31 | 174.61769699999996
  2951664667527397125 |    31 |         156.447891
  2951664667527397125 |    31 | 152.60754100000003
 -1864079628023708478 |     1 |         146.880246
   -38828163342769630 |    31 |         134.026149
  1331898319668487618 |  3000 |  115.9758540000001

Выведите текст самого длительного запроса, передав значение queryid в качестве параметра:

SELECT query FROM pg_stat_statements WHERE queryid = 7383078919277633246;
                              query
---------------------------------------------------------------------
 UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2

Определите, какой пользователь выполнил этот запрос:

SELECT rolname FROM pg_authid
    WHERE oid = (SELECT userid FROM pg_stat_statements WHERE queryid = 7383078919277633246);
 rolname
----------
 postgres

Удаление статистики

Следующая команда удаляет статистику для самого длительного запроса с queryid, равным 7383078919277633246:

SELECT pg_stat_statements_reset(0,0,7383078919277633246);

Также можно использовать текст запроса для его идентификации:

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';

Проверьте результат:

SELECT queryid, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
       queryid       | calls |  total_exec_time
---------------------+-------+--------------------
 4073535880199856896 |  3000 | 16169.558812999983
 8862263855866986518 |   415 |        5331.443348
 3489562727129942967 | 36053 |  4715.664210999961
 8862263855866986518 |   415 | 2367.5161030000013
 2951664667527397125 |   415 |        2133.705913
 2951664667527397125 |   415 |        2084.859443
  -38828163342769630 |   415 | 1821.4699970000001
 -848463905646043204 |   415 |        1342.723496
 5887070591661635964 |   415 |  1320.351474000002
 5309506800927155835 |   415 |  889.2803399999999

Статистика по запросу с queryid, равным 7383078919277633246, не отображается.

Оценка производительности при кешировании

Рассчитайте соотношение физических чтений (shared_blks_read) и попаданий в кеш (shared_blks_hit). Доля попаданий в кеш, близкая к 100%, говорит об эффективном кешировании:

SELECT
    queryid,
    round(total_exec_time::numeric, 0) AS total_time,
    round(mean_exec_time::numeric, 0) AS avg_time,
    shared_blks_hit,
    shared_blks_read,
    CASE
        WHEN (shared_blks_hit + shared_blks_read) = 0 THEN 0
        ELSE round((shared_blks_hit * 100.0 / (shared_blks_hit + shared_blks_read)), 0)
    END AS percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
       queryid       | total_time | avg_time | shared_blks_hit | shared_blks_read | percent
---------------------+------------+----------+-----------------+------------------+---------
 7383078919277633246 |      20019 |        7 |           60408 |                1 |     100
 4073535880199856896 |      16170 |        5 |           35252 |                1 |     100
 8862263855866986518 |       5127 |       13 |         4617883 |                0 |     100
 3489562727129942967 |       4548 |        0 |           69516 |                0 |     100
 8862263855866986518 |       2279 |        6 |         1023600 |                0 |     100
 2951664667527397125 |       2053 |        5 |          654800 |                0 |     100
 2951664667527397125 |       2006 |        5 |          682000 |                0 |     100
  -38828163342769630 |       1753 |        4 |          602800 |                0 |     100
 -848463905646043204 |       1291 |        3 |            3999 |                0 |     100
 5887070591661635964 |       1268 |        3 |            3999 |                0 |     100
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней