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

I chewed a bit on Heikki's comment that similarity to the query planning 
parameters might be useful, and Robert's that being able to explain how 
the feature works more easily has value.  I have an initial adjustment 
of my general idea that I think moves usefully in both those directions.

The existing VACUUM cost constants look like this:

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

These could be adjusted to instead be ratios like the query planner ones 
(seq_page_cost, random_page_cost, etc.), referenced off a value of 1.0 
for page miss ~= a read is expected:

vacuum_cost_page_hit = 0.1
vacuum_cost_page_miss = 1.0
vacuum_cost_page_dirty = 2.0

Now add in the new setting, which is explicitly said to be the read value:

vacuum_cost_read_limit = 8000 # maximum page miss read rate in 
kilobytes/second

And I can shuffle the numbers around internally such that things still 
work exactly the same, at the default parameters.  And then anyone who 
spends time learning how either the query planning or vacuum cost ratio 
constants work will find the learning curve to pick up the other set easier.

An interesting fall-out of this refactoring is that old postgresql.conf 
settings moved forward for *all* these values will still work fine.  The 
ratios are right and the internal computation won't care.  The math is 
just more complicated to explain when vacuum_cost_page_miss is anything 
but 1.0, which is a problem the manual doesn't have to address.  We 
don't worry about making every query planner parameter discussion 
consider what happens if someone moves seq_page_cost around, this will 
put vacuum_cost_page_miss into the same reference constant category.  
The only problem is for someone who changed one but not all of them in 
their old configuration; that's going to give an unexpected result.

It might be a bit more straightforward yet if things were renamed so it 
was more obvious that page miss~=read, but I haven't seen a good way to 
do that yet.  Renaming the reference cost value to vacuum_cost_page_read 
has two problems.  It makes the backward compatibility issues larger, 
and it's not quite true.  The way I think this should be explained, they 
really aren't the same; that's why I used ~= above.  A page miss is not 
guaranteed to be a read, it just is expected to be one in the worst 
case.  The read rate that vacuum page misses introduce will not be 
exactly the same as vacuum_cost_read_limit--but it will be below that 
limit, which is all it claims to be.

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