Thread: Why is restored database faster?

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

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

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

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

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

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

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