Thread

1. Why is restored database faster?

   David Shadovitz <david@shadovitz.com> — 2003-12-17T04:42:58Z

   I backed up my database using pg_dump, and then restored it onto a different 
   server using psql.  I see that the query "SELECT COUNT(*) FROM myTable" 
   executes immediately on the new server but takes several seconds on the old 
   one.  (The servers are identical.)
   
   What could account for this difference?  Clustering?  How can I get the 
   original server to perform as well as the new one?
   
   Thanks.
   -David
   
   

2. Re: Why is restored database faster?

   Neil Conway <neilc@samurai.com> — 2003-12-17T06:00:13Z

   David Shadovitz <david@shadovitz.com> writes:
   > What could account for this difference?
   
   Lots of things -- disk fragmentation, expired tuples that aren't being
   cleaned up by VACUUM due to a long-lived transaction, the state of the
   kernel buffer cache, the configuration of the kernel, etc.
   
   > How can I get the original server to perform as well as the new one?
   
   Well, you can start by giving us some more information. For example,
   what is the output of VACUUM VERBOSE on the slow server? How much disk
   space does the database directory take up on both machines?
   
   (BTW, "SELECT count(*) FROM table" isn't a particularly good DBMS
   performance indication...)
   
   -Neil
   
   
   

3. Re: Why is restored database faster?

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-12-17T06:31:06Z

   Neil Conway wrote:
   
   >>How can I get the original server to perform as well as the new one?
   
   Well, you have the answer. Dump the database, stop postmaster and restore it. 
   That should be faster than original one.
   
   > 
   > (BTW, "SELECT count(*) FROM table" isn't a particularly good DBMS
   > performance indication...)
   
   Particularly in case of postgresql..:-)
   
     Shridhar
   
   

4. Re: Why is restored database faster?

   Dennis Bjorklund <db@zigo.dhs.org> — 2003-12-17T06:42:46Z

   On Tue, 16 Dec 2003, David Shadovitz wrote:
   
   > I backed up my database using pg_dump, and then restored it onto a different 
   > server using psql.  I see that the query "SELECT COUNT(*) FROM myTable" 
   > executes immediately on the new server but takes several seconds on the old 
   > one.  (The servers are identical.)
   > 
   > What could account for this difference?  Clustering?  How can I get the 
   > original server to perform as well as the new one?
   
   You probably need to run VACUUM FULL. It locks the tables during its 
   execution so only do it when the database is not in full use.
   
   If this helps you probably need to do normal vacuums more often and maybe
   tune the max_fsm_pages to be bigger. 
   
   -- 
   /Dennis