X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A9E58329D22 for ; Thu, 9 Sep 2004 05:05:44 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56318-05 for ; Thu, 9 Sep 2004 04:05:42 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 193D5329D14 for ; Thu, 9 Sep 2004 05:05:42 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i8945fCn057151 for ; Thu, 9 Sep 2004 04:05:41 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i893o4gB052443 for pgsql-admin@postgresql.org; Thu, 9 Sep 2004 03:50:04 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: When will my database crash? Date: Wed, 08 Sep 2004 22:55:54 -0400 Organization: cbbrowne Computing Inc Lines: 30 Message-ID: References: <20040908020214.M78932@eg-1.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through Obscurity, linux) Cancel-Lock: sha1:44AyFOqKsqMNuBt5hBAzackFFf4= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/83 X-Sequence-Number: 14786 simon@2ndquadrant.com ("Simon Riggs") wrote: > Proactive, planned maintenance is more manageable than sudden, > grinding failure when you're at home in bed. Make sure your manager > is part of the call-out plan, thats usually a good way to make them > care about maintenance. And regular maintenance allows "maintenance sessions" to become individually _much_ less costly. If the heavily-updated tables were vacuumed daily or even hourly, it is quite likely that the issue would go away, from whence comes the "pg_autovacuum" strategy. > For most applications, you should be running VACUUM FULL at least > monthly, since any more than that is effectively the same thing as > "never", as your case shows. So long as you vacuum heavily-updated tables often enough, run 'plain VACUUM ANALYZE' once in a while, to catch the transaction ID rollover issue, and have enough space in the free space map, VACUUM FULL shouldn't be necessary. At Afilias, we _never_ run VACUUM FULL in the production transactional environment, or at least we haven't needed to since migrating to 7.4. (On 7.2, we needed to do so periodically, as well as periodically reindexing some tables.) -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www3.sympatico.ca/cbbrowne/sap.html "A good system can't have a weak command language." -- Alan Perlis [This explains why MS-DOS and Windows can't possibly be good systems...]