Subject: Re: Speed up Clog Access by increasing CLOG buffers
To: Amit Kapila <amit.kapila16@gmail.com>
References: 
 <CAA4eK1+8=X9mSNeVeHg_NqMsOR-XKsjuqrYzQf=iCsdh3U4EOA@mail.gmail.com>
 <09275f4d-f78d-dd69-22de-57c784dc410e@2ndquadrant.com>
 <CAFiTN-uQ+Jbd31cXvRbj48Ba6TqDUDpLKSPnsUCCYRju0Y0U8Q@mail.gmail.com>
 <CA+TgmobjO01bJ2caVWOzU=PuoKfx1X6epGokhHD8fc9hVwkhKw@mail.gmail.com>
 <CAFiTN-taV4iVkPHrxg=YCicKjBS6=QZm_cM4hbS_2q2ryLhUUw@mail.gmail.com>
 <acf46406-cf6d-41fe-4118-4e1c960b4790@2ndquadrant.com>
 <CAFiTN-tCqDucbikxue0x0dwNOAa6yzjMxJNgXGQm90jVVRSj0Q@mail.gmail.com>
 <CAFiTN-t15PjFTFQH0fBfM5jVSv6rfm5J_8vj=RGuKeFpdLgSoQ@mail.gmail.com>
 <CAA4eK1LyR2A+m=RBSZ6rcPEwJ=rVi1ADPSndXHZdjn56yqO6Vg@mail.gmail.com>
 <4a52a34f-57fa-7bcf-d34c-c15db40f0361@2ndquadrant.com>
 <CAA4eK1KTbNbZSDo=6k4YgaJh_FM20zJCKu2Yt0bxaFMv9QcSXQ@mail.gmail.com>
 <b3586234-6c80-5b64-1261-871e0e852bbb@2ndquadrant.com>
 <CAA4eK1Ksd6D0H9HPmMS3S7UpL2G8JMJ0kvRCDz=4=AqFn790sg@mail.gmail.com>
 <8efd9956-059a-78f3-32ff-f1e1a4dd09c8@2ndquadrant.com>
 <CAA4eK1KC6uQHWhOMmkoACx1OJeKjcGxMU42WapjFVvN6FFuxJQ@mail.gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
 Andres Freund <andres@anarazel.de>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <ca9836a6-3820-ce99-a2c8-853c91e4f896@2ndquadrant.com>
Date: Mon, 31 Oct 2016 22:36:28 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CAA4eK1KC6uQHWhOMmkoACx1OJeKjcGxMU42WapjFVvN6FFuxJQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 10/31/2016 08:43 PM, Amit Kapila wrote:
> On Mon, Oct 31, 2016 at 7:58 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers