Re: [GENERAL] Current best practice for maximum shared_bufferssettings on big hardware?

On Wed, May 24, 2017 at 6:24 AM, Bill Moran <wmoran@potentialtech.com> wrote:
>
> A few years ago, I was working with "big" servers. At least, they were
> big for that age, with *128G* of RAM!!!1 Holy mackeral, right?!!?
>
> Anyway, at that time, I tried allocating 64G to shared buffers and we
> had a bunch of problems with inconsistent performance, including "stall"
> periods where the database would stop responding for 2 or 3 seconds.
> After trying all sorts of tuning options that didn't help, the problem
> finally went away after reducing shared_buffers to 32G. I speculated, at
> the time, that the shared buffer code hit performance issues managing
> that much memory, but I never had the opportunity to really follow up
> on it.
>
> Now, this was back in 2012 or thereabouts. Seems like another lifetime.
> Probably PostgreSQL 9.2 at that time.
>
> Nowadays, 128G is a "medium sized" server. I just got access to one
> with 775G. It would appear that I could order from Dell with 1.5T of
> RAM if I'm willing to sell my house ...
>
> Yet, all the docs and advice I'm able to find online seem to have been
> written pre 2008 and say things like "if your server has more than 1G
> of RAM ..."
>
> I feel like it's time for a documentation update ;) But I, personally
> don't have the experience recently enough to know what sort of
> recommendations to make.
>
> What are people's experience with modern versions of Postgres on hardware
> this size? Do any of the experts have specific recommendations on large
> shared_buffers settings? Any developers care to comment on any work
> that's been done since 2012 to make large values work better?

My most recent employment was working on machines with 512GB to 1TB
memory. We never saw real performance increases past 10GB or so of
shared memory. That was with pg 9.2 and testing on 9.6.  The 512GB
machines were processing something on the order of 500 or so writes
per second and 3k to 5k reads per second. Under testing we were able
to push through 18k writes and reads per second on those machines.
These dbs were in the 4 to 5TB range so could not fit in memory.
Letting the linux kernel (3.11 or 3.13 at the time) handle the caching
seemed to get best, most reliable performance. These machines ran big
RAID-5 arrays (6 to 7 TB) with write caching off and could read from
the IO really fast, so mostly we were bound by IO performance not
memory caching.

If you allocate 50% of memory to shared buffers then you're basically
caching everything twice, once in kernel cache and once in shared
memory. The general consensus is that you're better off going one way
or another, either let linux do the caching work, or crank up the
shared memory to 90% or so and let postgresql do it. My experience has
been that the kernel wins almost every time.

But about 95% of all my testing and 100% of my production experience
is on 3.13 kernels with pgsql 9.2 on top of it. 9.6 and 10 etc may
well be much faster with bigger shared memory.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general