Re: Speed up Clog Access by increasing CLOG buffers

On Fri, Oct 21, 2016 at 6:31 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Oct 20, 2016 at 4:04 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>>> I then started a run at 96 clients which I accidentally killed shortly
>>> before it was scheduled to finish, but the results are not much
>>> different; there is no hint of the runaway CLogControlLock contention
>>> that Dilip sees on power2.
>>>
>> What shared_buffer size were you using? I assume the data set fit into
>> shared buffers, right?
>
> 8GB.
>
>> FWIW as I explained in the lengthy post earlier today, I can actually
>> reproduce the significant CLogControlLock contention (and the patches do
>> reduce it), even on x86_64.
>
> /me goes back, rereads post. Sorry, I didn't look at this carefully
> the first time.
>
>> For example consider these two tests:
>>
>> * http://tvondra.bitbucket.org/#dilip-300-unlogged-sync
>> * http://tvondra.bitbucket.org/#pgbench-300-unlogged-sync-skip
>>
>> However, it seems I can also reproduce fairly bad regressions, like for
>> example this case with data set exceeding shared_buffers:
>>
>> * http://tvondra.bitbucket.org/#pgbench-3000-unlogged-sync-skip
>
> I'm not sure how seriously we should take the regressions. I mean,
> what I see there is that CLogControlLock contention goes down by about
> 50% -- which is the point of the patch -- and WALWriteLock contention
> goes up dramatically -- which sucks, but can't really be blamed on the
> patch except in the indirect sense that a backend can't spend much
> time waiting for A if it's already spending all of its time waiting
> for B.
>

Right, I think not only WALWriteLock, but contention on other locks
also goes up as you can see in below table. I think there is nothing
much we can do for that with this patch. One thing which is unclear
is why on unlogged tests it is showing WALWriteLock?

test | clients |
wait_event_type | wait_event | master | granular_locking |
no_content_lock | group_update
--------------------------------------------------+---------+-----------------+----------------------+---------+------------------+-----------------+--------------

pgbench-3000-unlogged-sync-skip | 72 |
LWLockNamed | CLogControlLock | 217012 | 37326 |
32288 | 12040
pgbench-3000-unlogged-sync-skip | 72 |
LWLockNamed | WALWriteLock | 13188 | 104183 |
123359 | 103267
pgbench-3000-unlogged-sync-skip | 72 |
LWLockTranche | buffer_content | 10532 | 65880 |
57007 | 86176
pgbench-3000-unlogged-sync-skip | 72 |
LWLockTranche | wal_insert | 9280 | 85917 |
109472 | 99609
pgbench-3000-unlogged-sync-skip | 72 |
LWLockTranche | clog | 4623 | 25692 |
10422 | 11755

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