Re: Current best practice for maximum shared_buffers settings on big hardware?

Hello,

read it and test it , you will know why offical doc suggest no more
than 40% total memory for shared_buffers

http://raghavt.blogspot.tw/2012/04/caching-in-postgresql.html

And this is also a very good book -- http://www.interdb.jp/pg/index.html

IT job is just to keep repeating study and test for developing your own
experiences .

Best Regards,
Steven

2017-05-25 1:34 GMT+08:00 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:

> On Wed, May 24, 2017 at 6:24 AM, Bill Moran <wmoran(at)potentialtech(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>