Re: Why is my stats collector so busy?

> On Fri, Jun 5, 2009 at 9:38 AM, Bruce Momjian<bruce(at)momjian(dot)us> wrote:
>
>> Laszlo Nagy wrote:
>>
>>> On a 8 processor system, my stats collector is always at 100% CPU.
>>> Meanwhile disk I/O is very low. We have many databases, they are
>>> accessed frequently. Sometimes there are big table updates, but in most
>>> of the time only simple queries are ran against the databases, returning
>>> a few records only. From the maximum possible 8.0 system load, the
>>> average load is always above 1.1 and from this, 1.0 is the stats
>>> collector and 0.1 is the remaining of the system. If I restart the
>>> postgresql server, then the stats collector uses 0% CPU for about 10
>>> minutes, then goes up to 100% again. Is there a way to tell why it is
>>> working so much?
>>>
>>> I asked this problem some months ago on a different mailing list. I was
>>> asked to provide tracebacks of the stats collector, but due to a bug in
>>> the FreeBSD ppid() function, I'm not able to trace the stats collector
>>>
I've been having the same problem for several months now. I posted
something to the novice list back in January but it really never went
anywhere so I dropped it. Formalities... v8.3.7 build 1400, Windows XP
64-bit, two Opteron 2218. This is my personal database. It runs a
single database locally on my box and I'm the only person that ever
accesses it.

From a fresh start of the server I get one postgres process that will
run 100% of a CPU with no I/O essentially forever. If I use Process
Explorer to identify the process and attach the debugger it will
terminate and then restart with another process id. When I saw the
previous post I looked at the process a bit closer and below is what is
listed from Process Explorer for the problem process:

\BaseNamedObjects\pgident(3432): postgres: stats collector process

What I have resorted to is just suspending this process so it's not
wasting one of my CPUs and everything seems to be working fine. I
realize this is just a bandage but it works for me. I'm just a novice
so if anyone has suggestions on what I can do to provide more
information to try and track this down I'd appreciate it. I figured it
was just something I had screwed up but now that someone else is seeing
the same problem I know it's not just my problem.

Bob