Re: Speed up Clog Access by increasing CLOG buffers

On Fri, Nov 27, 2015 at 2:32 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> Okay, as discussed I have handled the case of sub-transactions without
> additional shmem in the attached patch. Apart from that, I have tried
> to apply this optimization for Prepared transactions as well, but as
> the dummy proc used for such transactions doesn't have semaphore like
> backend proc's, so it is not possible to use such a proc in group status
> updation as each group member needs to wait on semaphore. It is not tad
> difficult to add the support for that case if we are okay with creating
> additional
> semaphore for each such dummy proc which I was not sure, so I have left
> it for now.

"updation" is not a word. "acquirations" is not a word. "penality"
is spelled wrong.

I think the approach this patch takes is pretty darned strange, and
almost certainly not what we want. What you're doing here is putting
every outstanding CLOG-update request into a linked list, and then the
leader goes and does all of those CLOG updates. But there's no
guarantee that the pages that need to be updated are even present in a
CLOG buffer. If it turns out that all of the batched CLOG updates are
part of resident pages, then this is going to work great, just like
the similar ProcArrayLock optimization. But if the pages are not
resident, then you will get WORSE concurrency and SLOWER performance
than the status quo. The leader will sit there and read every page
that is needed, and to do that it will repeatedly release and
reacquire CLogControlLock (inside SimpleLruReadPage). If you didn't
have a leader, the reads of all those pages could happen at the same
time, but with this design, they get serialized. That's not good.

My idea for how this could possibly work is that you could have a list
of waiting backends for each SLRU buffer page. Pages with waiting
backends can't be evicted without performing the updates for which
backends are waiting. Updates to non-resident pages just work as they
do now. When a backend acquires CLogControlLock to perform updates to
a given page, it also performs all other pending updates to that page
and releases those waiters. When a backend acquires CLogControlLock
to evict a page, it must perform any pending updates and write the
page before completing the eviction.

I agree with Simon that it's probably a good idea for this
optimization to handle cases where a backend has a non-overflowed list
of subtransactions. That seems doable. Handling the case where the
subxid list has overflowed seems unimportant; it should happen rarely
and is therefore not performance-critical. Also, handling the case
where the XIDs are spread over multiple pages seems far too
complicated to be worth the effort of trying to fit into a "fast
path". Optimizing the case where there are 1+ XIDs that need to be
updated but all on the same page should cover well over 90% of commits
on real systems, very possibly over 99%. That should be plenty good
enough to get whatever contention-reduction benefit is possible here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company