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

В апреле этого года мы, команда производительности из 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

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

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

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

2. Каждый тест выполнялся 600 секунд.

3. pgbench использовал простой протокол запросов (параметр -M simple). В принципе, он выбирается по умолчанию, просто мы решили выбирать его явным образом.

4. Для каждой из семи комбинаций параметров Клиент/Поток последовательно запускались встроенные в pgbench тесты tpcb-like, select-only и simple-update

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

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

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

tpcb-like: Максимальная производительность составила 41877 TPS при тесте для 127 потоков

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

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

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

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

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

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

• Postgres Pro Enterprise 16.2.1 с поддержкой CFS по сравнению с PostgreSQL 16.2 показывает прирост в производительности до 27% на тестах при большом числе потоков.

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

• Результаты Postgres Pro Enterprise 16.2.1 + CFS почти во всех тестах НЕ лучше, чем у Postgres Pro Enterprise без CFS.

• 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%.

Итого:

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

2. Производительность Postgres Pro с использованием CFS значительно превосходит ванильную: в зависимости от теста, выигрыш составил от 30 до 50%.

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

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

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

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

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

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

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

Полученные значения 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 теста раскрыта.

Выводы

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

1. Тюнинг конфигурации 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. При одинаковых конфигурациях, настроенных для работы в производительном режиме (Конфигурация 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% при большом количестве потоков.

3. Применение специфичных настроек, имеющихся только в 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% на тестах при большом количестве потоков

4. Максимальный прирост производительности обеспечили параметры планировщика, имеющиеся только в редакции Postgres Pro Enterprise: autoprepare_for_protocol = simple и autoprepare_threshold = 2.

5. Заодно найдена интересная особенность поведения SLRU-кэшей при простановке Hint-битов. Тесты tpcb-like / select-only / simple-update запускаются сразу друг за другом в каждой итерации, а в следующей итерации число тредов увеличивается. Такой паттерн нагрузки приводит к тому, что  в течение двух минут после начала select-only теста, базы данных обеих редакций изменяют Hint-биты и производят интенсивную запись на диск. Как результат, наблюдается постепенный рост производительности от 10% до 100%.

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