increasing shared buffers: how much should be removed from OS filesystem cache?

From: Guillaume Cottenceau <gc(at)mnc(dot)ch>
To: pgsql-performance(at)postgresql(dot)org
Subject: increasing shared buffers: how much should be removed from OS filesystem cache?
Date: 2006-09-01 17:00:52
Message-ID: 87veo7a5dn.fsf@meuh.mnc.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matteo Sgalaberni 2006-09-01 17:28:29 Re: database bloat,non removovable rows, slow query etc...
Previous Message Markus Schaber 2006-09-01 16:09:31 Re: database bloat,non removovable rows, slow query etc...