Re: BUG #12071: Stat collector went crasy (50MB/s constant writes)

On 26.11.2014 23:06, Maxim Boguk wrote:
>
>
>     I'm not sure I understand what you mean.
>
>     The timestamp *IS* updated on every write - see this:
>
>     https://github.com/postgres/postgres/blob/REL9_2_STABLE/src/backend/postmaster/pgstat.c#L3469
>
>
>  I mean that usually 99.9% writes goes through mmap, which update the
> pgstat.tmp file timestamp (but not update last_statwrite value).

I don't follow. Can you explain what you mean by the mmap? I don't see
any mmap stuff in pgstat.c, nor I remember dealing with mmap in my
patches in this area ...

You mentioned the snippet:

    if (last_statwrite < last_statrequest)
        pgstat_write_statsfile(false);

and essentially what pgstat_write_statsfile() does is this:


    1) open pgstat.tmp file
    2) update globalStats.stats_timestamp to GetCurrentTimestamp();
    3) write globalStats to the new file
    4) ...

so it *does* update the timestamp on each write.

> But that enough to make backend_read_statsfile happy around
> https://github.com/postgres/postgres/blob/REL9_2_STABLE/src/backend/postmaster/pgstat.c#L3972
> without kicking  poor statistic collector (and forcing full file rewrite) at
> https://github.com/postgres/postgres/blob/REL9_2_STABLE/src/backend/postmaster/pgstat.c#L3978

Well, the thing is that with 10 databases and 1s naptime, the launcher
starts a new autovacuum process every 100 ms. The first thing every
worker does is pgstat_read_statsfile() and this checks whether the stats
are older than 10 ms (which it is, because the last worker started
~100ms ago), and sends a new inquiry.

Regular backends use 640ms threshold, which is less than 100ms.

> PS: 1second autovacuum_naptime could be useful if you have small but
> very very actively updated tables, over 1 minute they could growth
> 100-500x size without autovacuum.
> 1minute naptime will have the same issue with 500 small idle databases :).

True. That's why I have reworked this in 9.3.

Anyway, this is not a bug - it works as designed/expected. It may be
imperfect, but that's how it is. The only thing you can do in 9.2 is
moving the pgstat.tmp to a tmpfs filesystem

Tomas