Re: design for parallel backup

On Wed, Apr 22, 2020 at 11:24 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> *My* gut feeling is that you're going to have a harder time using CPU
> time efficiently when doing parallel compression via multiple processes
> and independent connections. You're e.g. going to have a lot more
> context switches, I think. And there will be network overhead from doing
> more connections (including worse congestion control).

OK, noted. I'm still doubtful that the optimal number of connections
is 1, but it might be that the optimal number of CPU cores to apply to
compression is much higher than the optimal number of connections. For
instance, suppose there are two equally sized tablespaces on separate
drives, but zstd with 10-way parallelism is our chosen compression
strategy. It seems to me that two connections has an excellent chance
of being faster than one, because with only one connection I don't see
how you can benefit from the opportunity to do I/O in parallel.
However, I can also see that having twenty connections just as a way
to get 10-way parallelism for each tablespace might be undesirable
and/or inefficient for various reasons.

> this results in a 16GB base backup. I think this is probably a good bit
> less compressible than most PG databases.
>
> method level parallelism wall-time cpu-user-time cpu-kernel-time size rate format
> gzip 1 1 305.37 299.72 5.52 7067232465 2.28
> lz4 1 1 33.26 27.26 5.99 8961063439 1.80 .lz4
> lz4 3 1 188.50 182.91 5.58 8204501460 1.97 .lz4
> zstd 1 1 66.41 58.38 6.04 6925634128 2.33 .zstd
> zstd 1 10 9.64 67.04 4.82 6980075316 2.31 .zstd
> zstd 3 1 122.04 115.79 6.24 6440274143 2.50 .zstd
> zstd 3 10 13.65 106.11 5.64 6438439095 2.51 .zstd
> zstd 9 10 100.06 955.63 6.79 5963827497 2.71 .zstd
> zstd 15 10 259.84 2491.39 8.88 5912617243 2.73 .zstd
> pixz 1 10 162.59 1626.61 15.52 5350138420 3.02 .xz
> plzip 1 20 135.54 2705.28 9.25 5270033640 3.06 .lz

So, picking a better compressor in this case looks a lot less
exciting. Parallel zstd still compresses somewhat better than
single-core lz4, but the difference in compression ratio is far less,
and the amount of CPU you have to burn in order to get that extra
compression is pretty large.

> I don't really see a problem with emitting .zip files. It's an extremely
> widely used container format for all sorts of file formats these days.
> Except for needing a bit more complicated (and I don't think it's *that*
> big of a difference) code during generation / unpacking, it seems
> clearly advantageous over .tar.gz etc.

Wouldn't that imply buying into DEFLATE as our preferred compression algorithm?

Either way, I don't really like the idea of having PostgreSQL have its
own code to generate and interpret various archive formats. That seems
like a maintenance nightmare and a recipe for bugs. How can anyone
even verify that our existing 'tar' code works with all 'tar'
implementations out there, or that it's correct in all cases? Do we
really want to maintain similar code for other formats, or even for
this one? I'd say "no". We should pick archive formats that have good,
well-maintained libraries with permissive licenses and then use those.
I don't know whether "zip" falls into that category or not.

> > Other options include, perhaps, (1) emitting a tarfile of compressed
> > files instead of a compressed tarfile
>
> Yea, that'd help some. Although I am not sure how good the tooling to
> seek through tarfiles in an O(files) rather than O(bytes) manner is.

Well, considering that at present we're using hand-rolled code...

> I think there some cases where using separate compression state for each
> file would hurt us. Some of the archive formats have support for reusing
> compression state, but I don't know which.

Yeah, I had the same thought. People with mostly 1GB relation segments
might not notice much difference, but people with lots of little
relations might see a more significant difference.

> Hm. There's some appeal to just store offsets in the manifest, and to
> make sure it's a seakable offset in the compression stream. OTOH, it
> makes it pretty hard for other tools to generate a compatible archive.

Yeah.

FWIW, I don't see it as being entirely necessary to create a seekable
compressed archive format, let alone to make all of our compressed
archive formats seekable. I think supporting multiple compression
algorithms in a flexible way that's not too tied to the capabilities
of particular algorithms is more important. If you want fast restores
of incremental and differential backups, consider using -Fp rather
than -Ft. Or we can have a new option that's like -Fp but every file
is compressed individually in place, or files larger than N bytes are
compressed in place using a configurable algorithm. It might be
somewhat less efficient but it's also way less complicated to
implement, and I think that should count for something. I don't want
to get so caught up in advanced features here that we don't make any
useful progress at all. If we can add better features without a large
complexity increment, and without drawing objections from others on
this list, great. If not, I'm prepared to summarily jettison it as
nice-to-have but not essential.

> I don't really see any of the concerns there to apply for the base
> backup case.

I felt like there was some reason that threads were bad, but it may
have just been the case you mentioned and not relevant here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company