Платформа WT
Практика
Синтаксис
База знаний

Настройка postgresql.conf

Небольшое предисловие:

Вы можете найти автоматические настройки конфигурации для PostgreSQL, но очень часто 1-2 тонких настройки могут решить целые дни оптимизации. Все советы ниже работают для PostgreSQL 10.5 на Windows. Настройки *nix подобных систем в некоторых местах будут другими. Для PostgreSQL 15+ некоторые настройки не нужны, а некоторые нужно считать иначе.

В этой статье перечислены 18 настроек, которые помогут оптимизировать работу базы данных на компьютере. Некоторые из них просто помогают избежать ошибок, которые создаются автогенераторами.

Информация собиралась из многих мест, но так как иногда для 1 настройки приходится пролистать с пяток статей, то и список источников информации выйдет примерно таким:

  1. https://habr.com

  2. https://postgresqlco.nf

  3. https://postgrespro.ru

  4. https://www.enterprisedb.com

  5. https://www.postgresql.org

  6. Самый опасный и оппозиционный ресурс Эрафии: https://www.youtube.com

Убедительная просьба: при работе с\покупке SSD оценивайте его качество, так как некачественные SSD на операциях кеширования(которые часто происходят при работе баз данных), теряют скорость, иногда до отношения 1 к 10.

Набор postgresql.conf для базовых настроек

Категория ждёт какого-то файлообменника

  • 4Gb RAM SSD

  • 4Gb RAM HDD

  • 8Gb RAM SSD

  • 8Gb RAM HDD

  • 16Gb RAM SSD

  • 16Gb RAM HDD

  • 32Gb RAM SSD

  • 32Gb RAM HDD

Настройки

  1. max_connections - определяет кол-во любых(в том числе и IDLE) соединений. Так как каждый DataConnection в WorkflowSystems - отдельное соединение, то обычная форма легко может занять до 20-30 соединений. Слишком больше количество соединений в этой настройке - проблема. Каждое соединение - отдельный процесс, плюс неиспользуемое соединение всё равно хранится в структуре, что отведена под управление базой данных.

Формула: {Количество пользователей} * 30 + Сonst, где Const - небольшой запас.

  1. shared_buffers - определяет объём памяти, выделенный сервером для контроля, соединений, активных операций, кеширования данных и много другого.

Формула: от 15 до 25% RAM. Приоритет в большую сторону. Не больше 8 Гб.

  1. work_mem - определяет объём памяти, который будет выделен для каждого подзапроса, в котором задействована сортировка или хеш-таблицы. Хэш-таблицы вызываются довольно часто они хороши тем, что создают пары, составленные из ключа и значения, что создаёт детерминированное время получения значения, вне зависимости от величины таблицы. Практически любой запрос с JOIN создаёт хеш-таблицу. Если же подзапрос выходит за выделенную память, то он пишется во временный файл на жестком диске. Эта настройка категорически важна.

Значение это настройки определяется как степень двойки, поэтому после расчётов рекомендуется понизить её до неё(например, если при расчёте вышло 1200 мб, можно смело опускать до 1Гб).

Тем не менее в нашей практике был случай, когда формулы ниже не подошли клиенту. Была ситуация: много маленьких запросов, которые потребляют по 5-10 мб, и 4 запроса, которые потребляют до 1Гб. И если установить согласно формулам, то выходило 16 мб, всё было замечательно, но не для этих 4 запросов, которые выводили всю систему в отказ. В итоге пришлось выставлять 1Гб на work_mem. Это спасло ситуацию, но тем не менее, все остальные запросы стали подвисать, так как оперативной памяти стало не хватать.

Формула: RAM / (2 * max_connections) Округляем до степени двойки вниз

Формула№2: (Ram * 0.9 - shared_buffers) / max_connections Округляем до степени двойки вниз

Первая формула больше подходит для баз, где больше работы с отчетами, но на малом количестве пользователей.

В любом случае, если вы в день записываете 2-3 миллиона записей в 1 из таблиц, в которой есть несколько индексов, и делаете это огромными функциями, или запрашиваете отчёты, которые в оперативной памяти занимают 1Гб, значит нужно увеличивать оперативную память сервера, брать хороший процессор на 30+ ядер или менять подход к расчётам.

  1. maintenance_work_mem - определяет объём памяти для операций обслуживания: AVTOVACUUM, VACUUM, CREATE INDEX, ALTER TABLE...

Формула: RAM * 0.1 > 1920MB ? 1920MB : RAM * 0.1.

  1. effective_cache_size - определяет объём памяти, доступной для кеширования. Параметр для планировщика запросов. Учитывается и shared_buffers и кеш самой файловой системы

Формула: RAM * 0.75

  1. random_page_cost - определяет приблизительную стоимость чтения случайной страницы с диска.

Значение для HDD = 4.0

Значение для SSD = 1.0-1.1

Чтобы проверить тип вашего жесткого диска нужно нажать ALT+R и ввести dfrgui. Вам откроется окно Дефрагментации дисков, там в типе носителя будет указано что это за диск. Если тип носителя - твердотельный, значит это SSD, если нет - HDD.

  1. cpu_index_tuple_cost и cpu_operator_cost - обе настройки, могут повлиять на выбор оптимизатором работы через индексы. Если же сервер имеет слабый процессор или индексы на базе почти не используются, то лучше оставить стандартные значения

cpu_index_tuple_cost = 0.001

cpu_operator_cost = 0.0005

  1. idle_in_transaction_session_timeout - определяет время сброса транзакций в idle режиме. Освобождение памяти, блокировок, открывает слот подключения. По умолчанию стоит 0, но неплохим вариантом для неспешных клиентов, где не пишется супер много всего постоянно и нет проблемы с кол-вом подключением будет значение 1min

idle_in_transaction_session_timeout = 1min

  1. statement_timeout - определяет время в мс, через которое запрос должен быть уничтожен. На случай зависаний. Подходят оба варианта, по умолчанию второй. Но обычно рекомендуют не делать эту настройку в конфиге, а редактировать роль(как во втором варианте), под которой всё работает, если правильно выстроена политика ролей и пользователей. Если не хочется с этим разбираться, то оставить настройку закомментированной

statement_timeout = 10 * самый длинный запрос # вариант глобальной работы

ALTER ROLE wf_sys_user SET statement_timeout = '55min'; # вариант с работой с ролями

  1. temp_file_limit - размер временного файла на диске, которое может занять транзакция. Стоит отметить, что по умолчанию значение -1, означает, что любая транзакция может занимать сколько угодно места. Как правило, если на харде хватает места, то можно так и оставить, хотя в большинстве случаев 10gb более, чем достаточно для работы транзакции. В нашей практике уже случались ситуации, когда у клиента на рабочем компе на диске с базой было мало свободного места. Это выливалось в то, что у него стабильно улетала в отказ база.

temp_file_limit = 10GB

  1. synchronous_commit - Определяет, будет ли сервер при фиксировании транзакции ждать, пока записи из WAL сохранятся на диске, прежде чем сообщить клиенту об успешном завершении операции. значение off для этого параметра не угрожает целостности данных: сбой операционной системы или базы данных может привести к потере последних транзакций, считавшихся зафиксированными, но состояние базы данных будет точно таким же, как и в случае штатного прерывания этих транзакций. Если вы можете себе позволить потерю данных(в радиусе 400 мс), и у вас в самом деле высокие нагрузки, то попробуйте поставить значение off. Но пока что это пригодилось только на тестовых базах

  2. default_statistics_target - влияет на статистику, которая используется для работы оптимизатора. Чем выше, тем дольше работа оптимизатора, но тем точнее он работает. Стандартное значение - 100, в большинстве случаев его хватает. Но как только база начала разрастаться и появились узкие места, лучше переключиться на 1000, чтобы оптимизатор тратил больше времени, но при этом выдавал нормальные планы

Значение: 100-1000

  1. Асинхронное поведение(ниже настройки для процессоров, которые стоят в WorkFlowSystems):

  • max_worker_processes = 2

  • max_parallel_workers =2

  • max_parallel_workers_per_gather = 1

Самый простой способ - отключить эти настройки.

max_parallel_workers = 0

Самый правильный способ - настроить по формулам ниже:

max_background_workers = Cores + (константа, если есть extensions для параллельных запросов)

max_parallel_workers = ( Cores )

max_parallel_workers_per_gather = ( 2 * Cores ) / ( Ожидаемое кол-во сессий )

  1. wal_buffers - определяет обём памяти, для буферизации данных журналов WAL, задаётся обычно как 3% от shared_buffers, но не более, чем размер одного сегмента WAL(стандартный размер 16 Мб). На нагруженном сервере рекомендуется поставить wal_buffers = размеру сегмента WAL

Частные случаи:

wal_buffers = = 16MB # Вариант ручной настройки для обычного сервера

wal_buffers = -1 # Доверить базе данных автоматически настраивать значение. По умолчанию

wal_buffers = 128MB # В случае, если у вашег оклиента 8 и более ядер у процессора

  1. wal_compression - определяет будет ли сжиматься страница, записываемая в журнал WAL. Этот параметр помогает сократить время записи в журналы, но накладывает нагрузку на процессор. Как правило лучше выключать. Включил в список, так как online сборщики настроек как правило включают эти настройки, что на тестах наших программ показало себя плохо. Стоит включать только в случае, если WAL почти постоянно в состояние ожидания IO потока(проверяется либо через запросы, либо через dashboard, либо через специальные утилиты типа pgMetrics) или у вас очень хороший процессор.

wal_compression = off

  1. работа с размерами WAL:

  • checkpoint_timeout

  • max_wal_size

  • min_wal_size

Три параметра выше задают максимальное время\размер до записи\ перезаписи журнала. Если вы не пишете 1 гб\час в базу и ваша база в логах не указывает, что слишком часто переписывает журналы WAL, то не стоит редактировать эти знрачения. Однако при выосокй нагрузке на базу, можно поднять размеры max_wal_size и min_wal_size до нескольких ГБ

  1. bg_writer - определяет работу фоновой записи. Если процессу потребуется вытеснить страницу из буфера, а страница окажется изменённой, ему придётся самостоятельно писать её на диск. Чтобы этого не происходило синхронно и не приходилось ждать, это происходит в фоновом режиме. Как правило хватает настроек по умолчанию. Добавлена сюда, только из-за того, что онлайн настройщики часто её добавляют.

  2. checkpoint_completion_target - Определяет целевое время для завершения процедуры контрольной точки, как коэффициент для общего времени между контрольными точками. В стандартном конфиге 10ой версии стоит 0.5, но в старших версиях перешли на 0.9, на 10ой версии это тоже работает.

Значение: 0.9

Заключение.

В целом этих настроек хватает для тонкой настройки среднего сервера без RAID, без нескольких компьютеров внутри одного кластера, без архивации и репликации.

Last updated