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

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