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

On Thu, Dec 7, 2017 at 2:31 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> Gunther wrote:
>> Something is wrong with the dump thing. And no, it's not SSL or whatever,
>> I am doing it on a local system with local connections. Version 9.5 something.
>
> That's a lot of useful information.
>
> Try to profile where the time is spent, using "perf" or similar.
>
> Do you connect via the network, TCP localhost or UNIX sockets?
> The last option should be the fastest.

You can use SSL over a local TCP connection. Whether it's the case is the thing.

In my experience, SSL isn't a problem, but compression *is*. With a
modern-enough openssl, enabling compression is tough, it's forcefully
disabled by default due to the vulnerabilities that were discovered
related to its use lately.

So chances are, no matter what you configured, compression isn't being used.

I never measured it compared to earlier versions, but pg_dump is
indeed quite slow, and the biggest offender is formatting the COPY
data to be transmitted over the wire. That's why parallel dump is so
useful, you can use all your cores and achieve almost perfect
multicore acceleration.

Compression of the archive is also a big overhead, if you want
compression but want to keep the overhead to the minimum, set the
minimum compression level (1).

Something like:

pg_dump -Fd -j 8 -Z 1 -f target_dir yourdb