X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 510493A3B7C for ; Sun, 24 Oct 2004 06:08:16 +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 41941-10 for ; Sun, 24 Oct 2004 05:07:46 +0000 (GMT) Received: from roue.portalpotty.net (roue.portalpotty.net [69.44.62.206]) by svr1.postgresql.org (Postfix) with ESMTP id 58B8C3A3B09 for ; Sun, 24 Oct 2004 06:08:07 +0100 (BST) Received: from max by roue.portalpotty.net with local (Exim 4.34) id 1CLabz-00039o-Q2; Sat, 23 Oct 2004 22:08:11 -0700 Date: Sun, 24 Oct 2004 01:08:11 -0400 From: Max Baker To: Rod Taylor Cc: Postgresql Performance Subject: Re: Vacuum takes a really long time, vacuum full required Message-ID: <20041024050811.GA12044@warped.org> Mail-Followup-To: Rod Taylor , Postgresql Performance References: <20041019153821.GA21258@warped.org> <1098200416.750.238.camel@home> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1098200416.750.238.camel@home> User-Agent: Mutt/1.4.1i X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - roue.portalpotty.net X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [514 32003] / [47 12] X-AntiAbuse: Sender Address Domain - roue.portalpotty.net X-Source: /usr/bin/mutt X-Source-Args: mutt X-Source-Dir: /home/max 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: 200410/392 X-Sequence-Number: 8868 On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote: > > Whatever the case, the database still slows down to a halt after a month or > > so, and I have to go in and shut everything down and do a VACUUM FULL by > > hand. One index (of many many) takes 2000 seconds to vacuum. The whole > > process takes a few hours. > > Do a REINDEX on that table instead, and regular vacuum more frequently. > > > $ pg_config --version > > PostgreSQL 7.3.2 > > 7.4.x deals with index growth a little better 7.3 and older did. I did a REINDEX of the database. The results are pretty insane, the db went from 16GB to 381MB. Needless to say things are running a lot faster. I will now take Tom's well-given advice and upgrade to 7.4. But at least now I have something to tell my users who are not able to do a DB upgrade for whatever reason. Thanks for all your help folks! -m Before: # du -h pgsql 135K pgsql/global 128M pgsql/pg_xlog 80M pgsql/pg_clog 3.6M pgsql/base/1 3.6M pgsql/base/16975 1.0K pgsql/base/16976/pgsql_tmp 16G pgsql/base/16976 16G pgsql/base 16G pgsql After Reindex: # du /data/pgsql/ 131K /data/pgsql/global 128M /data/pgsql/pg_xlog 81M /data/pgsql/pg_clog 3.6M /data/pgsql/base/1 3.6M /data/pgsql/base/16975 1.0K /data/pgsql/base/16976/pgsql_tmp 268M /data/pgsql/base/16976 275M /data/pgsql/base 484M /data/pgsql/ After Vacuum: # du /data/pgsql/ 131K /data/pgsql/global 144M /data/pgsql/pg_xlog 81M /data/pgsql/pg_clog 3.6M /data/pgsql/base/1 3.6M /data/pgsql/base/16975 1.0K /data/pgsql/base/16976/pgsql_tmp 149M /data/pgsql/base/16976 156M /data/pgsql/base 381M /data/pgsql/ netdisco=> select relname, relpages from pg_class order by relpages desc; Before: relname | relpages ---------------------------------+---------- idx_node_switch_port_active | 590714 idx_node_switch_port | 574344 idx_node_switch | 482202 idx_node_mac | 106059 idx_node_mac_active | 99842 After: relname | relpages ---------------------------------+---------- node_ip | 13829 node | 9560 device_port | 2124 node_ip_pkey | 1354 idx_node_ip_ip | 1017 idx_node_ip_mac_active | 846