RE: Make ringbuffer threshold and ringbuffer sizes configurable?

From: Andres Freund <andres(at)anarazel(dot)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 to read large tables sequentially multiple times, the second and subsequent reads didn't get the benefit of caching. another example 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 just the 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 specify CACHE, 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 reads or 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 most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute 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 the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.

CACHE READS

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

Regards
Takayuki Tsunakawa