Re: Vacuum rate limit in KBps

On Mon, Jan 23, 2012 at 3:21 AM, Benedikt Grundmann
<bgrundmann(at)janestreet(dot)com> wrote:
> 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.
>

I keep thinking Greg has mistaken happiness with the MB based info in
the vacuum patch as being happy without the output format, when really
it is all about increased visibility. (For the record, we've
backpatched that initial change to a large number of our customers,
just cause we're a bit zealous about monitoring).

> 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.
>

Hmm, I've always thought the answer here is just a systematic approach
to operations. We monitor free space across the system (along with a
bunch of other stuff) so that we know when we're not vacuuming /
analyzing enough.

> 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.
>

These pieces are out there. I guess I'd say they are crude, but you
can get a handle on it. Of course, if your problem is with analyze,
that's cheap enough that you should probably just do it more. We're
probably a lot more agressive on our vacuum / analyze scale settings
than some people (we cut the defaults in half as a matter of course),
and I come from the "don't limit stuff" camp too, but by and large
what we do works, even if it's more black magic than people would
like. :-)

Robert Treat
conjecture: xzilla.net
consulting: omniti.com