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

Ha! So forcing compression to 0 i went from 644 minutes to 87 minutes. And this time I backed it to a afp share and from the looks of it I hit the roof on that eth interface. Size of backup went from 50GB to 260 GB though, hehehe.

So something seems to have changed regarding default compression level between 8.x and 9.6 when doing a custom format dump. I will time all the different levels and see if I can find out more.

WHat's the normal way to deal with compression? Dump uncompressed and use something that threads better to compress the dump?

Cheers

--
Henrik Cednert
cto | compositor

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

On 22 Nov 2017, at 04:48, Henrik Cednert (Filmlance) <henrik(dot)cednert(at)filmlance(dot)se<mailto:henrik(dot)cednert(at)filmlance(dot)se>> wrote:

This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing>
Feedback<http://aka.ms/SafetyTipsFeedback>
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 <tgl(at)sss(dot)pgh(dot)pa(dot)us<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:

"Henrik Cednert (Filmlance)" <henrik(dot)cednert(at)filmlance(dot)se<mailto:henrik(dot)cednert(at)filmlance(dot)se>> 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