Re: Further reduction of bufmgr lock contention

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Fri, 2006-04-21 at 13:01 -0400, Tom Lane wrote:
>> I've been looking into Gavin Hamill's recent report of poor performance
>> with PG 8.1 on an 8-way IBM PPC64 box.

> BufMappingLock contention can be made worse by a poorly tuned bgwriter
> or if the cache hit rate is low. Perhaps in this case, increasing
> shared_buffers (again) might be enough to further reduce the contention?

Well, the cache hit rate is evidently pretty high, since so few of the
BufMappingLock accesses are exclusive (any miss would result in an
exclusive access). I did try increasing shared_buffers (from 40000 to
80000) but it didn't change performance noticeably.

> Another difference is the amount of read/write. My understanding is that
> Gavin's workload is mostly read-only which will greatly increase the
> buffer request rate since backends will spend proportionally more time
> consuming data and less time in xlog (etc).

I believe the particular test case being looked at here is read-only
(Gavin, is that correct?)

> The earlier lmgr lock partitioning had a hard-coded number of
> partitions, which was sensible because of the reduced likelihood of
> effectiveness beyond a certain number of partitions. That doesn't follow
> here since the BufMappingLock contention will vary with the size of
> shared_buffers and with the number of CPUs in use (for a given
> workload). I'd like to see the partitioning calculated at server startup
> either directly from shared_buffers or via a parameter. We may not be
> restricted to only using a hash function as we were with lmgr, perhaps
> using a simple range partitioning.

I don't think any of that follows; and a large number of partitions is
risky because it increases the probability of exhausting shared memory
(due to transient variations in the actual size of the hashtables for
different partitions).

> Test-wise: May be able to trial something next week, though system
> access not yet confirmed and I'm not sure we'll see an improvement on
> the workload we're testing on currently. I'll have a think about a pure
> test that we can run on both systems to measure the contention.

Keep in mind that Gavin's 8-way turns back into a pumpkin on Monday :-(

regards, tom lane