Re: Buffers: shared hit/read to shared_buffers dependence - Mailing list pgsql-general
From | Albe Laurenz |
---|---|
Subject | Re: Buffers: shared hit/read to shared_buffers dependence |
Date | |
Msg-id | A737B7A37273E048B164557ADEF4A58B50FA0E58@ntex2010i.host.magwien.gv.at Whole thread Raw |
In response to | Buffers: shared hit/read to shared_buffers dependence (Pavel Suderevsky <psuderevsky@gmail.com>) |
Responses |
Re: Buffers: shared hit/read to shared_buffers dependence
|
List | pgsql-general |
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 andit 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
pgsql-general by date: