Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6E6259FB4F1 for ; Wed, 24 Jan 2007 03:45:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 46616-03 for ; Wed, 24 Jan 2007 03:44:59 -0400 (AST) X-Greylist: delayed 02:07:08.904932 by SQLgrey-1.7.4 Received: from serv1.oss.ntt.co.jp (serv1.oss.ntt.co.jp [222.151.198.98]) by postgresql.org (Postfix) with ESMTP id CA06A9F9D4D for ; Wed, 24 Jan 2007 03:45:00 -0400 (AST) Received: from [127.0.0.1] (unknown [172.17.1.148]) by serv1.oss.ntt.co.jp (Postfix) with ESMTP id 3B6152600C6 for ; Wed, 24 Jan 2007 16:44:59 +0900 (JST) Message-ID: <45B70E77.9050102@oss.ntt.co.jp> Date: Wed, 24 Jan 2007 16:44:55 +0900 From: Galy Lee User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: how to plan for vacuum? References: <45B6F0A8.1050705@oss.ntt.co.jp> In-Reply-To: <45B6F0A8.1050705@oss.ntt.co.jp> Content-Type: text/plain; charset=ISO-2022-JP Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200701/359 X-Sequence-Number: 22960 Just have one example here: workload: run pgbench in 365x24x7 database size: 100GB the workload distribution: 06:00-24:00 100tps 00:00-06:00 20tps how should we plan vacuum for this situation to get the highest performance? Best regards Galy Galy Lee wrote: > Hi, > > For I can not find too much information about how to use vacuum, I want > to ask some general information about the guideline of vacuum planning. > > 1. How do we know if autovacuum is enough for my application, or should > I setup a vacuum manually from cron for my application? > > 2. How to set the GUC parameters for autovacuum? > There are two sets of parameters for autovacuum: > - vacuum threshold and scale factor (500/0.2) > 　 - analyze threshold and scale factor(250/0.1) > Is there any guideline to set these parameters? When does it need to > change the default values? > 　 > 3. How to tune cost-based delay vacuum? > I had searched in performance list; it seems that most of the practices > are based on experience / trial-and-error approach to meet the > requirement of disk utilization or CPU utilization. Is there any other > guild line to set them? > > For when autovacuum is turned on by default, if the parameters for > vacuum have not been set well, it will make the system rather unstable. > So I just wonder if we should setup a section in the manual about the > tips of vacuum, then many users can easily set the vacuum parameters for > their system.