Re: BufFreelistLock

On Dec 10, 2010, at 10:49 AM, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Excerpts from Jeff Janes's message of vie dic 10 12:24:34 -0300 2010:
>>> As far as I can tell, bgwriter never adds things to the freelist.
>>> That is only done at start up, and when a relation or a database is
>>> dropped.  The clock sweep does the vast majority of the work.
>
>> AFAIU bgwriter runs the clock sweep most of the time (BgBufferSync).
>
> I think bgwriter just tries to write out dirty buffers so they'll be
> clean when the clock sweep reaches them.  It doesn't try to move them to
> the freelist.

Yeah, it calls SyncOneBuffer which does nothing for the clock sweep.

> There might be some advantage in having it move buffers
> to a freelist that's just protected by a simple spinlock (or at least,
> a lock different from the one that protects the clock sweep).  The
> idea would be that most of the time, backends just need to lock the
> freelist for long enough to take a buffer off it, and don't run clock
> sweep at all.

Yeah, the clock sweep code is very intensive compared to pulling a buffer from the freelist, yet AFAICT nothing will
runthe clock sweep except backends. Unless I'm missing something, the free list is practically useless because buffers
areonly put there by InvalidateBuffer, which is only called by DropRelFileNodeBuffers and DropDatabaseBuffers. So we
makebackends queue up behind the freelist lock with very little odds of getting a buffer, then we make them queue up
forthe clock sweep lock and make them actually run the clock sweep. 

BTW, when we moved from 96G to 192G servers I tried increasing shared buffers from 8G to 28G and performance went down
enoughto be noticeable (we don't have any good benchmarks, so I cant really quantify the degradation). Going back to 8G
broughtperformance back up, so it seems like it was the change in shared buffers that caused the issue (the larger
serversalso have 24 cores vs 16). My immediate thought was that we needed more lock partitions, but I haven't had the
chanceto see if that helps. ISTM the issue could just as well be due to clock sweep suddenly taking over 3x longer than
before.

We're working on getting a performance test environment setup, so hopefully in a month or two we'd be able to actually
runsome testing on this. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net