Re: Speed up Clog Access by increasing CLOG buffers

On 17 November 2015 at 06:50, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services