Re: Monitoring buffercache...

On Mon, Nov 24, 2008 at 9:40 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Mon, 24 Nov 2008, Scott Marlowe wrote:
>
>> My guess is that the period of time for which pg_buffercache takes locks
>> on the buffer map are short enough that it isn't a real big deal on a fast
>> enough server.
>
> As the server involved gets faster, the amount of time the locks are
> typically held for drops.
>
> As your shared_buffers allocation increases, that amount of time goes up.
>
> So how painful the overhead is depends on how fast your CPU is relative to
> how much memory is in it. Since faster systems tend to have more RAM in
> them, too, it's hard to say whether the impact will be noticable.
>
> Also, noting that the average case isn't impacted much isn't the concern
> here. The problem is how much having all partition locks held will increase
> impact worst-case latency.

True. I was just looking to see how it impacted my servers. Just
FYI, it's an 8 core opteron 2.1GHz with 32 Gig 667MHz DDR2 ram. It
runs on a fast RAID-10 set (12 15k drives under an areca 1680, but I
don't know if that matters that much here.) It can pretty easily about
400 or so active transactions and still be responsive, but noticeably
slower. At anything under about 50 or so transactions it's still
quite fast.

It's configured to have 8Gig of the 32Gig allocated as shared buffers,
and a buffercache query takes about 1 second to run. Is the
shared_mem locked all that time? And is it only locked against
writes?