Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 855129FC193 for ; 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 ; 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 ; 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" To: "'Joshua D. Drake'" 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.