02.11.2023

Налаштування конфігураційного файлу Postgresql для підвищення швидкості роботи

За замовчуванням база даних Postgresql призначена для роботи на будь-якому обладнанні. Тому налаштування за замовчуванням використовують мінімум ресурсів, а це означає, що база даних Postgresql буде працювати з меншою швидкістю. Для того, щоб пришвидшити роботу бази даних на вашому обладнанні, потрібно відредагувати файл налаштувань, наприклад: /etc/postgresql/16/main/postgresql.conf

В нас віртуальна машина на 24 Гб. Як результат, по тексту надаємо параметри, які обрали.

Параметри, які потрібно змінити:

max_connections = 500..1000

Максимальна кількість сполук, рекомендуємо вказані вище значення, ми встановили 1000.

shared_buffers = RAM/4

Обсяг пам'яті для спільного кешу сторінок, що розділяється між усіма процесами Postgres, рекомендоване значення – чверть доступного обсягу пам'яті, у нашому випадку 6 ГБ.

temp_buffers = 256MB

Верхній ліміт для тимчасових таблиць у кожній сесії рекомендується фіксоване значення.

work_mem = RAM/32..64

Вказує обсяг пам'яті, який може бути використаний для запиту, перш ніж будуть залучені тимчасові файли на диску. Застосовується для кожного з'єднання і кожної операції, тому підсумковий обсяг пам'яті, що використовується, може істотно перевищувати зазначене значення. Для обсягу пам'яті 24 ГБ рекомендованими значеннями будуть 375-750 МБ, ми вибрали 512 МБ.

maintenance_work_mem = RAM/16..32 или work_mem * 4

Обсяг пам'яті для обслуговчих завдань (автовакуум, реіндексація тощо), вказуємо рекомендований калькулятором обсяг, у нашому випадку 2 ГБ.

max_files_per_process = 1000

Максимальна кількість відкритих файлів на один процес, у складанні від Postgres для Linux це значення за замовчуванням.

bgwriter_delay = 20ms
bgwriter_lru_maxpages = 400
bgwriter_lru_multiplier = 4.0

Параметри процесу фонового запису, який відповідає за синхронізацію сторінок у shared_buffers з диском.

effective_io_concurrency = 2 для RAID, 200 для SSD, 500..1000 для NVMe

Допустиме число одночасних операцій введення/виводу. Для жорстких дисків вказується кількість шпинделів, для масивів RAID5/6 слід виключити диски парності. Для SATA SSD це значення рекомендується вказувати рівним 200, а для швидких дисків NVMe його можна збільшити до 500-1000. При цьому слід розуміти, що високі значення у поєднанні з повільними дисками зроблять зворотний ефект, тому обирайте правильний підхід до цього.

Важливо! Параметр effective_io_concurrency налаштовується лише в середовищі Linux, у Windows системах його значення має дорівнювати нулю.

max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Налаштування фонових робочих процесів, що вибираються, виходячи з кількості процесорних ядер, беремо значення з калькулятора. Вище вказано налаштування для чотирьохядерного СРU.

fsync = on

Примушує сервер домагатися фізичного запису змін на диск. Вимкнення даної опції хоч і дозволяє підвищити продуктивність, але значно збільшує ризик невиправного псування даних при раптовому вимкненні живлення.

synchronous_commit = off

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

wal_buffers = 16MB

Задає розмір буферів журналу передзапису (WAL, він же журнал транзакцій), якщо залишити це налаштування без змін, то сервер автоматично встановлюватиме це значення в 1/32 від shared_buffers, але не менше 64 КБ і не більше розміру одного сегмента WAL в 16 МБ.

commit_delay = 1000
commit_siblings = 5

Вказує затримку в мс перед записом транзакцій на диск серед відкритих транзакцій, зазначених у другій опції. Має сенс при кількості транзакцій понад 1000 за секунду, на менших значеннях ефекту не має.

min_wal_size = 512MB..4G 
max_wal_size = 2..4 * min_wal_size

Мінімальний та максимальний розмір файлів журналу передзапису. Вказуємо значення з калькулятора, у нашому випадку це 4 ГБ та 16 ГБ.

checkpoint_completion_target = 0.5..0.9

Швидкість запису змін на диск, розраховується як час між точками збереження транзакцій (чекпойнти), помножений на даний показник, дозволяє розтягнути процес запису за часом і завдяки цьому знизити одномоментне навантаження на диски. У нашому випадку використано рекомендоване калькулятором максимальне значення 0,9.

seq_page_cost = 1.0

Вартість послідовного читання з диска є відносним числом, навколо якого визначаються всі інші змінні вартості, дане значення є значенням за замовчуванням.

random_page_cost = 1.5..2.0 для RAID, 1.1..1.3 для SSD

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

Оскільки це відносні значення, але не має сенсу встановлювати random_page_cost нижче seq_page_cost, проте при застосуванні продуктивних SSD є сенс знизити вартість обох значень, щоб підвищити пріоритет дискових операцій по відношенню до процесорних.

Для продуктивних SSD можна використовувати значення:

seq_page_cost = 0.5
random_page_cost = 0.5

А для NVme:

seq_page_cost = 0.1
random_page_cost = 0.1

Але ще раз нагадаємо – наведені значення не є панацеєю і повинні встановлюватися осмислено, з реальним розумінням продуктивності дискової підсистеми сервера, бо бездумне копіювання налаштувань здатне призвести до зворотного ефекту.

effective_cache_size = RAM - shared_buffers

Визначає ефективний розмір кешу, який можна використовувати при одному запиті. Цей параметр не впливає на розмір пам'яті, що виділяється, не резервує її, а служить для орієнтовної оцінки доступного розміру кеша планувальником запитів. Чим він вищий, тим більша можливість використання сканування за індексом, а не послідовного сканування. Під час розрахунку слід використовувати виділений серверу обсяг RAM, а чи не повний обсяг ОЗУ. У нашому випадку це 18 ГБ.

autovacuum = on

Увімкнення автовакууму, це дуже важливий для продуктивності бази параметр. Не вимикайте його!

autovacuum_max_workers = NCores/4..2 але не менше 4

Кількість робочих процесів автовакууму, розраховується за кількістю процесорних ядер, щонайменше 4, у разі 4.

autovacuum_naptime = 20s

Час сну процесів автовакууму, велике значення буде призводити до неефективної роботи, занадто мале тільки підвищить навантаження без видимого ефекту.

row_security = off

Відключає політику захисту на рівні рядків, ця опція не використовується платформою і її відключення дає певне підвищення продуктивності.

max_locks_per_transaction = 256 

Максимальна кількість блокувань в одній транзакції.

escape_string_warning = off
standard_conforming_strings = off

Дані опції регулюють використання символу для екранування.

Збережемо файл конфігурації та перезапустимо PostgreSQL, в Linux це можна виконати командами:

pg-setup service stop
pg-setup service start

Автор: Рудюк С.А. 2023. K2 Cloud ERP.