Re: performance problem - Mailing list pgsql-general

Ah, so then the 25% recomendation for a starting point is probably not a bad
one, but if you get over a 1 gig of ram you might as well stop at about
256mb for the shared buffers because it just won't do you much good and will
start to slow down small queries.

That makes sense to me.

----- Original Message -----
From: <fred@redhotpenguin.com>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: "Doug McNaught" <doug@mcnaught.org>; "scott.marlowe"
<scott.marlowe@ihs.com>; "PgSQL General ML" <pgsql-general@postgresql.org>
Sent: Thursday, November 20, 2003 1:59 PM
Subject: Re: [GENERAL] performance problem


> As discussed on previous posts and mentioned below, the kernel is often
> better at handling large amounts of buffers than Postgres.
>
> From researching previous posts and testing on my own setups, 256mb is
> about the max you want to go with shared buffers (if you have at least 1
> gig ram).  After that the smaller queries (which there are usually a lot
> of) slow down, and you only gain marginal improvement on the larger
> queries.  You'll see an improvement slightly on the large queries going
> over 256 but not enough to improve the overall performance.  There's a few
> more technically detailed explanations of this in the list archive I
> believe, by those who can explain it better than I.
>
> >> > > That's VERY high.  When postgresql has to manage a lot of buffers
it
> >> > > actually is slower than letting the kernel in Linux or BSD do it
for
> > you.
> >
> > I am confused.  In this tutorial (by Bruce Momjian)
> > http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
> > it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4%
> > for
> > sort size."
> >
> > If I've got 2g of RAM then that is 2097152k.  25% of that = 524288k.  So
> > the
> > tutorial is saying that 524288k is a good starting point for shared
> > buffers
> > with this amount of RAM.
> >
> > If each buffer is 8k
> > (http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node3.html)
> > then that would be 65536 buffers.
> >
> > I'm pretty sure that that is a lot more than I need to cache every tuple
> > in
> > my database.  Now everytime I see someone comment on this list about
> > appropriate numbers for shared buffers they say something like "(65536
> > buffers is) VERY high.  Now since I obviously don't need that much
shared
> > cache so I am not concerned but it seems to me that one of the following
> > must be true.
> >
> > My calculations here are wrong.
> > or The tutorial is not accurate in saying that 25% is a good starting
> > point.
> > or The people making comments that 65536 is "VERY high" are wrong.
> >
> > Am I just confused or does this make sense?
> >
> >> > Even if you've got the memory to spare?  Does postgres actually slow
> > down
> >> > just because it's slower to manager a lot of them just or because
> >> you're
> >> > taking the memory away from the kernel so the kernel has to swap
more?
> >>
> >> The latter, mainly, I think.  Also you *really* don't want your kernel
> >> to swap out any of your shared buffers, which can happen if they take
> >> up a significant portion of RAM...
> >
> > So if I'm not swapping at all and I've got over 1g of unused memory then
> > I'm
> > probably ok even with the very high buffer setting? (Although I will
> > probably reduce it anyway since it is unnecessary).
> >
> > rg
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>