Re: Speed up Clog Access by increasing CLOG buffers

pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Speed up Clog Access by increasing CLOG buffers
Date: Mon, 31 Oct 2016 22:36:28 +0100
Message-ID: <ca9836a6-3820-ce99-a2c8-853c91e4f896@2ndquadrant.com> (raw)
In-Reply-To: <CAA4eK1KC6uQHWhOMmkoACx1OJeKjcGxMU42WapjFVvN6FFuxJQ@mail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

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

view thread (248+ messages)  latest in thread

Message-ID: <ca9836a6-3820-ce99-a2c8-853c91e4f896@2ndquadrant.com>
Permalink:  ../ca9836a6-3820-ce99-a2c8-853c91e4f896@2ndquadrant.com/
Also on:    postgresql.org/message-id/ca9836a6-3820-ce99-a2c8-853c91e4f896@2ndquadrant.com

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, amit.kapila16@gmail.com, dilipbalaut@gmail.com, robertmhaas@gmail.com, andres@anarazel.de
  Subject: Re: Speed up Clog Access by increasing CLOG buffers
  In-Reply-To: <ca9836a6-3820-ce99-a2c8-853c91e4f896@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox