Re: shared_buffers/effective_cache_size on 96GB server

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux