EN Обрати сервер

Облік, бази даних і віддалена робота

Сервер під PostgreSQL і MS SQL Server: диски, пам’ять, налаштування

Оновлено 10 хв читання Read in English

Зміст статті

Стаття для адміністратора, який розгортає PostgreSQL або MS SQL Server на виділеному сервері під облікову систему, сайт чи внутрішній застосунок. Ви оберете диски, пам’ять і процесор під базу та задасте початкові налаштування СУБД, які можна перевірити командами.

Що знадобиться#

  • Виділений сервер із Debian чи Ubuntu (PostgreSQL) або Windows Server (MS SQL Server) і права адміністратора.
  • Встановлена СУБД. На жовтень 2026 року актуальна основна версія PostgreSQL — 18 (версія 19 ще в бета-тестуванні), MS SQL Server — 2025. Ліцензування в статті не розглядаємо.
  • Свіжа резервна копія і вікно обслуговування: частина параметрів починає діяти лише після перезапуску СУБД.

Обладнання: диски, пам’ять, процесор#

  • Диски — NVMe у дзеркалі. СУБД записує журнал транзакцій синхронно, тому від затримки диска безпосередньо залежить швидкість проведення документів. Два диски об’єднують у RAID 1, чотири й більше — у RAID 10; RAID 0 для бази не годиться. Дзеркало не замінює резервних копій. Стан масиву перевіряйте за статтею про RAID і здоров’я дисків.
  • Пам’ять — щоб активна частина бази вміщалася в кеш. Краще обрати пам’ять ECC: вона виправляє однобітові помилки, які інакше можуть непомітно зіпсувати дані.
  • Процесор — частота ядра чи кількість ядер. Звичайний запит облікової системи виконується на одному ядрі, тож для кількох десятків користувачів найважливіша швидкість ядра. Багато ядер потрібно для сотень одночасних запитів і важкої аналітики.

Лінійки під базу даних: Турбо — максимальна швидкість одного ядра для облікових баз, але без приватної мережі (СУБД і застосунок тримайте на одному сервері або з’єднуйте через VPN); Стандарт — процесори з високою частотою або серверні AMD EPYC, на більшості моделей є приватна мережа; Бізнес — AMD EPYC та Intel Xeon 6, DDR5, NVMe; Потужність — десятки ядер і терабайти пам’яті для великих баз та аналітики. Диски, тип RAID і пам’ять кожної моделі зазначено в картці сервера в каталозі.

PostgreSQL 18: перші налаштування#

У Debian і Ubuntu конфігурація лежить у /etc/postgresql/18/main/: postgresql.conf, pg_hba.conf і каталог conf.d, який підключено наприкінці основного файлу. Власні значення тримайте в окремому файлі — так їх легко знайти і прибрати. Версію, кластер і порт покаже pg_lsclusters, параметри описано в офіційній документації.

  1. Створіть файл /etc/postgresql/18/main/conf.d/90-tuning.conf. Приклад для сервера з 64 ГБ пам’яті й дисками NVMe, на якому працює лише PostgreSQL: 
    shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
checkpoint_timeout = 15min
max_wal_size = 16GB
random_page_cost = 1.1
effective_io_concurrency = 32
max_connections = 200
  2. Перевірте файл до перезапуску — з помилкою в конфігурації PostgreSQL не запуститься: 
    sudo -u postgres psql -c "SELECT f.sourcefile, f.sourceline, f.name, f.error, s.context FROM pg_file_settings f LEFT JOIN pg_settings s ON s.name = f.name WHERE f.error IS NOT NULL;"
    Для shared_buffers і max_connections (context — postmaster) повідомлення setting could not be applied очікуване: вони чекають на перезапуск. Проте так само позначено й хибне значення, тож ці два рядки перечитайте. Будь-який інший рядок — помилка, яку треба виправити.
  3. У вікні обслуговування перезапустіть кластер (з’єднання клієнтів розірвуться): 
    sudo systemctl restart postgresql@18-main
    Якщо служба не запустилася, причину покаже sudo journalctl -u postgresql@18-main -n 50. Щоб відкотити зміни, приберіть свій файл із conf.d і запустіть службу знову.

Як обирати значення (у дужках — типові):

  • shared_buffers (128MB) — документація радить починати з 25 % пам’яті, якщо сервер віддано під базу; понад 40 % навряд чи дасть користь.
  • effective_cache_size (4GB) — пам’яті не виділяє, лише підказує планувальнику розмір кешу; на практиці 50–75 % пам’яті.
  • work_mem (4MB) — обмеження на одну операцію сортування чи хешування, а не на з’єднання: складний запит бере стільки кілька разів, і так у кожному сеансі.
  • maintenance_work_mem (64MB) — для VACUUM і створення індексів; стільки ж може взяти кожен процес автоочищення.
  • checkpoint_timeout, max_wal_size (5min, 1GB) — що рідші контрольні точки, то менше повних образів сторінок у WAL, але довше відновлення після збою і більше місця під WAL.
  • random_page_cost (4.0) — документація радить знижувати, коли дані здебільшого в кеші, але числа для SSD не називає; на NVMe зазвичай ставлять 1.1.
  • effective_io_concurrency (16) — вищі значення корисні на дисках із великою кількістю IOPS, надмірні збільшують затримки; підвищуйте поступово.
  • max_connections (100) — кожне з’єднання є окремим процесом; замість тисяч «про запас» додайте пул з’єднань, наприклад PgBouncer.

Асинхронне введення-виведення у версії 18#

У PostgreSQL 18 з’явилося асинхронне введення-виведення для читання. Ним керує io_method: worker (типово; читають фонові процеси, їхню кількість задає io_workers, типово 3), io_uring (лише Linux і збірки з підтримкою liburing) або sync; зміна потребує перезапуску. Починайте з worker, а io_uring вмикайте лише після порівняння під тестовим навантаженням.

Увага. Не вимикайте fsync і full_page_writes: запис пришвидшиться, але після збою живлення чи операційної системи база може бути непоправно пошкоджена. Не вимикайте й автоочищення (autovacuum): без нього таблиці розбухають, а статистика планувальника застаріває.

Якщо велика таблиця розбухає, знизьте поріг автоочищення саме для неї: ALTER TABLE documents SET (autovacuum_vacuum_scale_factor = 0.02); (типово 0.2).

MS SQL Server: перші налаштування#

Частину цих значень пропонує майстер встановлення — перевірте, що він записав. Рекомендації взято з Microsoft Learn.

  1. Пам’ять. Типове значення max server memory означає «без обмежень»: SQL Server поступово забере всю пам’ять і залишить без неї операційну систему, сервер застосунків і сеанси RDP. Microsoft радить близько 75 % пам’яті, не зайнятої іншими процесами.
  2. MAXDOP і поріг паралелізму. Один вузол NUMA: MAXDOP не більший за кількість логічних процесорів і не більший за 8. Кілька вузлів: не більший за кількість процесорів на вузол, а якщо їх понад 16 — половина, але не більше ніж 16. Типове значення 5 для cost threshold for parallelism Microsoft називає початковою точкою, а не рекомендацією: підвищуйте його невеликими кроками і спостерігайте повний робочий цикл. Приклад для сервера з 64 ГБ пам’яті й 16 логічними процесорами лише під СУБД (30 — початкове значення, а не норма); параметри діють без перезапуску: 
    EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'max server memory', 49152;
EXECUTE sp_configure 'max degree of parallelism', 8;
EXECUTE sp_configure 'cost threshold for parallelism', 30;
RECONFIGURE;
  3. tempdb. Файлів даних — за кількістю логічних процесорів, але не більше ніж вісім; усі з однаковим розміром і кроком зростання. Розмір задайте одразу під звичайне навантаження.
  4. Миттєва ініціалізація файлів. Надайте обліковому запису служби SQL Server право Perform volume maintenance tasks (прапорець у майстрі встановлення або secpol.msc) і перезапустіть службу: файли даних зростатимуть без заповнення нулями. Для журналу це діє лише на автоматичне збільшення до 64 МБ (SQL Server 2022 і новіші).
  5. Розміщення файлів. Якщо томів кілька, розмістіть дані, журнали і tempdb на різних томах; на одному дзеркалі NVMe досить окремих тек. Для таких томів настанови Microsoft і далі називають NTFS із кластером 64 КБ. Розмір кластера задають лише під час форматування, а воно знищує дані, тож робіть це тільки на новому порожньому томі. Перевірка: fsutil fsinfo ntfsinfo D:, у рядку Bytes Per Cluster має бути 65536.
  6. Електроживлення. Схема «Збалансована» може знижувати частоту процесора; на сервері баз даних увімкніть «Висока продуктивність» (повернути назад — powercfg /setactive SCHEME_BALANCED): 
    powercfg /getactivescheme
powercfg /setactive SCHEME_MIN
  7. Модель відновлення. Simple: журнал очищується сам, але відновитися можна лише на момент останньої копії. Full: відновлення на довільний момент часу, але тільки з регулярними копіями журналу — без них журнал зростає, доки не заповнить диск.

Якщо база — для BAS#

У клієнт-серверному варіанті BAS працює не з кожною версією і збіркою СУБД. Підтримувані версії PostgreSQL і MS SQL Server, вимоги до збірки та рекомендовані параметри беріть із документації розробника платформи і не оновлюйте СУБД до версії, якої там немає.

Не відкривайте порт СУБД в інтернет#

Порти 5432 (PostgreSQL) і 1433 (MS SQL Server) в інтернеті постійно сканують. PostgreSQL типово слухає лише localhost; якщо застосунок працює на іншому сервері, додайте адресу приватної мережі й дозвольте вхід лише з її підмережі (адреси — приклад):

# postgresql.conf
listen_addresses = 'localhost,10.10.0.2'

# pg_hba.conf
host    all    all    10.10.0.0/24    scram-sha-256

Зміна listen_addresses потребує перезапуску, для pg_hba.conf досить sudo systemctl reload postgresql@18-main. Для MS SQL Server правило брандмауера Windows для порту 1433 створюйте лише для підмережі приватної мережі або VPN. Докладніше — у статтях про приватну мережу і про WireGuard.

Як перевірити результат#

PostgreSQL: у стовпці pending_restart має бути f; одне значення покаже SHOW shared_buffers;.

sudo -u postgres psql -c "SELECT name, setting, unit, source, pending_restart FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'io_method', 'fsync', 'full_page_writes');"

Тест pgbench запускайте лише в окремій базі (ключ -i видаляє і наново створює таблиці pgbench_*) і поза робочим часом. Порівнюйте tps і latency average однакових запусків до і після змін.

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 100 bench
sudo -u postgres pgbench -c 16 -j 4 -T 60 -P 10 bench
sudo -u postgres dropdb bench

MS SQL Server — параметри, миттєва ініціалізація файлів, моделі відновлення, файли tempdb:

SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'max degree of parallelism', 'cost threshold for parallelism');
SELECT servicename, instant_file_initialization_enabled FROM sys.dm_server_services;
SELECT name, recovery_model_desc FROM sys.databases;
SELECT name, type_desc, size * 8 / 1024 AS size_mb FROM tempdb.sys.database_files;

Увага. Дискові тести fio і diskspd спрямовуйте лише у файл на вільному місці. Ніколи не вказуйте як ціль пристрій (/dev/nvme0n1, /dev/md0, номер фізичного диска): тест із записом знищить дані на ньому. Запускайте тести поза робочим часом, а тестовий файл потім видаліть.

Перша команда — для Linux (пакет fio, потрібно 4 ГБ вільного місця), друга — для Windows (утиліта DiskSpd від Microsoft, тека D:\disktest має існувати):

fio --name=dbtest --filename=/var/tmp/fio-test.bin --size=4G --rw=randrw --rwmixread=70 --bs=8k --direct=1 --ioengine=libaio --iodepth=16 --numjobs=4 --runtime=60 --time_based --group_reporting
diskspd.exe -c4G -d60 -r -w30 -b8K -t4 -o16 -Sh -L D:\disktest\test.dat

Типові помилки#

  • Задати великий work_mem, коли з’єднань сотні: пам’ять закінчиться в найгарячіший момент.
  • Змінити десять параметрів одразу і не виміряти результат.

Що далі#