Buffers: shared hit/read to shared_buffers dependence

From: Pavel Suderevsky <psuderevsky(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Buffers: shared hit/read to shared_buffers dependence
Date: 2015-09-04 11:45:26
Message-ID: CAEBTBzuLjELRBoJQgGth+VSMn_U10H9r9ZMqnHu1kYSBnkCMwg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

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)

postgres=# \c understanding_explain
You are now connected to database "understanding_explain" as user
"postgres".
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
Planning time: 3.796 ms
Execution time: 195.557 ms
(4 rows)

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
Planning time: 0.029 ms
Execution time: 129.499 ms
(4 rows)

=============================================================================================================================
[root(at)dbtest3 ~]# systemctl stop postgres
[root(at)dbtest3 ~]# sync
[root(at)dbtest3 ~]# echo 3 > /proc/sys/vm/drop_caches
[root(at)dbtest3 ~]# systemctl start postgres
=============================================================================================================================

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
Planning time: 5.164 ms
Execution time: 181.306 ms
(4 rows)

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
Planning time: 0.025 ms
Execution time: 136.584 ms
(4 rows)

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ray Stell 2015-09-04 13:46:48 Re: bdr admin role
Previous Message Albe Laurenz 2015-09-04 09:45:29 Re: Postgresql C extension and SIGSEGV