Vvk Тюнінг деяких параметрів PostgreSQL

Будемо вважати, що ви вже прочитали рідну документацію PostgreSQL, але не зовсім її роздуплюєте, тому я простою мовою дещо поясню.

Допустимо, ви вже знаєте про pgtune і користуєтеся цією утилітою. Якщо ні, то раджу ознайомитися з нею (якщо звичайно ви не крутий DBA, який і так все може накрутити).

Тюнінг параметрів

Логування

Включаємо логірування тимчасових файлів:

Якщо в лозі бачимо використання тимчасових файлів, то треба збільшити work_mem.

Тюнінг write performance

Якщо йде багато транзакцій (особливо updates), має сенс підняти кількість checkpoint_segments і збільшити інтервал між чекпойнтами. Т.о. скидання dirty-буферів на диск відбуватиметься рідше і розмазаніша за часом. Також pgtune завжди виставляє checkpoint_completion_target = 0.9, це оптимально.

Тюнінг autovacuum_freeze_max_age

autovacuum_freeze_max_age за дефолтом – 10% (200 мільйонів) від максимального XID (2.1 мільярд), можна сміливо збільшувати до 50% – 1 мільярд. Правильний спосіб не допускати підходу до autovacuum_freeze_max_age – вручну робити VACUUM FREEZE коли база мало використовується.

vacuum_freeze_min_age - треба проаналізувати скільки транзакцій йде за годину, і виставити таке значення, щоб транзакцій вистачало на кілька годин. Дефолт – 100 мільйонів, досить великий, це означає, що превентивний фризинг зазвичай не відбувається. Плюс після freeze залишається багато XIDs, які близькі до 50% від дефолтного autovacuum_freeze_max_age.

vacuum_freeze_table_age: вакуум обходить лише dirty частини таблиці, решта залишається недоторканим. Тому через якийсь час це "все інше" перехеречить autovacuum, який запуститься після досягненняautovacuum_freeze_max_age. vacuum_freeze_table_age дозволяє перехерчити таблицю цілком заздалегідь. Оптимальне значення – 80% від autovacuum_freeze_max_age, тобто. 800 мільйонів.

У періоди найменшої завантаженості можна самостійно виконувати VACUUM FREEZE "м'яким" вакуумом, але глибше:

Тюнінг автовакууму для підтримки мінімального bloat

Дефолтні налаштування автовакууму розраховані на прогін ваккуму по таблиці в тому випадку, якщо було змінено/видалено 20% її tuples. Це означає, що у таблиці, у якій відбувається приблизно постійне кількість updates/inserts/deletes bloat так само триматиметься лише на рівні 20%, тобто. приблизно 20% місця на диску насправді є "непотрібним мотлохом" (старі версії rows) + ділянками порожнього місця в сторінках. Якщо взяти для прикладу нашу базу receiver та її таблицю marks, яка партиціонується по місяцях, це означає, що після місяця ця місячна таблиця так і залишається з 20% bloat-а. При цьому, якщо змінити налаштування автовакууму, вони ніяк не зможуть знизити bloat у цих старих таблицях, т.к. з ними немає активної роботи (inserts/updates). На таких таблицях треба робити VACUUM FULL або використовувати pgcompactor (recipe[postgresql::pgcompactor]).

Таким чином, виникає потреба налаштувати автовакуум таким чином, щоб він запускався частіше, при меншій кількості зроблених updates/deletes. Найголовніший параметр, який відповідає за періодичність запусків - autovacuum_vacuum_scale_factor (за дефолтом 0.2 - 20%). Відповідно, виходячи з розміру наших таблиць і кількості tuples в них, підраховуємо допустимий для нас рівень bloat. Наприклад для бази receiver та таблиць marks*: таблиця marks_YYYY_mm важить до 50GB, допустимо нам треба щоб "мотлоху" і порожнього місця в ній було не більше 1GB.1024 / (50 * 1024) = 0.02. При цьому знову ж таки треба мати на увазі, що при занадто низькому значенні автовакуум працюватиме безперервно.

Тюнінг впливу автовакууму на I/O

Ми хочемо, щоб автовакуум не заважав роботі " робочих " запитів, тобто. працював у фоновому режимі з об'ємом I/O, який не заважає. З іншого боку, якщо СУБД і так вже сильно навантажена, занадто "розслаблені" налаштування автовакууму призведуть до того, що bloat % буде вищим за очікуване. Зараз postgres (9.3) немає вбудованого в автовакуум аналізатор активності, тобто. він може працювати адаптивно навантаженню (інтенсивніше, коли навантаження маленька, і менш інтенсивно, коли велика). Тому ми змушені "на око" (проаналізувавши навантаження на IO сервері) і відповідно налаштувати cost параметри автовакууму.

  • autovacuum_max_workers = 3 - у робочих процесів
  • autovacuum_vacuum_cost_delay = 20ms - на скільки мілісекунд засинає автовакуум при досягненні cost-ліміту
  • autovacuum_vacuum_cost_limit = 200 - у деяких credits (порів них можна вважати, що є мірою вимірювання I/O операцій)
  • vacuum_cost_page_hit = 1 # 0-10000 credits - ціна за читання буфера з кешу
  • vacuum_cost_page_miss = 10 # 0-10000 credits - ціна за читання буфера з диска
  • vacuum_cost_page_dirty = 20 # 0-10000 credits - ціна за те, що в результаті роботи вакууму буфер стане dirty (потрібний його запис на диск)

Якщо у нас багато таблиць, в яких відбувається запис/видалення, має сенс збільшити кількість воркерів. Потрібно мати на увазі, що cost-и від усіх процесів автовакууму підсумовуються. Базовий параметр, який тюнім – це autovacuum_vacuum_cost_delay. Дивимося яке навантаження, тюним, дивимося. Точніше підганяємо за допомогою виставленняautovacuum_vacuum_cost_limit. Ціни за доступ до буферів так само можна потюнити - наприклад, ціну за читання з кешу виставити в 0, ціну за читання з диска трохи знизити, а за запис - підвищити.

Суть налаштування полягає в тому, щоб дати автовакууму максимально можливе I/O до того кордону, після якого почнеться зниження продуктивності робочих запитів.