Re: stats collector process high CPU utilization

От: Tom Lane
Тема: Re: stats collector process high CPU utilization
Дата: ,
Msg-id: 16282.1172779976@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: stats collector process high CPU utilization  (Benjamin Minshall)
Ответы: Re: stats collector process high CPU utilization  (Benjamin Minshall)
Re: stats collector process high CPU utilization  ("Merlin Moncure")
Re: stats collector process high CPU utilization  (Bruce Momjian)
Список: pgsql-performance

Скрыть дерево обсуждения

stats collector process high CPU utilization  (Benjamin Minshall, )
 Re: stats collector process high CPU utilization  (Tom Lane, )
  Re: stats collector process high CPU utilization  (Benjamin Minshall, )
   Re: stats collector process high CPU utilization  (Tom Lane, )
    Re: stats collector process high CPU utilization  (Benjamin Minshall, )
     Re: stats collector process high CPU utilization  (Tom Lane, )
      Re: stats collector process high CPU utilization  (Benjamin Minshall, )
       Re: stats collector process high CPU utilization  (Tom Lane, )
        Re: stats collector process high CPU utilization  (Benjamin Minshall, )
         Re: stats collector process high CPU utilization  (Tom Lane, )
          Re: stats collector process high CPU utilization  (, )
           Re: stats collector process high CPU utilization  (Tom Lane, )
            Re: stats collector process high CPU utilization  (Alvaro Herrera, )
             Re: stats collector process high CPU utilization  (Tom Lane, )
              Re: stats collector process high CPU utilization  (Tom Lane, )
               Re: stats collector process high CPU utilization  (Benjamin Minshall, )
                Re: stats collector process high CPU utilization  (Tom Lane, )
                 Re: stats collector process high CPU utilization  (Benjamin Minshall, )
                  Re: stats collector process high CPU utilization  (Tom Lane, )
                   Re: stats collector process high CPU utilization  (Benjamin Minshall, )
                   Re: stats collector process high CPU utilization  ("Merlin Moncure", )
                    Re: stats collector process high CPU utilization  (Tom Lane, )
                     Re: stats collector process high CPU utilization  ("Merlin Moncure", )
                      Re: stats collector process high CPU utilization  (Tom Lane, )
                       Re: stats collector process high CPU utilization  (Magnus Hagander, )
                   Re: stats collector process high CPU utilization  (Bruce Momjian, )
                    Re: stats collector process high CPU utilization  ("Joshua D. Drake", )

Benjamin Minshall <> writes:
> Tom Lane wrote:
>> It's sounding like what you had was just transient bloat, in which case
>> it might be useful to inquire whether anything out-of-the-ordinary had
>> been done to the database right before the excessive-CPU-usage problem
>> started.

> I don't believe that there was any unusual activity on the server, but I
> have set up some more detailed logging to hopefully identify a pattern
> if the problem resurfaces.

A further report led us to realize that 8.2.x in fact has a nasty bug
here: the stats collector is supposed to dump its stats to a file at
most every 500 milliseconds, but the code was actually waiting only
500 microseconds :-(.  The larger the stats file, the more obvious
this problem gets.

If you want to patch this before 8.2.4, try this...

Index: pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.140.2.2
diff -c -r1.140.2.2 pgstat.c
*** pgstat.c    26 Jan 2007 20:07:01 -0000    1.140.2.2
--- pgstat.c    1 Mar 2007 20:04:50 -0000
***************
*** 1689,1695 ****
      /* Preset the delay between status file writes */
      MemSet(&write_timeout, 0, sizeof(struct itimerval));
      write_timeout.it_value.tv_sec = PGSTAT_STAT_INTERVAL / 1000;
!     write_timeout.it_value.tv_usec = PGSTAT_STAT_INTERVAL % 1000;

      /*
       * Read in an existing statistics stats file or initialize the stats to
--- 1689,1695 ----
      /* Preset the delay between status file writes */
      MemSet(&write_timeout, 0, sizeof(struct itimerval));
      write_timeout.it_value.tv_sec = PGSTAT_STAT_INTERVAL / 1000;
!     write_timeout.it_value.tv_usec = (PGSTAT_STAT_INTERVAL % 1000) * 1000;

      /*
       * Read in an existing statistics stats file or initialize the stats to


            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: "Alex Deucher"
Дата:
Сообщение: Re: strange performance regression between 7.4 and 8.1
От: "Joshua D. Drake"
Дата:
Сообщение: Re: strange performance regression between 7.4 and 8.1