Настройка памяти в PostgreSQL

Обсуждение

Я не претендую на истину, так как мнения коллег очень расходятся.

Возьмем очень условный пример для нашей среды.
Предположим. 8 ГБ, 4 ЦПУ, 300 коннектов к бд.

Если воспользоваться pgtune автор инструмента считает почти по формулам, которые описаны в офф вики постгреса.

Вот как он считает память. Рассмотрим основное.
Но тут не соблюдается условие 1гб под ОС.

max_connections = 300

work_mem = 1747kB

shared_buffers = 2GB

effective_cache_size = 6GB

maintenance_work_mem = 512MB

 

Мы задаем лимит для потсгреса в 7гб(7168MB/7340032KB) памяти в limits.conf
Теперь работает с тем, что есть.

max_connections(тут уже cколько мы планируем предполагаем)

Параметр max_connections устанавливает максимальное количество клиентов, которые могут подключиться к PostgreSQL. Поскольку для каждого клиента требуется выделять память (work_mem), то этот параметр предполагает максимально возможное использование памяти для всех клиентов. Как правило, PostgreSQL может поддерживать несколько сотен подключений, но создание нового является дорогостоящей операцией. Поэтому, если требуются тысячи подключений, то лучше использовать пул подключений (отдельная программа или библиотека для продукта, что использует базу). - PGbouncer к примеру.

work_mem

work_mem параметр определяет максимальное количество оперативной памяти, которое может выделить одна операция сортировки, агрегации и др. Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Разумное значение параметра определяется следующим образом: количество доступной оперативной памяти (после того, как из общего объема вычли память, требуемую для других приложений, и shared_buffers) делится на максимальное число одновременных запросов умноженное на среднее число операций в запросе, которые требуют памяти.

Т.к мы не знаем сколько у нас сортировок/агрегаций в одном запросе то рекумендуется в качестве начального значения для параметра можно взять 2–4% доступной памяти.

так же есть мнение от leopard

Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ

ВАЖНО!

общее количество

памяти, которое может быть выделено серверным процессам

определяется как work_mem * max_connections.

тоесть если мы берем

work_mem = 1747kB * 300 активных коннектов то получаем примерно 512МБ расход памяти на все 300 подключение в которых происходит сложный запрос.

еще пример с ruhighload.com

Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10Мб для этого параметра скушает 100Мб оперативной

мои выводы и наблюдения.

что к базе не бывает никогда такого открытого кол-ва коннектов до "потолка" , если смотреть в графану то есть минимальное значение и среднее за минуту.

обычно до 100

значит можно

начать с стандарта для веб приложений что делают другие дба в 32/64MB

при этом тоже наблюдение..

Если в логах или explain видно, что создаются временные файлы для 

для внутренних операций сортировки и хеш-таблиц

То стоит увеличить в 2-3 раза это значение. После изменения этого параметра может дать большой прирост в производительности.

у нас же он 128МБ и 1000 коннектов.

Если реально столько будет соединений то база ляжет(?)

shared_buffers

25% от памяти сервера

PostgreSQL не читает данные напрямую с диска и не пишет их сразу на диск. Данные загружаются в общий буфер сервера, находящийся в разделяемой памяти, серверные процессы читают и пишут блоки в этом буфере, а затем уже изменения сбрасываются на диск.

shared_buffers = 7168/100*25=1792MB

effective_cache_size

Этот параметр не влияет на размер разделяемой памяти, выделяемой Postgres Pro, и не задаёт размер резервируемого в ядре дискового кеша; он используется только в качестве ориентировочной оценки.

Не выделяет память, это лишь указание оптимизатору запросов о количестве оперативной памяти используемой в ОС для кэша файловой системы.

Что нашел:

1/2 стандартная настройка. Планировщик будет сам определять как ему лучше поступить.

3/4 ( более 50% )от памяти выделенной ПГ. - будут чаще использоваться индексы

effective_cache_size = 7/4*3 = 5,25 (5GB)

maintenance_work_mem

Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию его значение — 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии.

Есть мнение

от aka leopard(pgtune)

Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить не возможно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа.Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 MB

Есть еще мнение что 

стоит сделать 10% от памяти сервера

увидел в гитхабе одного ДБА

т.е

7168 память под ПГ - 10% = 716.8 ˜ = 700 MB