Thread

1. Re: Why is restored database faster?

   David Shadovitz <david@shadovitz.com> — 2003-12-18T03:54:45Z

   Dennis, Shridhar, and Neil,
   
   Thanks for your input.  Here are my responses:
   
   I ran VACUUM FULL on the table in question.  Although that did reduce "Pages" 
   and "UnUsed", the "SELECT *" query is still much slower on this installation 
   than in the new, restored one.
   
     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.
   
   People don't have the practice of dumping and restoring just for the purpose of 
   improving performance, do they?
   
   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.
   
   -David 
   
   

2. Re: Why is restored database faster?

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-12-18T06:47:12Z

   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
   
   
   

3. Re: Why is restored database faster?

   Dennis Bjorklund <db@zigo.dhs.org> — 2003-12-18T15:12:16Z

   On Thu, 18 Dec 2003, Shridhar Daithankar wrote:
   
   > Well, then the only issue left is file sytem defragmentation.
   
   And the internal fragmentation that can be "fixed" with the CLUSTER 
   command.
   
   -- 
   /Dennis