[Solved] 8.3 Stats Collector Stuck at 100% CPU

Hi all,

I wanted to share the solution to $SUBJECT. I noticed that the stats
collector process for one of our servers was pegged around 80-100% CPU
usage, and looked to have been that way for several days or weeks.
Server info:
* Postgres 8.3.4
* Linux 2.6.18-92.1.13.el5 #1 SMP x86_64
* CentOS release 5.2 (Final)
* 7 databases, total of ~4300 rows in pg_class tables across all
these databases

I found several previous threads complaining about the same problem
with 8.3. A few concluded this was a FreeBSD-specific problem (it
might have been, for them), and one was with 8.4, but was from having
650 or so databases in a single instance.

The solution for me was simply to run pg_stat_reset(), and the problem
went away within a few seconds. The $PGDATA/global/pgstat.stat file
was 1.2GB before the reset, and went down to ~250KB after the reset.
It looks like this file is adding on 4MB or so per day at the current
rate, so I'll likely have to do this again in a few months.

It might be worthwhile to document this fix somewhere, perhaps at
http://www.postgresql.org/docs/8.3/static/monitoring-stats.html#MONITORING-STATS-FUNCS-TABLE
since I had to search around quite a bit to find the pg_stat_reset()
solution below, thanks to Tom Lane:

"100% of CPU utilization postgres process":
http://archives.postgresql.org/pgsql-general/2010-01/msg01079.php

Josh