Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

From: Henrik Cednert (Filmlance)
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
Date: ,
Msg-id: 01981850-E521-4DC8-A5EC-B7D8C1B9DDE6@filmlance.se
(view: Whole thread, Raw)
In response to: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Matthew Hall)
List: pgsql-performance

Tree view

pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
 RE: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Igor Neyman, )
  Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
   RE: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Igor Neyman, )
    Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
     RE: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Igor Neyman, )
      Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
       RE: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Igor Neyman, )
  Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Shaul Dar, )
 Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Tom Lane, )
  Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Robert Haas, )
   Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Tom Lane, )
    Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
     Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Tom Lane, )
      Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
       Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
        Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Matthew Hall, )
         Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
          Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
           Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Matthew Hall, )
            Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
         Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Andres Freund, )
    Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
 Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Patrick KUI-LI, )
  Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)", )
   Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Gunther, )
    Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Laurenz Albe, )
     Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Claudio Freire, )

Hi Matthew

Actually running that test in a vm right now. =) 

This is the same db dumped from 9.5 and 8.4 with compression 6 in the same system (smaller db in a vm).

9.5:
real 82m33.744s
user 60m55.069s
sys 3m3.375s

8.4
real 42m46.381s
user 23m50.145s
sys 2m9.853s

When looking at a sample and/or instruments I think I can confirm what your hunch was/is. But I'm not skilled enough to say what's right and wrong nor what action to take. But 8.4 seems to use a system library libz.1.dylib while the 9.4 dump refers to libz.1.2.8.dylib which I think is the one shipping with that particular installation I'm using (/Library/PostgreSQL//9.5/include/zlib.h).


I have no idea if I can relink the libs in 9.5 to other ones? support from the software company in question have suggested updating to a newer version of 9.5 but not sure that'll solve it. I'm on thin ice here and not sure how to proceed. I'm not even sure if I should or if I should dump uncompressed and let something threaded take care of the compression. Sadly i'm the type of guy that can't let go so would be nice to get this to work properly anyways. =) 

CHeers and many thanks again.
 


--
Henrik Cednert
cto | compositor

Filmlance International

On 22 Nov 2017, at 20:52, Matthew Hall <> wrote:

On Nov 22, 2017, at 5:06 AM, Henrik Cednert (Filmlance) <> wrote:

When investigating the zlib lead I looked at 8.4 installation and 9.5 installation. 9.5 includes zlib.h (/Library/PostgreSQL//9.5/include/zlib.h), but 8.4 doesn't. But that's a header file and I have no idea how that really works and if that's the one used by pgres9.5 or not. The version in it says 1.2.8 and that's what the Instruments are showing when I monitor pg_dump while running.

Guess I'll have to install instruments in a dev env and do a pg_dump with 8.4 to see the difference. Tedious. =/

I would also check the library linkages of the pg_dump binaries.

See if one thing is using an embedded zlib and the other a system zlib.

Then you could imagine one didn't get compiled with the best-performing CFLAGS, etc.

Matthew.


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Query became very slow after 9.6 -> 10 upgrade
From: "Henrik Cednert (Filmlance)"
Date:
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade