Josh Berkus wrote:


I certainly agree that less than 10% would be excessive, I still feel that 10% may not be high enough though. That's why I kinda liked the sliding scale I mentioned earlier, because I agree that for very large tables, something as low as 10% might be useful, but most tables in a database would not be that large.

Yes, but I thought that we were taking care of that through the "threshold" value?

Well the threshold is a combination of the base value and the scaling factor which you are proposing is 0.1, so the threshold is base + (scaling factor)(num of tuples) So with the default base of 1000 and your 0.1 you would have this:

Num Rows threshold Percent
1,000 1,100 110%
10,000 2,000 20% 100,000 11,000 11%
1,000,000 102,000 10%

I don't like how that looks, hence the thought of some non-linear scaling factor that would still allow the percent to reach 10%, but at a slower rate, perhaps just a larger base value would suffice, but I think small table performance is going to suffer much above 1000. Anyone else have an opinion on the table above? Good / Bad / Indifferent?

A sliding scale would also be OK. However, that would definitely require a leap to storing per-table pg_avd statistics and settings.

I don't think it would, it would correlate the scaling factor with the number of tuples, no per-table settings required.

Only that pg_autovacuum isn't smart enough to kick off more than one vacuum at a time. Basically, pg_autovacuum issues a vacuum on a table and waits for it to finish, then check the next table in it's list to see if it needs to be vacuumed, if so, it does it and waits for that vacuum to finish.

OK, then, we just need to detect the condition of the vacuums "piling up" because they are happening too often.

That would be good to look into at some point, especially if vacuum is going to get slower as a result of the page loop delay patch that has been floating around.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

Reply via email to