Re: pg_upgrade

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Rainer Leo
Sent: Tuesday, June 23, 2015 3:51 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] pg_upgrade

>>>>> we are still using PostgreSQL 9.0.2 on Windows Server.

>>>>> Now we are migrating to Windows Server 2012 R2 and we
>>>>> would like to migrate PostgreSQL at the same time to
>>>>> the current version 9.4.4-1

>>>>> Which is the best way to migrate the data?

>>>>> 1. pg_dump on the old server
>>>>> 2. pg_retore on the new server
>>>>> 3. pg_upgrade on the new server

>>>>> Is this correct or is there a "best procedure" to do this?

>>>> You do either 1 + 2 OR 3. pg_upgrade is binary upgrade, where as
>>>> pg_dump + pg_restore is "logical" (dump data and schemal to SQL
>>>> instructions). If you go that way also check pg_dumpall for dumping
>>>> the globals.

>>>> Regards

>>>> Jan

>>> Also, for 1+2 you would be advised to do the pg_dump/restore using
>>> the
>>> *new* binaries (9.4), things could get tricky otherwise...

>>> Ziggy

>> Thanks for your help.

>> Using the 9.4 pg_dump on the old server did not work (missing
>> libintl-8.dll), so I used the 9.0 pg_dump.

>> pg_restore on the new server worked fine, BUT the perfomance is
>> lousy, for example a query that took 1732ms on the old server now
>> takes longer than 32000ms every time on 9.4

>> I tuned the postgres.conf exactly like the old one, except for more
>> RAM in some parameters.

>> Does this mean I have to install 9.4 on the old server so I can use
>> pg_upgrade?

> That won't make a difference regarding resulting performance.
> Did you run ANALYZE after pg_restore?
Yes

> Also, did you run the query more
> than once? The new system is "cold" (caches are empty). It will take
> some time (depends on your amount of data and RAM, etc) until everything
> is properly loaded.
I ran the query more than 10 times. All queries cost more then 32sec.

> Did you compare EXPLAIN outputs on both systems (only makes sense after
> running ANALYZE)?
EXPLAIN shows that the 9.4 queryplaner has a different plan:

OLD (9.0):
==================================================================
QUERY PLAN
Hash Left Join (cost=5278.77..14784.76 rows=39672 width=1305)
Hash Cond: (k.lieferbedingungid = lb.lieferbedingungid)
-> Hash Left Join (cost=5277.41..14127.16 rows=39672 width=1286)
Hash Cond: (k.zahlungsbedingungid = zb.zahlungsbedingungid)
-> Hash Left Join (cost=5276.14..13668.47 rows=39672 width=1262)
Hash Cond: (k.zahlungsartid = za.zahlungsartid)
-> Hash Left Join (cost=5275.01..13518.55 rows=39672 width=1252)
Hash Cond: (k.bankverbindungid = b.bankverbindungid)
-> Hash Left Join (cost=4384.74..12159.15 rows=39672 width=1119)
Hash Cond: (a.nation_iso = n.iso_3166alpha2)
-> Hash Left Join (cost=4376.21..11605.12 rows=39672 width=1105)
Hash Cond: ((k.kundengruppeid)::text = (kg.kundengruppeid)::text)
-> Hash Left Join (cost=4374.94..11058.37 rows=39672 width=1103)
Hash Cond: (k.adresseid = a.adresseid)
-> Hash Left Join (cost=2819.09..8758.67 rows=39672 width=1014)
Hash Cond: (a.nation_iso = n.iso_3166alpha2)
-> Hash Left Join (cost=2810.55..8204.64 rows=39672 width=1003)
Hash Cond: (k.kontaktid = ko.kontaktid)
-> Hash Left Join (cost=1555.85..5970.69 rows=39672 width=609)
Hash Cond: (k.lieferadresseid = a.adresseid)
-> Seq Scan on kunde k (cost=0.00..4051.72 rows=39672 width=520)
-> Hash (cost=1021.49..1021.49 rows=42749 width=93)
-> Seq Scan on adresse a (cost=0.00..1021.49 rows=42749 width=93)
-> Hash (cost=763.20..763.20 rows=39320 width=394)
-> Seq Scan on kontakt ko (cost=0.00..763.20 rows=39320 width=394)
-> Hash (cost=5.46..5.46 rows=246 width=14)
-> Seq Scan on nation n (cost=0.00..5.46 rows=246 width=14)
-> Hash (cost=1021.49..1021.49 rows=42749 width=93)
-> Seq Scan on adresse a (cost=0.00..1021.49 rows=42749 width=93)
-> Hash (cost=1.12..1.12 rows=12 width=4)
-> Seq Scan on kundengruppe kg (cost=0.00..1.12 rows=12 width=4)
-> Hash (cost=5.46..5.46 rows=246 width=17)
-> Seq Scan on nation n (cost=0.00..5.46 rows=246 width=17)
-> Hash (cost=612.34..612.34 rows=22234 width=133)
-> Seq Scan on bankverbindung b (cost=0.00..612.34 rows=22234 width=133)
-> Hash (cost=1.06..1.06 rows=6 width=14)
-> Seq Scan on zahlungsart za (cost=0.00..1.06 rows=6 width=14)
-> Hash (cost=1.12..1.12 rows=12 width=28)
-> Seq Scan on zahlungsbedingung zb (cost=0.00..1.12 rows=12 width=28)
-> Hash (cost=1.16..1.16 rows=16 width=23)
-> Seq Scan on lieferbedingung lb (cost=0.00..1.16 rows=16 width=23)

NEW (9.4):
==================================================================
QUERY PLAN
Hash Left Join (cost=5203.29..12392.94 rows=39688 width=1292)
Hash Cond: (k.lieferbedingungid = lb.lieferbedingungid)
-> Hash Left Join (cost=5201.93..11734.23 rows=39688 width=1273)
Hash Cond: (k.zahlungsbedingungid = zb.zahlungsbedingungid)
-> Hash Left Join (cost=5200.66..11274.37 rows=39688 width=1249)
Hash Cond: (k.zahlungsartid = za.zahlungsartid)
-> Hash Left Join (cost=5199.53..11124.40 rows=39688 width=1239)
Hash Cond: (k.bankverbindungid = b.bankverbindungid)
-> Hash Left Join (cost=4326.26..9781.06 rows=39688 width=1106)
Hash Cond: (k.kontaktid = ko.kontaktid)
-> Hash Left Join (cost=3088.45..7563.41 rows=39688 width=712)
Hash Cond: (a_1.nation_iso = n_1.iso_3166alpha2)
-> Hash Left Join (cost=3079.91..7009.16 rows=39688 width=701)
Hash Cond: (k.lieferadresseid = a_1.adresseid)
-> Hash Left Join (cost=1544.86..5111.28 rows=39688 width=612)
Hash Cond: (a.nation_iso = n.iso_3166alpha2)
-> Hash Left Join (cost=1536.32..4557.03 rows=39688 width=598)
Hash Cond: (k.adresseid = a.adresseid)
-> Hash Left Join (cost=1.27..2277.83 rows=39688 width=509)
Hash Cond: ((k.kundengruppeid)::text = (kg.kundengruppeid)::text)
-> Seq Scan on kunde k (cost=0.00..1730.88 rows=39688 width=507)
-> Hash (cost=1.12..1.12 rows=12 width=4)
-> Seq Scan on kundengruppe kg (cost=0.00..1.12 rows=12 width=4)
-> Hash (cost=1000.58..1000.58 rows=42758 width=93)
-> Seq Scan on adresse a (cost=0.00..1000.58 rows=42758 width=93)
-> Hash (cost=5.46..5.46 rows=246 width=17)
-> Seq Scan on nation n (cost=0.00..5.46 rows=246 width=17)
-> Hash (cost=1000.58..1000.58 rows=42758 width=93)
-> Seq Scan on adresse a_1 (cost=0.00..1000.58 rows=42758 width=93)
-> Hash (cost=5.46..5.46 rows=246 width=14)
-> Seq Scan on nation n_1 (cost=0.00..5.46 rows=246 width=14)
-> Hash (cost=746.25..746.25 rows=39325 width=394)
-> Seq Scan on kontakt ko (cost=0.00..746.25 rows=39325 width=394)
-> Hash (cost=595.34..595.34 rows=22234 width=133)
-> Seq Scan on bankverbindung b (cost=0.00..595.34 rows=22234 width=133)
-> Hash (cost=1.06..1.06 rows=6 width=14)
-> Seq Scan on zahlungsart za (cost=0.00..1.06 rows=6 width=14)
-> Hash (cost=1.12..1.12 rows=12 width=28)
-> Seq Scan on zahlungsbedingung zb (cost=0.00..1.12 rows=12 width=28)
-> Hash (cost=1.16..1.16 rows=16 width=23)
-> Seq Scan on lieferbedingung lb (cost=0.00..1.16 rows=16 width=23)

I tried REINDEX, it had no effect on the queryplan.

> Do the systems differ in any other way, especially storage?
The old Server is over 5 years old, so the new hardware is totally
differnt: 12 CPU, 64GB RAM, RAID6 on SAS, virtual Hyper-V machine

> Jan

Any more suggestions?

Rainer

You should run EXPLAIN (ANALYZE, BUFFERS) instead of just EXPLAIN.

Also, please show memory-related and query tuning config parameters.

Regards,
Igor Neyman