Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9D78A2E2F47 for ; Sun, 27 Jan 2008 19:54:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 09438-07 for ; Sun, 27 Jan 2008 19:54:08 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by postgresql.org (Postfix) with ESMTP id DAA852E2F46 for ; Sun, 27 Jan 2008 19:54:13 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so2835808waf.5 for ; Sun, 27 Jan 2008 15:54:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=qqx2hyL7hecnUh5pHK27J2Tk4nGNpfY5Q8ibJcGjSpU=; b=Px25EggARdEV0OvMPJsOqXwKQFiwq5Zq+2DYuNvv9fhrTEHQE4CRKkn3/ZOVYIpioxYVFkMKvSjrC7MwG6QB1UpaQ0gjeR8ykhEsqTZql2/aTxoG3SRhLx9xJPqGCxra3VQXe0bJwEeCzRr28s9TCOE+fO67K7hdENHtKJxD0Gk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=mwWO8XKwJ7xgLBNpBskn306ZUo0+QM7HT2HRK9yVaNRIpdiglYmKPpMjxv4Ra1OoQEYHODFUyGUFYC9jWkUUx2LEdyKMPxrqayKLguZ252PBKRsa352fggt6AxPeJj1LQ+mCkX+ZKXe04IJacFraszMoxIQvhAE8s4q7xNcEw+8= Received: by 10.114.103.1 with SMTP id a1mr2946993wac.59.1201478052654; Sun, 27 Jan 2008 15:54:12 -0800 (PST) Received: by 10.114.133.5 with HTTP; Sun, 27 Jan 2008 15:54:12 -0800 (PST) Message-ID: Date: Sun, 27 Jan 2008 17:54:12 -0600 From: "Scott Marlowe" To: "Thomas Lozza" Subject: Re: Vacuum and FSM page size Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/251 X-Sequence-Number: 28490 On Jan 27, 2008 5:01 PM, Thomas Lozza wrote: > Thanks for the advice. > I used the default settings before, thought though that vacuum was a bit > aggressive, ie, using too many resources. Now its taking very long. So > will have to find reasonable settings in between I guess. > > On the other hand, if I keep the fsm_page number high enough, the system > should be fine with a low number of vacuum cycles, right. As memory is > not really scarce (16G, 32 bit PG though) an x million fsm_page entry > should be ok. Any thoughts on that? The issue you then run into is bloat, where you have a table or index that is 90% or so empty space, and performance on selects might suffer, especially on larger tables or indexes. What often works best is to let autovacuum handle most of your tables, then schedule individual tables to be vacuumed by cron, setting the nap time for vacuum at 20 or 30 milliseconds so they don't chew up all of your I/O