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

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

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

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

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

Влияние vm.swappiness на OLAP в PostgreSQL

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

• Эффект: Ядро активно выгружает данные в своп, даже если физическая память не исчерпана.

• Влияние на OLAP: Это может привести к "трешингу" (thrashing) — постоянному обмену данными между RAM и диском. Для OLAP-запросов, которые сканируют большие наборы данных, это означает падение производительности, так как система тратит время на ввод-вывод, а не на обработку.

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

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

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

Рекомендации по настройке для OLAP

Для серверов PostgreSQL, ориентированных на OLAP-нагрузку, общепринятая рекомендация — снизить значение vm.swappiness.

• Базовое значение: vm.swappiness=1 для минимального использования свопа, но не полного его отключения.

• Альтернатива: В некоторых продакшен-сценариях, особенно на системах с большим объемом RAM, используют значение vm.swappiness=10 как баланс между стабильностью и гибкостью.

• Важное предупреждение: Значение 0 использовать не рекомендуется, так как это может дестабилизировать систему и спровоцировать агрессивное действие OOM Killer (механизма, который завершает процессы при нехватке памяти).

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

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

1. Проверить текущее значение: cat /proc/sys/vm/swappiness

2. Изменить значение (временно, до перезагрузки): sudo sysctl -w vm.swappiness=1

3. Сделайть изменение постоянным. Добавить или изменить строку в файле /etc/sysctl.conf : vm.swappiness=1

4. После сохранения файла, применить изменения: sudo sysctl -p /etc/sysctl.conf

Итог

Для OLAP-нагрузок в PostgreSQL рекомендуется установить 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
# 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.

-- 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;

-- 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;

-- 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

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Максимальная нагрузка
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 в Эксперименте-2 (vm.swappiness = 1) составило 1.96%

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

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

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

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

Контекст:

• Эксперимент 1: vm.swappiness = 10

• Эксперимент 2: vm.swappiness = 1

• Характер нагрузки: Интенсивное чтение данных (OLAP-сценарий). Преобладают операции 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 (OLAP) характер нагрузки.

• 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) и предоставленного характера нагрузки (интенсивное чтение, вызывающее дефицит свободной оперативной памяти):

1. Отсутствие влияния на своппинг: Изменение параметра vm.swappiness с 10 на 1 не привело к качественному изменению поведения системы. В обоих случаях система предпочла не использовать раздел подкачки, даже в условиях хронической нехватки свободной оперативной памяти.

2. Несущественное влияние на производительность СУБД: Не было зафиксировано значимого улучшения или ухудшения итоговой операционной скорости (SPEED) СУБД. Основная проблема производительности в обоих экспериментах была одинакова и заключалась в дисковом вводе-выводе, который стал узким местом.

3. Изменение профиля конкуренции: Снижение 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 (OLAP-сценарий)

Анализ корреляций и паттернов нагрузки по файловой системе /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%)

• Характер нагрузки: OLAP с преобладанием операций чтения

• Проблема не в пропускной способности диска или 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 для оптимизации OLAP-нагрузки в PostgreSQL не нашла экспериментального подтверждения. Производительность системы определялась другими факторами.

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

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