From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Shaun Thomas <sthomas@optionshouse.com>
To: Alexey Vasiliev <leopard_ne@inbox.ru>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: pgtune + configurations with 9.3
Date: Sat, 15 Nov 2014 12:00:28 +1300
Message-ID: <5466898C.9070809@catalyst.net.nz> (raw)
In-Reply-To: <0683F5F5A5C7FE419A752A034B4A0B9797DCD4DA@sswchi5pmbx2.peak6.net>
References: <CAEaSS0bLy8=1yVVbAH6us3m5d1tQ9obCxeHHeTU6vrqkggcN+g@mail.gmail.com>
<1415975036.62330340@f358.i.mail.ru>
<0683F5F5A5C7FE419A752A034B4A0B9797DCD287@sswchi5pmbx2.peak6.net>
<1415983219.686386092@f34.i.mail.ru>
<0683F5F5A5C7FE419A752A034B4A0B9797DCD4DA@sswchi5pmbx2.peak6.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 15/11/14 06:06, Shaun Thomas wrote:
> Alexey,
>
> The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is that setting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system with 512GB of RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm pretty much any disk controller and end up completely ruining DB performance. And that's just *one* of the drawbacks.
>
It is probably time to revisit this 8GB limit with some benchmarking. We
don't really have a hard and fast rule that is known to be correct, and
that makes Alexey's job really difficult. Informally folk (including
myself at times) have suggested:
min(ram/4, 8GB)
as the 'rule of thumb' for setting shared_buffers. However I was
recently benchmarking a machine with a lot of ram (1TB) and entirely SSD
storage [1], and that seemed quite happy with 50GB of shared buffers
(better performance than with 8GB). Now shared_buffers was not the
variable we were concentrating on so I didn't get too carried away and
try much bigger than about 100GB - but this seems like a good thing to
come out with some numbers for i.e pgbench read write and read only tps
vs shared_buffers 1 -> 100 GB in size.
Cheers
Mark
[1] I may be in a position to benchmark the machines these replaced at
some not to distant time. These are the previous generation (0.5TB ram,
32 cores and all SSD storage) but probably still good for this test.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: mark.kirkwood@catalyst.net.nz, sthomas@optionshouse.com, leopard_ne@inbox.ru, josh@agliodbs.com
Subject: Re: pgtune + configurations with 9.3
In-Reply-To: <5466898C.9070809@catalyst.net.nz>
* 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