Обсуждение: Why is my stats collector so busy?

От:
Laszlo Nagy
Дата:

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.

Thank you,

   Laszlo


От:
Bruce Momjian
Дата:

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.

What version of Postgres are you using?

--
  Bruce Momjian  <>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

От:
Tom Lane
Дата:

Laszlo Nagy <> writes:
> On a 8 processor system, my stats collector is always at 100% CPU.

What platform?  What Postgres version?

            regards, tom lane

От:
Merlin Moncure
Дата:

On Fri, Jun 5, 2009 at 9:38 AM, Bruce Momjian<> 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.
>
> What version of Postgres are you using?

A little context here.  The stats collector is really version
dependent...it gets tweaked just about every version of postgres...it
is more or less unrecognizable since the 8.0 version of postgresql,
where I would simply turn it off and run analyze myself.

Be prepared for the advice to consider upgrading to help deal with
this issue.  8.4 in fact has some enhancements that will help with
situations like this.

merlin

От:
Robert Schnabel
Дата:

> On Fri, Jun 5, 2009 at 9:38 AM, Bruce Momjian<> 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


От:
Laszlo Nagy
Дата:

>
> What version of Postgres are you using?
>
>

8.3.5 on FreeBSD amd64


От:
Laszlo Nagy
Дата:

Tom Lane wrote:
> Laszlo Nagy <> writes:
>
>> On a 8 processor system, my stats collector is always at 100% CPU.
>>
>
> What platform?  What Postgres version?
>
>             regards, tom lane
>
>
8.3.5 on FreeBSD 7.0 amd64

От:
Tom Lane
Дата:

Laszlo Nagy <> writes:
> On a 8 processor system, my stats collector is always at 100% CPU.
> 8.3.5 on FreeBSD 7.0 amd64

Hmm.  How many tables in the installation?  Or perhaps more to the
point, how large is $PGDATA/global/pgstat.stat?  It might be that
it's just spending too much time dumping out that file.  There's
a fix for that planned for 8.4 ...

            regards, tom lane