Неожиданные последствия запуска PostgreSQL в Docker: замедление запросов в 100 раз

У одного из клиентов нашей системы мониторинга PostgreSQL серверов возникла проблема сильного замедления запросов при запуске базы в Docker. В этой статье расскажем о возможных последствиях использования PostgreSQL в Docker с конфигурацией по умолчанию.

Клиент обратился с проблемой - тормозит интерфейс при отображении логов. Анализ показал, что причиной является долгое выполнение запроса (приводим в сокращенном виде):

SELECT
	rc.pack
,	rc.recno
,	rc.ts
,	rc.type
,	rc.duration
,	coalesce(rc.unparsed, '') unparsed
,	rc.dt::text
,	get_rawdata_str(rc.dt, rc.pack, rc.recno) "text"
,	( SELECT ... ) query
,	regexp_replace(( SELECT ... )::text, '^[(.*)]$', '1') context
,	( SELECT ... ) parameters
,	( SELECT ... ) exectime
,	( SELECT ... ) plan
,	( SELECT ... ) "lock"
,	err.error
,	err.errargs
,	err.msg
FROM
	record rc
LEFT JOIN
	LATERAL( SELECT ...	) err
		ON TRUE
WHERE
	(rc.pack, rc.dt) = ($1::uuid, $2::date)
ORDER BY
	recno;

Загружаем план запроса на explain.tensor.ru :

Видим, что все время ушло на корневой узел "Nested Loop Left Join" .

Это произошло в результате вызова функций в столбцах корневого JOIN, но там или get_rawdata_str или regexp_replace . При этом обе функции не отображаются в плане, а все их время и ресурсы отражены в корневом узле:

Так как база тестовая и данных немного, то regexp_replace не может потреблять много ресурсов.

Проверяем PL/pgSQL функцию get_rawdata_str - она генерирует свой план, но он не отображается при вызове EXPLAIN ANALYZE . Получить план запроса из такой функции возможно лишь с помощью модуля auto_explain при включенном параметре:

SET auto_explain.log_nested_statements = on;

В этом случае в логе будут записи типа CONTEXT с планом вызываемой функции:

CONTEXT:  SQL function "get_rawdata_str" statement 1

Включаем этот параметр, загружаем план запроса из тела функции:

Видим что сам запрос отрабатывает за 309 мс, а вот JIT еще 1.7 сек :

Но почему у клиента включился JIT, а на наших серверах нет ?

Вообще JIT появился в 11 версии, здесь было обсуждение по поводу его включения.

В 11 версии он остался выключенным по умолчанию, а во всех версиях, начиная с 12, JIT включен.

При этом кроме опций конфига для использования JIT требуется соблюдение нескольких условий:

1. сборка PG должна быть произведена с опцией --with-llvm , в официальных пакетах это так и есть, а в случае сборки из исходников наличие опции проверить можно командой:

pg_config --configure | grep with-llvm

2. наличие провайдера JIT, по умолчанию это llvmjit , который устанавливается из пакета postgresql-llvmjit.

При развороте сервера по инструкции устанавливаются только пакеты postgresql-server , а также зависимые postgresql и postgresql-libs , при этом провайдер JIT по умолчанию не устанавливается.

На нашем сервере мы также не устанавливаем провайдер, поэтому у нас JIT выключен.

Кстати, проверить работоспособность JIT можно так:

SELECT pg_jit_available();

Клиент сообщил, что базу развернули в Docker, так как это была тестовая инсталляция с небольшим количеством данных.

Похоже в докер-образе PostgreSQL JIT включен и работает из коробки. Проверяем:

docker pull postgres
docker run --name postgres -e POSTGRES_PASSWORD=password -d postgres
docker exec -it postgres find / -name "*jit*"

/usr/lib/postgresql/16/lib/llvmjit_types.bc
/usr/lib/postgresql/16/lib/llvmjit.so
/usr/lib/postgresql/16/lib/bitcode/postgres/jit
/usr/lib/postgresql/16/lib/bitcode/postgres/jit/jit.bc
/usr/share/postgresql-common/server/test-with-jit.conf

docker exec -it postgres psql -U postgres -Atc 'SELECT pg_jit_available();'
t

Так и есть - в образе добавлен провайдер JIT, а значит при превышении порога стоимости запроса часть операций будет оптимизирована.

Решение о применении оптимизаций JIT принимается на основании общей стоимости запроса. В случае превышения значения jit_above_cost (по умолчанию 100 000) производится JIT-оптимизация выражений в WHERE, агрегатах, целевых списках и проекциях, а также преобразование кортежей при загрузке их с диска в память. При этом в плане запроса в разделе JIT будут включены параметры Expressions и Deforming:

JIT:
  Options: Inlining false, Optimization false, Expressions true, Deforming true

Если же стоимость запроса превысила jit_inline_above_cost (по умолчанию 500 000) или jit_optimize_above_cost (по умолчанию также 500 000) то тела небольших функций и операторов будут встроены в код и может применяться дорогостоящая оптимизация. В плане это будет отображено в параметрах Inlining и Optimization:

JIT:
  Options: Inlining true, Optimization true, Expressions true, Deforming true

В нашем запросе в теле функции get_rawdata_str несколько LEFT JOIN на таблицах с большим количеством строк, в результате стоимость запроса составила очень большие значения и планировщик подключил JIT для оптимизации.

Просим клиента отключить JIT:

ALTER SYSTEM SET jit=off;
SELECT pg_reload_conf();

И время выполнения запроса снизилось до 30 мс:

Проблема решена. Но чтобы она не повторялась, рекомендуем запускать PostgreSQL в Docker с выключенным по умолчанию JIT.

Для этого добавим в команду запуска опцию отключения JIT:

docker run --name postgres -e POSTGRES_PASSWORD=postgres -d postgres -c jit=off

Или создаем свой конфиг и запускаем с ним PostgreSQL:

docker run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample > my-postgres.conf
echo "jit=off" >> my-postgres.conf
docker run -d --name postgres -v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf -e POSTGRES_PASSWORD=password postgres -c 'config_file=/etc/postgresql/postgresql.conf'