Re: "--tuning" compile and runtime option (?)

Well, again, I will write a performance tuning article this month, which
hopefyully will help people.

My recommendation on shared memory is that if you have a machine that is
going to be used only for PostgreSQL, the shared memory should be
increased to the point where you are not seeing any swap page-ins during
normal use. I know you have the kernel buffer cache for all unused
memory, but those pages are copied in and out of the PostgreSQL buffer
cache for processing, which can be an expensive operation.

Now how do you automate something to increase shared memory until there
are no page swap-ins under normal use. I think the administrator will
have to be involved because a script has no idea what a normal load
looks like. The best we could do is to monitor swap-ins as part of the
running server and report to the administrator that there is extra
memory around that could be used for shared memory.

> Bruce Momjian wrote:
> >
> > OK, what options would you recommend be auto-tuned in each circumstance?
> > I can imagine open files and maybe sortmemory, but even then, other
> > backends can affect the proper value. Share memory usually has a kernel
> > limit which prevents us from auto-tuning that too much.
>
> Share memory might have a kernel limit, but that's no excuse for not
> allowing this process to auto-tune it.
>
> I have truckloads of memory in my server if I am setting it up for a
> serious database, and I usually edit /etc/sysctl.conf (Debian GNU/Linux,
> and some other Linux's - possibly other unixen as well) to set the
> shared memory. Usually I set it to around 90% of the actual RAM in the
> system.
>
> So, if I have 1G RAM, and my database is 600M but my application only
> ends up hitting 20% of that on a regular basis do I benefit from
> adjusting my -B beyond 12000 or so? A question that the docs seem to
> think is 'suck it and see'. I haven't had the time or equipment to
> benchmark stuff in a wide range of hardware environments, myself, but if
> an auto-tune option suggested to me that performance increased up to a
> -B of 4000 or so, and that the server stopped working past there, I'm
> afraid that only an idiot would cease investigating at that point :-)
>
> It would be wonderful if the auto-tuning gave sensible advice in these
> sorts of situations, and then made some further suggestions that an
> operator might use to take the tuning to the next level. A mention of
> kernel shared memory limits would seem appropriate in there somewhere.
>
> The problem I usually have with a lot of "auto tuning" (and other sorts
> of automation) on other software is that it takes the approach that the
> user knows nothing, and we "don't want to bother their pretty little
> heads with these sorts of problems". I feel like a total _blonde_ when
> I use MS SQL Server, because it either hides the possibility of me
> adjusting it, or it doesn't explain what/how/why to adjust. PostgreSQL
> should, of course, offer advice. It shouldn't assume that because I've
> said "auto-tune" that I don't want to know why it is doing what it is
> doing. What conclusions it has come to, and what decisions it has made
> as a result.
>
> Finally, thanks for pursuing these options. I think they will be a huge
> help, as well as hopefully providing more data on performance issues
> back to the core team.
>
> That'll be 2c, please :-)
> Andrew.
> --
> _____________________________________________________________________
> Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
> Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
> Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026