Re: Huge number of disk writes after migration to 8.1

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > Tom Lane wrote:
> >> I'm tempted to change pgstat_recv_vacuum
> >> and pgstat_recv_analyze so that they will not create new hash entries,
> >> but only insert the row count if the hash entry already exists. I am a
> >> bit worried that I might be missing something about possible
> >> interactions with autovacuum though. I see that autovac skips vacuuming
> >> tables that have no hash entry ... is there some circular reasoning
> >> going on there?
>
> > The idea was that autovac should skip tables for which it doesn't have
> > info, because it can't decide and we chose to err on the side of
> > caution. However, after a vacuum or analyze we do have info about the
> > table, which is what we store in the pgstat_recv functions
> > inconditionally. Thus the next autovacuum is able to make an informed
> > decision about this table.
>
> However, if it skips the table for lack of info, then there still won't
> be any info next time.

Of course, because there's nowhere to take it from.

> More to the point, that's not why we skip entry-less tables. We skip
> entry-less tables because there is no need for autovac to do anything to
> a table that's not being modified, and if it *is* being modified then
> those operations will result in creation of a table entry. So I'm still
> not seeing why vacuum or analyze should force creation of an entry.

Well, if you issue a manual vacuum then you can argue that it has been
"modified" (it's seeing some activity). But immediately after a vacuum
the table will not be vacuumed by autovac if there's no other activity,
because there's no need for it, so we can create the entry anyway and it
won't make an immediate difference. However, it (to have the pgstat
table entry) _will_ make a difference as soon as that entry is modified
by other activity, because the number of tuples pgstat knows about will
be more accurate.

So maybe we could supress the entry creation on analyze (because analyze
_can_ force a vacuum during the next autovac iteration, which is
something we may want to avoid), but keep it on vacuum.

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 17.7", W 73º 14' 26.8"
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)