From: "Ralph Mason" <ralph.mason@telogis.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'Richard Huxton'" <dev@archonet.com>,
  pgsql-performance@postgresql.org
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
	<464D6DE1.6040001@archonet.com>
	<005b01c79b1f$20aeb340$620c19c0$@mason@telogis.com>
	<17450.1179698150@sss.pgh.pa.us>
In-Reply-To: <17450.1179698150@sss.pgh.pa.us>
Subject: Re: Ever Increasing IOWAIT 
Date: Mon, 21 May 2007 17:17:42 +1200
Message-ID: <00d501c79b67$5bf18480$13d48d80$@mason@telogis.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: 7bit
Thread-Index: AcebKfWcYDTranBwRoWZc6Yf8W7ZKAAPHhbQ
Content-Language: en-nz

"Ralph Mason" <ralph.mason@telogis.com> writes:
> Ralph Mason wrote:
>> We have a database running on a 4 processor machine.  As time goes by 
>> the IO gets worse and worse peeking at about 200% as the machine loads
up.
>>  
>> The weird thing is that if we restart postgres it's fine for hours but
>> over time it goes bad again.

>Do you by any chance have stats collection enabled and
>stats_reset_on_server_start set to true? If so, maybe this is explained
>by growth in the size of the stats file over time.  It'd be interesting
>to keep an eye on the size of $PGDATA/global/pgstat.stat over a fast-to-
>slow cycle.

We do because we use the stats to figure out when we will vacuum.  Our
vacuum process reads that table and when it runs resets it using
pg_stat_reset() to clear it down each time it runs (about ever 60 seconds
when the db is very busy), stats_reset_on_server_restart is off.

Interestingly after a suggestion here I went and looked at the IO stat at
the same time.  It shows the writes as expected and picking up exactly where
they were before the reset, but the reads drop dramatically - like it's
reading far less data after the reset.

I will watch the size of the pgstat.stat table.

Ralph


-- 
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.9/573 - Release Date: 5/12/2006
4:07 p.m.