Re: Effective Cache Size - Mailing list 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
>