Re: Regular disk activity of an idle DBMS

On 05/29/2011 02:42 PM, Andrej Podzimek wrote:
> I identified the most active process, at least twenty times more
> active than any other process on the system:
>
> postgres 3086 0.1 0.0 34688 2584 ? Ss 03:11 1:16
> postgres: stats collector process
>
> So it's the statistics collector. However, there does not seem to be
> any database activity at all. I tried looking at the numbers returned
> by this query:
>
> select datname, tup_returned, tup_fetched from pg_stat_database ;
>
> Nothing changes there. When OpenFire, Courier-MTA and Apache are
> restarted, a few numbers change, but othrewise they remain unchanged
> pretty long. There is no obvious activity that could trigger a disk
> write 20 times a minute...

There are things that the statistics collector might be updating that
don't show up in the pg_stat_database totals. It aims to write updates
approximately every 500ms, so your write rate sounds normal. The
expectation is that the operating system is actually caching most of
those, so that the actual load on the system is minimal. So it sounds
like you've identified the cause here, and it is normal, expected activity.

One thing that can cause statistics overhead to be higher than it should
be is a larger statistics file than is strictly necessary. We hear
reports of those sometimes, I've never been completely clear on all of
the possible causes that make this happen. But running "select
pg_stat_reset();" should clear that out and start fresh again. That
will sometimes eliminate situations where the I/O seems larger than it
should be for people.

If you do that, and there's still activity going on, there's no easy way
to fix that. As mentioned in
http://www.postgresql.org/docs/9.0/static/monitoring-stats.html , it's
possible to change PGSTAT_STAT_INTERVAL at server compile time to make
it write statistics less frequently. There's no easier way to adjust
that though.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books