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.comPostgreSQL Training, Services and Support