2015-09-09 17:06 GMT+02:00 Pavel Suderevsky <psuderev...@gmail.com>: > 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? > > You're right.
> 2015-09-09 0:47 GMT+03:00 Merlin Moncure <mmonc...@gmail.com>: > >> On Sat, Sep 5, 2015 at 3:28 PM, Albe Laurenz <laurenz.a...@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 >> > > -- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com
