From: Matthew Hall <mhall@mhcomputing.net>
To: Henrik Cednert (Filmlance) <henrik.cednert@filmlance.se>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
Date: Wed, 22 Nov 2017 02:32:45 -0800
Message-ID: <C5EC3C9D-755B-4356-B1EB-7822030D03A2@mhcomputing.net> (raw)
In-Reply-To: <8337E0CC-47A3-4A5F-8156-31BE1E1F0484@filmlance.se>
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>
> On Nov 21, 2017, at 10:18 PM, Henrik Cednert (Filmlance) <henrik.cednert@filmlance.se> wrote:
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mhall@mhcomputing.net, henrik.cednert@filmlance.se, pgsql-performance@lists.postgresql.org
Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
In-Reply-To: <C5EC3C9D-755B-4356-B1EB-7822030D03A2@mhcomputing.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox