Re: We probably need autovacuum_max_wraparound_workers

On Jun 27, 2012, at 22:00, Josh Berkus <josh@agliodbs.com> wrote:

> Folks,
>
> Yeah, I can't believe I'm calling for *yet another* configuration
> variable either.  Suggested workaround fixes very welcome.
>
> The basic issue is that autovacuum_max_workers is set by most users
> based on autovac's fairly lightweight action most of the time: analyze,
> vacuuming pages not on the visibility list, etc.  However, when XID
> wraparound kicks in, then autovac starts reading entire tables from disk
> ... and those tables may be very large.
>
> This becomes a downtime issue if you've set autovacuum_max_workers to,
> say, 5 and several large tables hit the wraparound threshold at the same
> time (as they tend to do if you're using the default settings).  Then
> you have 5 autovacuum processes concurrently doing heavy IO and getting
> in each others' way.
>
> I've seen this at two sites now, and my conclusion is that a single
> autovacuum_max_workers isn't sufficient if to cover the case of
> wraparound vacuum.  Nor can we just single-thread the wraparound vacuum
> (i.e. just one worker) since that would hurt users who have thousands of
> small tables.
>
>

Would there be enough benefit to setting up separate small/medium?/large thresholds with user-changeable default table
sizeboundaries so that you can configure 6 workers where 3 handle the small tables, 2 handle the medium tables, and 1
handlesthe large tables.  Or alternatively a small worker consumes 1, medium 2, and large 3 'units' from whatever size
poolhas been defined.  So you could have 6 small tables or two large tables in-progress simultaneously. 

David J.