Re: Proposal of PITR performance improvement for 8.4.

"Koichi Suzuki" <koichi(dot)szk(at)gmail(dot)com> writes:

> This is my first proposal of PITR performance improvement for
> PostgreSQL 8.4 development. This proposal includes readahead
> mechanism of data pages which will be read by redo() routines in the
> recovery. This is especially effective in the recovery without full
> page write. Readahead is done by posix_fadvise() as proposed in
> index scan improvement.

Incidentally: a bit of background for anyone who wasn't around when last this
came up: prefetching is especially for our recovery code because it's
single-threaded. If you have a raid array you're effectively limited to using
a single drive at a time. This is a major problem because the logs could have
been written by many processes hammering the raid array concurrently. In other
words your warm standby database might not be able to keep up with the logs
from the master database even on identical (or even better) hardware.

Simon (I think?) proposed allowing our recovery code to be multi-threaded.
Heikki suggested using prefetching.

> Details of the implementation will be found in README file in the material.

I've read through this and I think I disagree with the idea of using a
separate program. It's a lot of extra code -- and duplicated code from the
normal recovery too.

I recognise that it's awkward to handle during recovery since you would have
to rework the wal reading logic quite a bit.

But it might be worth doing anyways for non-raid situations. Even if you don't
have a raid array it would be worthwhile to do the equivalent of a bitmap heap
scan by fetching blocks in order.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!