Re: WAL prefetch

On 14.06.2018 09:52, Thomas Munro wrote:
> On Thu, Jun 14, 2018 at 1:09 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> pg_wal_prefetch function will infinitely traverse WAL and prefetch block
>> references in WAL records
>> using posix_fadvise(WILLNEED) system call.
> Hi Konstantin,
>
> Why stop at the page cache... what about shared buffers?
>

It is good question. I thought a lot about prefetching directly to
shared buffers.
But the current c'est la vie with Postgres is that allocating too large
memory for shared buffers is not recommended.
Due to many different reasons: degradation of clock replacement
algorithm, "write storm",...

If your system has 1Tb of memory,  almost none of Postgresql
administrators will recommend to use all this 1Tb for shared buffers.
Moreover there are recommendations to choose shared buffers size based
on size of internal cache of persistent storage device
(so that it will be possible to flush changes without doing writes to
physical media). So at this system with 1Tb of RAM, size of shared
buffers will be most likely set to few hundreds of gigabytes.

Also PostgreSQL is not currently supporting dynamic changing of shared
buffers size. Without it, the only way of using Postgres in clouds and
another multiuser systems where system load is not fully controlled by 
user is to choose relatively small shared buffer size and rely on OS
caching.

Yes, access to shared buffer is about two times faster than reading data
from file system cache.
But it is better, then situation when shared buffers are swapped out and
effect of large shared buffers becomes negative.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company