To: pgsql-performance@postgresql.org
Subject: increasing shared buffers: how much should be removed from OS
	filesystem cache?
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 01 Sep 2006 19:00:52 +0200
Message-ID: <87veo7a5dn.fsf@meuh.mnc.lan>
Lines: 35
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi,

I've been looking at the results from the pg_statio* tables, to
view the impact of increasing the shared buffers to increase
performance.

As expected, increasing from the default by a factor of 10~20
moves table/index disk blocks reads to cache hits, but the
overall service time of my test page is not changed (I'm testing
with a set of queries implying an increase of 170,000 of
sum(heap_blks_hit) and 2,000 of sum(idx_blks_hit) from
pg_statio_user_tables).

I've seen that documentation says:

    data that is not in the PostgreSQL buffer cache may still
    reside in the kernel's I/O cache, and may therefore still be
    fetched without requiring a physical read

I guess this is the best explanation (btw, my test machine runs
Linux 2.6 on 1G of RAM), but I'm still wondering what should be
expected from moving caching from OS filesystem to PG - probably
PG can "cleverly" flush its cache when it is full (e.g. table
data before index data maybe?), whereas the OS will do it
"blindly", but I'm wondering about the limits of this behaviour,
particularly considering that being "very clever" about cache
flush would probably need realtime query statistics which I am
not sure PG does.

After all, memory added to shared buffers should be mecanically
removed from effective cache size (or others), so I cannot just
increase it until the OS cannot cache anymore :)

-- 
Guillaume Cottenceau