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 25B5C32A0A2 for ; Tue, 19 Oct 2004 16:38:26 +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 34760-08 for ; Tue, 19 Oct 2004 15:38:24 +0000 (GMT) Received: from roue.portalpotty.net (roue.portalpotty.net [69.44.62.206]) by svr1.postgresql.org (Postfix) with ESMTP id 30A3332A0A1 for ; Tue, 19 Oct 2004 16:38:23 +0100 (BST) Received: from max by roue.portalpotty.net with local (Exim 4.34) id 1CJw45-0005YA-F7 for pgsql-performance@postgresql.org; Tue, 19 Oct 2004 08:38:21 -0700 Date: Tue, 19 Oct 2004 11:38:21 -0400 From: Max Baker To: pgsql-performance@postgresql.org Subject: Vacuum takes a really long time, vacuum full required Message-ID: <20041019153821.GA21258@warped.org> Mail-Followup-To: pgsql-performance@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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/275 X-Sequence-Number: 8751 Hi Folks, This is my _4th_ time trying to post this, me and the mailing list software are fighting. I think it's because of the attachments so I'll just put links to them instead. All apologies if this gets duplicated. I've been having problems maintaining the speed of the database in the long run. VACUUMs of the main tables happen a few times a day after maybe 50,000 or less rows are added and deleted (say 6 times a day). I have a whole lot (probably too much) indexing going on to try to speed things up. 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. I would love suggestions on what I can do either inside my application, or from a dba point of view to keep the database maintained without having to inflict downtime. This is for 'Netdisco' -- an open source network management software by the way. I'ld like to fix this for everyone who uses it. Sys Info : $ uname -a FreeBSD xxxx.ucsc.edu 4.10-STABLE FreeBSD 4.10-STABLE #0: Mon Aug 16 14:56:19 PDT 2004 root@xxxx.ucsc.edu:/usr/src/sys/compile/xxxx i386 $ pg_config --version PostgreSQL 7.3.2 $ cat postgresql.conf max_connections = 32 shared_buffers = 3900 # 30Mb - Bsd current kernel limit max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes max_locks_per_transaction = 64 # min 10 wal_buffers = 8 # min 4, typically 8KB each The log of the vacuum and the db schema could not be attached, so they are at : http://netdisco.net/db_vacuum.txt http://netdisco.net/pg_all.input Thanks for any help! -m