From: Scott Carey <scott@richrelevance.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Merlin Moncure <mmoncure@gmail.com>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: The shared buffers challenge
Date: Fri, 27 May 2011 09:44:30 -0700
Message-ID: <CA051DED.39EC4%scott@richrelevance.com> (raw)
In-Reply-To: <4DDE2705020000250003DD4F@gw.wicourts.gov>
So how far do you go? 128MB? 32MB? 4MB?
Anecdotal and an assumption, but I'm pretty confident that on any server
with at least 1GB of dedicated RAM, setting it any lower than 200MB is not
even going to help latency (assuming checkpoint and log configuration is
in the realm of sane, and connections*work_mem is sane).
The defaults have been so small for so long on most platforms, that any
increase over the default generally helps performance -- and in many cases
dramatically. So if more is better, then most users assume that even more
should be better.
But its not so simple, there are drawbacks to a larger buffer and
diminishing returns with larger size. I think listing the drawbacks of a
larger buffer and symptoms that can result would be a big win.
And there is an OS component to it too. You can actually get away with
shared_buffers at 90% of RAM on Solaris. Linux will explode if you try
that (unless recent kernels have fixed its shared memory accounting).
On 5/26/11 8:10 AM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>Merlin Moncure <mmoncure@gmail.com> wrote:
>
>> So, the challenge is this: I'd like to see repeatable test cases
>> that demonstrate regular performance gains > 20%. Double bonus
>> points for cases that show gains > 50%.
>
>Are you talking throughput, maximum latency, or some other metric?
>
>In our shop the metric we tuned for in reducing shared_buffers was
>getting the number of "fast" queries (which normally run in under a
>millisecond) which would occasionally, in clusters, take over 20
>seconds (and thus be canceled by our web app and present as errors
>to the public) down to zero. While I know there are those who care
>primarily about throughput numbers, that's worthless to me without
>maximum latency information under prolonged load. I'm not talking
>90th percentile latency numbers, either -- if 10% of our web
>requests were timing out the villagers would be coming after us with
>pitchforks and torches.
>
>-Kevin
>
>--
>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: scott@richrelevance.com, Kevin.Grittner@wicourts.gov, mmoncure@gmail.com
Subject: Re: The shared buffers challenge
In-Reply-To: <CA051DED.39EC4%scott@richrelevance.com>
* 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