pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why is restored database faster?
7+ messages / 4 participants
[nested] [flat]

* Why is restored database faster?
@ 2003-12-17 04:42 David Shadovitz <david@shadovitz.com>
  2003-12-17 06:00 ` Re: Why is restored database faster? Neil Conway <neilc@samurai.com>
  2003-12-17 06:42 ` Re: Why is restored database faster? Dennis Bjorklund <db@zigo.dhs.org>
  0 siblings, 2 replies; 7+ messages in thread

From: David Shadovitz @ 2003-12-17 04:42 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Why is restored database faster?
  2003-12-17 04:42 Why is restored database faster? David Shadovitz <david@shadovitz.com>
@ 2003-12-17 06:00 ` Neil Conway <neilc@samurai.com>
  2003-12-17 06:31   ` Re: Why is restored database faster? Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  1 sibling, 1 reply; 7+ messages in thread

From: Neil Conway @ 2003-12-17 06:00 UTC (permalink / raw)
  To: david@shadovitz.com <david@shadovitz.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Why is restored database faster?
  2003-12-17 04:42 Why is restored database faster? David Shadovitz <david@shadovitz.com>
  2003-12-17 06:00 ` Re: Why is restored database faster? Neil Conway <neilc@samurai.com>
@ 2003-12-17 06:31   ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 0 replies; 7+ messages in thread

From: Shridhar Daithankar @ 2003-12-17 06:31 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: david@shadovitz.com <david@shadovitz.com>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Why is restored database faster?
  2003-12-17 04:42 Why is restored database faster? David Shadovitz <david@shadovitz.com>
@ 2003-12-17 06:42 ` Dennis Bjorklund <db@zigo.dhs.org>
  1 sibling, 0 replies; 7+ messages in thread

From: Dennis Bjorklund @ 2003-12-17 06:42 UTC (permalink / raw)
  To: David Shadovitz <david@shadovitz.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Why is restored database faster?
@ 2003-12-18 03:54 David Shadovitz <david@shadovitz.com>
  2003-12-18 06:47 ` Re: Why is restored database faster? Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 1 reply; 7+ messages in thread

From: David Shadovitz @ 2003-12-18 03:54 UTC (permalink / raw)
  To: Dennis Bjorklund <db@zigo.dhs.org>; 'shridhar_daithankar@persistent.co.in' <shridhar_daithankar@persistent.co.in>; 'neilc@samurai.com' <neilc@samurai.com>; +Cc: pgsql-performance

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 



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Why is restored database faster?
  2003-12-18 03:54 Re: Why is restored database faster? David Shadovitz <david@shadovitz.com>
@ 2003-12-18 06:47 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-12-18 15:12   ` Re: Why is restored database faster? Dennis Bjorklund <db@zigo.dhs.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Shridhar Daithankar @ 2003-12-18 06:47 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Why is restored database faster?
  2003-12-18 03:54 Re: Why is restored database faster? David Shadovitz <david@shadovitz.com>
  2003-12-18 06:47 ` Re: Why is restored database faster? Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-12-18 15:12   ` Dennis Bjorklund <db@zigo.dhs.org>
  0 siblings, 0 replies; 7+ messages in thread

From: Dennis Bjorklund @ 2003-12-18 15:12 UTC (permalink / raw)
  To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2003-12-18 15:12 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-17 04:42 Why is restored database faster? David Shadovitz <david@shadovitz.com>
2003-12-17 06:00 ` Neil Conway <neilc@samurai.com>
2003-12-17 06:31   ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-12-17 06:42 ` Dennis Bjorklund <db@zigo.dhs.org>
2003-12-18 03:54 Re: Why is restored database faster? David Shadovitz <david@shadovitz.com>
2003-12-18 06:47 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-12-18 15:12   ` Dennis Bjorklund <db@zigo.dhs.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox