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@xxxxxxxxx>
wrote:
On 10/10/2012 09:12, Strahinja Kustudić wrote:
Hi everyone,
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@xxxxxxxxxxxxxx)
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.
|
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@xxxxxxxxx
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
