Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade - Mailing list pgsql-performance
From | Henrik Cednert (Filmlance) |
---|---|
Subject | Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade |
Date | |
Msg-id | 12D87F70-956E-499B-9532-DAC52F419837@filmlance.se Whole thread Raw |
In response to | Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade ("Henrik Cednert (Filmlance)" <henrik.cednert@filmlance.se>) |
Responses |
Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
|
List | pgsql-performance |
When investigating the zlib lead I looked at 8.4 installation and 9.5 installation. 9.5 includes zlib.h (/Library/PostgreSQL//9.5/include/zlib.h), but 8.4 doesn't. But that's a header file and I have no idea how that really works and if that's the one used by pgres9.5 or not. The version in it says 1.2.8 and that's what the Instruments are showing when I monitor pg_dump while running.
Guess I'll have to install instruments in a dev env and do a pg_dump with 8.4 to see the difference. Tedious. =/
--
Henrik Cednert
cto | compositor
Filmlance International
mobile [ + 46 (0)704 71 89 54 ]
skype [ cednert ]
--
Henrik Cednert
cto | compositor
Filmlance International
mobile [ + 46 (0)704 71 89 54 ]
skype [ cednert ]
On 22 Nov 2017, at 13:17, Henrik Cednert (Filmlance) <henrik.cednert@filmlance.se> wrote:
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofingFeedback HelloI've ran it with all the different compression levels on one of the smaller db's now. And not sending any flags to it see is, as I've seen hinted on some page on internet, same as level 6.I do, somewhat, share the opinion that something is up with zlib. But at the same time I haven't touch it since the 8.4 installation so it's a mystery how it could've failed on its own. The only thing performed was an upgrade from 8.4 to 9.5. But yes, I can not really say exactly what that upgrade touched and what it didn't touch. Will investigate further.COMPRESSION LEVEL: 0FILE SIZE: 6205982696real 0m38.218suser 0m3.558ssys 0m17.309sCOMPRESSION LEVEL: 1FILE SIZE: 1391475419real 4m3.725suser 3m54.132ssys 0m5.565sCOMPRESSION LEVEL: 2FILE SIZE: 1344563403real 4m18.574suser 4m9.466ssys 0m5.417sCOMPRESSION LEVEL: 3FILE SIZE: 1267601394real 5m23.373suser 5m14.339ssys 0m5.462sCOMPRESSION LEVEL: 4FILE SIZE: 1241632684real 6m19.501suser 6m10.148ssys 0m5.655sCOMPRESSION LEVEL: 5FILE SIZE: 1178377949real 9m18.449suser 9m9.733ssys 0m5.169sCOMPRESSION LEVEL: 6FILE SIZE: 1137727582real 13m28.424suser 13m19.842ssys 0m5.036sCOMPRESSION LEVEL: 7FILE SIZE: 1126257786real 16m39.392suser 16m30.094ssys 0m5.724sCOMPRESSION LEVEL: 8FILE SIZE: 1111804793real 30m37.135suser 30m26.785ssys 0m6.660sCOMPRESSION LEVEL: 9FILE SIZE: 1112194596real 33m40.325suser 33m27.122ssys 0m6.498sCOMPRESSION LEVEL AT DEFAULT NO FLAG PASSED TO 'pg_dump'FILE SIZE: 1140261276real 13m18.178suser 13m9.417ssys 0m5.242s
--
Henrik Cednert
cto | compositor
Filmlance International
mobile [ + 46 (0)704 71 89 54 ]
skype [ cednert ]On 22 Nov 2017, at 11:32, Matthew Hall <mhall@mhcomputing.net> wrote: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
pgsql-performance by date: