Re: [HACKERS] [PERFORM] More detail on settings for pgavd?

[Matthew T. O'Connor]

Josh Berkus wrote:

Matthew,
 

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?
              http://archives.postgresql.org