Обсуждение: Why is my stats collector so busy?
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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Laszlo Nagy <gandalf@shopzeus.com> writes: > On a 8 processor system, my stats collector is always at 100% CPU. What platform? What Postgres version? regards, tom lane
On Fri, Jun 5, 2009 at 9:38 AM, Bruce Momjian<bruce@momjian.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. > > 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
> On Fri, Jun 5, 2009 at 9:38 AM, Bruce Momjian<bruce@momjian.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
> > What version of Postgres are you using? > > 8.3.5 on FreeBSD amd64
Tom Lane wrote: > Laszlo Nagy <gandalf@shopzeus.com> 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
Laszlo Nagy <gandalf@shopzeus.com> 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