Re: V2 of PITR performance improvement for 8.4

Hi,

As to checkpoint timeout, yes, this measurement is hard for FPW=on
case. I'll do the similar measurement for checkpoint timeout = 5min
and post the result. I expect that the recoevry time will be almost
the same in the case FPW=on, lesslog=yes and prefetpch = yes.

2008/12/2 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>
> On Thu, 2008-11-27 at 21:04 +0900, Koichi Suzuki wrote:
>
>> We ran the
>> benchmark for on hour with chekpoint timeout 30min and completion_target 0.5.
>> Then, collected all the archive log and run PITR.
>
>> ----------------------+------------+--------------------+---------------
>> WAL conditions | Recovery | Amount of | recovery
>> | time (sec) | physical read (MB) | rate (TX/min)
>> ----------------------+------------+--------------------+---------------
>> w/o prefetch | | |
>> archived with cp | 6,611 | 5,435 | 402
>> FPW=off | | |
>> ----------------------+------------+--------------------+---------------
>> With prefetch | | |
>> archived with cp | 1,161 | 5,543 | 2,290
>> FPW=off | | |
>> ----------------------+------------+--------------------+---------------
>
> There's clearly a huge gain using prefetch, when we have
> full_page_writes = off. But that does make me think: Why do we need
> prefetch at all if we use full page writes? There's nothing to prefetch
> if we can keep it in cache.

Agreed. This is why I proposed prefetch optional through GUC.

>
> I notice we set the checkpoint_timeout to 30 mins, which is long enough
> to exceed the cache on the standby. I wonder if we reduced the timeout
> would we use the cache better on the standby and not need readahead at
> all? Do you have any results to examine cache overflow/shorter timeouts?
>
>> w/o prefetch | | |
>> archived with cp | 1,683 | 801 | 1,458
>> FPW=on | | | (8.3)
>> ----------------------+------------+--------------------+---------------
>> w/o prefetch | | |
>> archived with lesslog | 6,644 | 5,090 | 369
>> FPW=on | | |
>> ----------------------+------------+--------------------+---------------
>> With prefetch | | |
>> archived with cp | 1,415 | 2,157 | 1,733
>> FPW=on | | |
>> ----------------------+------------+--------------------+---------------
>> With prefetch | | |
>> archived with lesslog | 1,196 | 5,369 | 2,051
>> FPW=on | | | (This proposal)
>> ----------------------+------------+--------------------+---------------
>
> So I'm wondering if we only need prefetch because we're using lesslog?
>
> If we integrated lesslog better into the new replication would we be
> able to forget about doing the prefetch altogether?

In the case of lesslog, almost all the FPW is replaced with
corresponding incremental log and recovery takes longer. Prefetch
dramatically improve this, as you will see in the above result. To
improve recovery time with FPW=off or FPW=on and lesslog=yes, we need
prefetch.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>

--
------
Koichi Suzuki