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

Guillaume

1G is really not a significant amount of memory these days,

That said 6-10% of available memory should be given to an 8.0 or
older version of postgresql

Newer versions work better around 25%

I'm not sure what you mean by mechanically removed from effective_cache

effective cache is really a representation of shared buffers plus OS
cache

Dave
On 1-Sep-06, at 1:00 PM, Guillaume Cottenceau wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>