Обсуждение: pg_dump doesn¹t dump everything?
I've been trying to replicate a database but each time I replication it the
performance of the copy is about 100 times slower (~100ms to ~8 seconds for
the same query). The only way I have found to replicate it and keep the
same performance is doing a hotcopy of the database.
Please note I didn't design this database, I just have to support it.
$ uname -a
Linux hostname 2.6.20-gentoo-r8-5 #2 SMP Wed Aug 1 19:43:33 CDT 2007 x86_64
Intel(R) Xeon(R) CPU 5130 @ 2.00GHz GenuineIntel GNU/Linux
$ psql --version
psql (PostgreSQL) 8.1.5
So the original database, cmpub, works great. But when I do a pg_dump and
import it to a test database on the same server, or another server for that
matter, the performance is awful.
Here is how I did the test....
Create test database:
$ ./createdb --template template1 --encoding UNICODE liam
$ ./pg_dump cmpub | ./psql liam
Run a vacuum full:
liam=# vacuum full verbose;
Then run the SAME query on the "liam" database which returns the SAME data
but takes 100 times longer - the same 74 rows are returned in each database.
I can see from the explain analyze that the sort in the liam database takes
much longer. But I don't understand why?
What am I missing? Any help would be much appreciated.
The explain analyze of both databases is below.
Thanks,
liam
cmpub=# explain analyze select distinct StdDisplayView.objectid,
StdDisplayView.stdLevel, StdDisplayView.stdLabel, StdDisplayView.stdText,
StdDisplayView.stdOrder, OrgStdLevel.levelName, OrgStdLevel.orgObjectID from
standardlist, orgstdlevel, stddisplayview WHERE StdDisplayView.objectid =
StandardList.objectid AND OrgStdLevel.orgObjectID =
StdDisplayView.stdOrgObjectID AND OrgStdLevel.level =
StdDisplayView.stdLevel AND StdDisplayView.stdOrgObjectID IN ( 100312910,
507594, 154828031) AND StdDisplayView.gradeObjectID = '41' AND
StdDisplayView.subject = 'MATHEMATICS' ORDER BY StdDisplayView.stdOrder ASC;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------------
Unique (cost=106122.76..106122.96 rows=10 width=706) (actual
time=101.866..102.429 rows=74 loops=1)
-> Sort (cost=106122.76..106122.79 rows=10 width=706) (actual
time=101.865..101.973 rows=355 loops=1)
Sort Key: stddisplayview.stdorder, stddisplayview.objectid,
stddisplayview.stdlevel, stddisplayview.stdlabel, stddisplayview.stdtext,
orgstdlevel.levelname, orgstdlevel.orgobjectid
-> Nested Loop (cost=106004.33..106122.60 rows=10 width=706)
(actual time=96.616..100.177 rows=355 loops=1)
-> Hash Join (cost=106004.33..106067.78 rows=10 width=706)
(actual time=96.587..97.892 rows=355 loops=1)
Hash Cond: (("outer".orgobjectid = "inner".orgobjectid)
AND ("outer".stdlevel = "inner"."level"))
-> Unique (cost=105959.96..105994.35 rows=724
width=301) (actual time=95.135..95.815 rows=355 loops=1)
-> Sort (cost=105959.96..105961.77 rows=724
width=301) (actual time=95.134..95.258 rows=355 loops=1)
Sort Key: keyl.objectid,
keyl.upperkeylevel1, keyl.upperkeylevel2, keyl.upperkeylevel3,
keyl.upperkeylevel4, keyl.upperkeylevel5, std.objectid, std.objectversion,
std.leveloneobjectid, std.stdlevel, stdpref.stdlabel, std.stdtext,
std.orgobjectid, std.parentstandardid, std.stdstatus, stdpref.orgobjectid,
stdpref.stdorder, stdgrd.gradeid
-> Nested Loop (cost=8999.00..105925.57
rows=724 width=301) (actual time=74.882..93.699 rows=355 loops=1)
-> Hash Join
(cost=8999.00..102023.24 rows=724 width=267) (actual time=74.856..91.345
rows=355 loops=1)
Hash Cond:
("outer".keywordobjectid = "inner".objectid)
-> Nested Loop
(cost=2178.68..95166.58 rows=3880 width=190) (actual time=20.400..65.747
rows=5116 loops=1)
-> Hash Join
(cost=2178.68..33391.73 rows=960 width=174) (actual time=20.369..50.522
rows=1245 loops=1)
Hash Cond:
("outer".leveloneobjectid = "inner".standardobjectid)
-> Bitmap Heap
Scan on standardlist std (cost=118.23..30960.68 rows=16044 width=166)
(actual time=2.904..18.678 rows=16956 loops=1)
Recheck Cond:
((orgobjectid = 100312910) OR (orgobjectid = 507594) OR (orgobjectid =
154828031))
Filter:
((stdstatus)::text = 'A'::text)
-> BitmapOr
(cost=118.23..118.23 rows=16065 width=0) (actual time=2.652..2.652 rows=0
loops=1)
->
Bitmap Index Scan on std_org_i (cost=0.00..6.34 rows=669 width=0) (actual
time=0.038..0.038 rows=0 loops=1)
Index Cond: (orgobjectid = 100312910)
->
Bitmap Index Scan on std_org_i (cost=0.00..105.54 rows=14726 width=0)
(actual time=2.600..2.600 rows=16956 loops=1)
Index Cond: (orgobjectid = 507594)
->
Bitmap Index Scan on std_org_i (cost=0.00..6.34 rows=669 width=0) (actual
time=0.011..0.011 rows=0 loops=1)
Index Cond: (orgobjectid = 154828031)
-> Hash
(cost=2039.72..2039.72 rows=8295 width=16) (actual time=16.321..16.321
rows=8098 loops=1)
-> Bitmap
Heap Scan on stdgrade stdgrd (cost=66.03..2039.72 rows=8295 width=16)
(actual time=1.497..10.477 rows=8098 loops=1)
Recheck
Cond: (gradeid = 41::bigint)
->
Bitmap Index Scan on stdgrd_grd_i (cost=0.00..66.03 rows=8295 width=0)
(actual time=1.313..1.313 rows=8098 loops=1)
Index Cond: (gradeid = 41::bigint)
-> Index Scan using
bstdkey_std_i on standardkeyword stdkey (cost=0.00..64.11 rows=19 width=16)
(actual time=0.004..0.007 rows=4 loops=1245)
Index Cond:
("outer".objectid = stdkey.standardobjectid)
-> Hash
(cost=6814.32..6814.32 rows=2400 width=85) (actual time=22.633..22.633
rows=2400 loops=1)
-> Index Scan using
keywordlist_pkey on keywordlist keyl (cost=0.00..6814.32 rows=2400
width=85) (actual time=1.792..20.125 rows=2400 loops=1)
Filter:
((upperkeylevel1)::text = 'MATHEMATICS'::text)
-> Index Scan using stdpref_std_i on
stdpreference stdpref (cost=0.00..5.38 rows=1 width=50) (actual
time=0.004..0.005 rows=1 loops=355)
Index Cond: ("outer".objectid =
stdpref.standardobjectid)
-> Hash (cost=40.60..40.60 rows=753 width=26) (actual
time=1.393..1.393 rows=753 loops=1)
-> Index Scan using orgstdlevel_level on
orgstdlevel (cost=0.00..40.60 rows=753 width=26) (actual time=0.022..0.799
rows=753 loops=1)
-> Index Scan using standardlist_pkey on standardlist
(cost=0.00..5.47 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=355)
Index Cond: ("outer".objectid = standardlist.objectid)
Total runtime: 102.932 ms
(42 rows)
cmpub=#
liam=# explain analyze select distinct StdDisplayView.objectid,
StdDisplayView.stdLevel, StdDisplayView.stdLabel, StdDisplayView.stdText,
StdDisplayView.stdOrder, OrgStdLevel.levelName, OrgStdLevel.orgObjectID from
standardlist, orgstdlevel, stddisplayview WHERE StdDisplayView.objectid =
StandardList.objectid AND OrgStdLevel.orgObjectID =
StdDisplayView.stdOrgObjectID AND OrgStdLevel.level =
StdDisplayView.stdLevel AND StdDisplayView.stdOrgObjectID IN ( 100312910,
507594, 154828031) AND StdDisplayView.gradeObjectID = '41' AND
StdDisplayView.subject = 'MATHEMATICS' ORDER BY StdDisplayView.stdOrder ASC;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------------------
Unique (cost=130500501687.30..130508848338.20 rows=92732061 width=810)
(actual time=7923.266..7923.824 rows=74 loops=1)
-> Sort (cost=130500501687.30..130501545018.67 rows=417332545
width=810) (actual time=7923.264..7923.401 rows=355 loops=1)
Sort Key: stddisplayview.stdorder, stddisplayview.objectid,
stddisplayview.stdlevel, stddisplayview.stdlabel, stddisplayview.stdtext,
orgstdlevel.levelname, orgstdlevel.orgobjectid
-> Merge Join (cost=129147443409.40..129157005075.21
rows=417332545 width=810) (actual time=7921.021..7921.803 rows=355 loops=1)
Merge Cond: ("outer".objectid = "inner".objectid)
-> Index Scan using standardlist_pkey on standardlist
(cost=0.00..3299290.65 rows=956263 width=8) (actual time=0.044..751.723
rows=792012 loops=1)
-> Sort (cost=129147443409.40..129147443627.61 rows=87284
width=810) (actual time=6503.863..6503.983 rows=355 loops=1)
Sort Key: stddisplayview.objectid
-> Hash Join (cost=128777244567.93..129147398991.25
rows=87284 width=810) (actual time=6501.798..6503.093 rows=355 loops=1)
Hash Cond: (("outer".orgobjectid =
"inner".orgobjectid) AND ("outer".stdlevel = "inner"."level"))
-> Unique
(cost=128777244519.88..129145589795.20 rows=4636603 width=3336) (actual
time=6500.380..6501.067 rows=355 loops=1)
-> Sort
(cost=128777244519.88..128796631113.32 rows=7754637375 width=3336) (actual
time=6500.378..6500.514 rows=355 loops=1)
Sort Key: keyl.objectid,
keyl.upperkeylevel1, keyl.upperkeylevel2, keyl.upperkeylevel3,
keyl.upperkeylevel4, keyl.upperkeylevel5, std.objectid, std.objectversion,
std.leveloneobjectid, std.stdlevel, stdpref.stdlabel, std.stdtext,
std.orgobjectid, std.parentstandardid, std.stdstatus, stdpref.orgobjectid,
stdpref.stdorder, stdgrd.gradeid
-> Merge Join
(cost=8361391.76..127534603.40 rows=7754637375 width=3336) (actual
time=6486.234..6498.079 rows=355 loops=1)
Merge Cond: ("outer".objectid =
"inner".standardobjectid)
-> Merge Join
(cost=28629.32..2886338.57 rows=1254713 width=756) (actual
time=1099.616..1122.335 rows=1245 loops=1)
Merge Cond:
("outer".standardobjectid = "inner".objectid)
-> Index Scan using
stdpref_std_i on stdpreference stdpref (cost=0.00..2836338.32 rows=1020092
width=172) (actual time=0.048..678.210 rows=860924 loops=1)
-> Sort
(cost=28629.32..28629.94 rows=246 width=584) (actual time=56.218..56.690
rows=1245 loops=1)
Sort Key:
std.objectid
-> Hash Join
(cost=1382.23..28619.56 rows=246 width=584) (actual time=20.280..54.163
rows=1245 loops=1)
Hash Cond:
("outer".leveloneobjectid = "inner".standardobjectid)
-> Bitmap
Heap Scan on standardlist std (cost=95.20..27317.64 rows=71 width=576)
(actual time=2.786..22.505 rows=16956 loops=1)
Recheck
Cond: ((orgobjectid = 100312910) OR (orgobjectid = 507594) OR (orgobjectid =
154828031))
Filter:
((stdstatus)::text = 'A'::text)
->
BitmapOr (cost=95.20..95.20 rows=14344 width=0) (actual time=2.536..2.536
rows=0 loops=1)
-> Bitmap Index Scan on std_org_i (cost=0.00..31.73 rows=4781 width=0)
(actual time=0.040..0.040 rows=0 loops=1)
Index Cond: (orgobjectid = 100312910)
-> Bitmap Index Scan on std_org_i (cost=0.00..31.73 rows=4781 width=0)
(actual time=2.481..2.481 rows=16956 loops=1)
Index Cond: (orgobjectid = 507594)
-> Bitmap Index Scan on std_org_i (cost=0.00..31.73 rows=4781 width=0)
(actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (orgobjectid = 154828031)
-> Hash
(cost=1285.29..1285.29 rows=693 width=16) (actual time=16.177..16.177
rows=8098 loops=1)
->
Bitmap Heap Scan on stdgrade stdgrd (cost=5.42..1285.29 rows=693 width=16)
(actual time=1.576..10.230 rows=8098 loops=1)
Recheck Cond: (gradeid = 41::bigint)
-> Bitmap Index Scan on stdgrd_grd_i (cost=0.00..5.42 rows=693 width=0)
(actual time=1.388..1.388 rows=8098 loops=1)
Index Cond: (gradeid = 41::bigint)
-> Sort
(cost=8332762.43..8335852.45 rows=1236005 width=2596) (actual
time=4817.153..5146.900 rows=527137 loops=1)
Sort Key:
stdkey.standardobjectid
-> Nested Loop
(cost=121.59..2291575.70 rows=1236005 width=2596) (actual
time=1.576..1650.886 rows=596915 loops=1)
-> Index Scan
using keywordlist_pkey on keywordlist keyl (cost=0.00..2215.60 rows=64
width=2588) (actual time=1.417..23.050 rows=2400 loops=1)
Filter:
((upperkeylevel1)::text = 'MATHEMATICS'::text)
-> Bitmap Heap
Scan on standardkeyword stdkey (cost=121.59..35529.84 rows=19313 width=16)
(actual time=0.095..0.444 rows=249 loops=2400)
Recheck Cond:
(stdkey.keywordobjectid = "outer".objectid)
-> Bitmap
Index Scan on bstdkey_key_i (cost=0.00..121.59 rows=19313 width=0) (actual
time=0.059..0.059 rows=249 loops=2400)
Index
Cond: (stdkey.keywordobjectid = "outer".objectid)
-> Hash (cost=44.28..44.28 rows=753 width=130)
(actual time=1.358..1.358 rows=753 loops=1)
-> Index Scan using orgstdlevel_level on
orgstdlevel (cost=0.00..44.28 rows=753 width=130) (actual time=0.021..0.754
rows=753 loops=1)
Total runtime: 7961.969 ms
(49 rows)
liam=#
On Sep 5, 2007, at 18:57 , Liam Slusser wrote: > > I've been trying to replicate a database but each time I > replication it the > performance of the copy is about 100 times slower (~100ms to ~8 > seconds for > the same query). The only way I have found to replicate it and > keep the > same performance is doing a hotcopy of the database. <snip> Is the hardware identical? Is your postgres.conf tuned identically? Cheers, M
Liam Slusser wrote: > I've been trying to replicate a database but each time I replication it the > performance of the copy is about 100 times slower (~100ms to ~8 seconds for > the same query). The only way I have found to replicate it and keep the > same performance is doing a hotcopy of the database. > > Please note I didn't design this database, I just have to support it. > > $ uname -a > Linux hostname 2.6.20-gentoo-r8-5 #2 SMP Wed Aug 1 19:43:33 CDT 2007 x86_64 > Intel(R) Xeon(R) CPU 5130 @ 2.00GHz GenuineIntel GNU/Linux > > $ psql --version > psql (PostgreSQL) 8.1.5 > > So the original database, cmpub, works great. But when I do a pg_dump and > import it to a test database on the same server, or another server for that > matter, the performance is awful. > > Here is how I did the test.... > > Create test database: > > $ ./createdb --template template1 --encoding UNICODE liam > $ ./pg_dump cmpub | ./psql liam > > Run a vacuum full: > liam=# vacuum full verbose; You need to analyze, not vacuum full. pg_dump doesn't include any analyze statements, you need to do that manually. -- Postgresql & php tutorials http://www.designmagick.com/
I don’t know how I could have missed that – that sure enough that did the trick! Thanks to all that answered! :)
liam
On 9/5/07 4:34 PM, "Chris" <dmagick@gmail.com> wrote:
You need to analyze, not vacuum full. pg_dump doesn't include any
analyze statements, you need to do that manually.
--
Postgresql & php tutorials
http://www.designmagick.com/