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>
 <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>
 <3dfb27e5-d3d3-20bb-2b35-3a0e2747625f@2ndquadrant.com>
 <CAA4eK1LOk=6omxO15fhhpaG4iOzdzW+9oMr=fh6nCeD37JvB6g@mail.gmail.com>
 <9e877db7-4dc2-88c1-67ae-034ad1a5cafe@2ndquadrant.com>
 <CA+Tgmoan8OxOfxCqiYr6T_Rc9qPFPBxWukBzi1xbGez1UpXx3Q@mail.gmail.com>
 <a87bfbfb-6511-b559-bab6-5966b7aabb8e@2ndquadrant.com>
 <CAA4eK1K7Jh1GxQeS+9-ZsadZpz+DfiCXVTjqk+X00aCV6gyP0g@mail.gmail.com>
 <26b69fb2-fa4d-530c-7783-1cb9d952c4e5@2ndquadrant.com>
 <781ac43c-da69-f5c7-a828-7b995691d4cc@2ndquadrant.com>
 <CAA4eK1K4HEsy819bkDxA3GxGBRsBvu9MmuGh3Q_CxUho29FG4A@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: <5da94f12-8141-2f2f-016a-09a8e37bdd30@2ndquadrant.com>
Date: Fri, 23 Sep 2016 16:52:40 +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: 
 <CAA4eK1K4HEsy819bkDxA3GxGBRsBvu9MmuGh3Q_CxUho29FG4A@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/23/2016 03:07 PM, Amit Kapila wrote:
> On Fri, Sep 23, 2016 at 6:16 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
>> On 09/23/2016 01:44 AM, Tomas Vondra wrote:
>>>
>>> ...
>>> The 4.5 kernel clearly changed the results significantly:
>>>
>> ...
>>>
>>>
>>> (c) Although it's not visible in the results, 4.5.5 almost perfectly
>>> eliminated the fluctuations in the results. For example when 3.2.80
>>> produced this results (10 runs with the same parameters):
>>>
>>>     12118 11610 27939 11771 18065
>>>     12152 14375 10983 13614 11077
>>>
>>> we get this on 4.5.5
>>>
>>>     37354 37650 37371 37190 37233
>>>     38498 37166 36862 37928 38509
>>>
>>> Notice how much more even the 4.5.5 results are, compared to 3.2.80.
>>>
>>
>> The more I think about these random spikes in pgbench performance on 3.2.80,
>> the more I find them intriguing. Let me show you another example (from
>> Dilip's workload and group-update patch on 64 clients).
>>
>> This is on 3.2.80:
>>
>>   44175  34619  51944  38384  49066
>>   37004  47242  36296  46353  36180
>>
>> and on 4.5.5 it looks like this:
>>
>>   34400  35559  35436  34890  34626
>>   35233  35756  34876  35347  35486
>>
>> So the 4.5.5 results are much more even, but overall clearly below 3.2.80.
>> How does 3.2.80 manage to do ~50k tps in some of the runs? Clearly we
>> randomly do something right, but what is it and why doesn't it happen on the
>> new kernel? And how could we do it every time?
>>
>
> As far as I can see you are using default values of min_wal_size,
> max_wal_size, checkpoint related params, have you changed default
> shared_buffer settings, because that can have a bigger impact.

Huh? Where do you see me using default values? There are settings.log 
with a dump of pg_settings data, and the modified values are

checkpoint_completion_target = 0.9
checkpoint_timeout = 3600
effective_io_concurrency = 32
log_autovacuum_min_duration = 100
log_checkpoints = on
log_line_prefix = %m
log_timezone = UTC
maintenance_work_mem = 524288
max_connections = 300
max_wal_size = 8192
min_wal_size = 1024
shared_buffers = 2097152
synchronous_commit = on
work_mem = 524288

(ignoring some irrelevant stuff like locales, timezone etc.).

> Using default values of mentioned parameters can lead to checkpoints in
> between your runs.

So I'm using 16GB shared buffers (so with scale 300 everything fits into 
shared buffers), min_wal_size=16GB, max_wal_size=128GB, checkpoint 
timeout 1h etc. So no, there are no checkpoints during the 5-minute 
runs, only those triggered explicitly before each run.

> Also, I think instead of 5 mins, read-write runs should be run for 15
> mins to get consistent data.

Where does the inconsistency come from? Lack of warmup? Considering how 
uniform the results from the 10 runs are (at least on 4.5.5), I claim 
this is not an issue.

> For Dilip's workload where he is using only Select ... For Update, i
> think it is okay, but otherwise you need to drop and re-create the
> database between each run, otherwise data bloat could impact the
> readings.

And why should it affect 3.2.80 and 4.5.5 differently?

>
> I think in general, the impact should be same for both the kernels
> because you are using same parameters, but I think if use
> appropriate parameters, then you can get consistent results for
> 3.2.80. I have also seen variation in read-write tests, but the
> variation you are showing is really a matter of concern, because it
> will be difficult to rely on final data.
>

Both kernels use exactly the same parameters (fairly tuned, IMHO).


-- 
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