pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy 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