25 окт. 2014 г., в 4:31, Jim Nasby <jim.na...@bluetreble.com> написал(а):

> 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 really important) - 
>> http://pastie.org/9671939.
>> As I can see, 17981 is actually waiting for LWLock on BufFreelistLock in 
>> StrategyGetBuffer function, freelist.c:134 while holding exclusive lock on 
>> relation. I will try to increase NUM_BUFFER_PARTITIONS (on read-only load it 
>> also gave us some 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(), which will end up getting the freelist lock. Currently 
> the free list is normally empty, which means we now need to run the clock 
> sweep to find a victim buffer. The clock sweep will keep running until it 
> finds a buffer that is not pinned and has 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 finally find 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 means no one else in the entire 
> system can allocate a new buffer.

I’ll try the same workload with recent patch from Andres Freund [0].

> This is one reason why a large shared_buffers setting is usually 
> counter-productive. Experience with older versions is that setting it higher 
> than about 8GB is more likely to hurt than to help. Newer versions are 
> probably better, but I think you'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 database is > 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 too big). You might be able 
> to do better than 8G, but I recommend not even trying unless you've got a 
> good way to test your performance.
> If you can test performance and find an optimal setting for shared_buffers, 
> please do share your test data and findings. :)

Of course, it works well with shared_buffers <= 8GB. But we have seen that on 
read-only load when data set fits in RAM with <=8GB shared_buffers we hit 
BufFreelistLock LWLock while moving pages between shared buffers and page 
cache. Increasing shared_buffers size to the size of data set improves 
performance up to 2,5X faster on this read-only load. So we started testing 
configuration with huge shared_buffers under writing load and that’s why I 
started this thread.

Since StrategyGetBuffer() does not use BufFreelistLock LWLock any more [1] I’ll 
also re-run tests with read-only load and small shared_buffers.


> -- 
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com


Reply via email to