Re: shared_buffers/effective_cache_size on 96GB server

Strahinja Kustudic wrote:
>> I have a Postgresql 9.1 dedicated server with 16 cores, 96GB RAM and RAID10 15K SCSI drives
>> which is runing Centos 6.2 x64. This server is mainly used for inserting/updating large amounts of
>> data via copy/insert/update commands, and seldom for running select queries.
>>
>> Here are the relevant configuration parameters I changed:
>>
>> shared_buffers = 10GB
>> effective_cache_size = 90GB
>> work_mem = 32MB
>> maintenance_work_mem = 512MB
>> checkpoint_segments = 64
>> checkpoint_completion_target = 0.8
>>
>> My biggest concern are shared_buffers and effective_cache_size, should I increase shared_buffers
>> and decrease effective_cache_size? I read that values above 10GB for shared_buffers give lower
>> performance, than smaller amounts?
>>
>> free is currently reporting (during the loading of data):
>>
>> $ free -m
>> total used free shared buffers cached
>> Mem: 96730 96418 311 0 71 93120
>> -/+ buffers/cache: 3227 93502
>> Swap: 21000 51 20949
>>
>> So it did a little swapping, but only minor, still I should probably decrease shared_buffers so
>> there is no swapping at all.

> Hm, I just notices that shared_buffers + effective_cache_size = 100 > 96GB, which can't be right.
> effective_cache_size should probably be 80GB.

I think you misunderstood effective_cache_size.
It does not influence memory usage, but query planning.
It gives the planner an idea of how much memory there is for caching
data, including the filesystem cache.

So a good value for effective_cache_size would be
total memory minus what the OS and others need minus what private
memory the PostgreSQL backends need.
The latter can be estimated as work_mem times max_connections.

To avoid swapping, consider setting vm.swappiness to 0 in
/etc/sysctl.conf.

10GB of shared_buffers is quite a lot.
If you can run realistic performance tests, start with a lower value
and increase until you cannot see a notable improvement.

Yours,
Laurenz Albe