Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
From: Matthew Hall <mhall@mhcomputing.net>
In-Reply-To: <8337E0CC-47A3-4A5F-8156-31BE1E1F0484@filmlance.se>
Date: Wed, 22 Nov 2017 02:32:45 -0800
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C5EC3C9D-755B-4356-B1EB-7822030D03A2@mhcomputing.net>
References: <8F33D0C7-E12E-4996-990C-3CF0C5ED0437@filmlance.se>
 <7442.1511283687@sss.pgh.pa.us>
 <CA+TgmoY3F+Yiheh-41wuLVzozw9sKQFqBG2fqSbskKvL20tFrw@mail.gmail.com>
 <14231.1511289969@sss.pgh.pa.us>
 <2D863ADA-134E-414B-82DD-88E53D621697@filmlance.se>
 <20863.1511298080@sss.pgh.pa.us>
 <13B8A868-BB94-4A4B-81AF-EB3DCC379439@filmlance.se>
 <8337E0CC-47A3-4A5F-8156-31BE1E1F0484@filmlance.se>
To: "Henrik Cednert (Filmlance)" <henrik.cednert@filmlance.se>
Precedence: bulk


> On Nov 21, 2017, at 10:18 PM, Henrik Cednert (Filmlance) =
<henrik.cednert@filmlance.se> wrote:
>=20
> WHat's the normal way to deal with compression? Dump uncompressed and =
use something that threads better to compress the dump?

I would say most likely your zlib is screwed up somehow, like maybe it =
didn't get optimized right by the C compiler or something else sucks w/ =
the compression settings. The CPU should easily blast away at that =
faster than disks can read.

I did do some studies of this previously some years ago, and I found =
gzip -6 offered the best ratio between size reduction and CPU time out =
of a very wide range of formats, but at the time xz was also not yet =
available.

If I were you I would first pipe the uncompressed output through a =
separate compression command, then you can experiment with the flags and =
threads, and you already get another separate process for the kernel to =
put on other CPUs as an automatic bonus for multi-core with minimal =
work.

After that, xz is GNU standard now and has xz -T for cranking up some =
threads, with little extra effort for the user. But it can be kind of =
slow so probably need to lower the compression level somewhat depending =
a bit on some time testing. I would try on some medium sized DB table, =
like a bit over the size of system RAM, instead of dumping this great =
big DB, in order to benchmark a couple times until it looks happy.

Matthew=