Re: Vacuum rate limit in KBps

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.

I attempt to explain those settings to people in training classes about
once a month. It's never been anything but a complete disaster. I am
barely concerned about preserving the current UI because, as far as I've
been able to tell, there are only a handful of PostgreSQL installatinos
on the planet that have managed to use it happily. Even the ones that
do have a non-default setup that works usually flailed about for some
time until they get something that works, over a few frustrating months.
And the result are settings few dare touch for fear of breaking it.

It's also worth pointing out that VACUUM problems are very close to the
top of the list of problems larger sites run into. So right now we have
an inscrutable UI around an often essential part of the database to
tune, one that any production site that gets over a few hundred GB of
data in it will run into problems with. I wouldn't care about this area
if it weren't for people screaming about how bad it is every time the
topic comes up.

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.

> What does that 8MB mean and how does it
> relate to vacuum_cost_page_miss? If I double vacuum_rate_page_miss,
> does that effectively also double the cost limit, so that dirty pages
> and hits become relatively cheaper? If so, then I think what that
> really means is that the limit is 8MB only if there are no hits and no
> dirtied pages - otherwise it's less, and the amount by which it is
> less is the result of some arcane calculation. Ugh!

Saying what I suggested is an arcane calculation strikes me as pretty
weird--we'd be hard pressed to design a more arcane calculation than the
one that's already happening.

The feedback here so far seems to lead toward making independent read
and write knobs. I'm going to chew on the scenarios Robert described
and the ones Jim has been commenting on and see if I can refactor this
into something friendlier that addresses them.

As for the suggestion that I'm bringing this up a bit late in the
release cycle, I've been trying. My first submission pushing in this
direction--improving the logging first, which is needed before you can
usefully measure a behavior change--happened back in September. I've
been moving this area as fast as I can get it to budge. I'm concerned
now that much will be made of improved performance in 9.2, leading to
people converting even larger systems than they used to. And it's not
hard at all to find a large system where inability to tune vacuum easily
is the top limiting factor on overall performance.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com