Re: ExclusiveLock on extension of relation with huge shared_buffers

Please don't top-post.

On 10/24/14, 3:40 AM, Borodin Vladimir wrote:
> I have taken some backtraces (they are attached to the letter) of two processes with such command:
> pid=17981; while true; do date; gdb -batch -e back /usr/pgsql-9.4/bin/postgres $pid; echo; echo; echo; echo; sleep
0.1;done
 
>
> Process 17981 was holding the lock for a long time - http://pastie.org/9671931.
> And process 13886 was waiting for lock (in different time and from different blocker actually but I don’t think it is
reallyimportant) - http://pastie.org/9671939.
 
>
> As I can see, 17981 is actually waiting for LWLock on BufFreelistLock in StrategyGetBuffer function, freelist.c:134
whileholding exclusive lock on relation. I will try to increase NUM_BUFFER_PARTITIONS (on read-only load it also gave
ussome performance boost) and write the result in this thread.
 

BufFreelistLock becomes very contended when shared buffers are under a lot of pressure.

Here's what I believe is happening:

If RelationGetBufferForTuple() decides it needs to extend, this happens:    LockRelationForExtension(relation,
ExclusiveLock);buffer= ReadBufferBI(relation, P_NEW, bistate);
 

Assuming bistate is false (I didn't check the bulk case), ReadBufferBI() ends up at ReadBuffer_common(), which calls
BufferAlloc().In the normal case, BufferAlloc() won't find the necessary buffer, so it will call StrategyGetBuffer(),
whichwill end up getting the freelist lock. Currently the free list is normally empty, which means we now need to run
theclock sweep to find a victim buffer. The clock sweep will keep running until it finds a buffer that is not pinned
andhas usage_count = 0. If shared buffers are under heavy pressure, you can have a huge number of them with usage_count
=5, which for 100GB shared buffers and an 8K BLKSZ, you could have to check buffers *52 million* times (assuming you
finallyfind a buffer on the start of the 5th loop) before you find a victim.
 

Keep in mind that's all happening while you're holding both the extension lock *and the freelist lock*, which basically
meansno one else in the entire system can allocate a new buffer.
 

This is one reason why a large shared_buffers setting is usually counter-productive. Experience with older versions is
thatsetting it higher than about 8GB is more likely to hurt than to help. Newer versions are probably better, but I
thinkyou'll be hard-pressed to find a workload where 100GB makes sense. It might if your entire database fits in
shared_buffers(though, even then there's probably a number of O(n) or worse operations that will hurt you), but if your
databaseis > shared_buffers you're probably in trouble.
 

I suggest cutting shared_buffers *way* down. Old-school advice for this machine would be 8G (since 25% of 128G would be
toobig). You might be able to do better than 8G, but I recommend not even trying unless you've got a good way to test
yourperformance.
 

If you can test performance and find an optimal setting for shared_buffers, please do share your test data and
findings.:)
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com