Re: rapid degradation after postmaster restart

Joe Conway wrote:

> A few pg_autovacuum questions came out of this:
>
>   First, the default vacuum scaling factor is 2, which I think implies
>   the big table should only get vacuumed every 56 million or so changes.
>   I didn't come anywhere near that volume in my tests, yet the table did
>   get vacuumed more than once (I was watching the pg_autovacuum log
>   output). Do I misunderstand this setting?


I think you understand correctly.  A table with 1,000,000 rows should
get vacuumed approx every 2,000,000 changes (assuming default values for
-V ).  FYI and insert and a delete count as one change, but and update
counts as two.

Unfortunately, the running with -d2 would show the numbers that
pg_autovacuum is using to decide if it when it should vacuum or
analyze.    Also, are you sure that it vacuumed more than once and
wasn't doing analyzes most of the time?

Also, I'm not sure if 2 is a good default value for the scaling factor
but I erred on the side of not vacuuming too often.

>   Second, Matthew requested pg_autovacuum run with -d2; I found that
>   with -d2 set, pg_autovacuum would immediately exit on start. -d0 and
>   -d1 work fine however.


That's unfortunate as that is the detail we need to see what
pg_autovacuum thinks is really going on.  We had a similar sounding
crash on FreeBSD due to some unitialized variables that were being
printed out by the debug code, however that was fixed a long time ago.
Any chance you can look into this?

> That's all I can think of at the moment. I'd like to try the 7.4 patch
> that makes vacuum sleep every few pages -- can anyone point me to the
> latest and greatest that will apply to 7.4?


Yes I would be very curious to see the results with the vacuum delay
patch installed (is that patch applied to HEAD?)