Re: Buffers: shared hit/read to shared_buffers dependence

Pavel Suderevsky wrote:
> When I have been passing through "Understanding explain" manual (http://www.dalibo.org/_media/understanding_explain.pdf)
> I've faced some strange situation when table with size of 65MB completely placed in cache with shared_buffers=320MB and it doesn't with shared_buffers <= 256MB.
> Actually behaviour of caching in my case is the same with either 256MB or 32MB. Im my mind shared_buffers
> with size of 256MB should be enough for caching table with size of 65MB, but it isn't. Could you please explain such behaviour?
>
> Steps:
>
> understanding_explain=# select pg_size_pretty(pg_relation_size('foo'));
> pg_size_pretty
> ----------------
> 65 MB
> (1 row)

> postgres=# show shared_buffers ;
> shared_buffers
> ----------------
> 320MB
> (1 row)
>

> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------
> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual time=0.786..143.686 rows=1000000 loops=1)
> Buffers: shared read=8334

> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------
> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual time=0.009..83.546 rows=1000000 loops=1)
> Buffers: shared hit=8334

> understanding_explain=# show shared_buffers;
> shared_buffers
> ----------------
> 256MB
> (1 row)
>
> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------
> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual time=0.772..126.242 rows=1000000 loops=1)
> Buffers: shared read=8334

> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------
> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual time=0.029..91.686 rows=1000000 loops=1)
> Buffers: shared hit=32 read=8302

> With every new query execution 32 hits adding to shared hit value.

This must be due to this commit:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d526575f893c1a4e05ebd307e80203536b213a6d

See also src/backend/storage/buffer/README, chapter
"Buffer Ring Replacement Strategy" and the functions initcan() and GetAccessStrategy()
in the source.

Basically, if in a sequential table scan shared_buffers is less than four times the estimated table size,
PostgreSQL will allocate a "ring buffer" of size 256 KB to cache the table data, so that a large sequential scan
does not "blow out" significant parts of the shared cache.
The rationale is that data from a sequential scan will probably not be needed again right away, while
other data in the cache might be hot.

That's what you see in your second example: 32 buffers equals 256 KB, and the ring buffer is chosen from
free buffer pages, so the amount of table data cached increases by 32 buffers every time.

Yours,
Laurenz Albe