Re: postgresql.conf recommendations - Mailing list pgsql-performance
| From | Charles Gomes |
|---|---|
| Subject | Re: postgresql.conf recommendations |
| Date | |
| Msg-id | BLU002-W93A8707C498E9A7EFC2241AB060@phx.gbl Whole thread Raw |
| In response to | Re: postgresql.conf recommendations (Strahinja Kustudić <strahinjak@nordeus.com>) |
| Responses |
Re: postgresql.conf recommendations
(Johnny Tan <johnnydtan@gmail.com>)
Re: postgresql.conf recommendations (Scott Marlowe <scott.marlowe@gmail.com>) |
| List | pgsql-performance |
I've benchmarked shared_buffers with high and low settings, in a server dedicated to postgres with 48GB my settings are:
shared_buffers = 37GB
effective_cache_size = 38GB
Having a small number and depending on OS caching is unpredictable, if the server is dedicated to postgres you want make sure postgres has the memory. A random unrelated process doing a cat /dev/sda1 should not destroy postgres buffers.
I agree your problem is most related to dirty background ration, where buffers are READ only and have nothing to do with disk writes.
From: strahinjak@nordeus.com
Date: Thu, 7 Feb 2013 13:06:53 +0100
Subject: Re: [PERFORM] postgresql.conf recommendations
To: kgrittn@ymail.com
CC: johnnydtan@gmail.com; ac@hsk.hk; jkrupka@gmail.com; alex@paperlesspost.com; pgsql-performance@postgresql.org
Strahinja Kustudić | System Engineer | Nordeus
shared_buffers = 37GB
effective_cache_size = 38GB
Having a small number and depending on OS caching is unpredictable, if the server is dedicated to postgres you want make sure postgres has the memory. A random unrelated process doing a cat /dev/sda1 should not destroy postgres buffers.
I agree your problem is most related to dirty background ration, where buffers are READ only and have nothing to do with disk writes.
From: strahinjak@nordeus.com
Date: Thu, 7 Feb 2013 13:06:53 +0100
Subject: Re: [PERFORM] postgresql.conf recommendations
To: kgrittn@ymail.com
CC: johnnydtan@gmail.com; ac@hsk.hk; jkrupka@gmail.com; alex@paperlesspost.com; pgsql-performance@postgresql.org
As others suggested having shared_buffers = 48GB is to large. You should never need to go above 8GB. I have a similar server and mine has
shared_buffers = 8GB
checkpoint_completion_target = 0.9
This looks like a problem of dirty memory being flushed to the disk. You should set your monitoring to monitor dirty memory from /proc/meminfo and check if it has any correlation with the slowdowns. Also vm.dirty_background_bytes should always be a fraction of vm.dirty_bytes, since when there is more than vm.dirty_bytes bytes dirty it will stop all writing to the disk until it flushes everything, while when it reaches the vm.dirty_background_bytes it will slowly start flushing those pages to the disk. As far as I remember vm.dirty_bytes should be configured to be a little less than the cache size of your RAID controller, while vm.dirty_background_bytes should be 4 times smaller.shared_buffers = 8GB
checkpoint_completion_target = 0.9
Strahinja Kustudić | System Engineer | Nordeus
On Wed, Feb 6, 2013 at 10:12 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Johnny Tan <johnnydtan@gmail.com> wrote:Spread checkpoints made the issue less severe, but on servers with
> Wouldn't this be controlled by our checkpoint settings, though?
a lot of RAM I've had to make the above changes (or even go lower
with shared_buffers) to prevent a burst of writes from overwhelming
the RAID controllers battery-backed cache. There may be other
things which could cause these symptoms, so I'm not certain that
this will help; but I have seen this as the cause and seen the
suggested changes help.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
pgsql-performance by date: