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.marl...@gmail.com>: > On Wed, May 24, 2017 at 6:24 AM, Bill Moran <wmo...@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 >
