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 14:32:19 +0100
Message-ID: <5960ada5-98f5-dacf-903f-6e153aed76ce@2ndquadrant.com> (raw)
In-Reply-To: <b3586234-6c80-5b64-1261-871e0e852bbb@2ndquadrant.com>
References: <CAA4eK1+8=X9mSNeVeHg_NqMsOR-XKsjuqrYzQf=iCsdh3U4EOA@mail.gmail.com>
	<CAA4eK1JiOOKN73yC3rHqF_F+yKEFfn15Gh9iitT-=7Q5BuCs=Q@mail.gmail.com>
	<65f47a46-f1b2-aee1-d56d-b67de49e3a3b@2ndquadrant.com>
	<CAA4eK1JPVwPW0X8Ss+Rz+VQcPTYxCMGQuHEHfOcCTOtGqE_=ZA@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

On 10/30/2016 07:32 PM, Tomas Vondra wrote:
> Hi,
>
> On 10/27/2016 01:44 PM, Amit Kapila wrote:
>> On Thu, Oct 27, 2016 at 4:15 AM, Tomas Vondra
>> <tomas.vondra@2ndquadrant.com> wrote:
>>>
>>> FWIW I plan to run the same test with logged tables - if it shows
>>> similar
>>> regression, I'll be much more worried, because that's a fairly typical
>>> scenario (logged tables, data set > shared buffers), and we surely can't
>>> just go and break that.
>>>
>>
>> Sure, please do those tests.
>>
>
> OK, so I do have results for those tests - that is, scale 3000 with
> shared_buffers=16GB (so continuously writing out dirty buffers). The
> following reports show the results slightly differently - all three "tps
> charts" next to each other, then the speedup charts and tables.
>
> Overall, the results are surprisingly positive - look at these results
> (all ending with "-retest"):
>
> [1] http://tvondra.bitbucket.org/index2.html#dilip-3000-logged-sync-retest
>
> [2]
> http://tvondra.bitbucket.org/index2.html#pgbench-3000-logged-sync-noskip-retest
>
>
> [3]
> http://tvondra.bitbucket.org/index2.html#pgbench-3000-logged-sync-skip-retest
>
>
> All three show significant improvement, even with fairly low client
> counts. For example with 72 clients, the tps improves 20%, without
> significantly affecting variability variability of the results( measured
> as stdddev, more on this later).
>
> It's however interesting that "no_content_lock" is almost exactly the
> same as master, while the other two cases improve significantly.
>
> The other interesting thing is that "pgbench -N" [3] shows no such
> improvement, unlike regular pgbench and Dilip's workload. Not sure why,
> though - I'd expect to see significant improvement in this case.
>
> I have also repeated those tests with clog buffers increased to 512 (so
> 4x the current maximum of 128). I only have results for Dilip's workload
> and "pgbench -N":
>
> [4]
> http://tvondra.bitbucket.org/index2.html#dilip-3000-logged-sync-retest-512
>
> [5]
> http://tvondra.bitbucket.org/index2.html#pgbench-3000-logged-sync-skip-retest-512
>
>
> The results are somewhat surprising, I guess, because the effect is
> wildly different for each workload.
>
> For Dilip's workload increasing clog buffers to 512 pretty much
> eliminates all benefits of the patches. For example with 288 client,
> the group_update patch gives ~60k tps on 128 buffers [1] but only 42k
> tps on 512 buffers [4].
>
> With "pgbench -N", the effect is exactly the opposite - while with
> 128 buffers there was pretty much no benefit from any of the patches
> [3], with 512 buffers we suddenly get almost 2x the throughput, but
> only for group_update and master (while the other two patches show no
> improvement at all).
>

The remaining benchmark with 512 clog buffers completed, and the impact 
roughly matches Dilip's benchmark - that is, increasing the number of 
clog buffers eliminates all positive impact of the patches observed on 
128 buffers. Compare these two reports:

[a] http://tvondra.bitbucket.org/#pgbench-3000-logged-sync-noskip-retest

[b] http://tvondra.bitbucket.org/#pgbench-3000-logged-sync-noskip-retest-512

With 128 buffers the group_update and granular_locking patches achieve 
up to 50k tps, while master and no_content_lock do ~30k tps. After 
increasing number of clog buffers, we get only ~30k in all cases.

I'm not sure what's causing this, whether we're hitting limits of the 
simple LRU cache used for clog buffers, or something else. But maybe 
there's something in the design of clog buffers that make them work less 
efficiently with more clog buffers? I'm not sure whether that's 
something we need to fix before eventually committing any of them.

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: <5960ada5-98f5-dacf-903f-6e153aed76ce@2ndquadrant.com>
Permalink:  ../5960ada5-98f5-dacf-903f-6e153aed76ce@2ndquadrant.com/
Also on:    postgresql.org/message-id/5960ada5-98f5-dacf-903f-6e153aed76ce@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: <5960ada5-98f5-dacf-903f-6e153aed76ce@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