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>
 <20150903114137.GE27649@awork2.anarazel.de>
 <CAA4eK1LMMGNQ439BUm0LcS3p0sb8S9kc-cUGU_ThNqMwA8_Tug@mail.gmail.com>
 <CAA4eK1+-=18HOrdqtLXqOMwZDbC_15WTyHiFruz7BvVArZPaAw@mail.gmail.com>
 <20160330230914.GH13305@awork2.anarazel.de>
 <CAA4eK1L2KO18G5-ajNitztnmn1G_Ex1N4oUHhF0Xx3QrY57Ufw@mail.gmail.com>
 <20160408153054.hgubabeycowlu3tc@alap3.anarazel.de>
 <CAA4eK1+6URLZYM+xjnvc027+mqQztWc8VDm0udY2X+bdC2HP+A@mail.gmail.com>
 <8bab43dd-bc76-0c41-950e-8101269dbf4b@2ndquadrant.com>
 <CAA4eK1JJOWO6u99gUMMQWELXbci428vRBbJnxg=V04NuZwBCSw@mail.gmail.com>
 <CAFiTN-u3=XUi7z8dTOgxZ98E7gL1tzL=q9Yd=CwWCtTtS6pOZw@mail.gmail.com>
 <CA+TgmoYO1wNi7dRwNxGUupmMPhOkae-pmU38ndC1s6FDQ-USJg@mail.gmail.com>
 <0733fb9c-c3dc-fe30-1ad1-8c3ddf05c92d@2ndquadrant.com>
 <CAA4eK1J85_243PcJbG-AJoRq3wwZcc4xvMJEQ605Yz+1TXJpow@mail.gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>,
 Andres Freund <andres@anarazel.de>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <3dfb27e5-d3d3-20bb-2b35-3a0e2747625f@2ndquadrant.com>
Date: Sat, 17 Sep 2016 19:55:44 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CAA4eK1J85_243PcJbG-AJoRq3wwZcc4xvMJEQ605Yz+1TXJpow@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 09/17/2016 07:05 AM, Amit Kapila wrote:
> On Sat, Sep 17, 2016 at 9:17 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
>> On 09/14/2016 05:29 PM, Robert Haas wrote:
...
>>> Sure, but you're testing at *really* high client counts here.
>>> Almost nobody is going to benefit from a 5% improvement at 256
>>> clients. You need to test 64 clients and 32 clients and 16
>>> clients and 8 clients and see what happens there. Those cases are
>>> a lot more likely than these stratospheric client counts.
>>>
>>
>> Right. My impression from the discussion so far is that the patches
>> only improve performance with very many concurrent clients - but as
>> Robert points out, almost no one is running with 256 active
>> clients, unless they have 128 cores or so. At least not if they
>> value latency more than throughput.
>>
>
> See, I am also not in favor of going with any of these patches, if
> they doesn't help in reduction of contention. However, I think it is
> important to understand, under what kind of workload and which
> environment it can show the benefit or regression whichever is
> applicable.

Sure. Which is why I initially asked what type of workload should I be 
testing, and then done the testing with multiple savepoints as that's 
what you suggested. But apparently that's not a workload that could 
benefit from this patch, so I'm a bit confused.

> Just FYI, couple of days back one of EDB's partner who was doing the
> performance tests by using HammerDB (which is again OLTP focussed
> workload) on 9.5 based code has found that CLogControlLock has the
> significantly high contention. They were using synchronous_commit=off
> in their settings. Now, it is quite possible that with improvements
> done in 9.6, the contention they are seeing will be eliminated, but
> we have yet to figure that out. I just shared this information to you
> with the intention that this seems to be a real problem and we should
> try to work on it unless we are able to convince ourselves that this
> is not a problem.
>

So, can we approach the problem from this direction instead? That is, 
instead of looking for workloads that might benefit from the patches, 
look at real-world examples of CLOG lock contention and then evaluate 
the impact on those?

Extracting the workload from benchmarks probably is not ideal, but it's 
still better than constructing the workload on our own to fit the patch.

FWIW I'll do a simple pgbench test - first with synchronous_commit=on 
and then with synchronous_commit=off. Probably the workloads we should 
have started with anyway, I guess.

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