X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 74623D1B46D for ; Thu, 18 Dec 2003 06:48:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 69309-03 for ; Thu, 18 Dec 2003 02:47:50 -0400 (AST) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 556EED1B46E for ; Thu, 18 Dec 2003 02:47:47 -0400 (AST) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id hBI6lqG1018277 for ; Thu, 18 Dec 2003 12:17:52 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id hBI6lpQK018257 for ; Thu, 18 Dec 2003 12:17:51 +0530 From: Shridhar Daithankar To: "pgsql-performance@postgresql.org" Subject: Re: Why is restored database faster? Date: Thu, 18 Dec 2003 12:17:12 +0530 User-Agent: KMail/1.5.4 References: <01C3C4D7.A7F88280.david@shadovitz.com> In-Reply-To: <01C3C4D7.A7F88280.david@shadovitz.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312181217.12520.shridhar_daithankar@persistent.co.in> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/262 X-Sequence-Number: 5122 On Thursday 18 December 2003 09:24, David Shadovitz wrote: > Old server: > # VACUUM FULL abc; > VACUUM > # VACUUM VERBOSE abc; > NOTICE: --Relation abc-- > NOTICE: Pages 1526: Changed 0, Empty 0; Tup 91528; Vac 0, Keep 0, UnUsed > 32. Total CPU 0.07s/0.52u sec elapsed 0.60 sec. > VACUUM > > New server: > # VACUUM VERBOSE abc; > NOTICE: --Relation abc-- > NOTICE: Pages 1526: Changed 0, Empty 0; Tup 91528; Vac 0, Keep 0, UnUsed > 0. Total CPU 0.02s/0.00u sec elapsed 0.02 sec. > VACUUM > > max_fsm_pages is at its default value, 10000. Well, then the only issue left is file sytem defragmentation. Which file system is this anyway > People don't have the practice of dumping and restoring just for the > purpose of improving performance, do they? Well, at times it is required. Especially if it is update intensive environment. An no database is immune to that > Neil asked how much disk space the database directory takes on each > machine. What directory is of interest? The whole thing takes up about 875 > MB on each machine. That is fairly small.. Should not take much time..in my guess, the time it takes to vacuum is more than time to dump and reload. Another quick way to defragment a file system is to copy entire data directory to another partition(Shutdown postmaster first), delete it from original partition and move back. Contegous wriing to a partition results in defragmentation effectively. Try it and see if it helps. It could be much less trouble than dump/restore.. HTH Shridhar