Re: Speed up Clog Access by increasing CLOG buffers

On 10/31/2016 08:43 PM, Amit Kapila wrote:
> On Mon, Oct 31, 2016 at 7:58 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> On 10/31/2016 02:51 PM, Amit Kapila wrote:
>> And moreover, this setup (single device for the whole cluster) is very
>> common, we can't just neglect it.
>>
>> But my main point here really is that the trade-off in those cases may not
>> be really all that great, because you get the best performance at 36/72
>> clients, and then the tps drops and variability increases. At least not
>> right now, before tackling contention on the WAL lock (or whatever lock
>> becomes the bottleneck).
>>
>
> Okay, but does wait event results show increase in contention on some
> other locks for pgbench-3000-logged-sync-skip-64? Can you share wait
> events for the runs where there is a fluctuation?
>

Sure, I do have wait event stats, including a summary for different
client counts - see this:

http://tvondra.bitbucket.org/by-test/pgbench-3000-logged-sync-skip-64.txt

Looking only at group_update patch for three interesting client counts,
it looks like this:

wait_event_type | wait_event | 108 144 180
-----------------+-------------------+-------------------------
LWLockNamed | WALWriteLock | 661284 847057 1006061
| | 126654 191506 265386
Client | ClientRead | 37273 52791 64799
LWLockTranche | wal_insert | 28394 51893 79932
LWLockNamed | CLogControlLock | 7766 14913 23138
LWLockNamed | WALBufMappingLock | 3615 3739 3803
LWLockNamed | ProcArrayLock | 913 1776 2685
Lock | extend | 909 2082 2228
LWLockNamed | XidGenLock | 301 349 675
LWLockTranche | clog | 173 331 607
LWLockTranche | buffer_content | 163 468 737
LWLockTranche | lock_manager | 88 140 145

Compared to master, this shows significant reduction of contention on
CLogControlLock (which on master has 20k, 83k and 200k samples), and
moving the contention to WALWriteLock.

But perhaps you're asking about variability during the benchmark? I
suppose that could be extracted from the collected data, but I haven't
done that.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services