As per my understanding Summarization of points raised by you and Andres
which this patch should address to have a bigger win:
1. Bgwriter needs to be improved so that it can help in reducing usage count and finding next victim buffer (run the clock sweep and add buffers to the free list). 2. SetLatch for bgwriter (wakeup bgwriter) when elements in freelist are less. 3. Split the workdone globallock (Buffreelist) in StrategyGetBuffer (a spinlock for the freelist, and an lwlock for the clock sweep). 4. Separate processes for writing dirty buffers and moving buffers to freelist 5. Bgwriter needs to be more aggressive, logic based on which it calculates how many buffers it needs to process needs to be improved. 6. There can be contention around buffer mapping locks, but we can focus on it later 7. cacheline bouncing around the buffer header spinlocks, is there anything we can do to reduce this?
My perspectives here would be
* BufFreelistLock is a huge issue. Finding a next victim block needs to be an O(1) operation, yet it is currently much worse than that. Measuring contention on that lock hides that problem, since having shared buffers lock up for 100ms or more but only occasionally is a huge problem, even if it doesn't occur frequently enough for the averaged contention to show as an issue.
* I'm more interested in reducing response time spikes than in increasing throughput. It's easy to overload a benchmark so we get better throughput numbers, but that's not helpful if we make the system more bursty.
* bgwriter's effectiveness is not guaranteed. We have many clear cases where it is useless. So the question should be to continually answer the question: do we need a bgwriter and if so, what should it do? The fact we have one already doesn't mean it should be given things to do. It is a possible option that things may be better if it did nothing. (Not saying that is true, just that we must consider that optione ach time).
From:
Amit Kapila Date: Subject:
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
