Re: Vacuum rate limit in KBps - Mailing list pgsql-hackers

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com