Re: Ever Increasing IOWAIT

From: "Ralph Mason" <ralph(dot)mason(at)telogis(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "'Richard Huxton'" <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Ever Increasing IOWAIT
Date: 2007-05-21 05:17:42
Message-ID: 00d501c79b67$5bf18480$13d48d80$@mason@telogis.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Ralph Mason" <ralph(dot)mason(at)telogis(dot)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.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2007-05-21 09:14:14 Re: Rewriting DISTINCT and losing performance
Previous Message Chuck D. 2007-05-21 04:28:30 Rewriting DISTINCT and losing performance