On Mon, Apr 20, 2015 at 10:33 AM, Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Apr 16, 2015 at 03:41:54PM +0100, Simon Riggs wrote: > That is how we arrive at the idea of a cleanup limit, further enhanced by a > limit that applies only to dirtying clean blocks, which we have 4? recent votes > in favour of. > > I would personally be in favour of a parameter to control the limit, since > whatever we chose is right/wrong depending upon circumstances. I am however > comfortable with not having a parameter if people think it is hard to tune > that, which I agree it would be, hence no parameter in the patch.
I think the limit has to be in terms of a percentage of the table size. For example, if we do one SELECT on a table with all non-dirty pages, it would be good to know that 5% of the pages were pruned --- that tells me that another 19 SELECTs will totally prune the table, assuming no future writes.
But why should 1 SELECT or 20 SELECTs or 200 SELECTs have to do a job, while the user waits, which is fundamentally VACUUM's duty to do in the background? If there are a handful of very hot pages, then it makes sense not to wait for vacuum to get to them. And that is what a block-count limit does.
But if the entire table is very hot, I think that that is just another of way of saying that autovacuum is horribly misconfigured. I think the purpose of this patch is to fix something that can't be fixed through configuration alone.
If there are future writes, they would dirty the pages and cause even more pruning, but the 5% gives me the maximum pruning number of SELECTs. If there aren't another 19 SELECTs, do I care if the table is pruned or not?
The use case I see for this is when there is a mixed workload. There is one select which reads the entire table, and hundreds of thousands of selects/updates/insert that don't, and of course vacuum comes along every now and then and does it thing. Why should the one massive SELECT have horrible performance just because it was run right before autovacuum would have kicked in instead of right after if finished?