Thread: good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9

I have same question

My Computer is running POS with Postgres 8.9.11 database
 Ram : 16GB
 OS : Windows 2008 R2
 CPU XEON 2G
 User : 50-60 user (connect ~ 200 connects, I increase Windows SharedSection=1024,20480,1024 for > 125 connects).
 DISK : RAID 1My application run slowly when >= 30 users

Thank for your help.
Tuan Hoang Anh

On Thu, Nov 11, 2010 at 2:59 AM, AI Rumman <rummandba@gmail.com> wrote:
> Server Specification:
>   dual-core 4 cpu
>   RAM: 32 GB
>   OS: Centos
> What will be good settings for DB parameters such as shared_buffers,
> checkpoint_segment and etc.

I'll take this one ... :)  On my 24GB quad-core Opteron servers
running FreeBSD 8.1, with big external fibre connected RAID array, I
use the following changes relative to the default 9.0.1
postgresql.conf.  You probably don't need to adjust the prepared
transactions setting, unless you use them :-)

The default config is pretty darned good, compared to what used to
ship with older releases like 8.1 :)


listen_addresses = '*'
max_connections = 200
shared_buffers = 4200MB
max_prepared_transactions = 100 # guideline: same number as max_connections
work_mem = 512MB
maintenance_work_mem = 1024MB
vacuum_cost_delay = 15
checkpoint_segments = 64
checkpoint_timeout = 15min
checkpoint_completion_target = 0.8
random_page_cost = 1.3    # fast disk with big buffer
effective_cache_size = 6400MB        # shared_buffers + `sysctl -n
vfs.hibufspace` / 8192 (BLKSZ)
log_checkpoints = on
update_process_title = on
log_autovacuum_min_duration = 0

On Thu, Nov 11, 2010 at 3:30 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> My Computer is running POS with Postgres 8.9.11 database
>  Ram : 16GB
>  OS : Windows 2008 R2
>  CPU XEON 2G
>  User : 50-60 user (connect ~ 200 connects, I increase Windows
> SharedSection=1024,20480,1024 for > 125 connects).
>  DISK : RAID 1
> What will be good settings for DB parameters such as shared_buffers,
> checkpoint_segment and etc.
> My application run slowly when >= 30 users

I'd start by optimizing your queries, and looking for both extra and
missing indexes that would help your queries.

What kind of disk do you have? Are these just local SATA drives?
Perhaps you need faster drives.

> On Thu, Nov 11, 2010 at 3:30 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
>> My Computer is running POS with Postgres 8.9.11 database

Not sure which version is that. There's nothing like 8.9.11 ...

>>  Ram : 16GB
>>  OS : Windows 2008 R2
>>  CPU XEON 2G
>>  User : 50-60 user (connect ~ 200 connects, I increase Windows
>> SharedSection=1024,20480,1024 for > 125 connects).
>>  DISK : RAID 1
>> What will be good settings for DB parameters such as shared_buffers,
>> checkpoint_segment and etc.
>> My application run slowly when >= 30 users
>
> I'd start by optimizing your queries, and looking for both extra and
> missing indexes that would help your queries.

Well, it's always useful to have a decent settings (default one is very
conservative and may significantly hurt performance in some cases).
Anyway, on Linux I'd bump up shared buffers (to something like 512MB) and
work_mem (maybe 4MB), increased effective_cache_size (to about 10GB), etc.
But he mentions Windows 2008 and I have no experience with running PG on
this OS.

> What kind of disk do you have? Are these just local SATA drives?
> Perhaps you need faster drives.

I think it's too early to recommend buying faster drives. You have not
identified the bottleneck and what's causing it.

regards
Tomas

On Thu, Nov 11, 2010 at 08:30:16AM -0500, Vick Khera wrote:
> On Thu, Nov 11, 2010 at 2:59 AM, AI Rumman <rummandba@gmail.com> wrote:
> > Server Specification:
> >   dual-core 4 cpu
> >   RAM: 32 GB
> >   OS: Centos
> > What will be good settings for DB parameters such as shared_buffers,
> > checkpoint_segment and etc.
>
> I'll take this one ... :)  On my 24GB quad-core Opteron servers
> running FreeBSD 8.1, with big external fibre connected RAID array, I
> use the following changes relative to the default 9.0.1
> postgresql.conf.  You probably don't need to adjust the prepared
> transactions setting, unless you use them :-)
>
> The default config is pretty darned good, compared to what used to
> ship with older releases like 8.1 :)
>
> listen_addresses = '*'
> max_connections = 200
> shared_buffers = 4200MB
> max_prepared_transactions = 100 # guideline: same number as max_connections

This should be either 0 (no 2PC) or the bounded from below by
max_connections.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, Nov 11, 2010 at 11:45 AM, David Fetter <david@fetter.org> wrote:
>> max_prepared_transactions = 100 # guideline: same number as max_connections
>
> This should be either 0 (no 2PC) or the bounded from below by
> max_connections.
>

In general, sure.  I have one app that uses 2PC, and it makes maybe 2%
of the connections, so it is pointless to have it set very high.  If
it were fatal, I'd hope Pg would enforce it with at least a warning.