Re: Ideal configuration for postgresql 9.3 config

Поиск
Список
Период
Сортировка
От Oliver Jagape
Тема Re: Ideal configuration for postgresql 9.3 config
Дата
Msg-id BLUPR07MB9641209B073AB57C4EC7D6395A00@BLUPR07MB964.namprd07.prod.outlook.com
обсуждение исходный текст
Ответ на Re: Ideal configuration for postgresql 9.3 config  (bricklen <bricklen@gmail.com>)
Список pgsql-admin

Thanks for the reply,


I tried some of suggested changes and some are modified based on this Link I stumbled on the web..


http://pgtune.leopard.in.ua/



Though I'll be able to observe the outcome on the next few days when most of the users will rush on using the apps that depend on this db.


Regards,

Oliver






From: bricklen <bricklen@gmail.com>
Sent: Wednesday, February 17, 2016 12:59 PM
To: Oliver Jagape
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Ideal configuration for postgresql 9.3 config
 
Hi Oliver,

I have suggested a few changes below.

On Tue, Feb 16, 2016 at 7:15 PM, Oliver Jagape <oliver.jagape@concentrix.com> wrote:

CPU: 16 cores

RAM: 48GB

Disk for Data is at a fiber channel SAN storage.

max_connections = 1024     

If you need that many concurrent connections, invest in a connection pooler. If no connection pooler is used, knock off 800 connections if you can.
 
shared_buffers = 8000MB

Test with a lower setting like 4GB, depending on your workload, that may or may not perform better.
 
work_mem = 4096MB

This magnitudes too high. It is per query * sort and aggregation steps. If you expect queries with large result sets (or distinct, group by, etc), 100MB is probably a reasonable starting point.
 
maintenance_work_mem = 512MB

Depending on your relation size, you might want to double this.
 
checkpoint_segments = 512  

Is that a reason you chose such a high number?
 
enable_nestloop = off

Unless you have a clear reason to disable this (it applies to all queries), leave this enabled.
 
autovacuum = off                      

autovacuum should be on unless you know your data churn patterns and have scheduled manual vacuums.
 
autovacuum_naptime = 60

You probably want that more aggressive, like 5s.

В списке pgsql-admin по дате отправления:

Предыдущее
От: "gaoqiangdba@163.com"
Дата:
Сообщение: Re: ANALYZE'ing table hierarchies
Следующее
От: nunks
Дата:
Сообщение: Re: ANALYZE'ing table hierarchies