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

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: increasing shared buffers: how much should be removed from OS filesystem cache?
Date: 2006-09-01 20:02:41
Message-ID: 3743D7B4-2749-41C3-ADE1-39B86F7C98D4@fastcrypt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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
>

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message weisong44@gmail.com 2006-09-01 21:56:20 Hanging queries on Windows 2003 SP1
Previous Message Scott Marlowe 2006-09-01 20:01:42 Re: database bloat,non removovable rows, slow query