Введение

pg_stat_statements — стандартное расширение PostgreSQL для сбора статистики выполнения SQL-запросов: количество запусков, общее и среднее время выполнения запросов, число возвращённых строк и другие показатели. Эта информация позволяет анализировать поведение запросов во времени, выявлять проблемные участки и принимать обоснованные решения по оптимизации. Однако в системах с высокой конкуренцией pg_stat_statements само по себе может стать узким местом и вызывать просадки производительности. В этой статье мы разберем, в каких сценариях расширение становится источником проблем, как устроено сэмплирование и в каких случаях его применение позволяет снизить накладные расходы.

Проблема

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

Ключевая структура данных в pg_stat_statements — это хэш-таблица. Каждый бакет в ней содержит метрики выполнения по конкретному запросу. Ключ для этой таблицы формируется на основе четырёх параметров:

• queryid — уникальный идентификатор нормализованного запроса;

• OID пользователя;

• OID базы данных;

• флаг toplevel, указывающий, является ли запрос верхнеуровневым, то есть не вложенным в какую-либо внутреннюю функцию или подзапрос.

По этому ключу хэш-таблица находит или создаёт соответствующую запись и обновляет собранные показатели запроса.

Когда поступает запрос, pg_stat_statements работает в таком порядке:

1. Поиск бакета: на хэш-таблицу накладывается shared-блокировка LWLock и по ключу происходит поиск в ней;

2. Нормализация (при необходимости): если подходящего бакета нет, запрос предварительно нормализуется: литералы заменяются на плейсхолдеры вида $1, $2 и т. д.;

3. Создание нового бакета: расширение повышает уровень блокировки LWLock до exclusive и создает новый бакет;

4. Запись информации о запросе в бакет: чтобы обновить метрики запроса в бакете, захватывается SpinLock этого бакета. Затем со SpinLock и LWLock снимается блокировка. 

Эти операции с блокировками при большом количестве уникальных запросов или высокой конкурентности pg_stat_statements и становятся узким местом. Рассмотрим это на примере сценария, при котором все SQL-запросы уникальны с точки зрения pg_stat_statements. Для воспроизведения такой нагрузки нам достаточно машины с 48 CPU. Для того, чтобы запросы были уникальными, создадим 1000 однотипных таблиц с разными именами:

DO $$
DECLARE
    i INT;
BEGIN
    FOR i IN 1..1000 LOOP
        EXECUTE format('CREATE TABLE table_%s (id INT PRIMARY KEY, value TEXT);', i);
        EXECUTE format('INSERT INTO table_%s (id, value) VALUES (1, ''test'');', i);
    END LOOP;
END;
$$;

Затем при помощи встроенного генератора случайных чисел в pgbench (pgbench_script.sql) сформируем запросы к этим таблицам так, чтобы каждый из них отличался и попадал в новый бакет хэш-таблицы pg_stat_statements.

\set table1_id random(1, 1000)
\set table2_id random(1, 1000)
\set table3_id random(1, 1000)

SELECT t1.value AS value1, t2.value AS value2, t3.value AS value3
FROM table_:table1_id t1
JOIN table_:table2_id t2 ON t1.id = t2.id
JOIN table_:table3_id t3 ON t2.id = t3.id
WHERE t1.id = 1 AND t2.id = 1 AND t3.id = 1;

Чтобы наглядно увидеть, из-за чего падает производительность, будем во время работы бенчмарка раз в секунду опрашивать pg_stat_activity. Результаты каждого запроса будем записывать в файл /tmp/waits:

\o /tmp/waits
select 'OUT', COALESCE(wait_event, 'None') wait_event, COALESCE(wait_event_type, 'No wait') wait_event_type from  pg_stat_activity where state = 'active';
\watch 1

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

cat /tmp/waits | grep OUT | awk '{print $2 "|" $3}' FS="|" | sort | uniq -c | sort -n -r -k1

Запустим бенчмарк, сравним производительность системы с включённым и выключенным расширением pg_stat_statements, и отобразим причины ее падения. Для этого будем использовать стандартную утилиту pgbench:

• Число клиентов (-c): 48 -по числу CPU;

• Потоки (-j): 20 - ограничивает конкуренцию на уровне ОС, чтобы не перегружать CPU и контекстные переключения;

• Продолжительность (-T): 120 секунд;

• Сценарий: запускаем скрипт pgbench_script.sql (-f pgbench_script.sql);

• Метрики: суммарное количество задержек и итоговый TPS.

pgbench -c48 -j20 -T120 -f pgbench_script.sql --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

Теперь соберем все эти действия в один скрипт:

RESULTS="/tmp/results"

rm -rfv $RESULTS
nohup pgbench -c48 -j20 -T120 -f pgbench_script.sql --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &
timeout 125 psql -f waits.sql
echo " count | wait_event | wait_event_type" >>$RESULTS
echo "--------------------------------------" >>$RESULTS
cat /tmp/waits | grep OUT | awk '{print $2 "|" $3}' FS="|" | sort | uniq -c | sort -n -r -k1 >>$RESULTS
cat $RESULTS
rm -rfv /tmp/waits

Получим следующие результаты:

# Выключенный pg_stat_statements
tps = 237 437.104223 (without initial connection time)
 count | wait_event | wait_event_type
--------------------------------------
   2922  None       	 No wait
    918  ClientRead    Client

# Включенный pg_stat_statements
tps =  32 112.129029 (without initial connection time)
 count |     wait_event     | wait_event_type
--------------------------------------
   4703  pg_stat_statements 	 LWLock
    884  None               	 No wait
    143  ClientRead         	 Client

Как видно, при большом количестве уникальных запросов включённый pg_stat_statements может значительно снизить производительность — вплоть до кратного падения TPS. Всё из-за частого захвата LWLock на уровне exclusive.

Теперь рассмотрим другой сценарий — с большим количеством повторяющихся запросов. Здесь нам понадобится более мощная машина с 192 CPU. Для теста мы снова будем использовать скрипт, периодически проверяющий pg_stat_activity, но на этот раз нагрузку создадим с помощью одного и того же запроса, выполняемого через pgbench -M prepared -S с числом клиентов, равным 192:

pgbench -c192 -j20 -T120 -M prepared -S --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

Запуская этот бенчмарк...

RESULTS="/tmp/results"

rm -rfv $RESULTS
pgbench -i -s500
nohup pgbench -c192 -j20 -T120 -M prepared -S --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &
timeout 125 psql -f waits.sql
echo " count | wait_event | wait_event_type" >>$RESULTS
echo "--------------------------------------" >>$RESULTS
cat /tmp/waits | grep OUT | awk '{print $2}' FS="|" | sort | uniq -c | sort -n -r -k1 >>$RESULTS
cat $RESULTS
rm -rfv /tmp/waits

...мы получим следующие результаты:

# Результаты при выключенном pg_stat_statements
tps = 1 015 425.438193 (without initial connection time)
 count | wait_event | wait_event_type
--------------------------------------
  13201  None             No wait
   3482  ClientRead       Client

# Результаты при включенном pg_stat_statements
tps =   484 338.163894 (without initial connection time)
 count | wait_event | wait_event_type
--------------------------------------
 11 214  SpinDelay        Timeout
   9481  None             No wait
    930  ClientRead       Client

Если же попробовать воспроизвести это на машине с 48 CPU (т.е. с 48 пользователями)...

pgbench -c48 -j20 -T120 -M prepared -S --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

...то проблемы производительности останутся в рамках статистической погрешности.

# Результаты при выключенном pg_stat_statements
tps = 625 335.965464 (without initial connection time)
 count | wait_event | wait_event_type
--------------------------------------
    979  ClientRead 	 	Client
    927  None       	 	No wait

# Результаты при включенном pg_stat_statements
tps = 611 708.477697 (without initial connection time)
 count | wait_event | wait_event_type
--------------------------------------
   1000  ClientRead 		Client
    978  None       		No wait

Это говорит о том, что влияние pg_stat_statements при работе с повторяющимися запросами становится заметным только при очень высокой степени параллелизма. Основная причина — конкуренция за одну и ту же запись в хэш-таблице, которая сопровождается частыми захватами SpinLock при обновлении метрик запроса в бакете хэш-таблицы. Когда множество потоков одновременно выполняют один и тот же запрос, они пытаются обновить одну и ту же структуру — увеличить счётчики вызовов, времени выполнения и другие показатели. Это приводит к интенсивной борьбе за SpinLock, что в условиях высокой нагрузки вызывает задержки и снижает TPS.

Что такое сэмплирование?

Сэмплирование запросов — это метод равномерной фильтрации, при котором в выборку попадает лишь часть всех запросов. В контексте pg_stat_statements это означает, что информация о метриках записывается не по каждому выполненному запросу, а только по некоторым из них, с равной вероятностью. Подобный подход в PostgreSQL используется и в других местах: для сокращения объёма логов используется log_transaction_sample_rate, log_statement_sample_rate, а также в расширениях auto_explain.sample_rate и pg_store_plans.sample_rate. В Tantor Postgres 17.5 в pg_stat_statements добавили соответствующую настройку — GUC-параметр pg_stat_statements.sample_rate, который позволяет задать долю запросов, попадающих в статистику расширения. Значение параметра (от 0 до 1) определяет долю запросов, которые попадут в pg_stat_statements. Запрос будет сэмплирован, если будет выполнено следующее условие:

is_query_sampled = pgss_sample_rate != 0.0 &&
					(pgss_sample_rate == 1.0 ||
					pg_prng_double(&pg_global_prng_state) <= pgss_sample_rate);

Поскольку количество запросов будет очень большим, использование такого неравенства позволяет отфильтровать лишь заданную долю из них.

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

Ограничения подхода сэмплирования

Если сэмплировать запросы на этапе добавления нового бакета в хэш-таблицу, тем самым разгружая LWLock, возникает риск потерять ценный запрос, который было бы полезно увидеть в pg_stat_statements. Более того, даже если бы сэмплирование действительно решало проблему производительности, остаётся проблема безопасности и корректности хранения запросов. Дело в том, что добавление бакета в хэш-таблицу может происходить как до, так и после выполнения запроса. При этом структура, необходимая для нормализации запроса, передаётся только до выполнения запроса, а именно на этапе парсинга, когда необходимо создать структуру, где будут храниться литералы для нормализации. Если на этом этапе pg_stat_statements решит не сохранять запрос из-за сэмплирования, но затем (после выполнения) всё-таки попытается добавить его в хэш-таблицу, то запрос будет записан в оригинальном (не нормализованном) виде. Это может привести к утечке чувствительной информации в pg_stat_statements (например, паролей или персональных данных в литералах SQL-запроса). Поэтому сэмплирование на этапе парсинга запроса недопустимо: оно может нарушить требования безопасности.

Тем не менее, как сообщество PostgreSQL, так и разработчики «Тантор Лабс» пытаются решать проблему большого количества уникальных запросов альтернативным способом — путём объединения похожих запросов под одним queryid. Это позволяет сократить количество уникальных записей в хэш-таблице и, соответственно, снизить частоту её блокировки. Сообщество уже объединило в один QueryId следующие запросы:

• В командах SET;

• Одинаковые запросы с разной длиной IN(...);

• Временные таблицы.

А вот в сценариях, где узким местом становится нагрузка на SpinLock — например, при частом обновлении одной и той же записи хэш-таблицы, — сэмплирование может оказаться весьма эффективным. Поскольку SpinLock защищает только отдельный бакет, уменьшение количества обращений к нему (за счёт пропуска части запросов) снижает конкуренцию между потоками и тем самым улучшает общую производительность.

Результаты сэмплирования

Рассмотрим сценарий, который мы проводили в начале статьи на мощной машине с 192 CPU. Для теста используются те же скрипты waits.sql и включенный pg_stat_statements.

Теперь проведём бенчмарк, чтобы оценить влияние параметра pg_stat_statements.sample_rate на производительность и характер ожиданий. Запускаем цикл по пяти значениям sample_rate: 1, 0.75, 0.5, 0.25 и 0. Для каждого значения выполняется нагрузочное тестирование с помощью pgbench:

CONNECTIONS=192
RESULTS="/tmp/results"
pgbench -i -s500

rm -rfv $RESULTS
for i in 1 .75 .5 .25 0
do
  psql -c "alter system set pg_stat_statements.sample_rate = ${i};" 2>/dev/null >/dev/null
  psql -c "select pg_reload_conf();" 2>/dev/null >/dev/null
  psql -c "show pg_stat_statements.sample_rate;" 2>/dev/null >/dev/null
  echo -e "\nsample_rate = $i" >>$RESULTS
  nohup pgbench -c $CONNECTIONS -j20 -T120 -S -Mprepared --progress 10 | grep "tps = " 2>>/tmp/results >>$RESULTS &
  timeout 125 psql -f /tmp/waits.sql
  echo " count | wait_event | wait_event_type" >>$RESULTS
  echo "--------------------------------------" >>$RESULTS
  cat /tmp/waits | grep OUT | awk '{print $2 "|" $3}' FS="|" | sort | uniq -c | sort -n -r -k1 >>$RESULTS
  rm -rfv /tmp/waits
done
cat $RESULTS

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

При sample_rate = 1.0, когда метрики собирается по всем запросам, TPS оказывается самым низким и наблюдается колоссальное количество ожиданий на SpinLock. При снижении sample_rate до 0.75 и ниже TPS резко возрастает, а SpinDelay уменьшается в 2,3 раза. При sample_rate = 0.25 и ниже SpinDelay исчезает.

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

Заключение

В pg_stat_statements LWLock используется при добавлении новой записи в хэш-таблицу, и при большом числе уникальных запросов может становиться узким местом. Сообщество PostgreSQL пытается решить эту проблему, сокращая количество новых записей за счёт объединения похожих запросов под одним queryid, а в Tantor Postgres 17.5 были добавлены параметры конфигурации, позволяющие маскировать массивы и временные таблицы: pg_stat_statements.mask_const_arrays и pg_stat_statements.mask_temp_tables. Это помогает точнее группировать однотипные запросы, что особенно актуально для таких систем как, например, 1С:ERP.

В свою очередь, SpinLock используется для защиты отдельных бакетов и при частом обновлении счётчиков для одинаковых запросов становится источником конкуренции, особенно на машинах с большим числом CPU. Для решения этой проблемы в Tantor Postgres SE 17.5 добавлен параметр pg_stat_statements.sample_rate: он позволяет снижать нагрузку, сэмплируя запросы, и тем самым устраняя описанную в статье проблему.

Разработчики «Тантор Лабс» предложили перенести механизм сэмплирования запросов в pg_stat_statements в основную ветку PostgreSQL. Обсуждение уже идёт в сообществе на pgsql-hackers — если у вас есть предложения или замечания, можете принять участие в обсуждении.