RE: Make ringbuffer threshold and ringbuffer sizes configurable? - Mailing list pgsql-hackers

From: Andres Freund <andres@anarazel.de>
> While I think there's a number of improvements[2] we could make to the
> ringbuffer logic, I think we should also just allow to make them
> configurable.  I think that'll allow a decent number of systems perform
> better (especially on slightly bigger systems the the current
> ringbuffers are *way* too small) , make the thresholds more discoverable
> (e.g. the NBuffers / 4 threshold is very confusing), and will make it
> easier to experiment with better default values.

+1
The NBuffers / 4 logic sometimes caused unexpected behavior.  IIRC, even when some batch or analytic processing needed
toread large tables sequentially multiple times, the second and subsequent reads didn't get the benefit of caching.
anotherexample is that before pg_prewarm became available, I couldn't cache the entire table by running "SELECT * from
table"before benchmarking performance. 


> I think it would make sense to have seqscan_ringbuffer_threshold,
> {bulkread,bulkwrite,vacuum}_ringbuffer_size. I think they often sensibly
> are set in proportion of shared_buffers, so I suggest defining them as
> floats, where negative values divide shared_buffers, whereas positive
> values are absolute sizes, and 0 disables the use of ringbuffers.
>
> I.e. to maintain the current defaults, seqscan_ringbuffer_threshold
> would be -4.0, but could be also be set to an absolute 4GB (converted to
> pages). Probably would want a GUC show function that displays
> proportional values in a nice way.

I think per-table reloption is necessary as well as or instead of GUC, because the need for caching depends on the
table(see below for Oracle's manual.) 

I'm afraid it would be confusing for a user-settable parameter to have different units (percent and size).  I think
justthe positive percentage would suffice. 


CREATE TABLE

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6
--------------------------------------------------
CACHE | NOCACHE | CACHE READS

Use these clauses to indicate how Oracle Database should store blocks in the buffer cache. For LOB storage, you can
specifyCACHE, NOCACHE, or CACHE READS. For other types of storage, you can specify only CACHE or NOCACHE.  

The behavior of CACHE and NOCACHE described in this section does not apply when Oracle Database chooses to use direct
readsor to perform table scans using parallel query.  

CACHE

For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the
mostrecently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed.
Thisattribute is useful for small lookup tables. 

NOCACHE

For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at
theleast recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the
defaultfor LOB storage.  

CACHE READS

CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during
readoperations but not during write operations.  
--------------------------------------------------


Regards
Takayuki Tsunakawa