После установки инстанс PostgreSQL в целом НЕ настроен и это может отрицательно отразиться на производительности.
Несмотря на то, что настроек в PostgreSQL более 350, можно выделить основные:
- shared_buffers
- max_connections
- effective_cache_size
- work_mem
- maintenance_work_mem
- wal_buffers
- min_wal_size / max_wal_size
- checkpoint_timeout
- synchronous_commit
- max_worker_processes / max_parallel_workers_per_gather
- max_parallel_maintenance_workers/ max_parallel_workers
shared_buffers
Используется для кэширования данных. По умолчанию низкое значение (для поддержки как можно большего кол-ва ОС). Начать стоит с его изменения. Согласно документации, рекомендуемое значение для данного параметра – 25% от общей оперативной памяти на сервере. PostgreSQL использует 2 кэша – свой (изменяется shared_buffers) и ОС. Редко значение больше, чем 40% окажет влияние на производительность.
max_connections
Максимальное количество соединений. Для изменения данного параметра придётся перезапускать сервер. Если планируется использование PostgreSQL как DWH, то большое количество соединений не нужно. Данный параметр тесно связан с work_mem. Поэтому будьте предельно аккуратны с ним
effective_cache_size
Служит подсказкой для планировщика, сколько ОП у него в запасе. Можно определить как shared_buffers + ОП системы – ОП используемое самой ОС и другими приложениями. За счёт данного параметра планировщик может чаще использовать индексы, строить hash таблицы. Наиболее часто используемое значение 75% ОП от общей на сервере.
work_mem
Используется для сортировок, построения hash таблиц. Это позволяет выполнять данные операции в памяти, что гораздо быстрее обращения к диску. В рамках одного запроса данный параметр может быть использован несколько раз. Если ваш запрос содержит 5 операций сортировки, то память, которая может использоваться для его выполнения уже как минимум work_mem * 5. Т.к. скорее-всего на сервере вы не одны и сессий много, то каждая из них может использовать этот параметр по нескольку раз, поэтому не рекомендуется делать его слишком большим. Можно выставить небольшое значение для глобального параметра в конфиге и потом, в случае сложных запросов, менять этот параметр локально (для текущей сессии). Обратите внимание, что при превышении этого параметра будет использовано временное пространство, расположенное на диске – запросы будут выполняться медленнее и при большом запросе с декартовым произведением могут привести к опустошению пустого места на диске и завершаться с ошибкой, также могут способствовать приходу ООМ киллера в зависимости от конфигурации ОС.
maintenance_work_mem
Определяет максимальное количество ОП для операций типа VACUUM, CREATE INDEX, CREATE FOREIGN KEY. Увеличение этого параметра позволит быстрее выполнять эти операции. Не связано с work_mem поэтому можно ставить в разы больше, чем work_mem
min_wal_size и max_wal_size
Тюнинг параметров min_wal_size и max_wal_size связан с управлением журналом транзакций (Write-Ahead Log – WAL) в системе управления базами данных (СУБД) PostgreSQL. Эти параметры позволяют настроить размеры журнальных сегментов, которые используются для записи изменений в базу данных перед их фиксацией.
min_wal_size: Этот параметр задает минимальный размер журнального сегмента, до которого должен “опуститься” WAL перед переиспользованием. Если установить его слишком низко, может возникнуть увеличение количества записей (высокий I/O), так как PostgreSQL не сможет эффективно переиспользовать журнальные файлы. Рекомендуется установить его на достаточно высокое значение, чтобы уменьшить I/O операции записи, но не слишком высокое, чтобы избежать излишнего потребления места на диске.
max_wal_size: Этот параметр устанавливает максимальный размер журнального сегмента. Если установить его слишком низко, это может привести к тому, что база данных перестанет работать, когда достигнет предела размера журнала, и потребуется архивация WAL для освобождения места. Но если значение установлено слишком высоко, это может привести к тому, что вам понадобится больше места на диске.
Рекомендуется выбирать значения для min_wal_size и max_wal_size таким образом, чтобы обеспечить баланс между эффективностью записи и использованием дискового пространства, а также учитывать конкретные характеристики вашей базы данных и потребности в производительности.
synchronous_commit
Отключаем синхронную запись журнала изменений данных на диск, что позволяет увеличить скорость ответа СУБД от 10% до 3000+ % за счет нивелирования времени подтверждения такой записи каждой транзакции. Конечно, при сбое ВМ, мы можем потерять небольшую часть последних изменений.
max_worker_processes / max_parallel_workers_per_gather / max_parallel_maintenance_workers/ max_parallel_workers
Используются для распараллеливания исполнения запросов – устанавливаем в зависимости от количества ядер ВМ. https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
random_page_cost
Задаёт приблизительную стоимость чтения одной произвольной страницы с диска. Значение по умолчанию равно 4.0. У твердотельных накопителей лучше выбрать меньшее значение random_page_cost – оптимально 1.1.
checkpoint_timeout
Чем реже происходит сбрасывание грязных буферов на диск, тем дольше будет восстановление БД после сбоя. Значение по умолчанию 5 минут, рекомендуемое – от 10 минут до часа.
Необходимо “синхронизировать” два этих параметра. Для этого можно поставить checkpoint_timeout в выбранный промежуток, включить параметр log_checkpoints и по нему отследить, сколько было записано буферов. После чего подогнать параметр max_wal_size
Для первоначальной настройки можно воспользоваться http://pgconfigurator.cybertec.at/ – продвинутым конфигуратором от Cybertec.
После того как введете характеристики инстанса вам предложат конфиг, который нужно скопировать в конец файла с настройками ПГ postgresql.conf и перезапустить инстанс для применения изменений.
Добавить комментарий