Skip site navigation (1) Skip section navigation (2)

Re: Why is restored database faster?

From: Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Why is restored database faster?
Date: 2003-12-18 06:47:12
Message-ID: 200312181217.12520.shridhar_daithankar@persistent.co.in (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Dennis BjorklundDate: 2003-12-18 15:12:16
Subject: Re: Why is restored database faster?
Previous:From: David ShadovitzDate: 2003-12-18 03:54:45
Subject: Re: Why is restored database faster?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group