Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EC0D59FB47B for ; Thu, 25 Jan 2007 06:52:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 81648-08 for ; Thu, 25 Jan 2007 06:52:57 -0400 (AST) X-Greylist: from auto-whitelisted 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 308F39FB5D8 for ; Thu, 25 Jan 2007 06:52:57 -0400 (AST) Received: from [127.0.0.1] (unknown [172.17.1.148]) by serv1.oss.ntt.co.jp (Postfix) with ESMTP id 0835E260046; Thu, 25 Jan 2007 19:52:55 +0900 (JST) Message-ID: <45B88C02.3010802@oss.ntt.co.jp> Date: Thu, 25 Jan 2007 19:52:50 +0900 From: Galy Lee User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: pgsql-hackers@postgresql.org CC: jim@nasby.net Subject: Re: [PERFORM] how to plan for vacuum? References: <45B6F0A8.1050705@oss.ntt.co.jp> <20070125033917.GD64372@nasby.net> <20070125035202.GI26006@alvh.no-ip.org> <20070125035741.GE64372@nasby.net> In-Reply-To: <20070125035741.GE64372@nasby.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/1215 X-Sequence-Number: 97116 Jim C. Nasby wrote: > On Thu, Jan 25, 2007 at 12:52:02AM -0300, Alvaro Herrera wrote: >> Jim C. Nasby wrote: >> >>> I'll generally start with a cost delay of 20ms and adjust based on IO >>> utilization. >> I've been considering set a default autovacuum cost delay to 10ms; does >> this sound reasonable? The problem in here is that we can not easily find a direct relation between Cost delay <-> CPU/IO utilization <--> real performance (response time in peak hour) It is very hard for any normal user to set this correctly. I think the experience / trial-and-error approach is awful for the user, every DBA need to be an expert of vacuum to keep the system stable. For vacuum is still a big threat to the performance, a more intelligent way is needed. A lot of efforts have contributed to make vacuum to be a more lightweight operation, but I think we should still need more efforts on how to make it can be used easily and safely. So I have proposed the "vacuum in time" feature in previous; just let vacuum know how long can it runs, and then it will minimize the impact in the time span for you. Some argue that it should not have the maintenance window assumption, but the most safely way is to run in the maintenance window.