Re: WAL prefetch - Mailing list pgsql-hackers

From Konstantin Knizhnik
Subject Re: WAL prefetch
Date
Msg-id 3c638976-a991-5fe2-ff89-d61324d33dd4@postgrespro.ru
Whole thread Raw
In response to Re: WAL prefetch  (Thomas Munro <thomas.munro@enterprisedb.com>)
Responses Re: WAL prefetch
List pgsql-hackers

On 14.06.2018 09:52, Thomas Munro wrote:
> On Thu, Jun 14, 2018 at 1:09 AM, Konstantin Knizhnik
> <k.knizhnik@postgrespro.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



pgsql-hackers by date:

Previous
From: Andrew Gierth
Date:
Subject: Re: ntile() throws ERROR when hashagg is false
Next
From: Amit Langote
Date:
Subject: Re: why partition pruning doesn't work?