From ralph.mason@telogis.com Thu May  17 22:45:55 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id AC5D69FC1D3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 May 2007 19:45:55 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 88318-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 May 2007 19:45:51 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from telogis.co.nz (telogis.co.nz [203.98.10.169])
	by postgresql.org (Postfix) with ESMTP id 0DCF99FC1D8
	for <pgsql-performance@postgresql.org>;
	Thu, 17 May 2007 19:45:49 -0300 (ADT)
Received: from RALPH2 ([::ffff:192.168.3.157])
	by telogis.co.nz with esmtp; Fri, 18 May 2007 10:45:43 +1200
	id 00978C09.464CDB1A.000014A2
From: "Ralph Mason" <ralph.mason@telogis.com>
To: pgsql-performance@postgresql.org
Subject: Ever Increasing IOWAIT
Date: Fri, 18 May 2007 10:45:29 +1200
Message-ID: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_011C_01C79939.A94845A0"
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AceY1REWtM7va4ygQ++E2ZK6zSfPDA==
Content-language: en-nz
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.344 tagged_above=0 required=5 tests=AWL=-0.576,
	BAYES_05=-1.11, HTML_MESSAGE=0.001, HTML_TEXT_AFTER_BODY=0.115,
	MSGID_MULTIPLE_AT=1.914
X-Spam-Level: 
X-Archive-Number: 200705/304
X-Sequence-Number: 24694

This is a multipart message in MIME format.

------=_NextPart_000_011C_01C79939.A94845A0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

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.

=20

The weird thing is that if we restart postgres it=92s fine for hours but =
over
time it goes bad again.

=20

(CPU usage graph here HYPERLINK
"http://www.flickr.com/photos/8347741@N02/502596262/"http://www.flickr.co=
m/p
hotos/8347741@N02/502596262/ )  You can clearly see where the restart
happens in the IO area

=20

This is Postgres  8.1.4 64bit.

=20

Anyone have any ideas?

=20

Thanks

Ralph

=20


--=20
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.
=20

------=_NextPart_000_011C_01C79939.A94845A0
Content-Type: text/html;
	charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dwindows-1250">


<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
	{mso-style-priority:99;
	mso-style-link:"Balloon Text Char";
	margin:0cm;
	margin-bottom:.0001pt;
	font-size:8.0pt;
	font-family:"Tahoma","sans-serif";}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
span.BalloonTextChar
	{mso-style-name:"Balloon Text Char";
	mso-style-priority:99;
	mso-style-link:"Balloon Text";
	font-family:"Tahoma","sans-serif";}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-GB link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><span lang=3DEN-US>We have a database running on a =
4 processor
machine.&nbsp; As time goes by the IO gets worse and worse peeking at =
about
200% as the machine loads up.<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>The weird thing is that if we =
restart
postgres it&#8217;s fine for hours but over time it goes bad =
again.<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>(CPU usage graph here <a
href=3D"http://www.flickr.com/photos/8347741@N02/502596262/">http://www.f=
lickr.com/photos/8347741@N02/502596262/</a>
)&nbsp; You can clearly see where the restart happens in the IO =
area<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>This is Postgres &nbsp;8.1.4 =
64bit.<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>Anyone have any =
ideas?<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>Thanks<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>Ralph<o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

</div>

</body>

</html>
<BR>

<P><FONT SIZE=3D2>--<BR>
Internal Virus Database is out-of-date.<BR>
Checked by AVG Free Edition.<BR>
Version: 7.5.432 / Virus Database: 268.15.9/573 - Release Date: =
5/12/2006 4:07 p.m.<BR>
</FONT> </P>

------=_NextPart_000_011C_01C79939.A94845A0--




From jd@commandprompt.com Thu May  17 23:02:05 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 726719FC03F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 May 2007 20:02:05 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 72663-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 May 2007 20:01:56 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from lists.commandprompt.com (host-254.commandprompt.net
	[207.173.203.254])
	by postgresql.org (Postfix) with ESMTP id 1A6B69FBAC8
	for <pgsql-performance@postgresql.org>;
	Thu, 17 May 2007 20:02:00 -0300 (ADT)
Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net
	[67.76.146.141]) (authenticated bits=0)
	by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l4HN22K9006346;
	Thu, 17 May 2007 16:02:03 -0700
Message-ID: <464CDF0A.5050309@commandprompt.com>
Date: Thu, 17 May 2007 16:02:34 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
User-Agent: Thunderbird 1.5.0.10 (X11/20070403)
MIME-Version: 1.0
To: Ralph Mason <ralph.mason@telogis.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Ever Increasing IOWAIT
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
In-Reply-To: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
Content-Type: text/plain; charset=windows-1250; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: ClamAV version 0.88.5,
	clamav-milter version 0.88.5 on projects.commandprompt.com
X-Virus-Status: Clean
X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by
	milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]);
	Thu, 17 May 2007 16:02:03 -0700 (PDT)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/305
X-Sequence-Number: 24695

Ralph Mason wrote:
> We have a database running on a 4 processor machine.  As time goes by=20
> the IO gets worse and worse peeking at about 200% as the machine loads =
up.
>=20
> =20
>=20
> The weird thing is that if we restart postgres it=92s fine for hours bu=
t=20
> over time it goes bad again.
>=20
> =20
>=20
> (CPU usage graph here=20
> http://www.flickr.com/photos/8347741@N02/502596262/ )  You can clearly =

> see where the restart happens in the IO area
>=20
> =20
>=20
> This is Postgres  8.1.4 64bit.

1. Upgrade to 8.1.9. There is a bug with autovac that is fixed that is=20
pretty important.

>=20
> =20
>=20
> Anyone have any ideas?
>=20

Sure... you aren't analyzing enough. You are using prepared queries that =

have plans that get stale... you are not running autovac... You are=20
cursed (kidding)..

Joshua D. Drake

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


--=20

       =3D=3D=3D The PostgreSQL Company: Command Prompt, Inc. =3D=3D=3D
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/





From ralph.mason@telogis.com Thu May  17 23:19:30 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 855129FC193
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 May 2007 20:19:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 25195-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 May 2007 20:19:22 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from telogis.co.nz (telogis.co.nz [203.98.10.169])
	by postgresql.org (Postfix) with ESMTP id 541079FBFB2
	for <pgsql-performance@postgresql.org>;
	Thu, 17 May 2007 20:19:25 -0300 (ADT)
Received: from RALPH2 ([::ffff:192.168.3.157])
	by telogis.co.nz with esmtp; Fri, 18 May 2007 11:19:22 +1200
	id 00948007.464CE2FC.0000150A
From: "Ralph Mason" <ralph.mason@telogis.com>
To: "'Joshua D. Drake'" <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
	<464CDF0A.5050309@commandprompt.com>
In-Reply-To: <464CDF0A.5050309@commandprompt.com>
Subject: Re: Ever Increasing IOWAIT
Date: Fri, 18 May 2007 11:19:09 +1200
Message-ID: <012d01c798d9$c6d520d0$547f6270$@mason@telogis.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AceY12E7RA4CVAR7SfWQ33gEUrsGLgAALUrg
Content-language: en-nz
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/306
X-Sequence-Number: 24696

Hi Josh - thanks for thoughts.

> 
> This is Postgres  8.1.4 64bit.

>1. Upgrade to 8.1.9. There is a bug with autovac that is fixed that is 
>pretty important.

We don't use pg_autovac - we have our own process that runs very often
vacuuming tables that are dirty. It works well and vacuums when activity is
happening.  During busy time active tables are vacuumed about once a minute.
The 'slack' space on busy tables sits at about 100% (eg the table has 2X the
number of pages it would after a cluster)  We use rows updated and deleted
to decide what to vacuum.  Those busy tables are reasonably small and take
less than a second to vacuum.  

Also, If it were a vacuuming problem why would a restart of the engine fix
it fully?

> 
> Anyone have any ideas?
> 

>Sure... you aren't analyzing enough. You are using prepared queries that 
>have plans that get stale... you are not running autovac... You are 
>cursed (kidding)..

The shape of the data never changes and we don't reanalyze on start-up so
suspect analyzing won't do much (although we do every so often).  

We don't use prepared queries - just lots of functions - but like I said
above the shape of the data doesn't change. So even if postgres stores plans
for those (does it?) it seems like it should be just fine.


Thanks
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.
 




From dev@archonet.com Fri May  18 09:12:14 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id C3C119FC25C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 May 2007 06:12:14 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 70628-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 May 2007 06:12:11 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id A11399FC238
	for <pgsql-performance@postgresql.org>;
	Fri, 18 May 2007 06:12:11 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 952B5766F7;
	Fri, 18 May 2007 10:12:04 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 6DDC327411A;
	Fri, 18 May 2007 10:12:01 +0100 (BST)
Message-ID: <464D6DE1.6040001@archonet.com>
Date: Fri, 18 May 2007 10:12:01 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Ralph Mason <ralph.mason@telogis.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Ever Increasing IOWAIT
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
In-Reply-To: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
Content-Type: text/plain; charset=windows-1250; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/311
X-Sequence-Number: 24701

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.
> 
> (CPU usage graph here HYPERLINK
> "http://www.flickr.com/photos/8347741@N02/502596262/"http://www.flickr.com/p
> hotos/8347741@N02/502596262/ )  You can clearly see where the restart
> happens in the IO area

I'm assuming here we're talking about that big block of iowait at about 
4-6am?

I take it vmstat/iostat show a corresponding increase in disk activity 
at that time.

The question is - what?
Does the number of PG processes increase at that time? If that's not 
intentional then you might need to see what your applications are up to.

Do you have a vacuum/backup scheduled for that time? Do you have some 
other process doing a lot of file I/O at that time?

> This is Postgres  8.1.4 64bit.

You'll want to upgrade to the latest patch release - you're missing 5 
lots of bug-fixes there.

-- 
   Richard Huxton
   Archonet Ltd



From mark.lewis@mir3.com Fri May  18 13:58:10 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 3CD1F9FC2C8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 May 2007 10:58:10 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 14390-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 May 2007 10:58:00 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mir3-fs.mir3.com (mail.mir3.com [65.208.188.100])
	by postgresql.org (Postfix) with ESMTP id 0CBBF9FC2AA
	for <pgsql-performance@postgresql.org>;
	Fri, 18 May 2007 10:58:05 -0300 (ADT)
Received: MIR3-FS.mir3.com 172.16.1.11 from 172.16.2.68 172.16.2.68 via HTTP
	with MS-WebStorage 6.0.6249
Received: from archimedes by MIR3-FS.mir3.com; 18 May 2007 06:58:03 -0700
Subject: Re: Ever Increasing IOWAIT
From: Mark Lewis <mark.lewis@mir3.com>
To: Ralph Mason <ralph.mason@telogis.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Organization: MIR3, Inc.
Date: Fri, 18 May 2007 06:58:03 -0700
Message-Id: <1179496683.31471.72.camel@archimedes>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.1 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/321
X-Sequence-Number: 24711

You're not swapping are you?  One explanation could be that PG is
configured to think it has access to a little more memory than the box
can really provide, which forces it to swap once it's been running for
long enough to fill up its shared buffers or after a certain number of
concurrent connections are opened.

-- Mark Lewis

On Fri, 2007-05-18 at 10:45 +1200, 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.
>=20
> =20
>=20
> The weird thing is that if we restart postgres it=E2=80=99s fine for hour=
s but
> over time it goes bad again.
>=20
> =20
>=20
> (CPU usage graph here
> http://www.flickr.com/photos/8347741@N02/502596262/ )  You can clearly
> see where the restart happens in the IO area
>=20
> =20
>=20
> This is Postgres  8.1.4 64bit.
>=20
> =20
>=20
> Anyone have any ideas?
>=20
> =20
>=20
> Thanks
>=20
> Ralph
>=20
> =20
>=20
>=20
>=20
> --
> 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.
>=20
>=20



From ralph.mason@telogis.com Sun May  20 20:41:11 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E14D29FB4DC
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 20 May 2007 17:41:11 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 39277-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 20 May 2007 17:41:08 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from telogis.co.nz (telogis.co.nz [203.98.10.169])
	by postgresql.org (Postfix) with ESMTP id A12839FB33C
	for <pgsql-performance@postgresql.org>;
	Sun, 20 May 2007 17:41:08 -0300 (ADT)
Received: from RALPH2 ([::ffff:192.168.3.157])
	by telogis.co.nz with esmtp; Mon, 21 May 2007 08:40:55 +1200
	id 00978C09.4650B257.00002F96
From: "Ralph Mason" <ralph.mason@telogis.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
	<464D6DE1.6040001@archonet.com>
In-Reply-To: <464D6DE1.6040001@archonet.com>
Subject: Re: Ever Increasing IOWAIT
Date: Mon, 21 May 2007 08:40:39 +1200
Message-ID: <005b01c79b1f$20aeb340$620c19c0$@mason@telogis.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AceZLOfOYWzCG3IgTL+sb1NK9aIOjwB8Zp8A
Content-Language: en-nz
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/384
X-Sequence-Number: 24774

Ralph Mason wrote:
> We have a database running on a 4 processor machine.  As time goes by=20
> the IO gets worse and worse peeking at about 200% as the machine loads =
up.
>=20
> The weird thing is that if we restart postgres it=92s fine for hours =
but=20
> over time it goes bad again.
>=20
> (CPU usage graph here HYPERLINK
> "http://www.flickr.com/photos/8347741@N02/502596262/"http://www.flickr
> .com/p hotos/8347741@N02/502596262/ )  You can clearly see where the=20
> restart happens in the IO area
>I'm assuming here we're talking about that big block of iowait at about =

>4-6am?

Actually no - that is a vacuum of the whole database to double check =
It's
not a vacuuming problem (I am sure it's not).  The restart is at at =
22:00
where you see the io drop to nothing, the database is still doing the =
same
work.

>>I take it vmstat/iostat show a corresponding increase in disk activity =

>>at that time.

I didn't know you could have IO/wait without disk activity - I will =
check
that out.=20

>>The question is - what?
>>Does the number of PG processes increase at that time? If that's not=20
>>intentional then you might need to see what your applications are up =
to.

No the number of connections is stable and the jobs they do stays the =
same,
just this deteriorating of i/o wait over time.

>>Do you have a vacuum/backup scheduled for that time? Do you have some=20
>>other process doing a lot of file I/O at that time?

> This is Postgres  8.1.4 64bit.

>You'll want to upgrade to the latest patch release - you're missing 5=20
>lots of bug-fixes there.

Thanks - will try that.


--=20
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.
=20




From ralph.mason@telogis.com Sun May  20 20:42:02 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 3A5D79FB62C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 20 May 2007 17:42:02 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 55586-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 20 May 2007 17:41:55 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from telogis.co.nz (telogis.co.nz [203.98.10.169])
	by postgresql.org (Postfix) with ESMTP id 94BEC9FB33C
	for <pgsql-performance@postgresql.org>;
	Sun, 20 May 2007 17:41:58 -0300 (ADT)
Received: from RALPH2 ([::ffff:192.168.3.157])
	by telogis.co.nz with esmtp; Mon, 21 May 2007 08:41:57 +1200
	id 00978C2A.4650B295.00002FCB
From: "Ralph Mason" <ralph.mason@telogis.com>
To: "'Mark Lewis'" <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
	<1179496683.31471.72.camel@archimedes>
In-Reply-To: <1179496683.31471.72.camel@archimedes>
Subject: Re: Ever Increasing IOWAIT
Date: Mon, 21 May 2007 08:41:41 +1200
Message-ID: <005c01c79b1f$459c2160$d0d46420$@mason@telogis.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AceZVNtdNIBT+Zh0TKmplZ8CDVR2SABykjSQ
Content-Language: en-nz
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/385
X-Sequence-Number: 24775



>You're not swapping are you?  One explanation could be that PG is
>configured to think it has access to a little more memory than the box
>can really provide, which forces it to swap once it's been running for
>long enough to fill up its shared buffers or after a certain number of
>concurrent connections are opened.
>
>-- Mark Lewis

No - no swap on this machine. The number of connections is stable.

Ralph


On Fri, 2007-05-18 at 10:45 +1200, 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.
>=20
> =20
>=20
> The weird thing is that if we restart postgres it=E2=80=99s fine for =
hours but
> over time it goes bad again.
>=20
> =20
>=20
> (CPU usage graph here
> http://www.flickr.com/photos/8347741@N02/502596262/ )  You can clearly
> see where the restart happens in the IO area
>=20
> =20
>=20
> This is Postgres  8.1.4 64bit.
>=20
> =20
>=20
> Anyone have any ideas?
>=20
> =20
>=20
> Thanks
>=20
> Ralph
>=20
> =20
>=20
>=20
>=20
> --
> 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.
>=20
>=20

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

--=20
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.
=20

--=20
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.
=20




From tgl@sss.pgh.pa.us Sun May  20 21:58:15 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 0D6479FBA22
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 20 May 2007 18:58:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 42337-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 20 May 2007 18:58:01 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 0FA709FB65C
	for <pgsql-performance@postgresql.org>;
	Sun, 20 May 2007 18:58:10 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l4KLtoj0017451;
	Sun, 20 May 2007 17:55:50 -0400 (EDT)
To: "Ralph Mason" <ralph.mason@telogis.com>
cc: "'Richard Huxton'" <dev@archonet.com>, pgsql-performance@postgresql.org
Subject: Re: Ever Increasing IOWAIT 
In-reply-to: <005b01c79b1f$20aeb340$620c19c0$@mason@telogis.com> 
References: <011b01c798d5$141365a0$3c3a30e0$@mason@telogis.com>
	<464D6DE1.6040001@archonet.com>
	<005b01c79b1f$20aeb340$620c19c0$@mason@telogis.com>
Comments: In-reply-to "Ralph Mason" <ralph.mason@telogis.com>
	message dated "Mon, 21 May 2007 08:40:39 +1200"
Date: Sun, 20 May 2007 17:55:50 -0400
Message-ID: <17450.1179698150@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/386
X-Sequence-Number: 24776

"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.

			regards, tom lane



From ralph.mason@telogis.com Mon May  21 05:18:05 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id F2C619FBA35
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 21 May 2007 02:18:05 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 74602-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 21 May 2007 02:18:01 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from telogis.co.nz (telogis.co.nz [203.98.10.169])
	by postgresql.org (Postfix) with ESMTP id 1D1CC9FB67D
	for <pgsql-performance@postgresql.org>;
	Mon, 21 May 2007 02:18:01 -0300 (ADT)
Received: from RALPH2 ([::ffff:192.168.3.157])
	by telogis.co.nz with esmtp; Mon, 21 May 2007 17:17:59 +1200
	id 00836B5A.46512B87.00003770
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
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcebKfWcYDTranBwRoWZc6Yf8W7ZKAAPHhbQ
Content-Language: en-nz
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.276 tagged_above=0 required=5 tests=AWL=-0.528,
	BAYES_05=-1.11, MSGID_MULTIPLE_AT=1.914
X-Spam-Level: 
X-Archive-Number: 200705/388
X-Sequence-Number: 24778

"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.