Re: Buffers: shared hit/read to shared_buffers dependence

Laurenz, Merlin,

Thanks a lot for your explanations.

>Even if postgres does not cache the table, the o/s will probably
> still cache it assuming it has the memory to do so.

Could you please clarify, do I understand right that there are no way to
determine with 'explain' whether postgres applies to hard drive or OS cache
buffer?

2015-09-09 0:47 GMT+03:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Sat, Sep 5, 2015 at 3:28 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
> > 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.
>
> Yeah. Couple more points:
> *) If your table has an index on it, you can try disabling sequential
> scans temporarily (via set enable_seqscan) in order to get the
> bitmapscan which IIRC does not use ring buffers.
>
> *) for a more robust approach to that, check out the prewarm utility:
> http://www.postgresql.org/docs/9.4/static/pgprewarm.html
>
> *) Even if postgres does not cache the table, the o/s will probably
> still cache it assuming it has the memory to do so. Shared buffers
> are faster than reading from memory cached by the kernel, but that's
> much faster than reading from storage unless your storage is very,
> very fast.
>
> merlin
>