Re: pg_upgrade - Mailing list pgsql-admin

Hello Igor,

I still have trouble with the new Server.

I would really appreciate if you could take
a look at the attached .conf 

Maybe you can point me to a parameter which
would speed up the performance.


Thank you very much for your help!

Regards, 
Rainer



------------------------- Original Nachricht -------------------------

Von.......: Igor Neyman <ineyman@perceptron.com> 
An........: Rainer Leo 
Datum.....: Dienstag, 23. Juni 2015, 15:50 
Betreff...: Re: [ADMIN] pg_upgrade

 
 
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rainer Leo
Sent: Tuesday, June 23, 2015 3:51 AM
To: pgsql-admin@postgresql.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

Leo,

If this machine is dedicated database server, considering you have 64GB RAM, I’d increase shared_buffers to 8GB and effective_cache_size to about 546GB (again, depending on what else is running on this machine).

Also, work_mem and temp_buffers may need some tuning, but you still didn’t provide the results of EXPLAIN (ANALYZE, BUFFERS) for your slow queries, so it’s hard to tell.

Also, try keeping this conversation on PG mailing list: there are people much more knowledgeable than I am who could help.

Regards,

Igor Neyman