On 2018-06-19 12:08:27 +0300, Konstantin Knizhnik wrote: > I do not think that prefetching in shared buffers requires much more efforts > and make patch more envasive... > It even somehow simplify it, because there is no to maintain own cache of > prefetched pages...
> But it will definitely have much more impact on Postgres performance: > contention for buffer locks, throwing away pages accessed by read-only > queries,... These arguments seem bogus to me. Otherwise the startup process is going to do that work. > Also there are two points which makes prefetching into shared buffers more > complex: > 1. Need to spawn multiple workers to make prefetch in parallel and somehow > distribute work between them. I'm not even convinced that's true. It doesn't seem insane to have a queue of, say, 128 requests that are done with posix_fadvise WILLNEED, where the oldest requests is read into shared buffers by the prefetcher. And then discarded from the page cache with WONTNEED. I think we're going to want a queue that's sorted in the prefetch process anyway, because there's a high likelihood that we'll otherwise issue prfetch requets for the same pages over and over again. That gets rid of most of the disadvantages: We have backpressure (because the read into shared buffers will block if not yet ready), we'll prevent double buffering, we'll prevent the startup process from doing the victim buffer search. > Concerning WAL perfetch I still have a serious doubt if it is needed at all: > if checkpoint interval is less than size of free memory at the system, then > redo process should not read much. I'm confused. Didn't you propose this? FWIW, there's a significant number of installations where people have observed this problem in practice. > And if checkpoint interval is much larger than OS cache (are there cases > when it is really needed?) Yes, there are. Percentage of FPWs can cause serious problems, as do repeated writouts by the checkpointer. > then quite small patch (as it seems to me now) forcing full page write > when distance between page LSN and current WAL insertion point exceeds > some threshold should eliminate random reads also in this case. I'm pretty sure that that'll hurt a significant number of installations, that set the timeout high, just so they can avoid FPWs. Greetings, Andres Freund
