Re: Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

On Thu, Jul 2, 2015 at 4:35 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Jul 2, 2015 at 4:16 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>
>> On 13 May 2015 at 09:35, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>>
>>>
>>> In summary, the X^1.5 correction seems to work pretty well. It doesn't
completely eliminate the problem, but it makes it a lot better.
>>
>>
>> Agreed
>
>
> Do we want to consider if wal_compression is enabled as that
> can reduce the effect full_page_writes?
>
>
> Also I am planning to run some tests for this patch, but not sure
> if tps and or latency numbers by pgbench are sufficient or do you
> people want to see actual read/write count via some form of
> dynamic tracing (stap) as done by the reporter of this issue?
>

I have ran some tests with this patch and the detailed results of the runs
are
attached with this mail. I thought the patch should show difference if I
keep
max_wal_size to somewhat lower or moderate value so that checkpoint
should get triggered due to wal size, but I am not seeing any major
difference
in the writes spreading. Below is the configuration, I have used to run the
tests

synchronous_commit=off
checkpoint_timeout = 5min
max_wal_size=1GB
min_wal_size=80MB
checkpoint_completion_target=0.9
shared_buffers=8GB

Test (used almost similar testing mechanism as reported in mail)
------------------------------------------------------------------------------------------------
1.
Use the test table and data:
create table tbl(id int primary key,info text,crt_time timestamp);
insert into tbl select generate_series(1,50000000),now(),now();

2.
Use pgbench to test it.
$ vi test.sql
\setrandom id 1 50000000
update tbl set crt_time=now() where id = :id ;

3.
$ pgbench -M prepared -n -r -f ./test.sql -P 1 -c 28 -j 28 -T 300

on another session, I ran system tap script
stap pgbenchrun.stp -DMAXSKIPPED=100000 -o pgbenchrun.out

pgbenchrun.stp (attached with mail).

During configure, you need to use (--enable-dtrace) and need to use
debug-build option if you want capture results using system tap script.
Also to run this SystemTap should be installed in your system.

I should have ran more tests to see in which cases this patch makes
difference, but I thought it is better to first discuss the current results
and see what other kind of tests you want to run for this patch.

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