PG_EXPECTO 5.1: Влияние vm.swappiness=1 на производительность PostgreSQL / Хабр

Миф о swappiness: когда рекомендация не работает на практике.

Настоящее исследование посвящено экспериментальной проверке общепринятой рекомендации по снижению параметра vm.swappiness для серверов PostgreSQL . В ходе нагрузочного тестирования на синтетической рабочей нагрузке, имитирующей аналитические запросы, было оценено влияние значений vm.swappiness = 10 и vm.swappiness = 1 на производительность СУБД и инфраструктуры. Результаты выявили неожиданные закономерности, ставящие под сомнение универсальность данной рекомендации.

В статье представлены результаты нагрузочного тестирования PostgreSQL на синтетической рабочей нагрузке. Цель исследования: определить - как снижение параметра vm.swappiness влияет на взаимодействие СУБД с подсистемой ввода-вывода Linux.

Глоссарий терминов | Postgres DBA | Дзен

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Теоретическая часть

Влияние vm.swappiness

Высокое значение (например, 60 по умолчанию):

Эффект: Ядро активно выгружает данные в своп, даже если физическая память не исчерпана.
Влияние : Это может привести к "трешингу" (thrashing) — постоянному обмену данными между RAM и диском.

Низкое значение (например, 1-10):

Эффект: Ядро свопирует данные, только когда свободной оперативной памяти практически не осталось. Кэш PostgreSQL (например, shared_buffers) и данные для больших запросов с большей вероятностью останутся в быстрой RAM.
Влияние: Это обеспечивает стабильность для длительных аналитических операций, минимизируя риск внезапных задержек из-за подкачки с диска. Однако слишком низкое значение (0) может привести к неожиданному завершению процесса (OOM Kill) при резком росте нагрузки.

Как применить настройку

Настройка производится через файл /etc/sysctl.conf (для постоянного изменения) или через интерфейс sysctl.

Проверить текущее значение: cat /proc/sys/vm/swappiness
Изменить значение (временно, до перезагрузки): sudo sysctl -w vm.swappiness=1
Сделайть изменение постоянным. Добавить или изменить строку в файле /etc/sysctl.conf : vm.swappiness=1
После сохранения файла, применить изменения: sudo sysctl -p /etc/sysctl.conf

Итог

Рекомендуется установить vm.swappiness=1, чтобы минимизировать обращения к медленному диску и обеспечить стабильную производительность при обработке больших данных. Это заставит ядро использовать своп только в крайнем случае, сохраняя рабочие данные в оперативной памяти.

Ссылки:

Mastering PostgreSQL Performance: Linux Tuning and Database Optimization - DEV Community

PostgreSQL load tuning on Red Hat Enterprise Linux

PostgreSQL Tuning Tips for Better Performance - Genexdbs

Практическая часть: Экспериментальная проверка влияния vm.swappiness=1

Тестовая среда, инструменты и конфигурация СУБД:

СУБД: PostgreSQL 17
Инструмент нагрузочного тестирования: pg_expecto
Тестовая база данных: pgbench (10GB, простая структура)
CPU = 8
RAM = 8GB

postgresql.auto.conf

postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
track_io_timing = 'on'
listen_addresses = '0.0.0.0'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'postgresql-%u.log'
log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| '
log_truncate_on_rotation = 'on'
log_checkpoints = 'on'
archive_mode = 'on'
archive_command = 'true'
archive_timeout = '30min'
checkpoint_warning = '60'
checkpoint_completion_target = '0.9'
min_wal_size = '2GB'
synchronous_commit = 'on'
wal_compression = 'on'
random_page_cost = '1.1'
effective_io_concurrency = '300'
wal_sender_timeout = '0'
autovacuum_naptime = '1s'
autovacuum_vacuum_scale_factor = '0.01'
autovacuum_analyze_scale_factor = '0.005'
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_max_workers = '4'
autovacuum_work_mem = '256MB'
vacuum_cost_limit = '4000'
bgwriter_delay = '10ms'
bgwriter_lru_multiplier = '4'
bgwriter_lru_maxpages = '400'
max_locks_per_transaction = '256'
max_pred_locks_per_transaction = '256'
temp_buffers = '14MB'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
max_parallel_maintenance_workers = '4'
hash_mem_multiplier = '2'
autovacuum_vacuum_insert_scale_factor = '0.01'
shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling'
commit_delay = '1000'
log_autovacuum_min_duration = '0'
wipe_file_on_delete = 'on'
wipe_heaptuple_on_delete = 'on'
wipe_mem_on_free = 'on'
wipe_memctx_on_free = 'on'
wipe_xlog_on_free = 'on'
log_connections = 'on'
log_disconnections = 'on'
pg_stat_statements.track = 'all'
max_connections = '1000'
max_parallel_workers_per_gather = '1'
max_parallel_workers = '16'
max_worker_processes = '16'
effective_cache_size = '6GB'
work_mem = '32MB'
maintenance_work_mem = '512MB'
max_wal_size = '32GB'
checkpoint_timeout = '5min'
shared_buffers = '4GB'

Важное изменение в версии 5.1 - изменены тестовые сценарии scenario-2 и scenario-3.

scenario-2 (INSERT)

-- scenario2.sql
-- INSERT
CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$
DECLARE
 min_i bigint ;
 max_i bigint ;
 current_aid bigint ;
 current_tid bigint ;
 current_bid bigint ;
 current_delta bigint ;
BEGIN
---------------------------------------------------
--СЦЕНАРИЙ 3 - INSERT ONLY
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
SELECT MIN(tid) INTO min_i FROM pgbench_tellers ;
SELECT MAX(tid) INTO max_i FROM pgbench_tellers ;
current_tid = floor(random() * (max_i - min_i + 1)) + min_i ;
SELECT MIN(bid) INTO min_i FROM pgbench_branches ;
SELECT MAX(bid) INTO max_i FROM pgbench_branches ;
current_bid = floor(random() * (max_i - min_i + 1)) + min_i ;
SELECT random() * 1000.0
INTO current_delta;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES ( current_tid , current_bid , current_aid , current_delta , CURRENT_TIMESTAMP );
--ССЦЕНАРИЙ 3 - INSERT ONLY
---------------------------------------------------
 return 0 ;
END
$$ LANGUAGE plpgsql;

scenario-3 (UPDATE)

-- scenario3.sql
-- UPDATE
CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$
DECLARE
 min_i bigint ;
 max_i bigint ;
 current_aid bigint ;
 current_delta bigint ;
BEGIN
---------------------------------------------------
--1)UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
current_delta = (ROUND( random ())::bigint)*10 + 1 ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
--1)
UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE aid = current_aid ;
--1)
 return 0 ;
END
$$ LANGUAGE plpgsql;

scenario-1 (SELECT) - без изменений

-- scenario1.sql
-- version 1.0
CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$
DECLARE
 test_rec record ;
 min_i bigint ;
 max_i bigint ;
 current_aid bigint ;
 current_tid bigint ;
 current_bid bigint ;
 current_delta bigint ;
 counter bigint;
BEGIN
---------------------------------------------------
--СЦЕНАРИЙ 1 - SELECT ONLY
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
select br.bbalance
into test_rec
from pgbench_branches br
join pgbench_accounts acc on (br.bid = acc.bid )
where acc.aid = current_aid ;
--СЦЕНАРИЙ 1 - SELECT ONLY
---------------------------------------------------
return 0 ;
END
$$ LANGUAGE plpgsql;

Веса тестовых сценариев и параметры нагрузочного тестирования

# Веса сценариев по умолчанию

scenario1 = 0.7

scenario2 = 0.2

scenario3 = 0.1

param.conf

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Максимальная нагрузка
finish_load = 20
# Тестовая БД
testdb = default
# testdb = demo
# Веса сценариев по умолчанию
scenario1 = 0.7
scenario2 = 0.2
scenario3 = 0.1
# Инициализировать тестовую БД
init_test_db = on
#init_test_db = off
# Размер тестовой БД
# ~200MB
#scale = 20
#~10GB
scale = 685

Нагрузка на СУБД в ходе экспериментов

Измененные параметры операционной системы

Общие параметры производительности

vm.dirty_ratio = 10

vm.dirty_background_ratio = 5

Параметры IO-планировщика

[mq-deadline] kyber bfq none

Настройки кэширования и буферизации

vm.vfs_cache_pressure = 50

Оптимизация параметров файловой системы

/dev/mapper/vg_data-LG_data on /data type ext4 (rw,noatime,nodiratime)

Изменение размера буферов для операций с блочными устройствами

read_ahead_kb = 256

Экспериментальная проверка эффективности изменения параметров операционной системы

PG_EXPECTO: Оптимизация вместо апгрейда - практический кейс увеличения производительности СУБД PostgreSQL на 65% через настройку ОС. | Postgres DBA | Дзен

Эксперимент-1 ( vm.swappiness = 10 )

PG_EXPECTO 5.1 + OS tuning : Эксперимент-2(shared_buffers = 4GB) | Postgres DBA | Дзен

Изменение vm.swappiness

#Текущее значение
# cat /proc/sys/vm/swappiness
10

#Изменение значения
# sysctl -w vm.swappiness=1
vm.swappiness = 1
# vi /etc/sysctl.conf
vm.swappiness = 1
# sysctl -p /etc/sysctl.conf
vm.swappiness = 1

#Контроль изменения
# cat /proc/sys/vm/swappiness
1

Эксперимент-2 (vm.swappiness = 1)

Корреляционный анализ производительности и ожиданий СУБД

Операционная скорость

Изменение операционной скорости СУБД в ходе нагрузочного тестирования

Среднее уменьшение операционной скорости в Эксперименте-2 (vm.swappiness = 1) составило 14.37%

Ожидания СУБД

Изменение ожиданий СУБД в ходе нагрузочного тестирования

Среднее увеличение ожиданий в Эксперименте-2 (vm.swappiness = 1) составило 1.03%

Производительность подсистемы IO

Предварительный итог

Изменение параметра vm.swappiness = 1 практически не оказывает влияния на метрики производительности подсистемы IO.

IOPS (Производительность IO)

Изменение производительности IO(IOPS) в ходе нагрузочного тестирования

Среднее уменьшение производительности IO в Эксперименте-2 (vm.swappiness = 1) составило 1.96%

MB/s (Пропускная способность IO)

Изменение пропускной способности IO(MB/s) в ходе нагрузочного тестирования

Среднее уменьшение пропускной способности IO в Эксперименте-2 (vm.swappiness = 1) составило 7.16%

Сравнительный анализ производительности и ожиданий СУБД и метрик vmstat

Цель анализа: Оценка влияния параметра vm.swappiness на производительность СУБД PostgreSQL и инфраструктуры при заданной нагрузке.

Контекст:

Эксперимент 1: vm.swappiness = 10
Эксперимент 2: vm.swappiness = 1
Характер нагрузки: Интенсивное чтение данных . Преобладают операции DataFileRead.
Конфигурация СУБД: Направлена на агрессивную автоочистку (autovacuum), высокий effective_io_concurrency, раздельные диски для данных и WAL.
Инфраструктура: 8 CPU, 8GB RAM, дисковая подсистема LVM.

Сравнение ключевых метрик производительности СУБД

Операционная скорость (SPEED):

Эксперимент 1: Начальная скорость выше (~611 850). Наблюдается выраженный отрицательный тренд (угол наклона -23.65). Скорость снижается на ~26% к концу теста.
Эксперимент 2: Начальная скорость ниже (~500 504). Отрицательный тренд также присутствует (угол наклона -26.99), но абсолютное падение скорости меньше (~7%).
Вывод: В условиях эксперимента снижение swappiness не привело к улучшению максимальной пропускной способности, однако общее относительное падение производительности под нагрузкой было менее выраженным.

Ожидания СУБД (WAITINGS):

Оба эксперимента демонстрируют:

Сильный и стабильный рост ожиданий во времени (R² ~0.91, угол наклона ~43.6).
Практически полную корреляцию общих ожиданий с ожиданиями типа IO (WAITINGS - IO = 1.000).
80% и более всех ожиданий приходятся на операцию DataFileRead.

Ключевое различие:

Эксперимент 1: Ожидания коррелируют с IPC (межпроцессное взаимодействие) и LOCK отрицательно или слабо.
Эксперимент 2: Появилась заметная положительная корреляция ожиданий с IPC (0.86) и LWLOCK (0.86), что указывает на возросшую конкуренцию за легковесные блокировки и буферы в памяти.

Сравнение метрик инфраструктуры (vmstat) и их корреляция с СУБД

Загрузка CPU и очередь процессов:

Оба эксперимента:

Нагрузка на CPU (us + sy) не является проблемой (низкие значения, нет превышения 80%).
Очередь исполняемых процессов (procs_r) не превышает количество ядер CPU.
Высокий процент времени ожидания ввода-вывода (cpu_wa): 50-71% в Эксперименте 1, 51-69% в Эксперименте 2.
Различия: Существенных различий в метриках CPU между экспериментами не выявлено.

Дисковый ввод-вывод (IO):

Оба эксперимента показывают идентичные проблемы:

ALARM: Очень высокая корреляция ожиданий СУБД типа IO с cpu_wa (~0.94) и procs_b (~0.98). Это указывает, что процессы СУБД массово переходят в состояние непрерываемого сна (D) из-за ожидания ответа от диска.
ALARM: Количество процессов в состоянии D (procs_b) стабильно растет и в пике превышает количество ядер CPU в 2 раза.
ALARM: Соотношение читаемых блоков к записываемым высокое (>2.7), что подтверждает read-intensive характер нагрузки.
ALARM: Прямая сильная корреляция операционной скорости СУБД с количеством прочитанных с диска блоков (~0.97). Производительность напрямую упирается в скорость чтения с диска.
Вывод: Параметр swappiness не оказал влияния на профиль и тяжесть проблем с дисковым IO. Система упирается в пределы производительности дисковой подсистемы.

Память (RAM):

Общая картина в обоих экспериментах:

ALARM: Свободная оперативная память (memory_free) практически отсутствует (<5% на протяжении >50% наблюдений).
OK: Своппинг (swap_si, swap_so) не использовался, несмотря на нехватку свободной RAM.
Ключевой вывод: Даже при swappiness=10 система не прибегала к своппингу. Нехватка свободной RAM компенсировалась активным вытеснением файлового кэша, что усугубляло проблему с дисковым IO, заставляя чаще читать данные с диска.

Итоговый вывод о влиянии vm.swappiness

Для данной конкретной конфигурации (8 CPU, 8 GB RAM) и предоставленного характера нагрузки (интенсивное чтение, вызывающее дефицит свободной оперативной памяти):

Отсутствие влияния на своппинг: Изменение параметра vm.swappiness с 10 на 1 не привело к качественному изменению поведения системы. В обоих случаях система предпочла не использовать раздел подкачки, даже в условиях хронической нехватки свободной оперативной памяти.
Несущественное влияние на производительность СУБД: Не было зафиксировано значимого улучшения или ухудшения итоговой операционной скорости (SPEED) СУБД. Основная проблема производительности в обоих экспериментах была одинакова и заключалась в дисковом вводе-выводе, который стал узким местом.
Изменение профиля конкуренции: Снижение swappiness до 1 привело к появлению статистически значимой корреляции ожиданий СУБД с IPC и LWLOCK. Это косвенно свидетельствует о том, что при более жестком ограничении на использование свопа процессы СУБД активнее конкурировали за структуры данных в оперативной памяти (буферы, легковесные блокировки), однако это не стало новым узким местом по сравнению с дисковым IO.

Общий вывод:

В рамках проведенных экспериментов с интенсивной нагрузкой, вызывающей дефицит оперативной памяти, параметр vm.swappiness не оказал решающего влияния на общую производительность системы и СУБД PostgreSQL. Основным лимитирующим фактором в обоих случаях была производительность дисковой подсистемы. Система в обоих конфигурациях избегала своппинга, выбирая стратегию вытеснения файлового кэша, что лишь усиливало нагрузку на диски. Изменения в поведении СУБД были минимальны и не повлияли на ключевую проблему производительности.

Анализ производительности IO для файловой системы /data

Общая характеристика системы

Аппаратная конфигурация:
CPU: 8 ядер (x86_64)
RAM: 8 GB
Диск для /data: vdd → vg_data-LG_data (99 GB LVM)

Критические проблемы производительности по файловой системе /data

Загрузка диска (utilization): 100% в 100% наблюдений в обоих экспериментах
Очередь запросов (aqu_sz): постоянно превышает 1 (100% наблюдений)
Ожидание CPU для IO (cpu_wa): стабильно >50% (в 100% наблюдений)
Процессы в состоянии ожидания IO (proc_b): регулярно превышают количество ядер CPU (25-50% наблюдений)
Соотношение чтения/записи: ~2.9:1

Анализ корреляций и паттернов нагрузки по файловой системе /data

Высокая корреляция cpu_wa и util: 0.94 (Эксперимент-1) и 0.87 (Эксперимент-2) — процессы ждут диск
Отрицательная корреляция cache с операциями чтения/записи: эффективное использование кэша
Высокая корреляция скорости операций с shared_blks_read: 0.97 — производительность напрямую зависит от чтения с диска
Слабые/отрицательные корреляции с IOPS и MB/s: производительность не ограничена пропускной способностью или IOPS

Диагностика узких мест IO по файловой системе /data

Сравнение метрик между экспериментами:

r_await(ms):

Эксперимент-1: 1-5 мс (MIN: 1.47, MAX: 5.12)
Эксперимент-2: 1-6 мс (MIN: 1.47, MAX: 5.68)
Изменение: незначительное увеличение максимума во втором эксперименте

w_await(ms):

Эксперимент-1: 1-15 мс (MIN: 1.63, MAX: 14.91)
Эксперимент-2: 1-5 мс (MIN: 1.63, MAX: 4.49)
Изменение: значительное улучшение во втором эксперименте

aqu_sz (глубина очереди):

Эксперимент-1: 6-38 (MIN: 6.07, MAX: 38.36)
Эксперимент-2: 6-30 (MIN: 6.07, MAX: 29.88)
Изменение: снижение максимальной глубины очереди

proc_b (процессы в uninterruptible sleep):

Оба эксперимента: регулярно превышают количество ядер CPU
Эксперимент-1: 42.99% наблюдений с превышением
Эксперимент-2: 42.73% наблюдений с превышением
Изменение: минимальное улучшение

cpu_wa(%) (время ожидания CPU для IO):

Эксперимент-1: 50-71%
Эксперимент-2: 51-69%
Изменение: незначительное снижение диапазона

Корреляция speed с IOPS:

Эксперимент-1: -0.7012 (отрицательная)
Эксперимент-2: -0.7442 (отрицательная)
Изменение: усиление отрицательной корреляции

Корреляция speed с пропускной способностью (MB/s):

Эксперимент-1: -0.8707 (отрицательная)
Эксперимент-2: -0.7820 (отрицательная)
Изменение: ослабление отрицательной корреляции

Вывод по диагностике узких мест IO:

Диск /data является узким местом в обоих экспериментах
Высокая загрузка диска (100%) приводит к образованию очереди запросов
Процессы проводят значительное время в ожидании IO (cpu_wa >50%)
Характер нагрузки: преобладание операций чтения
Проблема не в пропускной способности диска или IOPS

Вывод о влиянии параметра vm.swappiness на производительность IO для файловой системы /data

Минимальное влияние на ключевые метрики: изменение vm.swappiness с 10 на 1 не привело к существенному изменению производительности подсистемы IO

Незначительные улучшения во втором эксперименте:

Снижение максимального времени ожидания записи (w_await)
Уменьшение максимальной глубины очереди запросов (aqu_sz)
Небольшое снижение времени ожидания CPU для IO (cpu_wa)

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

Характер нагрузки определяет производительность: сценарий с высоким соотношением чтения/записи создает устойчивую нагрузку на диск, которая не может быть компенсирована настройками swappiness в данных условиях

Результат согласуется с анализом корреляций: в обоих экспериментах выявлено, что узкое место не в IOPS или пропускной способности, а в самой загрузке диска, что делает изменение vm.swappiness неэффективным для решения основной проблемы

Общий вывод

Проведенные эксперименты не подтвердили ожидаемого положительного влияния снижения параметра vm.swappiness на производительность PostgreSQL при интенсивной нагрузке с дефицитом оперативной памяти.

Изменение параметра не привело к качественному изменению поведения подсистемы памяти: система в обоих случаях избегала своппинга, предпочитая вытеснять файловый кэш.
Не было зафиксировано значимого улучшения или ухудшения итоговой операционной скорости СУБД. Ключевым узким местом в обоих экспериментах стала производительность дисковой подсистемы.
Снижение vm.swappiness до 1 привело лишь к косвенному изменению профиля конкуренции за ресурсы в памяти, но это не повлияло на общую картину производительности, ограниченную вводом-выводом.
Для подсистемы IO изменение параметра также не оказало существенного влияния: проблема 100% загрузки диска и высокой очереди запросов сохранилась при обоих значениях.

В условиях конкретной тестовой конфигурации (интенсивное чтение, нехватка RAM, ограниченная дисковая производительность) рекомендация по снижению vm.swappiness для оптимизации PostgreSQL не нашла экспериментального подтверждения. Производительность системы определялась другими факторами.

P.S. Анализ причин снижения производительности СУБД при применении vm.swappiness=1

PG_EXPECTO: правда о vm.swappiness и производительности PostgreSQL под нагрузкой

PG_EXPECTO 5.1: Влияние vm.swappiness=1 на производительность PostgreSQL