Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TLs1y-0003Mo-5f for pgsql-performance@postgresql.org; Wed, 10 Oct 2012 08:52:42 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TLs1v-0007il-JL for pgsql-performance@postgresql.org; Wed, 10 Oct 2012 08:52:41 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApgBAOY2dVCkD30E/2dsb2JhbAANN4JLg0eFX7ZhAQEBAwEjSwoBBQsLGAkEEggDAgICBwMCAQIBNBEGDQEFAgEBBYd2EaNAbol4iQiLRoUOgRIDjm6BIIssg26JYg Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP; 10 Oct 2012 10:52:34 +0200 Message-ID: <50753752.6080300@ulb.ac.be> Date: Wed, 10 Oct 2012 10:52:34 +0200 From: Julien Cigar Organization: Belgian Biodiversity Platform User-Agent: Mozilla/5.0 (X11; FreeBSD i386; rv:12.0) Gecko/20120503 Thunderbird/12.0 MIME-Version: 1.0 To: =?UTF-8?B?U3RyYWhpbmphIEt1c3R1ZGnEhw==?= CC: pgsql-performance@postgresql.org Subject: Re: shared_buffers/effective_cache_size on 96GB server References: <50752DB2.6060004@ulb.ac.be> In-Reply-To: Content-Type: multipart/mixed; boundary="------------090900020701040303000902" X-Pg-Spam-Score: -6.3 (------) X-Archive-Number: 201210/106 X-Sequence-Number: 48065 This is a multi-part message in MIME format. --------------090900020701040303000902 Content-Type: multipart/alternative; boundary="------------030203080709000901080705" --------------030203080709000901080705 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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@postgresql.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. --------------030203080709000901080705 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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@ulb.ac.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@postgresql.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.
--------------030203080709000901080705-- --------------090900020701040303000902 Content-Type: text/x-vcard; charset=utf-8; name="jcigar.vcf" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="jcigar.vcf" YmVnaW46dmNhcmQNCmZuOkp1bGllbiBDaWdhcg0KbjpDaWdhcjtKdWxpZW4NCm9yZztxdW90 ZWQtcHJpbnRhYmxlOkJlbGdpYW4gQmlvZGl2ZXJzaXR5IFBsYXRmb3JtO1VMQiAoVW5pdmVy c2l0PUMzPUE5IExpYnJlIGRlIEJydXhlbGxlcykNCmFkcjo7OztCcnVzc2Vsczs7O0JlbGdp dW0NCmVtYWlsO2ludGVybmV0OmpjaWdhckB1bGIuYWMuYmUNCnRlbDt3b3JrOiszMigwKTI2 NTA1NzUyDQp4LW1vemlsbGEtaHRtbDpGQUxTRQ0KdXJsOmh0dHA6Ly93d3cuYmlvZGl2ZXJz aXR5LmJlDQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg== --------------090900020701040303000902--