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 8B70C329DC4 for ; Wed, 8 Sep 2004 05:05:34 +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 93518-01 for ; Wed, 8 Sep 2004 04:05:29 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 9C6D8329DB3 for ; Wed, 8 Sep 2004 05:05:29 +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 i8845SCn094725 for ; Wed, 8 Sep 2004 04:05:28 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i883o3xU090801 for pgsql-admin@postgresql.org; Wed, 8 Sep 2004 03:50:03 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: When will my database crash? Date: Tue, 07 Sep 2004 23:22:31 -0400 Organization: cbbrowne Computing Inc Lines: 46 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:VipEQTVN88pCeU6bQDrMLUtuW5Q= 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/70 X-Sequence-Number: 14773 After takin a swig o' Arrakan spice grog, sc@eg-1.com ("S. C.") belched out: > We have a postgres 7.4 database which never vacuum for 4 months. I try to > vacuum one time. But my manager can't bear the low performance of website. So > I had to kill the vacuum before it finished. Is it ok for a postgres 7.4 > database never do vacuuming? > > We have 10,000 trans every day. That would be 10,000 inserts and 10,000 > updates each day. No, what you need to do is to _regularly_ vacuum it. That is, _ALL THE TIME_. Not vacuuming for four months was a severe error, which means that you now need to run a really full maintenance cycle. At the next possible opportunity, you really need to do a VACUUM FULL on the whole database. It's a little like running an automobile and not bothering to change the oil or to do any other maintenance for several years. You'd discover that you need to take the automobile entirely out of service for an extended period in order to do _major_ repair work, and the fact that this inconveniences someone is irrelevant. _Perhaps_ you can do the maintenance a table at a time, here and there. But whenever you kill a vacuum, you are discarding any work it has done. Don't waste everyone's time by watching it for a while, and then stopping it; keep vacuuming until the tables are DONE. And then put in a proper maintenance regimen, perhaps using pg_autovacuum, or at least doing a vacuum of the whole database at low periods either daily, or at worst, weekly. In some of our systems, we have tables that need to get vacuumed every five minutes so that the cost of vacuuming never gets unbearable. Vacuuming every five minutes goes in a flash because there are normally only a few hundred tuples, with only a couple hundred of them "live." For those tables, vacuuming once an hour would be unacceptable, and would destroy performance of the systems. -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org'). http://www3.sympatico.ca/cbbrowne/wp.html 'Mounten' wird fuer drei Dinge benutzt: 'Aufsitzen' auf Pferde, 'einklinken' von Festplatten in Dateisysteme, und, nun, 'besteigen' beim Sex.