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. [0] http://git.postgresql.org/pg/commitdiff/d72731a70450b5e7084991b9caa15cb58a2820df [1] http://git.postgresql.org/pg/commitdiff/1dcfb8da09c47d2a7502d1dfab06c8be4b6cf323 > -- > Jim Nasby, Data Architect, Blue Treble Consulting > Data in Trouble? Get it in Treble! http://BlueTreble.com -- Vladimir
