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

Поиск
Список
Период
Сортировка
От Guillaume Cottenceau
Тема increasing shared buffers: how much should be removed from OS filesystem cache?
Дата
Msg-id 87veo7a5dn.fsf@meuh.mnc.lan
обсуждение исходный текст
Ответы Re: increasing shared buffers: how much should be removed from OS filesystem cache?  ("Merlin Moncure" <mmoncure@gmail.com>)
Re: increasing shared buffers: how much should be removed from OS filesystem cache?  (Dave Cramer <pg@fastcrypt.com>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Markus Schaber
Дата:
Сообщение: Re: database bloat,non removovable rows, slow query etc...
Следующее
От: Matteo Sgalaberni
Дата:
Сообщение: Re: database bloat,non removovable rows, slow query etc...