Re: Vacuum rate limit in KBps

On Fri, Jan 20, 2012 at 12:35 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 01/20/2012 10:37 AM, Robert Haas wrote:
>>
>> On Thu, Jan 19, 2012 at 11:29 PM, Greg Smith<greg(at)2ndquadrant(dot)com>  wrote:
>>>
>>> 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
>>
>> That may be a little better, but I still don't think it's worth
>>
>> breaking backward compatibility for.  I mean, suppose I don't care
>> about read rate, but I want to limit my dirty data rate to 1MB/s.
>> What parameters should I set?
>
> vacuum_cost_page_dirty = 8.0
>
> The resulting maximum rates will then be:
>
> hit = 80MB/s
> miss = 8MB/s
> dirty = 1MB/s

That is, of course, not quite what I asked for. In fact it's likely
that my actual rate will be less than 1MB/s, because there will
probably be a miss for ever dirty. It will probably be about 8/9ths
of a MB/s.

> The question you should ask yourself next is "how do I limit my dirty data
> rate to 1MB/s in 9.1?"  Working that out by hand is a good exercise, to show
> just how much less complicated this proposal is over the current state of
> things.

OK, sure. Our block size is 8kB, so we need every 128 blocks to
involve 1000 ms of delay. Obviously there are many combinations of
parameters that will make that work, but here's one: delay for 125
milliseconds after each 16 blocks:

vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 0
vacuum_cost_page_dirty = 1
vacuum_cost_limit = 16
autovacuum_vacuum_cost_delay = 125ms

Maybe that strikes you as worse than what you're proposing; it strikes
me as better. Either way I think it's not going to be a good day for
people who are bad at math. :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company