Тюнинг некоторых параметров PostgreSQL
В процессе настройи автовакуума на postgres на работе я накидал небольшую документацию по этому процессу. Т.к. в рунете что-то не густо такого материала, я решил опубликовать и тут, да и себе на будущее тоже пригодится.
Будем считать, что вы уже прочитали родную документацию PostgreSQL, но не совсем её раздупляете, поэтому я так сказать простым языком кое-что поясню.
Допустим, вы уже знаете про pgtune и пользуетесь этой утилитой. Если нет, то советую ознакомиться с ней (если конечно вы не крутой DBA, который и так всё в состоянии накрутить).
Тюнинг параметров
Для начала вообще почитайте про сам postgres: http://www.slideshare.net/FedericoCampoli/postgresql-dba-01
И wiki на предмет тюнинга: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Логирование
log_line_prefix = '%t:%r:%u@%d:[%p]: '
Включаем логирвоание временных файлов:
log_temp_files = 0
Если в логе видим использование временных файлов, значит надо увеличить work_mem.
Тюнинг write performance
Если идёт много транзакций (особенно updates), имеет смысл поднять кол-во checkpoint_segments и увеличить интервал между чекпойнтами. Т.о. сброс dirty-буферов на диск будет происходить реже и более размазаннее по времени. Так же, pgtune всегда выставляет checkpoint_completion_target = 0.9, это оптимально.
Тюнинг autovacuum_freeze_max_age
Отличная статья про проблему с wraparound-ом транзакций http://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
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 "мягким" вакуумом, но более глубоко:
vacuum_cost_delay = 50 # soft vacuum
vacuum_freeze_table_age = 50% от autovacuum_freeze_max_age
vacuum_freeze_min_age = 10% от обычного значения
Тюнинг автовакуума для поддержки минимального 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
http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html
Мы всегда хотим, чтобы автовакуум не мешал работе "рабочих" запросов, т.е. работал в фоновом режиме с объёмом 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 до той границы, после которой начнётся снижение производительности "рабочих" запросов.
P.S. DBA приглашаются пинать меня в комментах. Я могу в чём-то тут ошибаться, не стесняйтесь меня потыкать.
