Re: V2 of PITR performance improvement for 8.4

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.

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?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support