Re: Postgres 8.4 memory related parameters

On Thu, Aug 4, 2011 at 2:38 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Claire Chang <yenhsiac(at)yahoo(dot)com> wrote:
>
>> hi, We recently bought a 4 8core 128G memory database server and I
>> am setting it up to replace our old 4 4cores 128G memory database
>> server as a master.  The memory related settings that we use on
>> the old machine seem a bit wrong according to the experts on IRC:
>
>> max_connections = 600
>
> You're probably going to get better performance by setting that to 2
> to 3 times the number of actual cores (don't county hyperthreading
> for this purpose), and using a connection pooler to funnel the 600
> user connections down to a smaller number of database connections.
>
>> shared_buffers = 32GB
>
> I seem to remember seeing some benchmarks showing that performance
> falls off after 10GB or 20GB on that setting.
>
>> effective_cache_size = 64GB
>
> Seems sane.
>
>> work_mem = 5MB
>
> You could bump that up, especially if you go to the connection pool.
>
>> maintenance_work_mem = 1GB
>
> OK, but I might double that.
>
>> wal_buffers = 64kB
>
> This should definitely be set to 16MB.

Agreed with everything so far. A few other points. If you're doing a
LOT of writing, and the immediate working set will fit in less
shared_buffers then lower it down to something in the 1 to 4G range
max. Lots of write and a large shared_buffer do not mix well. I have
gotten much better performance from lowering shared_buffers on
machines that need to write a lot. I run Ubuntu 10.04 for my big
postgresql servers right now. With that in mind, here's some
pointers.

I'd recommend adding this to rc.local:

# turns off swap
/sbin/swapoff -a

I had a few weird kswapd storms where the kernel just seems to get
confused about having 128G of ram and swap space. Machine was lagging
very hard at odd times of the day until I just turned off swap.

and if you have a caching RAID controller with battery backup then I'd
add a line like this:

echo noop > /sys/block/sda/queue/scheduler

for every RAID drive you have. Any other scheduler really just gets
in the way of a good caching RAID controller.

There's also some parameters that affect how fast dirty caches are
written out by the OS, worth looking into, but they made no big
difference on my 128G 48 core 34 15krpm drive system.

If you do a lot of inserts / updates / deletes then look at making
vacuum more aggressive. Also look at making the bgwriter a bit more
aggressive and cranking up the timeout and having lots of checkpoint
segments.