Re: shared_buffers/effective_cache_size on 96GB server

Thanks for your help everyone.

I set:
shared_buffers = 4GB
effective_cache_size = 72GB
work_mem = 128MB
maintenance_work_mem = 4GB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
random_page_cost = 3.5
cpu_tuple_cost = 0.05

Where can I get the values for random_page_cost and for cpu_tuple_cost
where they depend on hardware? I know that for SSDs random_page_cost should
be 1.0, but I have no idea what value this should be for different types of
drives.

I also set:
vm.swappiness = 0
vm.overcommit_memory = 2
vm.overcommit_ratio = 50

But I don't understand why do I need to set overcommit_memory, since I only
have postgres running, nothing else would allocate memory anyway?

I will set readahead later, first I want to see how is this working.

Strahinja Kustudić | System Engineer | Nordeus

On Wed, Oct 10, 2012 at 10:52 AM, Julien Cigar <jcigar(at)ulb(dot)ac(dot)be> wrote:

> On 10/10/2012 10:30, Strahinja Kustudić wrote:
>
> Thanks for very fast replies everyone :)
>
> @Laurenz I know that effective cache size is only used for the query
> planner, what I was saying is that if I tell it that it can have 90GB
> cached items, that is not trues, since the OS and Postgres process itself
> can take more than 6GB, which would mean 90GB is not the correct value, but
> if effective_cache size should be shared_buffers+page cache as Tomas said,
> than 90GB, won't be a problem.
>
>
> @Tomas here are the values:
>
> # cat /proc/sys/vm/swappiness
> 60
> # cat /proc/sys/vm/overcommit_memory
> 0
> # cat /proc/sys/vm/overcommit_ratio
> 50
>
> I will turn of swappiness, I was meaning to do that, but I don't know much
> about the overcommit settings, I will read what they do.
>
>
> @Julien thanks for the suggestions, I will tweak them like you suggested.
>
>
> also with 15k SCSI you can reduce random_page_cost to 3.5 (instead of 4.0)
> I also recommend to raise cpu_tuple_cost to 0.05 (instead of 0.01), set
> vm.swappiness to 0, vm.overcommit_memory to 2, and finally raise the
> read-ahead (something like 8192)
>
>
> Strahinja Kustudić | System Engineer | Nordeus
>
>
>
> On Wed, Oct 10, 2012 at 10:11 AM, Julien Cigar <jcigar(at)ulb(dot)ac(dot)be> wrote:
>
>> On 10/10/2012 09:12, Strahinja Kustudić wrote:
>>
>>> Hi everyone,
>>>
>>
>> Hello,
>>
>>
>>
>>> 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
>>>
>>
>> Generally going over 4GB for shared_buffers doesn't help.. some of the
>> overhead of bgwriter and checkpoints is more or less linear in the size of
>> shared_buffers ..
>>
>> effective_cache_size = 90GB
>>>
>>
>> effective_cache_size should be ~75% of the RAM (if it's a dedicated
>> server)
>>
>> work_mem = 32MB
>>>
>>
>> with 96GB of RAM I would raise default work_mem to something like 128MB
>>
>> maintenance_work_mem = 512MB
>>>
>>
>> again, with 96GB of ram you can raise maintenance_work_mem to something
>> like 4GB
>>
>>
>> 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.
>>>
>>> Thanks in advance,
>>> Strahinja
>>>
>>
>> Julien
>>
>>
>> --
>> No trees were killed in the creation of this message.
>> However, many electrons were terribly inconvenienced.
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>
>
> --
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
>
>