Re: Buffers: shared hit/read to shared_buffers dependence - Mailing list pgsql-general

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@gmail.com>:

On Sat, Sep 5, 2015 at 3:28 PM, Albe Laurenz <laurenz.albe@wien.gv.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