Thread: increasing shared buffers: how much should be removed from OS filesystem cache?
increasing shared buffers: how much should be removed from OS filesystem cache?
From
Guillaume Cottenceau
Date:
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
Re: increasing shared buffers: how much should be removed from OS filesystem cache?
From
"Merlin Moncure"
Date:
On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau <gc@mnc.ch> 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. > I think 'shared buffers' is one of the most overrated settings from a performance standpoint. however you must ensure there is enough for things the server does besides caching. It used to be a bigger deal than it is in modern versionf of postgresql modern operating systems. merlin
Re: increasing shared buffers: how much should be removed from OS filesystem cache?
From
Dave Cramer
Date:
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 >
Re: increasing shared buffers: how much should be removed from OS filesystem cache?
From
Dave Cramer
Date:
On 1-Sep-06, at 3:49 PM, Merlin Moncure wrote: > On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau <gc@mnc.ch> 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. >> > > I think 'shared buffers' is one of the most overrated settings from a > performance standpoint. however you must ensure there is enough for > things the server does besides caching. It used to be a bigger deal > than it is in modern versionf of postgresql modern operating systems. > > merlin > So if shared buffers is the most overrated, what do you consider the proper way of tuning ? > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
>>
>> I think 'shared buffers' is one of the most overrated settings from a
>> performance standpoint. however you must ensure there is enough for
>> things the server does besides caching. It used to be a bigger deal
>> than it is in modern versionf of postgresql modern operating systems.
Previous to 8.1 I would agree with you, but as of 8.1 it is probably the
most underrated.
Joshua D. Drake
>>
>> merlin
>>
> So if shared buffers is the most overrated, what do you consider the
> proper way of tuning ?
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Re: increasing shared buffers: how much should be removed from OS filesystem cache?
From
Guillaume Cottenceau
Date:
Dave Cramer <pg 'at' fastcrypt.com> writes:
> Guillaume
>
> 1G is really not a significant amount of memory these days,
Yeah though we have 2G or 4G of RAM in our servers (and not only
postgres running on it).
> 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
I mean that when you allocate more memory to applications, the
consequence is less memory the OS will be able to use for disk
cache.
> effective cache is really a representation of shared buffers plus OS
> cache
Are you sure the shared buffers should be counted in? As I
understand the documentation, they should not (as shared buffers
is allocated memory for the OS, not part of "kernel's disk
cache"):
Sets the planner's assumption about the effective size of the
disk cache (that is, the portion of the kernel's disk cache
that will be used for PostgreSQL data files). This is
measured in disk pages, which are normally 8192 bytes each.
The default is 1000.
--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
Re: increasing shared buffers: how much should be removed from OS filesystem cache?
From
Guillaume Cottenceau
Date:
"Merlin Moncure" <mmoncure 'at' gmail.com> writes: > On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau <gc@mnc.ch> 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. > > > > I think 'shared buffers' is one of the most overrated settings from a > performance standpoint. however you must ensure there is enough for > things the server does besides caching. It used to be a bigger deal "Beside caching".. It's unfornatunate that the documentation on pg.org is very vague about the actual use(s) of the shared buffers :/ -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
Re: increasing shared buffers: how much should be removed from OS filesystem cache?
From
Dave Cramer
Date:
On 4-Sep-06, at 8:07 AM, Guillaume Cottenceau wrote: > Dave Cramer <pg 'at' fastcrypt.com> writes: > >> Guillaume >> >> 1G is really not a significant amount of memory these days, > > Yeah though we have 2G or 4G of RAM in our servers (and not only > postgres running on it). > >> 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 > > I mean that when you allocate more memory to applications, the > consequence is less memory the OS will be able to use for disk > cache. > >> effective cache is really a representation of shared buffers plus OS >> cache > > Are you sure the shared buffers should be counted in? As I > understand the documentation, they should not (as shared buffers > is allocated memory for the OS, not part of "kernel's disk > cache"): Yes, I am sure this should be counted, however effective_cache is not actually allocating anything so it doesn't have to be exact, but it has to be in the correct order of magnitude > > Sets the planner's assumption about the effective size of the > disk cache (that is, the portion of the kernel's disk cache > that will be used for PostgreSQL data files). This is > measured in disk pages, which are normally 8192 bytes each. > The default is 1000. > > -- > Guillaume Cottenceau > Create your personal SMS or WAP Service - visit http:// > mobilefriends.ch/ >
On 9/1/06, Joshua D. Drake <jd@commandprompt.com> wrote: > >> > >> I think 'shared buffers' is one of the most overrated settings from a > >> performance standpoint. however you must ensure there is enough for > >> things the server does besides caching. It used to be a bigger deal > >> than it is in modern versionf of postgresql modern operating systems. > > Previous to 8.1 I would agree with you, but as of 8.1 it is probably the > most underrated. really? what are the relative advantages of raising shared buffers? I was thinking maybe there might be less context switches in high load environments...I'm really curious what you have to say here. merlin
On 5-Sep-06, at 9:31 AM, Merlin Moncure wrote: > On 9/1/06, Joshua D. Drake <jd@commandprompt.com> wrote: >> >> >> >> I think 'shared buffers' is one of the most overrated settings >> from a >> >> performance standpoint. however you must ensure there is >> enough for >> >> things the server does besides caching. It used to be a bigger >> deal >> >> than it is in modern versionf of postgresql modern operating >> systems. >> >> Previous to 8.1 I would agree with you, but as of 8.1 it is >> probably the >> most underrated. > > really? what are the relative advantages of raising shared buffers? I > was thinking maybe there might be less context switches in high load > environments...I'm really curious what you have to say here. Have you tried it ? The results are quite dramatic. So if shared buffers aren't the first tool you reach for, what is ? > > merlin > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >