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: 13B8A868-BB94-4A4B-81AF-EB3DCC379439@filmlance.se
(view: Whole thread, Raw)
In response to: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  (Tom Lane)
Responses: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade  ("Henrik Cednert (Filmlance)")
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 Tom

I'm honestly not sure about anything. =) I use the exact same flags as with 8.4 for the dump:

${BINARY_PATH}/pg_dump --host=localhost --user=postgres --no-password --blobs --format=custom --verbose --file=${pg_dump_filename}_${database}.backup ${database} 

So unless the default behaviour have changed in 9.x I'd say I don't use compression. I will try to force it to no compression and see if it's different.

Sadly the instruments session stopped recording when I logged out of the system yesterday. Doh. =/

Cheers

--
Henrik Cednert
cto | compositor

Filmlance International
mobile [ + 46 (0)704 71 89 54 ]
skype  [ cednert ]

On 21 Nov 2017, at 22:01, Tom Lane <> wrote:

"Henrik Cednert (Filmlance)" <> writes:
I'm not sure if I can attach screenshots here. Trying, screenshot from instruments after running for a few mins.

It looks like practically all of pg_dump's time is going into deflate(),
ie zlib.  I don't find that terribly surprising in itself, but it offers
no explanation for why you'd see a slowdown --- zlib isn't even our
code, nor has it been under active development for a long time, so
presumably 8.4 and 9.5 would have used the same version.  Perhaps you
were doing the 8.4 dump without compression enabled?

regards, tom lane


pgsql-performance by date:

From: "Henrik Cednert (Filmlance)"
Date:
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
From: Matthew Hall
Date:
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade