Re: Read-ahead and parallelism in redo recovery

["Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>]

Decibel! wrote:
> On Feb 29, 2008, at 8:10 AM, Florian Weimer wrote:
> > In the end, I wouldn't be surprised if for most loads, cache warming
> > effects dominated recovery times, at least when the machine is not
> > starved on RAM.
>
>
> Uh... that's exactly what all the synchronous reads are doing... warming 
> the cache. And synchronous reads are only fast if the system understands 
> what's going on and reads a good chunk of data in at once. I don't know 
> that that happens.
>
> Perhaps a good short-term measure would be to have recovery allocate a 
> 16M buffer and read in entire xlog files at once.

The problem isn't reading the WAL. The OS prefetches that just fine.

The problem is the random reads, when we read in the blocks mentioned in 
the WAL records, to replay the changes to them. The OS has no way of 
guessing and prefetching those blocks, and we read them synchronously, 
one block at a time, no matter how big your RAID array is.

I used to think it's a big problem, but I believe the full-page-write 
optimization in 8.3 made it much less so. Especially with the smoothed 
checkpoints: as checkpoints have less impact on response times, you can 
shorten checkpoint interval, which helps to keep the recovery time 
reasonable.

It'd still be nice to do the prefetching; I'm sure there's still 
workloads where it would be a big benefit. But as Tom pointed out, we 
shouldn't invent something new just for recovery. I think we should look 
at doing prefetching for index accesses etc. first, and once we have the 
infrastructure in place and tested, we can consider use it for recovery 
as well.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com