Как выжать максимум из PostgreSQL? Тестируем БД на разных конфигурациях

В предыдущей статье мы приводили сравнение производительности железа (конфигураций серверов Selectel) в разных БД. Теперь пошли дальше, чтобы углубиться в тестирование на разных аппаратных платформах, а также доработать существующие тесты.

Как и раньше, одна из ключевых целей исследования — предоставление полезных данных, благодаря которым вы сможете подобрать конфигурации под различные БД. Еще одно применение результатов — улучшение внутренних процессов Selectel, например, в сфере облачных баз данных.

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

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

Список выбранных конфигураций почти не отличается от предыдущего тестирования, но в этот раз в сравнении не участвуют кастомные решения. Были выбраны наиболее популярные выделенные серверы Selectel. Так как в сравнительной таблице присутствует ARM-платформа с большим количеством ядер и потоков, остальные платформы были выбраны с максимальным приближением к этим показателям.

Конфиг Selectel	AL63-NVMe	PL64-NVME	PL84-NVME	ARM01-NVME	PL23-NVME	PL33-NVME-10GE	AL83-NVME-10GE
Процессор	2 × AMD EPYC® 7343	2 × Intel® Xeon® Silver 4314	2 × Intel® Xeon® Gold 6336Y	1 x Ampere® Altra® Max M128-30	2 × Intel® Xeon® Silver 4214R	2 × Intel® Xeon® Gold 6240R	2 × AMD EPYC® 7513
Всего ядер	32	32	48	128	24	48	64
Всего потоков	64	64	96	128	48	96	128
RAM	Micron DDR4	Micron DDR4	Micron DDR4	Micron DDR4	Micron DDR4	Micron DDR4	Micron DDR4
Диски SSD	intel® NVMe PCIe 4	intel® NVMe PCIe 4	intel® NVMe PCIe 4	intel® NVMe PCIe 4	intel® NVMe PCIe 4	intel® NVMe PCIe 4	intel® NVMe PCIe 4

Исходя из предыдущих результатов тестирования, нашим фаворитом стал ARM01-NVME с одним сокетом. За свою стоимость сервер составляет отличную конкуренцию двухсокетным системам.

Отмечу, что у участников сравнения используется RAM DDR4 одного производителя, а также одинаковые SSD-диски с поддержкой NVMe и PCIe Gen 4. Все рассматриваемые серверы фиксированной конфигурации. Это значит, что их можно использовать в рабочих задачах уже через несколько минут после аренды. Собрать любое решение, в том числе кастомное, можно в конфигураторе выделенных серверов.

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

Выбор ОС и версии PostgreSQL

Операционную систему установили на SSD-диски. Использовали Ubuntu 22.04 LTS. Причина выбора PostgreSQL проста, ведь это одна из самых популярных БД среди наших пользователей. Напомню, что в предыдущем тесте мы использовали PostgreSQL 14:

В этот раз мы будем сравнивать Postgres Pro Enterprise 15-16 и PostgreSQL 15-16.

Настройка BIOS

BIOS и ОС настроены в режиме performance. Рассмотрим ключевые настройки BIOS, которые могут влиять на максимальную производительность сервера. Далее я покажу наиболее распространенные настройки для платформ на базе процессоров Intel®, AMD® и Ampere®.

Intel®

Включаем Hyper-Threading. Благодаря технологии Intel® Hyper-Threading каждое ядро процессора может выполнять сразу два потока. Это позволяет увеличить количество одновременно обрабатываемых задач и повысить эффективность использования ресурсов процессора.

Включение Hyper-Threading может значительно улучшить производительность в многопоточных приложениях и средах, где в один момент времени выполняется много задач. Например, в серверах виртуализации.

Включаем Turbo Boost. Если условия теплового пакета и энергопотребления позволяют, то Intel® Turbo Boost Technology динамически повышает частоту работы ядер процессора выше базовой.

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

Включаем Power Management (C-States, P-States) на P0 и С0. C-States позволяют процессору переходить в состояние пониженного энергопотребления при простое. P-States регулируют производительность процессора, изменяя его частоту и напряжение в зависимости от текущей нагрузки.

Правильная конфигурация состояний поможет достичь оптимального баланса между производительностью и энергоэффективностью. Особенно это актуально для сред с переменной нагрузкой.

В Memory Configuration включаем максимальную частоту и используем двухканальный режим. Таким образом, мы открываем доступ к таким настройкам, как выбор режима работы памяти (single, dual, channel и т. д.) и ручной конфигурации времени ее задержки. Оптимизация настроек может значительно улучшить пропускную способность и скорость доступа к памяти, что критически важно для производительности вычислительных систем.

AMD®

Включаем SMT (Simultaneous Multithreading). Аналогично Intel® Hyper-Threading, SMT® увеличивает количество потоков, которое может обрабатывать каждое ядро. Таким образом, улучшается общая производительность системы в многопоточных приложениях.

Включаем Core Performance Boost. Технология AMD® Core Performance Boost аналогична Intel® Turbo Boost. Она позволяет процессорам повышать свою частоту выше базовой в зависимости от условий тепловыделения и энергопотребления.

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

Отключаем Power Management (Cool’n’Quiet, Global C-state Control). Настройки помогают управлять энергопотреблением процессора, регулируя его частоту и напряжение в зависимости от текущей нагрузки.

Оптимизация Power Management обеспечивает баланс между производительностью и энергоэффективностью, снижая энергопотребление в периоды низкой нагрузки.

Включаем Memory Interleaving. Настройка позволяет оптимизировать доступ к памяти, распределяя обращения к данным по ее различным каналам.

Включение Memory Interleaving может значительно улучшить пропускную способность и время доступа к памяти. Это особенно важно для приложений, интенсивно использующих память.

Ampere® (серверные ARM-процессоры)

В Frequency Scaling включаем max_perfomance. Настройка позволяет динамически изменять частоту процессора в зависимости от текущей рабочей нагрузки, оптимизируя энергопотребление и производительность.

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

В Memory Configuration включаем максимальную частоту памяти. Открываем доступ к настройкам таймингов и конфигурациям памяти, направленным на оптимизацию ее скорости и пропускной способности.

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

Отключаем Energy Efficiency Modes. Настройки режимов энергоэффективности позволяют выбирать оптимальный баланс между производительностью и потреблением энергии.

Адаптация к различным рабочим условиям позволяет максимизировать производительность при минимальном энергопотреблении. Это особенно важно для долгосрочной работы серверов.

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

Memory Timing. Настройка таймингов памяти влияет на скорость обработки данных. Установка меньших значений задержек (CAS Latency, tRCD, tRP, tRAS) может повысить производительность.

NUMA (Non-Uniform Memory Access) Configuration. Настройка использования памяти в мультипроцессорных системах может существенно повысить производительность приложений, чувствительных к задержкам доступа к памяти.

Специфические настройки для Intel®

SpeedStep/EIST (Enhanced Intel SpeedStep® Technology). Это технология, позволяющая изменять частоту процессора в зависимости от нагрузки. Она необходима для оптимизации потребления энергии и производительности.

Intel® UPI Link Configuration. Это настройка скорости взаимодействия CPU между собой по протоколу Intel® Ultra Path Interconnect, который соединяет процессоры в многосокетных системах. Влияет на производительность системы и памяти.

Специфические настройки для AMD®

Memory Access Mode. Настройка режима доступа к памяти (Distributed или Local). Используется для оптимизации производительности под характер рабочей нагрузки.

Специфические настройки для Ampere®

Core Frequency Scaling. Динамическая настройка частоты ядер в зависимости от нагрузки. С ее помощью можно достичь баланса между производительностью и энергопотреблением.

Configurable TDP (Thermal Design Power). Настройка максимальной мощности, которую может потреблять процессор. Позволяет управлять балансом между производительностью и тепловым режимом.

При настройке BIOS важно помнить о возможном влиянии на стабильность и надежность системы. Рекомендуется провести тестирование после применения изменений.

Настройка ОС

В ОС отключаем политики энергосбережения и включаем режим perfomance. Напомню, что мы используем Ubuntu 22.04.

    echo performance | tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

В тестах используем максимальную рабочую частоту процессоров и памяти. Охлаждение тоже устанавливается на максимум. В двухсокетных системах NUMA настроена на равномерное распределение памяти между CPU. 

Устанавливаем количество подключений (MAX connection) и адреса расположения БД. В нашем случае тесты проводятся локально, чтобы исключить дополнительные точки отказа и сетевые задержки. Остальные настройки БД оставляем по умолчанию.

Стандартные настройки PostgreSQL используем потому, что мы не знаем профиль нагрузки пользователя на систему и нам нужна точка отсчета производительности.

Выбор режима тестирования

Используем встроенный бенчмарк pgbench, предназначенный для запуска тестов на PostgreSQL. Он выполняет одну и ту же последовательность SQL-команд, а затем вычисляет среднюю скорость транзакций (количество транзакций в секунду, TPS). 

По умолчанию pgbench тестирует сценарий, основанный на TPC-B. Он включает пять команд SELECT, UPDATE и INSERT для каждой транзакции. Мы использовали метод по умолчанию и модификации теста.

Выбрали ключи (режимы) тестирования:

• pgbench –– (смешаные запросы),
• pgbench –S –T (SELECT ONLY),
• pgbench –N –T (UPDATE).

Автоматизация теста pgbench

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

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

    CORES=$((CPU_THREADS))
HOST="127.0.0.1"
TIME="600"
# Создаем базу данных генерим таблицы
pgbench --username=root -h "${HOST}" test -i -s 10000
# Считаем кол-во потоков и подключенных пользователей
THREADS=(1 2 $(echo "scale=0; $CORES * 20 / 100" | bc -l) \
         $(echo "scale=0; $CORES * 40 / 100" | bc -l) \
         $(echo "scale=0; $CORES * 60 / 100" | bc -l) \
         $(echo "scale=0; $CORES * 80 / 100" | bc -l) \
         $(echo "scale=0; $CORES * 99.9 / 100" | bc -l))

  echo "pgbench --username=root -h ${HOST} test ${PARAM} -T ${TIME}" >> "${FILE}"
  pgbench --username=root -h "${HOST}" test ${PARAM} -T "${TIME}" >> "${FILE}"
 
  echo "pgbench --username=root -h ${HOST} test ${PARAM} -S -T ${TIME}" >> "${FILE}"
  pgbench --username=root -h "${HOST}" test ${PARAM} -S -T "${TIME}" >> "${FILE}"
 
  echo "pgbench --username=root -h ${HOST} test ${PARAM} -N -T ${TIME}" >> "${FILE}"
  pgbench --username=root -h "${HOST}" test ${PARAM} -N -T "${TIME}" >> "${FILE}"
done

Здесь мы получаем количество ядер и рассчитываем количество потоков в конкретной системе. Далее — указываем HOST, что БД будет располагаться локально на машине, устанавливаем время тестирования в секундах (TIME). После — создаем БД test и задаем количество строк. В нашем тестировании создавался миллиард строк, которые занимают примерно 250 ГБ.

Как вы можете видеть, в pgbench-тесте используются три ключа для тестирования:

• «-» — смешанные SQL-запросы (Select, Update),
• «-S -T» — упорядоченная последовательность SQL-запросов (Select only), 
• «-N -T» — режим простых SQL запросов (Update only).

Результаты тестирования получаем в виде текстового файла:

    pgbench --username=admin -h 127.0.0.1 test -c 52 -j 26 -T 600
pgbench (14.6 (Ubuntu 14.6-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10000
query mode: simple
number of clients: 52
number of threads: 26
duration: 600 s
number of transactions actually processed: 22881325
latency average = 1.363 ms
initial connection time = 235.470 ms
tps = 38150.347817 (without initial connection time)

Парсинг результатов в таблицу

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

    #!/usr/bin/awk -f
 
BEGIN {
    # Параметры таблицы, выравнивание
    client_width = 10
    thread_width = 12
    tps_width = 20
    trans_width = 20
    latency_width = 20
    init_conn_width = 25
     
    # Вывод столбцов
    printf "%-s%-s%-s%-s%-s%-s\n", "Num Clients", "Num Threads", "TPS", "Num Transactions", "Latency Average", "Initial Connection Time"
}
 
# Извлечение нужных значений из строки запуска pgbench
match($0, /-j\s+([0-9]+)/, arr) {
    num_clients = arr[1]
}
match($0, /-c\s+([0-9]+)/, arr) {
    num_threads = arr[1]
}
 
# Поиск по строкам нужных значений
/number of transactions actually processed:/ {
    num_transactions = $NF
}
/latency average/ {
    latency_average = $(NF-1) " " $NF
}
/initial connection time.*[0-9]+\.[0-9]+/ {
    # Извлечение только числовых значений
    for (i = 1; i <= NF; i++) {
        if ($i ~ /^[0-9]+\.[0-9]+$/) {
            init_conn_time = $i " " $(i+1)
            break
        }
    }
}
/tps/ {
    # Извлечение всех знаков (if present)
    for (i = 1; i <= NF; i++) {
        if ($i == "tps") {
            tps = $(i+2)
            gsub(/[()]/, "", tps) # Удаление TPS знач
            break
        }
    }
     
    # Вывод
    printf "%-*s%-*s%-*s%-*s%-*s%-*s\n", client_width, num_clients, thread_width, num_threads, tps_width, tps, trans_width, num_transactions, latency_width, latency_average, init_conn_width, init_conn_time
}

После выполнения скрипта в терминале выводятся колонки с нужными данными.

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

На каждую версию PostgreSQL и Postgres Pro Enterprise мы генерировали график для оценки производительности в многопоточном режиме:

График для оценки производительности на ядро (в одном и двух потоках):

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

Перейдем к главной задаче — оценке общей производительности разных версий.

При сравнении стандартных версий мы получили разницу в производительности от 2 до 7%, а также значительный разрыв по SELECT only. Перейдем к сравнению Pro-версий.

Видим, что в процентном соотношении результат схож со сравнением стандартных версий. Однако количество операций в секунду здесь гораздо больше. Теперь сравним PostgreSQL и Postgres Pro Enterprise:

Здесь видим значительную разницу между стандартной и Pro-версией. Одной из причин является автоконфигурирование Postgres Pro Enterprise, что немного «тюнит» БД для лучшей производительности (для нас это все равно дефолт, так как из коробки мы ничего не делаем). Скорее всего, под капотом находится еще много оптимизаций, которых мы не видим как обычные пользователи БД.

Далее мы решили выжать из железа максимум. Вот что получили:

Для режима performance была произведена настройка БД, ориентированная на максимальную производительность.

• Huge Pages эффективно управляет большим объемом памяти.
• BG Writer оптимизирует фоновую запись данных на диск.
• Prefetching обеспечивает повышенную производительность при сканировании данных.
• Вынесение каталога pg_stat_tmp в RAM (tmpfs) позволяет ускорить работу с временными файлами.
• Autovacuum обеспечивает автоматическую очистку.
• Write-Ahead Logging — запись перед чтением, увеличивает размер журнала, что позволяет редко выполнять контрольные точки.

Ниже предоставлен полный список настроек. С подробным описанием можно ознакомиться в документации Postgres Pro.

    #Определить количество HugePages. Обычно их размер равен 2 МБ. Например, нам нужно 3 100 страниц.
#Прописать в /etc/sysctl.conf настройку число больших страниц:
 
systemctl stop psotgresql
#Проверяем сколько нужно Huge page.
sudo -u postgres/opt/pgpro/ent-15/bin/postgres --shared-buffers=2GB -D /var/lib/pgpro/ent-15/data/ -C shared_memory_size_in_huge_pages
systemctl start psotgresql
 
echo 3100 | sudo tee /proc/sys/vm/nr_hugepages
 
#Проверяем настройку:
cat /proc/meminfo
#Число HugePages должно быть ненулевое, а общее должно равняться 3 100.
#Выключаем настройку THP:
echo never > /sys/kernel/mm/transparent_hugepage/enabled
#Устанавливаем группу для Huge Pages.
pg_group_id=$(id -g postgres)
echo "$pg_group_id" | sudo tee /proc/sys/vm/hugetlb_shm_group
 
sudo sed -i 's/huge_pages = try/huge_pages = on/' /var/lib/pgpro/ent-16/data/postgresql.conf
 
systemctl restart psotgresql
 
sudo sed -i "s/^#bgwriter_delay =.*/bgwriter_delay = '10ms'/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#bgwriter_flush_after =.*/bgwriter_flush_after = 0/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#bgwriter_lru_maxpages =.*/bgwriter_lru_maxpages = 4000/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#bgwriter_lru_multiplier =.*/bgwriter_lru_multiplier = 10/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#checkpoint_completion_target =.*/checkpoint_completion_target = 0.9/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#checkpoint_timeout =.*/checkpoint_timeout = '30min'/" /var/lib/pgpro/ent-16/data/postgresql.conf
 
sudo sed -i "s/^#max_wal_size =.*/max_wal_size = '32GB'/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#wal_compression =.*/wal_compression = 'lz4'/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#default_toast_compression =.*/default_toast_compression = 'lz4'/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#lwlock_shared_limit =.*/lwlock_shared_limit = 16/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#log2_num_lock_partitions =.*/log2_num_lock_partitions = 8/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#slru_buffers_size_factor =.*/slru_buffers_size_factor = 6/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#default_statistics_target =.*/default_statistics_target = 1000/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#autoprepare_threshold =.*/autoprepare_threshold = 2/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#autoprepare_for_protocol =.*/autoprepare_for_protocol = 'simple'/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#generic_plan_fuzz_factor =.*/generic_plan_fuzz_factor = 0.9/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#effective_io_concurrency =.*/effective_io_concurrency = 100/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#autovacuum_naptime =.*/autovacuum_naptime = '1s'/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#autovacuum_max_workers =.*/autovacuum_max_workers = 6/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#vacuum_cost_limit =.*/vacuum_cost_limit = 2000/" /var/lib/pgpro/ent-16/data/postgresql.conf && \
sudo sed -i "s/^#autovacuum_work_mem =.*/autovacuum_work_mem = '1GB'/" /var/lib/pgpro/ent-16/data/postgresql.conf

Скрипт для переноса каталога pg_stat_tmp в RAM-диск:

    #!/bin/bash
 
# Путь к RAM-диску
RAMDISK_PATH="/mnt/ramdisk"
 
# Размер RAM-диска (1GB)
RAMDISK_SIZE="1G"
 
# Путь к каталогу pg_stat_tmp
PG_STAT_TMP_PATH="/var/lib/pgpro/ent-16/data/pg_stat_tmp"
 
# Переменная для резервного копирования оригинального каталога
PG_STAT_TMP_BACKUP_PATH="/var/lib/pgpro/ent-16/data/pg_stat_tmp_backup"
 
# Создание каталога RAM-диска
sudo mkdir $RAMDISK_PATH
 
# Примонтирование RAM-диска
sudo mount -t tmpfs -o size=$RAMDISK_SIZE tmpfs $RAMDISK_PATH
 
# Копирование данных из pg_stat_tmp в RAM-диск
sudo cp -r $PG_STAT_TMP_PATH/. $RAMDISK_PATH/
 
# Создание символической ссылки
sudo mv $PG_STAT_TMP_PATH $PG_STAT_TMP_BACKUP_PATH
sudo ln -s $RAMDISK_PATH $PG_STAT_TMP_PATH
 
# Проверка создания символической ссылки
ls -l $PG_STAT_TMP_PATH

Заключение

Все оптимальные настройки производительности, о которых я рассказал в рамках статьи, уже выполнены при развитии продукта облако для PostgreSQL. Также, у сервиса есть такие дополнительные преимущества, как:

• подбор оборудования для высокой производительности СУБД;
• установка и оптимизация параметров СУБД;
• установка, настройка и обновление ОС и служебных компонентов;
• настройка реплик отказоустойчивого кластера;
• обеспечение ресурсов для масштабирования СУБД;
• настройка локальной сети в ЦОД для высокой производительности кластера;
• мониторинг СУБД;
• Бесплатное ежедневное резервное копирование кластера (Point-in-time Recovery), глубина хранения — одна неделя;
• хранение данных по 152-ФЗ;
• соответствие основным стандартам безопасности, включая PCI DSS 3.2.1, ISO 27001, ISO 27017, ISO 27018.

Результаты исследований постоянно используются в развитии продуктов и услуг Selectel, благодаря чему мы предоставляем лучшие базы данных. Помимо прочего, в тестировании мы подтвердили, что по возможности лучше обновляться на новую версию PostgreSQL. Это актуально как для стандартной версии, так и для Postgres Pro Enterprise.

Для предварительной настройки BIOS, ОС и самой БД вы можете воспользоваться нашими подсказками. Если нужен сервис, где все необходимые оптимизации уже применены, то это облачные базы данных Selectel. Чтобы добиться максимальной производительности, используйте Postgres Enterprise Pro с тюнингом настроек БД.