pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Effective Cache Size
6+ messages / 5 participants
[nested] [flat]

* Effective Cache Size
@ 2003-07-01 13:50  Howard Oblowitz <HowardO@LEWIS-STORES.com>
  0 siblings, 3 replies; 6+ messages in thread

From: Howard Oblowitz @ 2003-07-01 13:50 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-performance

Thanks.

Some theoretical questions.

The documentation says that Effective Cache Size "sets the optimizer'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 ).

What then will be the effect of setting this too high?

And too low?

How does it impact on other applications eg Java ?


> -----Original Message-----
> From:	scott.marlowe [SMTP:scott.marlowe@ihs.com]
> Sent:	01 July 2003 03:20
> To:	Howard Oblowitz
> Cc:	pgsql-performance@postgresql.org
> Subject:	Re: FW: [PERFORM] Version 7 question
> 
> The best way to set it is to let the machine run under normal load for a 
> while, then look at the cache / buffer usage using top (or some other 
> program, top works fine for this).
> 
> My server with 1.5 gig ram shows 862328K cache right now.  So, just divide
> 
> by page size (usually 8192) and I get ~ 100,000 blocks.
> 
> On Tue, 1 Jul 2003, Howard Oblowitz wrote:
> 
> > What would be the best value range for effective_cache_size
> > on Postgres 7.3.2, assuming say 1.5 GB of RAM and
> > shared_buffers set to 8192, and shmmax set to 750mb?
> > 
> > And what are the most important factors one should take
> > into account in determining the value?
> > 
> > 
> > 
> > > -----Original Message-----
> > > From:	scott.marlowe [SMTP:scott.marlowe@ihs.com]
> > > Sent:	01 July 2003 02:56
> > > To:	Michael Mattox
> > > Cc:	Hilary Forbes; pgsql-performance@postgresql.org
> > > Subject:	Re: [PERFORM] Version 7 question
> > > 
> > > 8192 is only 64 megs of RAM, not much, but a good number.  Keep in
> mind 
> > > that the kernel tends to be better at buffering huge amounts of disk, 
> > > while postgresql is better left to use buffers that are large enough
> for 
> > > the current working set (i.e. not your whole database, just the
> largest 
> > > amount of data you're slinging about on a regular basis in one query.)
> > > 
> > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768
> 
> > > (256 megs of ram) to run well, but anything over that doesn't help.
> Of 
> > > course, we don't toss around more than a hundred meg or so at a time.
> If
> > > 
> > > our result sets were in the gigabyte range, I'd A: want more memory
> and B:
> > > 
> > > Give more of it to postgresql.
> > > 
> > > The original poster was, I believe running 7.0.x, which is way old, so
> no,
> > > 
> > > I don't think there was an equivalent of effective_cache_size in that 
> > > version.  Upgrading would be far easier than performance tuning 7.0.
> since
> > > 
> > > the query planner was much simpler (i.e. more prone to make bad
> decisions)
> > > 
> > > in 7.0.
> > > 
> > > On Tue, 1 Jul 2003, Michael Mattox wrote:
> > > 
> > > > I have my shared buffers at 8192 and my effective cache at 64000
> (which
> > > is
> > > > 500 megs).  Depends a lot on how much RAM you have.  I have 1.5 gigs
> and
> > > > I've been asking my boss for another 512megs for over a month now.
> I
> > > have
> > > > no idea if my buffers are too high/low.
> > > > 
> > > > Michael
> > > > 
> > > > > -----Original Message-----
> > > > > From: pgsql-performance-owner@postgresql.org
> > > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > > > > Forbes
> > > > > Sent: Tuesday, July 01, 2003 2:10 PM
> > > > > To: pgsql-performance@postgresql.org
> > > > > Subject: [PERFORM] Version 7 question
> > > > >
> > > > >
> > > > > I'm just trying to improve performance on version 7 before doing
> > > > > some tests and hopefully upgrading to 7.3.
> > > > >
> > > > > At the moment we have
> > > > > B=64  (no  of shared buffers)
> > > > > N=32 (no of connections)
> > > > > in postmaster.opt which I take it is the equivalent of the new
> > > > > postgresql.conf file.
> > > > >
> > > > >  From all that is being written about later versions I suspect
> > > > > that this is far too low.  Would I be fairly safe in making the
> > > > > no of shared buffers larger?  Also is there an equivalent of
> > > > > effective_cache_size that I can set for version 7?
> > > > >
> > > > > Many thanks in advance
> > > > > Hilary
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Hilary Forbes
> > > > > -------------
> > > > > DMR Computer Limited:   http://www.dmr.co.uk/
> > > > > Direct line:  01689 889950
> > > > > Switchboard:  (44) 1689 860000  Fax: (44) 1689 860330
> > > > > E-mail:  hforbes@dmr.co.uk
> > > > >
> > > > > **********************************************************
> > > > >
> > > > >
> > > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > > TIP 5: Have you checked our extensive FAQ?
> > > > >
> > > > >                http://www.postgresql.org/docs/faqs/FAQ.html
> > > > >
> > > > 
> > > > 
> > > > 
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > > 
> > > >                http://www.postgresql.org/docs/faqs/FAQ.html
> > > > 
> > > 
> > > 
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > > 
> > >                http://archives.postgresql.org
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Effective Cache Size
@ 2003-07-01 14:02  Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  parent: Howard Oblowitz <HowardO@LEWIS-STORES.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Shridhar Daithankar @ 2003-07-01 14:02 UTC (permalink / raw)
  To: pgsql-performance

On 1 Jul 2003 at 15:50, Howard Oblowitz wrote:

> The documentation says that Effective Cache Size "sets the optimizer'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 ).
> 
> What then will be the effect of setting this too high?
> 
> And too low?

Let's say postgresql is preparing a plan for a scan and it estimates data set 
size as 100MB whereas your shared buffers+effective cache is 80M. So postgresql 
would deduce that it would be better off with sequential scan rather than index 
scan. Where in fact you have much more memory to make a file system cache and 
the machine can afford index scan.

There is nothing too low or too high of a setting. This isn't exactly 
performance tuning paramter as other. This is more of information to 
postgresql. The closer it gets to truer, the plans produced would get optimal.

About how to set this parameter, it is roughly

effective cache size= (Physical RAM size-shared buffers-requirement for other 
apps) * 0.8

This is very very rough. You need to make sure that some setting does not 
trigger a swap avelanche

HTH

Bye
 Shridhar

--
Slous' Contention:	If you do a job too well, you'll get stuck with it.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Effective Cache Size
@ 2003-07-01 14:18  scott.marlowe <scott.marlowe@ihs.com>
  parent: Howard Oblowitz <HowardO@LEWIS-STORES.com>
  2 siblings, 0 replies; 6+ messages in thread

From: scott.marlowe @ 2003-07-01 14:18 UTC (permalink / raw)
  To: Howard Oblowitz <HowardO@LEWIS-STORES.com>; +Cc: pgsql-performance

Good questions.  Basically, telling postgresql it has a larger disk cache 
makes it favor index operations, smaller makes it favor seq scans.

If your machine has super fast I/O then you may want it to favor seq 
scans, whereas if you have more CPU power than I/O bandwidth then you'd 
likely want it to favor index operations.

Note that even if you are running java and it is using a few hundred megs 
of ram, it's quite likely that postgresql is still using most of the disk 
cache, as the memory java is using is likely allocated to hold its created 
data structures, not stuff loaded from disk.

On Tue, 1 Jul 2003, Howard Oblowitz wrote:

> Thanks.
> 
> Some theoretical questions.
> 
> The documentation says that Effective Cache Size "sets the optimizer'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 ).
> 
> What then will be the effect of setting this too high?
> 
> And too low?
> 
> How does it impact on other applications eg Java ?
> 
> 
> > -----Original Message-----
> > From:	scott.marlowe [SMTP:scott.marlowe@ihs.com]
> > Sent:	01 July 2003 03:20
> > To:	Howard Oblowitz
> > Cc:	pgsql-performance@postgresql.org
> > Subject:	Re: FW: [PERFORM] Version 7 question
> > 
> > The best way to set it is to let the machine run under normal load for a 
> > while, then look at the cache / buffer usage using top (or some other 
> > program, top works fine for this).
> > 
> > My server with 1.5 gig ram shows 862328K cache right now.  So, just divide
> > 
> > by page size (usually 8192) and I get ~ 100,000 blocks.
> > 
> > On Tue, 1 Jul 2003, Howard Oblowitz wrote:
> > 
> > > What would be the best value range for effective_cache_size
> > > on Postgres 7.3.2, assuming say 1.5 GB of RAM and
> > > shared_buffers set to 8192, and shmmax set to 750mb?
> > > 
> > > And what are the most important factors one should take
> > > into account in determining the value?
> > > 
> > > 
> > > 
> > > > -----Original Message-----
> > > > From:	scott.marlowe [SMTP:scott.marlowe@ihs.com]
> > > > Sent:	01 July 2003 02:56
> > > > To:	Michael Mattox
> > > > Cc:	Hilary Forbes; pgsql-performance@postgresql.org
> > > > Subject:	Re: [PERFORM] Version 7 question
> > > > 
> > > > 8192 is only 64 megs of RAM, not much, but a good number.  Keep in
> > mind 
> > > > that the kernel tends to be better at buffering huge amounts of disk, 
> > > > while postgresql is better left to use buffers that are large enough
> > for 
> > > > the current working set (i.e. not your whole database, just the
> > largest 
> > > > amount of data you're slinging about on a regular basis in one query.)
> > > > 
> > > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768
> > 
> > > > (256 megs of ram) to run well, but anything over that doesn't help.
> > Of 
> > > > course, we don't toss around more than a hundred meg or so at a time.
> > If
> > > > 
> > > > our result sets were in the gigabyte range, I'd A: want more memory
> > and B:
> > > > 
> > > > Give more of it to postgresql.
> > > > 
> > > > The original poster was, I believe running 7.0.x, which is way old, so
> > no,
> > > > 
> > > > I don't think there was an equivalent of effective_cache_size in that 
> > > > version.  Upgrading would be far easier than performance tuning 7.0.
> > since
> > > > 
> > > > the query planner was much simpler (i.e. more prone to make bad
> > decisions)
> > > > 
> > > > in 7.0.
> > > > 
> > > > On Tue, 1 Jul 2003, Michael Mattox wrote:
> > > > 
> > > > > I have my shared buffers at 8192 and my effective cache at 64000
> > (which
> > > > is
> > > > > 500 megs).  Depends a lot on how much RAM you have.  I have 1.5 gigs
> > and
> > > > > I've been asking my boss for another 512megs for over a month now.
> > I
> > > > have
> > > > > no idea if my buffers are too high/low.
> > > > > 
> > > > > Michael
> > > > > 
> > > > > > -----Original Message-----
> > > > > > From: pgsql-performance-owner@postgresql.org
> > > > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary
> > > > > > Forbes
> > > > > > Sent: Tuesday, July 01, 2003 2:10 PM
> > > > > > To: pgsql-performance@postgresql.org
> > > > > > Subject: [PERFORM] Version 7 question
> > > > > >
> > > > > >
> > > > > > I'm just trying to improve performance on version 7 before doing
> > > > > > some tests and hopefully upgrading to 7.3.
> > > > > >
> > > > > > At the moment we have
> > > > > > B=64  (no  of shared buffers)
> > > > > > N=32 (no of connections)
> > > > > > in postmaster.opt which I take it is the equivalent of the new
> > > > > > postgresql.conf file.
> > > > > >
> > > > > >  From all that is being written about later versions I suspect
> > > > > > that this is far too low.  Would I be fairly safe in making the
> > > > > > no of shared buffers larger?  Also is there an equivalent of
> > > > > > effective_cache_size that I can set for version 7?
> > > > > >
> > > > > > Many thanks in advance
> > > > > > Hilary
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Hilary Forbes
> > > > > > -------------
> > > > > > DMR Computer Limited:   http://www.dmr.co.uk/
> > > > > > Direct line:  01689 889950
> > > > > > Switchboard:  (44) 1689 860000  Fax: (44) 1689 860330
> > > > > > E-mail:  hforbes@dmr.co.uk
> > > > > >
> > > > > > **********************************************************
> > > > > >
> > > > > >
> > > > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > > > TIP 5: Have you checked our extensive FAQ?
> > > > > >
> > > > > >                http://www.postgresql.org/docs/faqs/FAQ.html
> > > > > >
> > > > > 
> > > > > 
> > > > > 
> > > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > > TIP 5: Have you checked our extensive FAQ?
> > > > > 
> > > > >                http://www.postgresql.org/docs/faqs/FAQ.html
> > > > > 
> > > > 
> > > > 
> > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > > 
> > > >                http://archives.postgresql.org
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Effective Cache Size
@ 2003-07-01 18:22  Manfred Koizar <mkoi-pg@aon.at>
  parent: Howard Oblowitz <HowardO@LEWIS-STORES.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Manfred Koizar @ 2003-07-01 18:22 UTC (permalink / raw)
  To: Howard Oblowitz <HowardO@LEWIS-STORES.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; pgsql-performance

On Tue, 1 Jul 2003 15:50:14 +0200 , Howard Oblowitz
<HowardO@LEWIS-STORES.com> wrote:
>What then will be the effect of setting this too high?

The planner might choose an index scan where a sequential scan would
be faster.

>And too low?

The planner might choose a sequential scan where an index scan would
be faster.

>How does it impact on other applications eg Java ?

It doesn't -- at least not directly.  (There could be very subtle
effects when Postgres does a sequential scan over a large relation
thus pushing everything else out of the cache, where an index scan
would have read only a small number of pages.  Or when a large index
scan turns your machine from CPU bound to I/O bound.)

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Effective Cache Size
@ 2003-09-17 10:48  Nick Barr <nick.barr@webbased.co.uk>
  0 siblings, 1 reply; 6+ messages in thread

From: Nick Barr @ 2003-09-17 10:48 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have been following a thread on this list "Inconsistent performance"
and had a few questions especially the bits about effective_cache_size.
I have read some of the docs, and some other threads on this setting,
and it seems to used by the planner to either choose a sequential or
index scan. So it will not necessarily increase performance I suppose
but instead choose the most optimal plan. Is this correct?

We are not that we are suffering massive performance issues at the
moment but it is expected that our database is going to grow
considerably in the next couple of years, both in terms of load and
size.

Also what would an appropriate setting be? 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Effective Cache Size
@ 2003-09-17 11:03  Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  parent: Nick Barr <nick.barr@webbased.co.uk>
  0 siblings, 0 replies; 6+ messages in thread

From: Shridhar Daithankar @ 2003-09-17 11:03 UTC (permalink / raw)
  To: pgsql-performance

On 17 Sep 2003 at 11:48, Nick Barr wrote:

> Hi,
> 
> I have been following a thread on this list "Inconsistent performance"
> and had a few questions especially the bits about effective_cache_size.
> I have read some of the docs, and some other threads on this setting,
> and it seems to used by the planner to either choose a sequential or
> index scan. So it will not necessarily increase performance I suppose
> but instead choose the most optimal plan. Is this correct?

That is correct.

> Danger maths ahead. Beware!!!!
> 
> <maths>
>   141816K  buff
> + 1781764K cached
> -----------------
>   1923580K total
> 
> effective_cache_size = 1923580 / 8 = 240447.5
> </maths>

That would be bit too aggressive. I would say set it around 200K to leave room 
for odd stuff.

Rest seems fine with your configuration. Of course a latest version of 
postgresql is always good though..

Bye
 Shridhar

--
Power is danger.		-- The Centurion, "Balance of Terror", stardate 1709.2




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-09-17 11:03 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-01 13:50 Effective Cache Size Howard Oblowitz <HowardO@LEWIS-STORES.com>
2003-07-01 14:02 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-01 14:18 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-01 18:22 ` Manfred Koizar <mkoi-pg@aon.at>
2003-09-17 10:48 Effective Cache Size Nick Barr <nick.barr@webbased.co.uk>
2003-09-17 11:03 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox