Re: Speed up Clog Access by increasing CLOG buffers

On Tue, Nov 17, 2015 at 2:45 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> 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.
>

The reason for not doing this optimization for subtransactions is that we
need to advertise the information that Group leader needs for updating
the transaction status and if we want to do it for sub transactions, then
all the subtransaction id's needs to be advertised. Now here the tricky
part is that number of subtransactions for which the status needs to
be updated is dynamic, so reserving memory for it would be difficult.
However, we can reserve some space in Proc like we do for XidCache
(cache of sub transaction ids) and then use that to advertise that many
Xid's at-a-time or just allow this optimization if number of subtransactions
is lesser than or equal to the size of this new XidCache. I am not sure
if it is good idea to use the existing XidCache for this purpose in which
case we need to have a separate space in PGProc for this purpose. I
don't see allocating space for 64 or so subxid's as a problem, however
doing it for bigger number could be cause of concern.

> 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.
>
>
I have mentioned above the reason for not doing it for sub transactions, if
you think it is viable to reserve space in shared memory for this purpose,
then
I can include the optimization for subtransactions as well.

> 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 understand that increasing alone the number of buffers is not
enough, that's why I have tried this group leader idea. However
if we do something on lines what you have described below
(handling page faults) could avoid the need for increasing buffers.

> 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.
>
>

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com