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 2245D537C2 for ; Thu, 21 Apr 2005 16:08:35 -0300 (ADT) 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 72842-02 for ; Thu, 21 Apr 2005 19:08:24 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 0EE125379E for ; Thu, 21 Apr 2005 16:08:23 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j3LJ8Gda073004 for ; Thu, 21 Apr 2005 19:08:17 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j3LJ42Ze072047 for pgsql-admin@postgresql.org; Thu, 21 Apr 2005 19:04:02 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: vacuumdb -v output Date: Thu, 21 Apr 2005 14:43:56 -0400 Organization: cbbrowne Computing Inc Lines: 66 Message-ID: <607jiw2cnn.fsf@dba2.int.libertyrms.com> References: <003301c5469b$eead09c0$0b02a594@csekcopley2> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:juJA96bWy61YbkdkX2+GTwQB6k0= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200504/305 X-Sequence-Number: 17318 kcopley@bart.gov ("Kevin Copley") writes: > Hi, > > I've just put a system into production in which some tables are updated frequently - several times per > second. > > I'm doing a nightly vacuumdb -v, but am not sure if it's achieving anything. Here's the output for one > table: > >   > > ---------------------------------------------------------------------------------------------------------------------------------------------------- > > INFO:  vacuuming "public.fip_track_circuit" > INFO:  index "fip_track_circuit_pk" now contains 1557427 row versions in 4538 pages > DETAIL:  10 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.22s/0.14u sec elapsed 6.51 sec. > INFO:  "fip_track_circuit": removed 10 row versions in 9 pages > DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO:  "fip_track_circuit": found 10 removable, 1557427 nonremovable row versions in 14305 pages > DETAIL:  1555321 dead row versions cannot be removed yet. > There were 1 unused item pointers. > 0 pages are entirely empty. > CPU 0.42s/0.24u sec elapsed 6.82 sec. > INFO:  vacuuming "public.fip_xl_switch" > > > ---------------------------------------------------------------------------------------------------------------------------------------------------- > >   > > I'm concerned about ".... 1555321 dead row versions cannot be removed yet...." > >   > > I'd be extremely grateful if anyone could cast some light on this. That sounds pretty bad... It seems likely to me that you have some idle transaction hanging around that is preventing VACUUM from doing anything useful about cleaning out that table. If you have command statistics turned on, you might try the query: select * from pg_stat_activity where current_query <> ''; If you see some items that indicate ' in transaction' that are rather old, that's a nice "smoking gun" to indicate where the problem lies. Long running transactions are the bane of our existence, and are an all too common result of buggy connection pool implementations :-(. Go "thump" the offending connection, and you should see things clear out. After that, you may need to do a VACUUM FULL to get things totally cleared out, and probably a REINDEX after that... -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"