Продолжаем выжимать максимум из PostgreSQL

Kate · 17 Авг 2024

В апреле этого года мы, команда производительности из Postgres Professional, совместно с коллегами из Selectel решили протестировать несколько дистрибутивов PostgreSQL и узнать, как они себя поведут на разных архитектурах. С результатами можно ознакомиться в этой статье, но, как сразу было верно отмечено читателями, там был один важный косяк – мы не сравнили производительность ванильного PostgreSQL с применением всем известных настроек по улучшению производительности и Postgres Pro Enterprise из коробки as is. Терпеть такое не было решительно никакой возможности, поэтому сегодня будет продолжение истории и ответ на важный для многих вопрос: «А есть ли у нашего форка хоть какое-то преимущество перед бесплатной ваниллой?» Или мы просто накатили общеизвестный конфиг и занимаемся импортозаместительным переклеиванием наклеек?

Чтобы сохранить общую картину, валидность с предыдущими результатами и не изобретать свой велосипед, мы взяли тесты pgbench, которыми с нами любезно поделились коллеги из Selectel. Всё красиво и аккуратно расписано в их статье, поэтому не будем останавливаться на этом моменте. Единственное – мы немного усложнили себе задачу и поставили цель выявить ключевые настройки, которые дают максимальный прирост производительности. Также был добавлен тест при использовании компрессии на уровне хранения данных (CFS). В остальном всё максимально близко к тому, что делали в Selectel.

Тестовый стенд

В тестах Selectel использовалось несколько конфигураций bare metal серверов, так как среди прочих там была и задача проверить производительность различных платформ. Мы же взяли только один сервер со следующими характеристиками:

Процессор: Intel(R) Xeon(R) Gold 6338 CPU @ 2.00GHz (2 Sockets)
128 RAM Kingston DDR4 1TB
Диск: iSCSI-storage 1TB (Selectel использовали в тестах SSD-диски с поддержкой NVMe и PCIe Gen 4)

В качестве ОС использовалась Debian GNU/Linux 11 (bullseye) с ядром 5.15.116-1-pve

СУБД для сравнения были выбраны:

Postgres Pro Enterprise 16.2.1
PostgreSQL 16.2

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

Подготовка и сценарии тестов

Пропустим стандартные процессы установки из готовых пакетов и первичной настройки СУБД PostgreSQL и перейдём сразу к сути. Для тестов pgbench создаём пользователя и базы:

CREATE ROLE pgbench with login password 'pgbench';
CREATE DATABASE pgbench WITH owner = pgbench;
CREATE TABLESPACE cfs LOCATION '/u02/cfsdir' WITH (compression=true);
CREATE DATABASE pgbench2 WITH TABLESPACE cfs owner = pgbench;
Генерируем тестовые данные с множителем scale=10000. У нас объём БД составил 146 Gb без сжатия и 13 Gb при включённом CFS.

lab@lab:~$ pgbench -i -s 10000 pgbench
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1000000000 of 1000000000 tuples (100%) done (elapsed 1341.08 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1725.21 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 1344.56 s, vacuum 0.95 s, primary keys 379.70 s).
И чтобы всё по-честному, и результаты воспроизводились, перед каждой серией тестов БД пересоздавалась с нуля.

Тестовые конфигурации

Никаких тайн, рассказываем всё как на духу. У нас было три тестовых конфигурации. Конфигурация 1, известная под кодовым именем «Postgres Pro Enterprise 16.2.1 из коробки с дефолтным конфигом» включала в себя только то, что разворачивалось по дефолту:

max_connections = 1600
shared_buffers = 257915MB # 25% of RAM
effective_cache_size = 3GB
maintenance_work_mem = 64478MB
max_wal_size = 4GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9
effective_cache_size = 773745MB # 75% of RAM
wal_buffers = 16MB
default_statistics_target = 100
Конфигурация 2, она же «Postgres, тюнингованный по советам лучших интернет-сомелье». Абсолютно одинаковый набор параметров, который прописывался в конфиг ванильного PostgreSQL 16.2, Postgres Pro Enterprise 16.2.1 и Postgres Pro Enterprise 16.2.1 с включённым CFS. Конфиг длинный, поэтому спрячем под спойлер.

Конфиг под спойлером
archive_timeout = 1200
autovacuum_analyze_scale_factor = 0.6
autovacuum_max_workers = 6
autovacuum_naptime = 1s
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000
autovacuum_vacuum_scale_factor = 0.6
autovacuum_work_mem = 1GB
bgwriter_delay = 10ms
bgwriter_flush_after = 0
bgwriter_lru_maxpages = 4000
bgwriter_lru_multiplier = 10
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min
commit_delay = 100
commit_siblings = 5
datestyle = 'iso, mdy'
default_statistics_target = 1000
default_text_search_config = 'pg_catalog.english'
default_toast_compression = lz4
dynamic_shared_memory_type = posix
effective_cache_size = 792315195kB
effective_io_concurrency = 100
from_collapse_limit = 30
fsync = on
huge_pages = on
join_collapse_limit = 30
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
log_filename = 'PostgreSQL-%a.log'
logging_collector = on
log_line_prefix = '%m [%p] %q%u@%d '
log_rotation_age = 1d
log_rotation_size = 1GB
log_timezone = 'Europe/Moscow'
log_truncate_on_rotation = on
maintenance_work_mem = 2GB
max_connections = 1000
max_parallel_workers_per_gather = 0
max_replication_slots = 30
max_wal_senders = 30
max_wal_size = 32GB
min_wal_size = 10GB
port = 5432
random_page_cost = 1.1
shared_buffers = 257915MB # 25% of RAM
ssl=off
synchronous_commit = on
timezone = 'Europe/Moscow'
vacuum_cost_limit = 2000
wal_buffers = -1
wal_compression = lz4
wal_level = replica
wal_sync_method = fdatasync
work_mem = 1MB
И Конфигурация 3, представляющая собой версию Конфигурации 2, дополненную параметрами, которые есть только в Postgres Pro Enterprise.
autoprepare_for_protocol = simple
autoprepare_threshold = 2
generic_plan_fuzz_factor = 0.9
log2_num_lock_partitions = 8
lwlock_shared_limit = 16
slru_buffers_size_scale = 6

Параметры тестирования

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

Тестирование производилось в рамках локалхоста, чтобы избежать возможное влияние сетевых задержек.
Каждый тест выполнялся 600 секунд.
pgbench использовал простой протокол запросов (параметр -M simple). В принципе, он выбирается по умолчанию, просто мы решили выбирать его явным образом.
Для каждой из семи комбинаций параметров Клиент/Поток последовательно запускались встроенные в pgbench тесты tpcb-like, select-only и simple-update

Итого получается 21 запуск. Также для снижения флуктуаций было выполнено по 3 итерации тестов с усреднением полученных результатов. Вот табличка получившихся комбинаций параметров:

№	Клиенты (-c)	Потоки (-j)	% потоков pgbench от общего кол-ва CPU Threads
1	2	1	-
2	4	2	-
3	50	25	20
4	102	51	40
5	152	76	60
6	204	102	80
7	254	127	99.9

Тестирование Конфигурации 1

Суть тестирования Postgres Pro Enterprise из коробки в том, чтобы найти референтные значения, относительно которых уже можно делать выводы – улучшилась у нас ситуация или внесённый в конфиг улучшайзинг сделал всё только хуже. Поэтому давайте посмотрим на графики и оценим, какие результаты получились в этих трёх тестах.
tpcb-like: Максимальная производительность составила 41877 TPS при тесте для 127 потоков

select-only: Максимальная производительность составила 870776 TPS при тесте для 76 потоков

simple-update: Максимальная производительность составила 47157 TPS при тесте для 127 потоков

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

Тестирование Конфигурации 2

Напомню, что в этом тесте мы сравниваем ванильную, Pro-версию и Pro-версию с CFS. Не будем вас томить и сразу переходим к полученным TPS и промежуточным выводам.

tpcb-like

PostgreSQL 16.2 НЕ проигрывает Postgres Pro Enterprise 16.2.1. И даже показывает лучший результат на 3-6%, кроме замера для 127 потоков.
Postgres Pro Enterprise 16.2.1 с поддержкой CFS по сравнению с PostgreSQL 16.2 показывает прирост в производительности до 27% на тестах при большом числе потоков.

select-only

PostgreSQL 16.2 НЕ проигрывает Postgres Pro Enterprise 16.2.1 в среднем. Но в зависимости от числа потоков, наблюдаем небольшое преимущество той или иной редакции.
Результаты Postgres Pro Enterprise 16.2.1 + CFS почти во всех тестах НЕ лучше, чем у Postgres Pro Enterprise без CFS.

simple-update

PostgreSQL 16.2 НЕ проигрывает Postgres Pro Enterprise 16.2.1, и показывает лучший результат на 2-3% (кроме теста при 127 потоках).
Postgres Pro Enterprise 16.2.1 с поддержкой CFS по сравнению с PostgreSQL 16.2 показывает прирост в производительности до 45% на тестах при большем числе потоков.

Здесь можно сделать следующие обобщающие выводы:

Производительность ванильного Postgres в целом находится на уровне сопоставимом с версией Postgres Pro, когда не используется сжатие.
Postgres Pro с использованием CFS ощутимо выигрывает у ваниллы на тестах tpcb-like и simple-update.

Тестирование Конфигурации 3

И переходим к самому интересному – тестам Postgres Pro с параметрами, которых нет в ванильной версии. По аналогии с предыдущими тестами, на диаграммах сравниваются достигнутые различными редакциями TPS.
tpcb-like: Результаты изменились незначительно и аналогичны результатам, полученным на одинаковом наборе настроек.

PostgreSQL 16.2 показывает лучший результат на 2-3% в сравнении с Postgres Pro Enterprise.
Postgres Pro Enterprise 16.2.1 с поддержкой CFS по сравнению с PostgreSQL 16.2 теперь показывает прирост в производительности до 29%. Прирост в 2%

select-only: Ванильная версия PostgreSQL 16.2 проигрывает редакциям Postgres Pro Enterprise 16.2.1 / Postgres Pro 16.2.1 + CFS до 37%. При одинаковых настройках производительность была примерно равной.

simple-update: Результаты сопоставимы с результатами, полученными на одинаковом наборе настроек:

PostgreSQL 16.2 в целом показывает сопоставимые результаты с Postgres Pro Enterprise 16.2.1 – небольшой выигрыш 1-2% при меньшем количестве потоков и заметно худший результат при 127 потоках.
Postgres Pro 16.2.1 с поддержкой CFS по сравнению с PostgreSQL 16.2 показывает прирост в производительности до 50%.

Итого:

Ванильный Postgres все еще немного производительнее Postgres Pro на тесте tpcb-like и в целом сопоставим по производительности при тесте simple-update. На select-only тесте Postgres Pro показал производительность на ~37% лучше, чем у ваниллы.
Производительность Postgres Pro с использованием CFS значительно превосходит ванильную: в зависимости от теста, выигрыш составил от 30 до 50%.

Что действительно влияет на производительность?

Для выявления параметров, которые внесли максимальное влияние, были проведены дополнительные тесты. По их результатам мы выявили двух победителей:

autoprepare_for_protocol = simple: этот параметр устанавливает протокол, по которому передаются запросы, обрабатываемые механизмом автоподготовки.
autoprepare_threshold = 2: параметр определяет минимальное количество выполнений оператора, после которого он будет подготовлен.

Сводные таблицы производительности Postgres Pro Enterprise относительно PostgreSQL (при использовании конфигурации 2):

Тест	Конфигурация 2	Конфигурация 3
Postgres Pro Enterprise 16.2.1	Postgres Pro Enterprise 16.2.1 + CFS	Postgres Pro Enterprise 16.2.1	Postgres Pro Enterprise 16.2.1 + CFS
tpcb-like	- 3%	+ 27%	- 3%	+ 29%
select-only	- 3%	=	+ 37%	+ 37%
simple-update	- 3%	+ 45%	- 3%	+ 50%

Cравнение производительности Postgres Pro Enterprise относительно самого себя на Конфигурации 3 относительно Конфигурации 1:

Тест	Postgres Pro Enterprise 16.2.1	Postgres Pro Enterprise 16.2.1 + CFS
tpcb-like	+ 25%	+ 63%
select-only	+ 79%	+ 79%
simple-update	+ 32%	+ 74 %

Загрузка железа во время тестов

Полученные значения TPS – это хорошо и наглядно, но по уму надо их соотнести с реальной загрузкой железа. Иначе может оказаться, что выигрыш в TPS нивелируется несоразмерным увеличением загрузки железа. Вот, что мы увидели:

При тестах tpcb-like и simple-update с аналогичным числом клиентов/потоков, на БД с включенной CFS утилизация CPU выше на 5-10%, но и объем записи на диск кратно меньше (до 4 раз в начале теста). На графиках ниже БД с CFS справа. При тестах без CFS объем дисковой записи большую часть теста превышал 300MB/sec, в то время как при тестах на БД с поддержкой CFS был ~150MB/sec.
При текущем паттерне нагрузки (тесты tpcb-like / select-only / simple-update, запускаемые сразу друг за другом) на select-only тестах Postgres обеих редакций в начале теста присутствует просадка производительности. Для примера, график TPS для теста выполненного для 102 потоков:
Графики утилизации системных ресурсов показывают, что в начале select-only тестов производится интенсивная запись на диск:

Это интересное поведение, поэтому с помощью инструмента pgpro-otel-collector была собрана информация из pg_stat_activity, позволившая выявить типы ожиданий, с которыми связана запись на диск: XactSLRU, WALInsert, WALWrite.

Также отметим, что тесту select-only предшествует тест tpcb-like, транзакции которого добавляют/обновляют случайные строки:
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES

tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
Соответственно, поскольку фиксация изменений осуществляется только в структуре журнала фиксации транзакций clog, при дальнейшем запуске select-only теста происходит обращение к табличным страницам и проверка статуса ранее выполненных транзакций через обращение к SLRU-кешу. Часть блоков отсутствует в кеше, что приводит к необходимости считывать их с диска. После выяснения статуса транзакций, они записываются в заголовок версии строки в информационные биты (hint bits), что и вызывает интенсивную запись на диск. Кроме того, изменение hint bits журналируется в виде полного образа страницы (FPI), с чем и связаны ожидания записи журнала. Таким образом, тайна высокой нагрузки на запись при запуске select-only теста раскрыта.

Выводы

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

Тюнинг конфигурации Postgres Pro Enterprise (c обобщением на PostgreSQL) и настроек ОС (включение huge_pages, перенос pg_stat_tmp в RAM-диск, ака Конфигурация 2), позволяет достичь следующего прироста производительности по сравнению с настройками Postgres Pro Enterprise / PostgreSQL «из коробки»:
1. в сценарии tpcb-like – до 63% на тестах при большем числе потоков
2. в сценарии select-only – до 79%
3. в сценарии simple-update – до 74%
При одинаковых конфигурациях, настроенных для работы в производительном режиме (Конфигурация 2), Postgres Pro Enterprise 16.2.1 и ванильная версия PostgreSQL 16.2 показывают схожие результаты на всех сценариях тестирования (tpcb-like, select-only, simple-update), с небольшим преимуществом ванильной версии от 2 до 6%. При этом, если Postgres Pro Enterprise используется вместе с CFS, то по сравнению с ваниллой:
1. в тесте tpcb-like – редакция Enterprise выигрывает до 27% в тестах с большим числом потоков
2. в тесте select-only – демонстрирует практически такой же результат
3. в тесте simple-update — показывает прирост в производительности до 45% при большом количестве потоков.
Применение специфичных настроек, имеющихся только в Enterprise-редакции (Конфигурация 3), позволяет Postgres Pro Enterprise 16.2.1 в сравнении с ванильным PostgreSQL 16.2 увеличить производительность в тесте select-only до 37% на тестах при большом числе потоков. А при использовании CFS Enterprise опережает ваниллу во всех тестах.
1. tpcb-like – выигрывает до 29% на тестах при большом количестве потоков
2. select-only –- выигрывает до 37% на тестах при большом количестве потоков
3. simple-update – показывает прирост в производительности до 50% на тестах при большом количестве потоков
Максимальный прирост производительности обеспечили параметры планировщика, имеющиеся только в редакции Postgres Pro Enterprise: autoprepare_for_protocol = simple и autoprepare_threshold = 2.
Заодно найдена интересная особенность поведения SLRU-кэшей при простановке Hint-битов. Тесты tpcb-like / select-only / simple-update запускаются сразу друг за другом в каждой итерации, а в следующей итерации число тредов увеличивается. Такой паттерн нагрузки приводит к тому, что в течение двух минут после начала select-only теста, базы данных обеих редакций изменяют Hint-биты и производят интенсивную запись на диск. Как результат, наблюдается постепенный рост производительности от 10% до 100%.

На этом всё. Сделать какие-то более глубокие метафизические выводы мы оставляем за вами, т.к. наша задача – показать результаты как есть. Остаётся добавить, что методика тестирования и сами тесты доступны всем и каждому, поэтому, если у вас получаются кардинально другие результаты, будем бесконечно рады их обсудить и подумать над причинами.