From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benjamin Minshall <minshall@intellicon.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: stats collector process high CPU utilization
Date: Thu, 01 Mar 2007 15:12:56 -0500
Message-ID: <16282.1172779976@sss.pgh.pa.us> (raw)
In-Reply-To: <45CCBF13.2000707@intellicon.biz>
References: <45CB5D06.7090004@intellicon.biz>
<7994.1170960391@sss.pgh.pa.us>
<45CB9330.3000603@intellicon.biz>
<19935.1170969862@sss.pgh.pa.us>
<45CB9C4D.30902@intellicon.biz>
<20601.1170972476@sss.pgh.pa.us>
<45CBA443.9090101@intellicon.biz>
<7959.1170986672@sss.pgh.pa.us>
<62089.75.14.32.120.1170994418.squirrel@intellicon.biz>
<14668.1170995218@sss.pgh.pa.us>
<20070209142459.GB4253@alvh.no-ip.org>
<24717.1171033699@sss.pgh.pa.us>
<29832.1171036557@sss.pgh.pa.us>
<45CCA0DB.2010903@intellicon.biz>
<1116.1171038786@sss.pgh.pa.us>
<45CCBF13.2000707@intellicon.biz>
Benjamin Minshall <minshall@intellicon.biz> 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...
inline patch 1: pgstat.c (+1/-1 lines) download inline-1.patchIndex: 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, minshall@intellicon.biz
Subject: Re: stats collector process high CPU utilization
In-Reply-To: <16282.1172779976@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox