From: Andres Freund <and...@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 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
