Re: Vacuum rate limit in KBps

On 01/15/2012 04:17 PM, Heikki Linnakangas wrote:
> I think it makes more sense to use the max read rate as the main knob,
> rather than write rate. That's because the max read rate is higher
> than the write rate, when you don't need to dirty pages. Or do you
> think saturating the I/O system with writes is so much bigger a
> problem than read I/O that it makes more sense to emphasize the writes?

I haven't had the I/O rate logging available for long enough to have a
good feel for which is more important to emphasize. I'm agnostic on
this. I'd have no problem accepting the argument that exposing the
larger of the two rates--which is the read one--makes for a cleaner UI.
Or that it is the one more like other knobs setting precedents here.

My guess is that the changed documentation will actually be a bit
cleaner that way. I give an example in the patch of how read and write
rate are related, based on the ratio of the values for dirty vs. hit. I
wasn't perfectly happy with how that was written yet, and I think it
could be cleaner if the read rate is the primary tunable.

> We usually try to preserve that backwards-compatibility, although we
> always recommend using the pg_dump from the newer version on upgrade.
> However, you need to know the vacuum_cost_page_miss setting effective
> in the old server to do the transformation correctly (or
> vacuum_cost_page_dirty, if we use the write max rate as the main knob
> as you suggested), and we don't have access when restoring a dump.

If someone does a storage parameter change to
autovacuum_vacuum_cost_limit but doesn't touch
autovacuum_vacuum_cost_delay there, I think it's possible to need the
GUC value for autovacuum_vacuum_cost_delay too, which can then refer to
vacuum_cost_delay as well.

I don't think that tweaking these parameters, particularly at the
storage options level, is a popular thing to do. I've run into
customers who made changes there while trying to navigate the complexity
of autovacuum tuning, but not very many. My guess as I think about that
history is that I've ended up reverting them as often, or maybe even
slightly more often, than I've ended up keeping them around. It's been
hard to do well. And the level of PostgreSQL deployment that reaches
that stage of tuning, where they managed to tweak those productively,
doesn't seem likely to do a blind upgrade to me.

One of the reasons I thought now was a good time to work on this change
is because there's already things brewing that are going to make 9.2
break a few more things than anyone would like, all for long-term
positive benefits. recovery.conf and pg_stat_activity changes are the
two of those I've been tracking the closest.

My current thinking on this is that we ought to learn a lesson from the
8.3 casting breakage saga and provide a clear "9.2 Upgrade Migration
Guide" that goes beyond just listing what changed in the release notes.
Aim more toward having a checklist of things to look for and tools to
help find them. In this case, having the migration guide include a
query that pokes through the catalogs looking for this particular
customization would be helpful. It would be easy to produce a bit of
post-9.2 upgrade SQL that converted a customization here if you started
by running something against the existing installation.

And that should be a wiki page so it can be extended as new things are
discovered, some of which will include application specific guidance.
The rough idea in this direction I put together for 8.3 (it didn't catch
on for later versions) is at
http://wiki.postgresql.org/wiki/Version_History Note how that grew to
include tsearch2 migration info and MediaWiki specific advice at one
point (some of that was then lost in a Planet fire, but you can get the
idea just from the article titles). Needing a version migration guide
with specific details about issues like this is something I think
PostgreSQL needs to accept as part of the release cycle. Expecting that
pg_upgrade can transparently handle every possible change would be
setting a high bar to clear, higher than I think is expected by the
database industry at large.

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