Ben Chobot wrote:
Hm, my shared_buffers is already 10GB, but I'm using about 80GB for filesystem cache. Would a larger shared_buffers make sense? I thought I read somewhere that 10GB is on the high end of the useful size for shared_buffers.
Yeah, I figured that out when I was analyzing your figures and thought I'd missed a decimal place when I first saw it. The problem with huge increases in shared_buffer is that they can increase the amount of time it takes to allocate a new buffer. If all you've got in there are lots of records with high usage counts, it can end up taking multiple "sweeps of the clock hand" over things to drop usage counts. With >75% of your buffers already having a usage count of 4 or 5, you've already gone pretty far in the direction where that could happen. With still around 13% only have a 0 or 1 usage count I don't think it's too bad yet.
You're certainly not in well explored territory though. If you were seeing large amounts of backend writes or buffers being allocated, maybe a larger shared_buffers would make sense. From the snapshots of data you've provided, that doesn't seem to be the case though, so I wouldn't be too worried about it. The only thing you could really do here is increase checkpoint_timeout - with this much data, having a checkpoint every 5 minutes is on the fast side, and I'd bet you could tolerate the disk space and additional crash recovery time in return for better average performance the rest of the time.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
