Re: Vacuum rate limit in KBps

On 19/01/12 17:39, Greg Smith wrote:
> On 1/19/12 1:10 PM, Robert Haas wrote:
> >I have to say that I find that intensely counterintuitive. The
> >current settings are not entirely easy to tune correctly, but at least
> >they're easy to explain.
>
> If there's anyone out there who has run a larger PostgreSQL database
> and not at some point been extremely frustrated with how the current
> VACUUM settings are controlled, please speak up and say I'm wrong
> about this. I thought it was well understood the UI was near unusably
> bad, it just wasn't obvious what to do about it.
>
We are frustrated but mostly our frustration is not about the
somewhat inscrutable knobs but the inscrutable meters or lack
there of.

Postgres (auto or manual for that matter) vacuuming and analyzing
is essentially a performance tuning problem without a good way to
measure the current performance, the fact that the knobs to turn
are confusing as well is secondary.

What I think is missing is a clear way to know if you are vacuuming
(and analyzing) enough, and how much you are paying for that.

At the moment we are basically changing the knobs blindly based on
some back of the envelope calculations and hearsay. Than sometimes
month later we find out that eps we haven't been analyzing enough
and that's why on that particular table the planner is now picking
a "bad" query.

What I want is that page

http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html

to start with "Here is how you know if you are vacuuming enough..."

In an ideal world one would like some meter in a statistics table
or similar that returns a percentage 100% means just enough 50%
means you have to double 150% means 50% too much (e.g. wasted)...
But I could do with a boolean as well. A complicated extension
and the recommendation to install 3 different extensions would
be better than what is there right now but only very barely. Of
course a meter wouldn't tell you that if traffic doubled you would
still keep up and for that you need a complicated calculation or
(you just keep looking at the meter and adjust).

But at the moment there is no such meter (at least I don't know
of it) and that is the actual problem.

My 2cents,

Bene