Re: Clock sweep not caching enough B-Tree leaf pages?

On 4/15/14, 1:15 PM, Peter Geoghegan wrote:
> On Tue, Apr 15, 2014 at 9:30 AM, Merlin Moncure<mmoncure(at)gmail(dot)com> wrote:
>> >There are many reports of improvement from lowering shared_buffers.
>> >The problem is that it tends to show up on complex production
>> >workloads and that there is no clear evidence pointing to problems
>> >with the clock sweep; it could be higher up in the partition locks or
>> >something else entirely (like the O/S). pgbench is also not the
>> >greatest tool for sniffing out these cases: it's too random and for
>> >large database optimization is generally an exercise in de-randomizing
>> >i/o patterns. We really, really need a broader testing suite that
>> >covers more usage patterns.
> I find it quite dissatisfying that we know so little about this.

This is an area where additional stats gathering would be very valuable. We're running 8G buffers on 512G servers because bumping it up hurt performance, but we have no clue why. Was it due to buffer pins? How many times the clock had to sweep to find a victim? Something else entirely? No idea... :(
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net