On 15.01.2012 10:24, Greg Smith wrote:
That got me thinking: if MB/s is what everyone wants to monitor, can we provide a UI to set these parameters that way too? The attached patch is a bit rough still, but it does that. The key was recognizing that the cost delay plus cost limit can be converted into an upper limit on cost units per second, presuming the writes themselves are free. If you then also assume the worst case--that everything will end up dirty--by throwing in the block size, too, you compute a maximum rate in MB/s. That represents the fastest you can possibly write.
+1. I've been thinking we should do that for a long time, but haven't gotten around to it.
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 was thinking of something like this, in postgresql.conf: # - Vacuum Throttling - #vacuum_cost_page_miss = 1.0 # measured on an arbitrary scale #vacuum_cost_page_dirty = 2.0 # same scale as above #vacuum_cost_page_hit = 0.1 # same scale as above #vacuum_rate_limit = 8MB # max reads per second This is now similar to the cost settings for the planner, which is good.
There's one serious concern I don't have a quick answer to. What do we do with in-place upgrade of relations that specified a custom vacuum_cost_limit? I can easily chew on getting the right logic to convert those to equals in the new setting style, but I am not prepared to go solely on the hook for all in-place upgrade work one might do here. Would this be easiest to handle as one of those dump/restore transformations?
It needs to be handled at dump/restore time. I'm not sure where that transformation belongs to, though. Do we have any precedence for this? I think we have two options:
1. Accept the old "autovacuum_cost_limit" setting in CREATE TABLE, and transform it immediately into corresponding autovacuum_rate_limit setting.
2. Transform in pg_dump, so that the CREATE TABLE statements in the dump use the new autovacuum_rate_limit setting.
The advantage of 1. option is that dumps taken with old 9.1 pg_dump still work on a 9.2 server. 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.
My guess is that's more sensible than the alternative of making an on-read converter that only writes in the new format, then worrying about upgrading all old pages before moving forward.
This requires any page format changes, so I don't think the above sentence makes any sense.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
