Re: Feature Request --- was: PostgreSQL Performance Tuning

In response to Dan Harris <fbsd(at)drivefaster(dot)net>:

> Michael Stone wrote:
> > On Fri, Apr 27, 2007 at 09:27:49AM -0400, Carlos Moreno wrote:
> >> Notice that the second part of my suggestion covers this --- have
> >> additional
> >> switches to initdb
> <snip>
> > If the person knows all that, why wouldn't they know to just change the
> > config parameters?
>
> Exactly.. What I think would be much more productive is to use the great amount
> of information that PG tracks internally and auto-tune the parameters based on
> it. For instance:
>
> Why does the user need to manually track max_fsm_pages and max_fsm_relations? I
> bet there are many users who have never taken the time to understand what this
> means and wondering why performance still stinks after vacuuming their database
> ( spoken from my own experience )

But there are two distinct routes that can be taken if there's not enough
fsm space: add fsm space or vacuum more frequently. I don't want the system
to eat up a bunch of memory for fsm entries if my workload indicates that
I can easily vacuum more frequently.

> How about work_mem? shared_buffers? column statistics sizes? random_page_cost?

The only one that seems practical (to me) is random_page_cost. The others are
all configuration options that I (as a DBA) want to be able to decide for
myself. For example, I have some dedicated PG servers that I pretty much
max those values out at, to let PG know that it can use everything on the
system -- but I also have some shared use machines with PG, where I carefully
constrain those values so that PG doesn't muscle other daemons out of their
share of the RAM (work_mem is probably the best example)

It would be nice to have some kind of utility that could tell me what
random_page_cost should be, as I've never felt comfortable tweaking it.
Like some utility to run that would say "based on the seek tests I just
ran, you should set random_page_cost to x". Of course, if such a thing
existed, it could just fill in the value for you. But I haven't figured
out how to pick a good value for that setting, so I have no idea how to
suggest to have it automatically set.

> Couldn't some fairly simple regression tests akin to a VACUUM process spot
> potential problems? "Hey, it looks like you need more fsm_relations.. I bumped
> that up automatically for you". Or "These indexes look bloated, shall I
> automatically reindex them for you?"

A lot of that stuff does happen. A vacuum verbose will tell you what it
thinks you should do, but I don't _want_ it to do it automatically. What
if I create huge temporary tables once a week for some sort of analysis that
overload the fsm space? And if I'm dropping those tables when the analysis
is done, do I want the fsm space constantly adjusting?

Plus, some is just impossible. shared_buffers requires a restart. Do you
want your DB server spontaneously restarting because it thought more
buffers might be nice?

> I'm sure there are many more examples, that with some creative thinking, could
> be auto-adjusted to match the usage patterns of the database. PG does an
> excellent job of exposing the variables to the users, but mostly avoids telling
> the user what to do or doing it for them. Instead, it is up to the user to know
> where to look, what to look for, and how to react to things to improve
> performance. This is not all bad, but it is assuming that all users are hackers
> ( which used to be true ), but certainly doesn't help when the average SQLServer
> admin tries out Postgres and then is surprised at the things they are now
> responsible for managing. PG is certainly *not* the only database to suffer
> from this syndrome, I know..

I expect the suffering is a result of the fact that databases are non-trivial
pieces of software, and there's no universally simple way to set them up
and make them run well.

> I like to think of my systems as good employees. I don't want to have to
> micromanage everything they do. I want to tell them "here's what I want done",
> and assuming I made a good hiring choice, they will do it and take some liberty
> to adjust parameters where needed to achieve the spirit of the goal, rather than
> blindly do something inefficiently because I failed to explain to them the
> absolute most efficient way to accomplish the task.

That's silly. No software does that. You're asking software to behave like
humans. If that were the case, this would be Isaac Asimov's world, not the
real one.

> Granted, there are some people who don't like the developers making any
> assumptions about their workload. But this doesn't have to be an either/or
> proposition. I don't think any control needs to be abandoned. But
> self-adjusting defaults seem like an achievable goal ( I know, I know, "show us
> the patch" ). I just don't know if this feeling has resonated well between new
> users and long-term developers. I know it must be grating to have to answer the
> same questions over and over and over "have you analyzed? Did you leave
> postgresql.conf at the defaults??". Seems like a win-win for both sides, IMHO.

Well, it seems like this is happening where it's practical -- autovacuum is
a good example.

Personally, I wouldn't be opposed to more automagic stuff, just as long as
I have the option to disable it. There are some cases where I still
disable autovac.

> In closing, I am not bashing PG! I love it and swear by it. These comments are
> purely from an advocacy perspective. I'd love to see PG user base continue to grow.

I expect that part of the problem is "who's going to do it?"

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023