Re: checkpointer continuous flushing

On Tue, Jun 23, 2015 at 10:29 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:

>
> Hello Amit,
>
> medium2: scale=300 shared_buffers=5GB checkpoint_timeout=30min
>>> max_wal_size=4GB warmup=1200 time=7500
>>>
>>> flsh | full speed tps | percent of late tx, 4 clients
>>> /srt | 1 client | 4 clients | 100 | 200 | 400 |
>>> N/N | 173 +- 289* | 198 +- 531* | 27.61 | 43.92 | 61.16 |
>>> N/Y | 458 +- 327* | 743 +- 920* | 7.05 | 14.24 | 24.07 |
>>> Y/N | 169 +- 166* | 187 +- 302* | 4.01 | 39.84 | 65.70 |
>>> Y/Y | 546 +- 143 | 681 +- 459 | 1.55 | 3.51 | 2.84 |
>>>
>>> The effect of sorting is very positive (+150% to 270% tps). On this run,
>>>
>> flushing has a positive (+20% with 1 client) or negative (-8 % with 4
>> clients) on throughput, and late transactions are reduced by 92-95% when
>> both options are activated.
>>
>> Why there is dip in performance with multiple clients,
>>
>
> I'm not sure to see the "dip". The performances are better with 4 clients
> compared to 1 client?
>

What do you mean by "negative (-8 % with 4 clients) on throughput"
in above sentence? I thought by that you mean that there is dip
in TPS with patch as compare to HEAD at 4 clients.

Also I am not completely sure what's +- means in your data above?

> can it be due to reason that we started doing more stuff after holding
>> bufhdr lock in below code?
>>
>
> I think it is very unlikely that the buffer being locked would be
> simultaneously requested by one of the 4 clients for an UPDATE, so I do not
> think it should have a significant impact.
>
> BufferSync() [...]
>>
>
> BufferSync()
>> {
>> ..
>> - buf_id = StrategySyncStart(NULL, NULL);
>> - num_to_scan = NBuffers;
>> + active_spaces = nb_spaces;
>> + space = 0;
>> num_written = 0;
>> - while (num_to_scan-- > 0)
>> +
>> + while (active_spaces != 0)
>> ..
>> }
>>
>> The changed code doesn't seems to give any consideration to
>> clock-sweep point
>>
>
> Indeed.
>
> which might not be helpful for cases when checkpoint could have flushed
>> soon-to-be-recycled buffers. I think flushing the sorted buffers w.r.t
>> tablespaces is a good idea, but not giving any preference to clock-sweep
>> point seems to me that we would loose in some cases by this new change.
>>
>
> I do not see how to do both, as these two orders seem more or less
> unrelated?

I understand your point and I also don't have any specific answer
for it at this moment, the point of worry is that it should not lead
to degradation of certain cases as compare to current algorithm.
The workload where it could effect is when your data doesn't fit
in shared buffers, but can fit in RAM.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com