Руководство по оптимизации производительности Linux

Аудит производительности: PostgreSQL 14 на Ubuntu 22.04 (методология USE)

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

По имеющимся данным картина такая:

Рабочая гипотеза: доминирующий ресурс — дисковый I/O. Его усиливают неуправляемое число подключений (давление на память → вытеснение кэша → лишние чтения; OOM → crash-restart postmaster) и настройки ядра по умолчанию, рассчитанные не на 256 GB RAM.

⚠️ Честное ограничение. Цель p99 150 мс достижима только программным тюнингом, если основная часть латентности приходится на ожидания (I/O, блокировки, очередь подключений). Если в топе pg_stat_statements окажутся запросы, которые медленные сами по себе (seq scan по каталогу и т. п.), понадобится работа с запросами. Отдельно нужно согласовать, считается ли CREATE INDEX CONCURRENTLY «изменением схемы». Формально это не меняет модель данных, но это DDL.

Шаг 1. Базовая линия

1.1 Подготовка инструментов

# Набор инструментов
sudo apt install -y sysstat iotop-c htop bpfcc-tools linux-tools-$(uname -r) \
                    nvme-cli smartmontools numactl ethtool rasdaemon

# sysstat: включить сбор и увеличить частоту с 10 до 1 минуты
sudo sed -i 's/^ENABLED=.*/ENABLED="true"/' /etc/default/sysstat
sudo systemctl edit sysstat-collect.timer
# [Timer]
# OnCalendar=
# OnCalendar=*:*:00
sudo systemctl enable --now sysstat sysstat-collect.timer

Со стороны PostgreSQL:

-- track_io_timing меняется без рестарта
ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_temp_files = 0;              -- логировать любые temp-файлы
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();
-- pg_stat_statements требует shared_preload_libraries, то есть рестарта (см. шаг 5).
-- Если расширение уже загружено: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1.2 Скрипт сбора базовой линии

Запускать в пик (например, в 19:00–21:00), три будних дня подряд плюс один «спокойный» день для сравнения.

#!/usr/bin/env bash
# /usr/local/bin/baseline.sh — сбор базовой линии, ~10 минут
OUT=/var/tmp/baseline-$(date +%F-%H%M); mkdir -p "$OUT"; cd "$OUT"
D=600   # длительность, с

# ---------- CPU ----------
mpstat -P ALL 5 $((D/5))        > mpstat.txt   &  # %usr/%sys/%iowait/%soft по ядрам; перекос на отдельные ядра = IRQ/softirq
vmstat -w 1 $D                  > vmstat.txt   &  # r (runnable) vs b (blocked in D), cs, in, si/so, wa
pidstat -u -w 5 $((D/5))        > pidstat_cpu.txt & # cswch/s, nvcswch/s по процессам postgres
sar -q 5 $((D/5))               > sar_q.txt    &  # runq-sz, plist-sz, blocked

# ---------- Память ----------
sar -r -B -S -W 5 $((D/5))      > sar_mem.txt  &  # kbcommit/%commit, pgscank/pgscand (reclaim), majflt, swap
( for i in $(seq 1 $((D/10))); do date +%T; cat /proc/pressure/{cpu,memory,io}; sleep 10; done ) > psi.txt &

# ---------- Диск ----------
iostat -dxmt 5 $((D/5))         > iostat.txt   &  # r/s w/s r_await w_await aqu-sz rareq-sz wareq-sz (для nvme*, md*, sd*)
pidstat -d 5 $((D/5))           > pidstat_io.txt & # какие процессы читают/пишут (checkpointer, bgwriter, autovacuum, backend)
timeout 120 /usr/sbin/biolatency-bpfcc -D 10 12 > biolatency.txt & # гистограмма латентности по устройствам

# ---------- Сеть ----------
sar -n DEV,EDEV,TCP,ETCP,SOCK 5 $((D/5)) > sar_net.txt & # throughput, ошибки, retrans/s, tcp-tw
( for i in $(seq 1 $((D/30))); do date +%T; ss -s; sleep 30; done ) > ss_summary.txt &

wait

# ---------- Снимки состояния (однократно) ----------
cat /proc/meminfo                   > meminfo.txt
cat /proc/mdstat                    > mdstat.txt
numastat -m                         > numastat.txt
cat /sys/kernel/mm/transparent_hugepage/{enabled,defrag} > thp.txt
sysctl -a 2>/dev/null               > sysctl_all.txt
for d in /sys/block/{nvme*,md*,sd*}; do
  echo "$d $(cat $d/queue/scheduler 2>/dev/null) ra=$(cat $d/queue/read_ahead_kb)"; done > block_queue.txt
findmnt -o TARGET,SOURCE,FSTYPE,OPTIONS > mounts.txt
cpupower frequency-info             > cpufreq.txt 2>&1
cat /proc/net/bonding/bond0         > bond.txt
ss -tan state time-wait | awk 'NR>1{split($4,a,":"); print a[length(a)]}' | sort | uniq -c | sort -rn | head > tw_by_localport.txt
journalctl -k --since "-7d" | grep -iE -A25 "out of memory|oom-kill" > oom.txt
dmesg -T | grep -iE "nvme|md[0-9]|i/o error|timeout|mce|edac|link is down" > dmesg_err.txt
echo "done: $OUT"

1.3 Интерактивно в момент деградации

htop                    # F2 → включить колонки STATE, IO_RATE; много 'D' у postgres = ожидание I/O
sudo iotop-c -oPa       # накопительно: кто больше всех пишет/читает
top -H -p $(head -1 /var/lib/postgresql/14/main/postmaster.pid)   # нити postmaster

1.4 Метрики на стороне PostgreSQL (самое информативное)

-- (a) Выборка wait events раз в 1 секунду в течение 5 минут (psql: \watch 1).
--     Показывает, ЧЕГО ждут бэкенды: IO / LWLock / Lock / Client / CPU (NULL).
SELECT coalesce(wait_event_type,'CPU') wt, coalesce(wait_event,'-') we, count(*)
FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1,2 ORDER BY 3 DESC LIMIT 15;

-- (b) Сколько из 5000 подключений реально работают
SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;

-- (c) Checkpoint'ы: requested >> timed означает, что max_wal_size мал
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync
FROM pg_stat_bgwriter;

-- (d) Hit ratio и temp-файлы
SELECT datname, blks_hit*100.0/nullif(blks_hit+blks_read,0) AS hit_pct,
       temp_files, pg_size_pretty(temp_bytes) temp, deadlocks, blk_read_time, blk_write_time
FROM pg_stat_database WHERE datname NOT LIKE 'template%';

-- (e) Размер БД против RAM
SELECT pg_size_pretty(sum(pg_database_size(datname))) FROM pg_database;

-- (f) Топ запросов по суммарному времени (после включения pg_stat_statements)
SELECT queryid, calls, round(total_exec_time) total_ms, round(mean_exec_time,1) mean_ms,
       round(stddev_exec_time,1) sd_ms, shared_blks_read, temp_blks_written,
       round(blk_read_time) io_read_ms, left(query,80)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- (g) Текущие параметры
SELECT name, setting, unit, source FROM pg_settings
WHERE name IN ('max_connections','shared_buffers','work_mem','maintenance_work_mem',
 'effective_cache_size','max_wal_size','checkpoint_timeout','checkpoint_completion_target',
 'wal_buffers','huge_pages','random_page_cost','effective_io_concurrency','jit',
 'autovacuum_max_workers','autovacuum_vacuum_cost_limit','synchronous_commit','temp_tablespaces');

Шаг 2. Локализация узкого места

2.1 Матрица USE для этого сервера

2.2 Как отличить одно от другого

                     load avg высокий
                            │
          vmstat: r (runnable) против b (blocked)
            ┌───────────────┴────────────────┐
     r ≫ ядер, b мал                    b велик, r < ядер
     %usr+%sys > 80%                    %iowait высокий
     PSI cpu some > 20%                 PSI io full > 10%
            │                                 │
     → CPU-bound                        → I/O-bound ──┐
       (perf top, pg wait=NULL,                        │
        много LWLock при 5000 conn)         iostat: w_await ≫ r_await?
                                              ├─ да → запись: checkpoint, writeback,
                                              │        WAL fsync, mdadm bitmap
                                              └─ нет → чтение: кэш не вмещает рабочий набор
                                                       или его вытеснили (память!)

     sar -B pgscand > 0, majflt растёт,       → нехватка памяти: reclaim давит на I/O
     Committed_AS → CommitLimit, OOM в логе     (порочный круг: память → чтения с диска)

     ss -s TIME-WAIT растёт, но Recv-Q = 0,   → это симптом модели подключений,
     retrans < 0.1%, полоса < 50%               а НЕ сетевой лимит

Интерпретация текущих цифр:

1. iowait 25–40% при load 32 — классический I/O-bound. Если в vmstat колонка b окажется 15–30, а r меньше 20, гипотеза подтверждена.
2. await 45 мс на NVMe — сразу проверить r_await и w_await по отдельности, а также на md0 против nvme0n1/nvme1n1:
   • высокий await на md0 при низком на nvme* означает накладные расходы mdadm (bitmap, resync);
   • пилообразные всплески w_await, совпадающие с checkpoint в логе PG, означают checkpoint или writeback-шторм;
   • высокий r_await при большом r/s означает вытеснение рабочего набора из кэша.
3. OOM при полном page cache. Ядро не может превратить кэш в анонимную память быстрее, чем её запрашивают тысячи бэкендов, либо упирается в лимит (overcommit, cgroup). Грубая оценка: 5000 × (~5–10 MB собственной памяти + work_mem × 2–3 узла) при work_mem = 16MB даёт ~200–300 GB в худшем случае, то есть больше физической RAM. Когда OOM-killer убивает бэкенд, postmaster перезапускает все подключения и проходит crash recovery. Это прямой удар и по p99, и по SLA 99.9%.
4. TIME_WAIT. Если он накапливается на серверном порту 5432, значит сервер (PG) активно закрывает соединения: клиенты подключаются и отключаются на каждый запрос или отваливаются по таймауту. Лечится пулингом, а не sysctl.

2.3 Что обязательно собрать до изменений

# Быстрые проверки по пунктам 8–12
findmnt -no FSTYPE,OPTIONS /var/lib/postgresql
sudo mdadm --detail /dev/md0 | grep -iE "state|bitmap|resync|check"
cat /sys/block/md0/md/sync_action                      # idle — хорошо; check/resync — фоновая нагрузка
sudo nvme smart-log /dev/nvme0n1 | grep -iE "warning|temperature|percentage_used|media_errors"
systemctl list-timers --all; ls /etc/cron.d; crontab -l -u postgres
systemctl show postgresql@14-main -p MemoryMax -p OOMScoreAdjust
grep -E "Committed_AS|CommitLimit|HugePages_|AnonHugePages" /proc/meminfo
cat /proc/$(head -1 /var/lib/postgresql/14/main/postmaster.pid)/oom_score_adj

Шаг 3. Оптимизации

Порядок важен: сначала устраняем причину (модель подключений и память), затем давление на I/O, в конце — второстепенное (CPU, сеть).

3.1 Пул подключений: PgBouncer (главная мера)

Эффект: 5000 клиентских соединений превращаются в 150–250 серверных. Результат:

• на порядок меньше анонимной памяти, OOM уходит;
• освободившаяся RAM возвращается в кэш, чтений с диска становится меньше;
• меньше переключений контекста и конкуренции за LWLock;
• соединения PgBouncer → PG через unix-сокет, поэтому TCP и TIME_WAIT между ними нет;
• клиентские TCP-сессии становятся долгоживущими.

# Нужен PgBouncer >= 1.21 (поддержка prepared statements в transaction mode).
# В репозитории Ubuntu 22.04 версия 1.16, поэтому ставим из PGDG.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt jammy-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg
sudo apt update && sudo apt install -y pgbouncer

; /etc/pgbouncer/pgbouncer.ini
[databases]
; к PG через unix-сокет: без TCP, без TIME_WAIT
shop = host=/var/run/postgresql port=5432 dbname=shop

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1                 ; позволяет поднять 2–4 инстанса на одном порту (pgbouncer однопоточный)
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          ; ключевой режим для OLTP
max_client_conn = 6000
default_pool_size = 150          ; на пару (db,user); ориентир 2–4 × физ. ядер, подбирать по wait events
min_pool_size = 50
reserve_pool_size = 20
reserve_pool_timeout = 2
max_db_connections = 250         ; жёсткий потолок к PG
max_prepared_statements = 200    ; 1.21+: prepared statements в transaction mode
server_idle_timeout = 300
query_wait_timeout = 10          ; клиент не висит бесконечно в очереди
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 6
admin_users = pgbouncer_admin
stats_users = monitoring

# Лимит файловых дескрипторов для pgbouncer
sudo systemctl edit pgbouncer
# [Service]
# LimitNOFILE=65536

# Несколько инстансов через so_reuseport: шаблонный юнит pgbouncer@{1..3} с одним конфигом
# (при необходимости, если один процесс упирается в 100% одного ядра — видно в top)

⚠️ Совместимость transaction mode. Ломаются: SET на уровне сессии (использовать SET LOCAL или параметры роли), advisory locks уровня сессии, LISTEN/NOTIFY, временные таблицы, живущие дольше транзакции, WITH HOLD курсоры. Эти вещи нужно проверить в коде приложения. Для несовместимых клиентов заведите отдельный пул с pool_mode = session.

Внедрение без простоя: PgBouncer поднимается на порту 6432 рядом с PG, приложения переключаются поэтапно (канарейкой: 10% → 50% → 100%). Снижение max_connections выполняется только после полной миграции, в окне обслуживания (требует рестарта PG).

3.2 Память: PostgreSQL, OOM-защита, huge pages, THP, NUMA

3.2.1 Параметры памяти PostgreSQL (после внедрения пула)

# /etc/postgresql/14/main/conf.d/10-memory.conf
max_connections = 400              # было ~5000+; RESTART
shared_buffers = 64GB              # ~25% RAM; RESTART
huge_pages = try                   # в первом окне try, после проверки on; RESTART
effective_cache_size = 176GB       # подсказка планировщику, память не выделяет
work_mem = 32MB                    # безопасно при ~250 активных: 250 × 32MB × ~3 ≈ 24GB
maintenance_work_mem = 2GB
autovacuum_work_mem = 1GB
wal_buffers = 64MB                 # RESTART

Отдельные «тяжёлые» роли (отчёты) получают свой work_mem, а не глобальный:

ALTER ROLE reporting SET work_mem = '256MB';

3.2.2 Защита postmaster от OOM-killer

# Проверить текущее значение (pg_ctlcluster в Debian/Ubuntu может уже выставлять -900)
cat /proc/$(head -1 /var/lib/postgresql/14/main/postmaster.pid)/oom_score_adj

# Если не выставлено:
sudo systemctl edit postgresql@14-main
# [Service]
# OOMScoreAdjust=-900

# Дочерние бэкенды возвращают себе 0, чтобы в крайнем случае убивался бэкенд, а не postmaster.
# /etc/postgresql/14/main/environment
#   PG_OOM_ADJUST_FILE = '/proc/self/oom_score_adj'
#   PG_OOM_ADJUST_VALUE = '0'

3.2.3 Huge pages для shared_buffers

Эффект: таблицы страниц для 64 GB shared memory × сотни процессов сжимаются с гигабайт до мегабайт. Меньше TLB-промахов, меньше PageTables в /proc/meminfo. При 5000 процессах PageTables сейчас может занимать десятки GB — проверьте.

grep PageTables /proc/meminfo     # до изменений

# Расчёт для PG14 (shared_memory_size_in_huge_pages появился только в PG15):
# после рестарта с новым shared_buffers и huge_pages=try
PID=$(head -1 /var/lib/postgresql/14/main/postmaster.pid)
grep ^VmPeak /proc/$PID/status          # например 70000000 kB
grep Hugepagesize /proc/meminfo          # 2048 kB
# nr_hugepages = VmPeak / 2048 + ~2% запас, например 70000000/2048 ≈ 34180 → 34900

# /etc/sysctl.d/60-hugepages.conf
vm.nr_hugepages = 34900
vm.hugetlb_shm_group = <gid группы postgres>   # getent group postgres

⚠️ Выделение ~68 GB huge pages «на лету» может не получиться из-за фрагментации памяти (HugePages_Total окажется меньше запрошенного). Надёжнее выделять при загрузке (sysctl.d применяется рано) или сразу после echo 3 > /proc/sys/vm/drop_caches в окне обслуживания. Поэтому в первом окне ставим huge_pages = try, а on — только после подтверждения.

3.2.4 THP, swappiness, overcommit, NUMA

# THP: для PG рекомендуется never (Ubuntu по умолчанию ставит madvise — это приемлемо, always — нет)
cat /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defrag
# Постоянно: параметр ядра transparent_hugepage=never (см. 3.4), нужна перезагрузка

# /etc/sysctl.d/61-pg-memory.conf
vm.swappiness = 1                 # не выталкивать анонимную память ради кэша (swap оставить как страховку)
vm.zone_reclaim_mode = 0          # 2 сокета: не форсировать локальный reclaim, использовать кэш удалённого узла
kernel.numa_balancing = 0         # миграция страниц NUMA даёт латентность на shared memory PG
vm.min_free_kbytes = 1048576      # 1 GB резерва: меньше direct reclaim и сбоев атомарных аллокаций (сеть)
kernel.sched_autogroup_enabled = 0

# Overcommit — ГЛУБОКОЕ изменение, вводится отдельно и только после расчёта:
# vm.overcommit_memory = 2
# vm.overcommit_ratio = <N>
# CommitLimit = Swap + (MemTotal − HugePages_Total×2MB) × ratio/100
# Пример: (256GB − 68GB) × 90% ≈ 169GB + swap. Условие: в пик Committed_AS
# должен оставаться хотя бы на 20% ниже CommitLimit, иначе PG получит ENOMEM на fork.

На ядре 5.15 kernel.sched_migration_cost_ns перенесён в debugfs, поэтому через sysctl его не выставить. Старые руководства устарели.

3.3 Диск и I/O

3.3.1 Writeback грязных страниц

По умолчанию dirty_background_ratio = 10, dirty_ratio = 20 — от доступной памяти. На 256 GB это ~25/50 GB грязных страниц, которые затем сбрасываются лавиной при fsync на checkpoint. Отсюда пики w_await до десятков мс.

# /etc/sysctl.d/62-pg-io.conf
vm.dirty_background_bytes = 536870912    # 512 MB: фоновый сброс начинается рано и идёт ровно
vm.dirty_bytes = 2147483648              # 2 GB: потолок, после которого пишущий процесс блокируется
vm.dirty_expire_centisecs = 1000         # 10 с (по умолчанию 30)
vm.dirty_writeback_centisecs = 100       # будить flusher раз в 1 с
# Примечание: установка *_bytes автоматически обнуляет *_ratio

3.3.2 Checkpoint, WAL, bgwriter (без рестарта)

ALTER SYSTEM SET max_wal_size = '64GB';            -- меньше вынужденных checkpoint (checkpoints_req → 0)
ALTER SYSTEM SET min_wal_size = '8GB';
ALTER SYSTEM SET checkpoint_timeout = '15min';     -- реже full page writes, меньше WAL
ALTER SYSTEM SET checkpoint_completion_target = 0.9;  -- размазать запись на 90% интервала
ALTER SYSTEM SET wal_compression = on;             -- PG14: pglz; сжимает FPW, меньше записи WAL ценой CPU (есть запас)
ALTER SYSTEM SET bgwriter_delay = '50ms';
ALTER SYSTEM SET bgwriter_lru_maxpages = 1000;     -- бэкенды реже пишут грязные буферы сами
ALTER SYSTEM SET bgwriter_lru_multiplier = 4.0;
ALTER SYSTEM SET random_page_cost = 1.1;           -- NVMe: планировщик охотнее выбирает index scan
ALTER SYSTEM SET effective_io_concurrency = 256;   -- prefetch для bitmap heap scan
ALTER SYSTEM SET maintenance_io_concurrency = 256;
ALTER SYSTEM SET jit = off;                        -- в OLTP JIT часто добавляет 10–100+ мс к «средним» запросам
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';  -- висящие транзакции держат снапшоты и мешают vacuum
SELECT pg_reload_conf();

Место на диске: при max_wal_size = 64GB в pg_wal на 1.9 TB-массиве должно быть свободно не меньше 100 GB. Проверьте это до изменения.

3.3.3 Autovacuum: быстрее, но ровнее

Раздутые таблицы и индексы — это лишние чтения. Задача — чтобы autovacuum успевал, но не давал всплесков.

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;   -- по умолчанию 200 (для NVMe очень мало)
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
ALTER SYSTEM SET autovacuum_naptime = '15s';
SELECT pg_reload_conf();
-- autovacuum_max_workers = 6 требует RESTART (добавить в conf.d в окне)

-- Для горячих таблиц заказов — индивидуально:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

REINDEX CONCURRENTLY для раздутых индексов схему не меняет. Проверить раздутость можно через расширение pgstattuple.

3.3.4 Блочный уровень: планировщик, readahead, ФС

# NVMe: планировщик none (в Ubuntu 22.04 обычно уже стоит). HDD архива: mq-deadline.
# /etc/udev/rules.d/60-io-scheduler.rules
ACTION=="add|change", KERNEL=="nvme[0-9]*n[0-9]*", ATTR{queue/scheduler}="none", ATTR{queue/rq_affinity}="2"
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/rotational}=="1", ATTR{queue/scheduler}="mq-deadline"
# md-устройства — без планировщика (bio-based); readahead для OLTP 8K random умеренный:
ACTION=="add|change", KERNEL=="md0", ATTR{bdi/read_ahead_kb}="128"

sudo udevadm control --reload && sudo udevadm trigger --subsystem-match=block
blockdev --getra /dev/md0      # в секторах по 512 байт: 256 = 128 KB

# ФС: убрать синхронный discard, включить noatime. Барьеры НЕ отключать (nobarrier = риск потери данных).
# /etc/fstab (пример для ext4; для XFS — аналогично без discard)
# UUID=...  /var/lib/postgresql  ext4  defaults,noatime  0 2
sudo mount -o remount,noatime /var/lib/postgresql     # без простоя
# Если в опциях есть discard — убрать из fstab (вступит при следующем монтировании)
# и перейти на периодический TRIM вне пика:
sudo systemctl enable --now fstrim.timer   # по умолчанию раз в неделю; проверить, что не попадает на 18–23

3.3.5 mdadm RAID1

# 1) Проверка/resync не должны идти в пик
cat /sys/block/md0/md/sync_action
systemctl list-timers | grep -i mdcheck       # Ubuntu 22.04: mdcheck_start/continue timers
# ограничить скорость фоновых проверок, чтобы не забирать полосу у PG
echo 50000 | sudo tee /proc/sys/dev/raid/speed_limit_max     # KB/s (постоянно — через sysctl.d: dev.raid.speed_limit_max)

# 2) Write-intent bitmap: мелкий chunk = лишняя синхронная запись на каждый случайный write.
sudo mdadm --detail /dev/md0 | grep -i bitmap
sudo mdadm -X /dev/nvme0n1p1 2>/dev/null | grep -i chunk     # текущий размер chunk

# ГЛУБОКОЕ изменение (выполнять в окне, при полном бэкапе и массиве в состоянии clean):
# sudo mdadm --grow /dev/md0 --bitmap=none
# sudo mdadm --grow /dev/md0 --bitmap=internal --bitmap-chunk=128M
# Эффект: реже обновления bitmap при случайной записи → ниже w_await.
# Цена: после аварийного отключения resync будет чуть дольше.

3.3.6 Изоляция от архивного массива и фоновых задач

• temp_tablespaces и pg_wal не должны лежать на SAS HDD RAID10 (задержки ×10–50). Проверьте через \db+ и ls -l $PGDATA/pg_wal.
• Бэкапы (pg_basebackup, pg_dump) и archive_command, если они запускаются в 18–23 ч, нужно перенести или ограничить: ionice -c3, pg_basebackup --max-rate.
• Выборочный synchronous_commit = off допустим только для некритичных записей (сессии, корзины, аналитика) через SET LOCAL или параметры отдельной роли. Для заказов оставить on.

3.4 CPU (вторично, но дёшево)

# Частотный governor: performance вместо powersave/schedutil
cpupower frequency-info | grep -E "driver|governor"
sudo cpupower frequency-set -g performance
# постоянно:
echo 'GOVERNOR="performance"' | sudo tee /etc/default/cpufrequtils   # либо systemd-юнит с cpupower

# Глубокие C-states добавляют десятки–сотни мкс на пробуждение ядра.
# Вариант без перезагрузки: tuned с профилем latency-performance (держит /dev/cpu_dma_latency)
sudo apt install -y tuned && sudo tuned-adm profile latency-performance
tuned-adm active

# Вариант через параметры ядра (ТРЕБУЕТ ПЕРЕЗАГРУЗКИ) — вместе с THP:
# /etc/default/grub
# GRUB_CMDLINE_LINUX_DEFAULT="... transparent_hugepage=never intel_idle.max_cstate=1 processor.max_cstate=1"
# sudo update-grub

В BIOS/iDRAC/iLO нужно выставить профиль энергопотребления «Performance» (это тоже требует перезагрузки).

3.5 Сетевой стек

Сеть не узкое место, поэтому меры здесь скорее гигиенические.

# /etc/sysctl.d/63-pg-net.conf
net.core.somaxconn = 4096               # backlog listen-сокета PgBouncer/PG
net.ipv4.tcp_max_syn_backlog = 8192
net.core.netdev_max_backlog = 16384     # очередь при всплесках softirq
net.ipv4.tcp_tw_reuse = 1               # влияет ТОЛЬКО на исходящие соединения (актуально, если PG/pgbouncer сам ходит наружу)
net.ipv4.ip_local_port_range = 10240 65535
net.ipv4.tcp_keepalive_time = 60        # быстрее находить мёртвых клиентов вместо висящих бэкендов
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 6
net.ipv4.tcp_slow_start_after_idle = 0

Мифы, которые не нужно применять: tcp_fin_timeout не сокращает TIME_WAIT (он управляет FIN_WAIT_2; TIME_WAIT в Linux жёстко 60 с). tcp_tw_recycle удалён из ядра ещё в 4.12. Серверный TIME_WAIT лечится долгоживущими соединениями через пулер.

# Bonding: равномерное распределение потоков по слейвам — проверить в /proc/net/bonding/bond0
# /etc/netplan/01-bond.yaml (фрагмент)
bonds:
  bond0:
    interfaces: [eno1, eno2]
    parameters:
      mode: 802.3ad
      lacp-rate: fast
      transmit-hash-policy: layer3+4   # по умолчанию layer2: весь трафик до одного шлюза уходит в один линк
      mii-monitor-interval: 100
# Хеширование layer3+4 нужно настроить и на стороне коммутатора.
# Применять через `netplan try` (автооткат через 120 с при потере связи).

# Проверка перекоса и потерь
sar -n DEV 1 10 | grep -E "eno|bond"                 # трафик по слейвам
ethtool -S eno1 | grep -iE "drop|miss|err"
ethtool -g eno1                                      # увеличить RX-ring при rx_missed: ethtool -G eno1 rx 4096
cat /proc/net/softnet_stat                           # 2-я колонка (drops) и 3-я (time_squeeze) должны быть ~0
systemctl status irqbalance

3.6 Сводный postgresql.conf для окна обслуживания (параметры, требующие рестарта)

# /etc/postgresql/14/main/conf.d/20-restart.conf — применяется в окне
max_connections = 400
shared_buffers = 64GB
huge_pages = try                         # → on после проверки HugePages_Rsvd
wal_buffers = 64MB
autovacuum_max_workers = 6
shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = off           # on даёт оверхед; включать точечно
max_parallel_workers_per_gather = 2      # OLTP: не давать одному запросу съедать ядра

Шаг 4. Проверка результата

4.1 Принципы

• Где тестировать. Синтетику (fio, stress-ng) — на стенде, восстановленном из бэкапа на аналогичном железе. На продуктиве fio запускается только в окне и только в файл на ФС, никогда в сырое /dev/md0.
• Как сравнивать. Одинаковые дни недели, одинаковый трафик (заказы в час), по возможности по одному изменению или одной группе изменений на итерацию.
• Откуда брать p99. pg_stat_statements даёт только mean и stddev, перцентилей там нет. p99 измеряется на стороне клиента (APM или гистограммы Prometheus в приложении) либо в pgbench (--log, -P).

4.2 Синтетика дисковой подсистемы (fio)

# Стенд или окно обслуживания; каталог на той же ФС, что и PGDATA
mkdir -p /var/lib/postgresql/fio && cd /var/lib/postgresql/fio

# (a) Профиль OLTP: 8K случайные чтение/запись 70/30, прямой I/O
fio --name=oltp --filename=fio.dat --size=32G --direct=1 --ioengine=libaio \
    --rw=randrw --rwmixread=70 --bs=8k --iodepth=32 --numjobs=8 --group_reporting \
    --time_based --runtime=120 --percentile_list=50:99:99.9 --output-format=json > fio_oltp.json

# (b) Профиль WAL: последовательная запись 8K с fdatasync после каждой — латентность коммита
fio --name=wal --filename=wal.dat --size=4G --rw=write --bs=8k --ioengine=sync \
    --fdatasync=1 --numjobs=1 --time_based --runtime=60 --percentile_list=50:99:99.9

# (c) Утилита PG: сколько fsync/с выдерживает устройство
/usr/lib/postgresql/14/bin/pg_test_fsync -f /var/lib/postgresql/fio/pgtf.dat -s 5

rm -rf /var/lib/postgresql/fio

Сравнить clat p99 до и после изменений mdadm, writeback и ФС. Для NVMe RAID1 ориентир: wal p99 < 1 мс, oltp p99 < 2–3 мс при разумной глубине очереди.

4.3 Нагрузка, приближенная к реальной (pgbench)

# Сценарий из топ-запросов pg_stat_statements (пример: карточка товара + оформление заказа)
cat > /tmp/shop.sql <<'EOF'
\set pid random(1, 2000000)
\set uid random(1, 5000000)
BEGIN;
SELECT * FROM products WHERE id = :pid;
SELECT * FROM stock WHERE product_id = :pid;
INSERT INTO orders(user_id, product_id, created_at) VALUES (:uid, :pid, now());
COMMIT;
EOF

# 5000 клиентов через PgBouncer (порт 6432) против прямого подключения (5432)
pgbench -h db -p 6432 -U app -n -f /tmp/shop.sql -c 5000 -j 64 -T 900 -P 10 \
        --log --log-prefix=after --sampling-rate=0.1 -M prepared shop

# Перцентили из логов pgbench (3-я колонка — латентность в мкс)
cat after.* | awk '{print $3}' | sort -n | \
  awk '{a[NR]=$1} END{print "p50="a[int(NR*.5)]/1000"ms p99="a[int(NR*.99)]/1000"ms"}'

Лучший вариант — воспроизведение реального трафика: утилита pgreplay по логу с log_min_duration_statement = 0, снятому за 15–30 минут пика (лог объёмный, собирать на короткий интервал).

4.4 Проверка защиты от OOM (только на стенде)

# Имитация давления памяти: убедиться, что OOM-killer выбирает не postmaster
stress-ng --vm 8 --vm-bytes 90% --vm-keep --timeout 120s &
watch -n1 'grep -E "MemAvailable|Committed_AS|CommitLimit" /proc/meminfo'
journalctl -k -f | grep -i oom
# Ожидание: убит stress-ng; postmaster жив; в логе PG нет "terminating any other active server processes"

4.5 Таблица сравнения «до/после» (заполнить)

Критерий приёмки: p99 ≤ 150 мс держится 5 рабочих дней подряд в окне 18:00–23:00 без OOM и без регрессии TPS.

Шаг 5. Риски, постоянство, откат

5.1 Что требует рестарта или перезагрузки

5.2 Основные риски и их снижение

1. Снижение max_connections до полного перехода на PgBouncer приведёт к отказам в подключении. Порядок строго такой: пулер → миграция 100% клиентов → неделя наблюдения → снижение лимита.
2. Transaction mode ломает сессионную семантику. До переключения провести аудит кода на SET, advisory locks, LISTEN, temp tables и прогнать регрессионные тесты приложения через PgBouncer на стенде.
3. huge_pages = on при нехватке страниц — PG не стартует и простой выходит за 15 минут. Поэтому в первом окне try, проверка HugePages_Rsvd/HugePages_Free, и только потом on.
4. overcommit_memory = 2 при неверном расчёте — fork бэкендов и autovacuum падают с ENOMEM. Вводить отдельным шагом после двух недель мониторинга Committed_AS.
5. Изменение mdadm bitmap — операция с метаданными массива. Выполнять только при состоянии clean, свежем проверенном бэкапе и в окне.
6. Рост max_wal_size — после сбоя восстановление дольше (до десятков GB WAL на NVMe — порядка 1–3 минут), плюс нужно место под WAL. Учесть в бюджете SLA.
7. Рестарт с 64 GB shared_buffers и холодный кэш — первые 10–20 минут после старта латентность выше. Рестартовать только в ночном окне; опционально прогреть кэш через pg_prewarm (расширение, autoprewarm через shared_preload_libraries).
8. Длинный shutdown checkpoint — простой может превысить лимит. Перед рестартом вручную выполнить CHECKPOINT;, тогда shutdown займёт секунды.

5.3 Процедура в окне обслуживания (сб → вс, 02:00–04:00, простой ≤ 15 мин)

# --- 02:00 подготовка (без простоя) ---
sudo etckeeper commit "before pg tuning window" || sudo tar czf /root/etc-$(date +%F).tgz /etc
sudo -u postgres psql -c "SELECT pg_switch_wal();" -c "CHECKPOINT;"   # укоротить shutdown checkpoint
sync; echo 3 | sudo tee /proc/sys/vm/drop_caches                      # снизить фрагментацию перед выделением huge pages
sudo sysctl -p /etc/sysctl.d/60-hugepages.conf
grep HugePages_Total /proc/meminfo          # убедиться, что выделено ≈ nr_hugepages

# --- 02:15 простой начинается ---
sudo -u postgres psql -c "CHECKPOINT;"
sudo cp /root/staging/20-restart.conf /etc/postgresql/14/main/conf.d/
sudo systemctl restart postgresql@14-main                             # обычно 30–90 с
# --- простой заканчивается ---

# --- проверка ---
sudo -u postgres psql -c "SHOW shared_buffers" -c "SHOW huge_pages" -c "SHOW max_connections"
grep -E "HugePages_(Total|Free|Rsvd)" /proc/meminfo                   # Rsvd > 0 — huge pages используются
tail -50 /var/log/postgresql/postgresql-14-main.log

# --- откат (если что-то не так; укладывается в 5 мин) ---
# sudo mv /etc/postgresql/14/main/conf.d/20-restart.conf{,.disabled}
# sudo systemctl restart postgresql@14-main

Перезагрузку ядра (GRUB: THP, C-states) и изменение mdadm bitmap лучше вынести в отдельное окно, чтобы не суммировать риски и простой в одну ночь.

Сводная таблица

Чек-лист приоритетных действий

🟢 Быстрые победы (без простоя, в рабочее время, по одной группе в день с наблюдением в пик)

• Установить sysstat и bcc, запустить baseline.sh в пик 3 дня; собрать данные из раздела 2.3
• Выборка wait events и pg_stat_activity по состояниям — подтвердить гипотезу (IO + idle-подключения)
• Найти процесс, убиваемый OOM; включить OOMScoreAdjust для postmaster
• Проверить /proc/mdstat, cron и таймеры на 18–23 ч; убрать бэкапы и проверки из пика
• vm.dirty_background_bytes / vm.dirty_bytes, swappiness = 1, zone_reclaim_mode = 0, numa_balancing = 0
• PG reload: max_wal_size, checkpoint_timeout, checkpoint_completion_target, wal_compression, bgwriter, random_page_cost, effective_io_concurrency, jit = off, autovacuum cost, логирование
• THP → never (runtime), governor → performance / tuned latency-performance
• noatime через remount; проверить отсутствие discard, включить fstrim.timer
• Сетевые sysctl (backlog, keepalive); проверить хеширование bond
• Развернуть PgBouncer ≥ 1.21 на 6432 и перевести приложения канарейкой (10 → 50 → 100%)

🟡 Окно №1 (сб 02:00–04:00, рестарт PG ≈ 1–3 мин)

• После 100% миграции на PgBouncer: max_connections = 400
• shared_buffers = 64GB, wal_buffers = 64MB, vm.nr_hugepages, huge_pages = try
• shared_preload_libraries = pg_stat_statements, auto_explain; autovacuum_max_workers = 6
• Проверить HugePages_Rsvd и логи; неделя наблюдения → в следующем окне huge_pages = on

🔴 Глубокие изменения (отдельные окна, после анализа результатов)

• GRUB: transparent_hugepage=never, intel_idle.max_cstate=1 (перезагрузка 5–10 мин); профиль BIOS — Performance
• mdadm: увеличить bitmap-chunk до 128M (при clean-массиве и свежем бэкапе)
• vm.overcommit_memory = 2 с рассчитанным overcommit_ratio — после двух недель мониторинга Committed_AS
• Работа с топом pg_stat_statements: ANALYZE и default_statistics_target для перекошенных колонок, REINDEX CONCURRENTLY раздутых индексов, выборочный synchronous_commit = off для некритичных записей
• Согласовать с владельцами: допустим ли CREATE INDEX CONCURRENTLY в рамках «без изменения схемы» — это может понадобиться, чтобы дойти до p99 150 мс, если причина в самих запросах

Итог. С высокой вероятностью основной выигрыш дадут три меры: пулинг подключений, укрощение checkpoint и writeback и huge pages вместе с корректным распределением памяти. Они снимают OOM, iowait и TIME_WAIT одновременно. Параметры в postgresql.conf и sysctl — это стартовые значения. Их нужно уточнить по фактическим данным из раздела 2.3, прежде всего по размеру БД, текущему конфигу и распределению wait events.