Re: Speed up Clog Access by increasing CLOG buffers

On 17 November 2015 at 06:50, Amit Kapila <amit.kapila16@gmail.com> wrote:

 

In anycase, I went ahead and tried further reducing the CLogControlLock

contention by grouping the transaction status updates.  The basic idea

is same as is used to reduce the ProcArrayLock contention [1] which is to

allow one of the proc to become leader and update the transaction status for

other active transactions in system.  This has helped to reduce the contention

around CLOGControlLock.

Sounds good. The technique has proved effective with proc array and makes sense to use here also.

 

Attached patch group_update_clog_v1.patch

implements this idea.

I don't think we should be doing this only for transactions that don't have subtransactions.

We are trying to speed up real cases, not just benchmarks.

So +1 for the concept, patch is going in right direction though lets do the full press-up.

The above data indicates that contention due to CLogControlLock is

reduced by around 50% with this patch.

The reasons for remaining contention could be:

1. Readers of clog data (checking transaction status data) can take

Exclusive CLOGControlLock when reading the page from disk, this can

contend with other Readers (shared lockers of CLogControlLock) and with
exclusive locker which updates transaction status. One of the ways to

mitigate this contention is to increase the number of CLOG buffers for which

patch has been already posted on this thread.

2. Readers of clog data (checking transaction status data) takes shared

CLOGControlLock which can contend with exclusive locker (Group leader) which

updates transaction status.  I have tried to reduce the amount of work done

by group leader, by allowing group leader to just read the Clog page once

for all the transactions in the group which updated the same CLOG page

(idea similar to what we currently we use for updating the status of transactions

having sub-transaction tree), but that hasn't given any further performance boost,

so I left it.

I think we can use some other ways as well to reduce the contention around

CLOGControlLock by doing somewhat major surgery around SLRU like using

buffer pools similar to shared buffers, but this idea gives us moderate

improvement without much impact on exiting mechanism.

My earlier patch to reduce contention by changing required lock level is still valid here. Increasing the number of buffers doesn't do enough to remove that.

I'm working on a patch to use a fast-update area like we use for GIN. If a page is not available when we want to record commit, just store it in a hash table, when not in crash recovery. I'm experimenting with writing WAL for any xids earlier than last checkpoint, though we could also trickle writes and/or flush them in batches at checkpoint time - your code would help there.

The hash table can also be used for lookups. My thinking is that most reads of older xids are caused by long running transactions, so they cause a page fault at commit and then other page faults later when people read them back in. The hash table works for both kinds of page fault.