From: Julien Cigar <jcigar@ulb.ac.be>
To: Strahinja Kustudić <strahinjak@nordeus.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared_buffers/effective_cache_size on 96GB server
Date: Wed, 10 Oct 2012 10:52:34 +0200
Message-ID: <50753752.6080300@ulb.ac.be> (raw)
In-Reply-To: <CADKbJJW8jEMwFQDXTZzqupckgyVvZ6Jbx2-EXO8o-rtX+_91FQ@mail.gmail.com>
References: <CADKbJJUTr9ZWkbajR-7_6qaAsWH0=09=2AMo67C_Az-raKZE4w@mail.gmail.com>
<50752DB2.6060004@ulb.ac.be>
<CADKbJJW8jEMwFQDXTZzqupckgyVvZ6Jbx2-EXO8o-rtX+_91FQ@mail.gmail.com>
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
> <mailto: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
> <mailto: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.
Attachments:jcigar.vcf (text/x-vcard, 304B) download
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jcigar@ulb.ac.be, strahinjak@nordeus.com
Subject: Re: shared_buffers/effective_cache_size on 96GB server
In-Reply-To: <50753752.6080300@ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox