On Wed, May 2, 2018 at 12:27:19PM -0400, Robert Haas wrote: > I've seen customer have very good luck going higher if it lets all the > data fit in shared_buffers, or at least all the data that is accessed > with any frequency. I think it's useful to imagine a series of > concentric working sets - maybe you have 1GB of the hottest data, 3GB > of data that is at least fairly hot, 10GB of data that is at least > somewhat hot, and another 200GB of basically cold data. Increasing > shared_buffers in a way that doesn't let the next "ring" fit in > shared_buffers isn't likely to help very much. If you have 8GB of > shared_buffers on this workload, going to 12GB is probably going to > help -- that should be enough for the 10GB of somewhat-hot stuff and a > little extra so that the somewhat-hot stuff doesn't immediately start > getting evicted if some of the cold data is accessed. Similarly, > going from 2GB to 4GB should be a big help, because now the fairly-hot > stuff should stay in cache. But going from 4GB to 6GB or 12GB to 16GB > may not do very much. It may even hurt, because the duplication > between shared_buffers and the OS page cache means an overall > reduction in available cache space. If for example you've got 16GB of > memory and shared_buffers=2GB, you *may* be fitting all of the > somewhat-hot data into cache someplace; bumping shared_buffers=4GB > almost certainly means that will no longer happen, causing performance > to tank.
I would love to know how we can help people find out how much data is in each of these rings so they can tune shared buffers accordingly. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
