Re: reducing statistics write overhead

Alvaro Herrera wrote:
> Tom Lane escribió:
> 
>> (In fact, maybe this patch ought to include some sort of maximum update
>> rate tunable?  The worst case behavior could actually be WORSE than now.)
> 
> Some sort of "if stats were requested in the last 500 ms, just tell the
> requester to read the existing file".
> 
> Things that come to mind:
> 
> - autovacuum could use a more frequent stats update in certain cases
> 

[ digging up an old tread ... ]

I recently tested the on-demand stats write on an installation with over 100
databases on a single cluster (ca 5MB stats file). As I saw no visible reduction
in the stats file updates, I started investigating. Turned out that autovacuum
was configured with 1minute naptime, and was constantly walking the database list
and launching workers. Each worker does several stats file requests, and often
it turns out that the file is older than the allowed 10ms. Increasing the naptime
somewhat alleviates the problem, but still introduces peaks.

As I understand the autovacuum workers need up to date stats to minimize the
risk of re-vacuuming a table (in case it was already vacuumed by someone else).
However, with the visibility map based vacuums the cost of re-vacuuming  should
be reasonably low. So I'd propose to increase the max allowed stats age for
autovacuum workers. Perhaps even as high as to allow reusing of the file requested
by the launcher. Or am I missing something?

regards,
Martin