Mariabackup, Xrtrabackup, how those handling InnodDB page compression, meaning: sparse files?

Question

InnoDB engine, when using page compression, heavily depend on file system sparse file support. InnoDB Page Compression.

It raises the question, how will mariabackup/xtrabackup handle those InooDB sparse files in

• --backup
• --prepare
• --copy-back

operations?

Suppose we have total of 100G InnoDB tables,(ls reports 100G but du reports 0.5G). The following questions raise:

• When I --backup this with mariabackup/xtrabackup will the backup itself sparse in the target dir?

• When I --prepare this with mariabackup/xtrabackup will the result is sparse?

• When I --copy-back this with mariabackup/xtrabackup will the result is sparse

• Say mariabackup/xtrabackup does not handle sparse files. (tragedy). Can I correct this behavior with using cp --sparse=always instead of mariabackup --copy-back ... or this is not that simple...

Answer 1

mariabackup recognizes page compressed files, and whenever it copies pages, it

1. calculates compressed size of the page, which is page size without trailing zeros
2. copies compressed part of the page to the destination file
3. advances file pointer (i.e lseek) past the end of the page in the destination file, that is, it "skips" what would be binary zeros

This logic is encapsulated in this function, and it works for --backup and --copy-back (and whenever mbstream "unpacks" the stream).

One can recognize that this is the same technique - write behind the end of file, that Unixes had for ages to get the sparse files.

Surprisingly, what worked for the old Unix, does not necessary works for all file systems now, apparently, this logic seems to break on XFS

Of course, you can "sparse" the file yourself, use any tool that punches holes in it.

PS. mariabackup's --prepare is equivalent to starting the server to do a crash recovery, so whenever --prepare writes to data files, it uses usual server logic, punching the holes.

PPS. mariabackup --backup --stream does write binary zeroes to the stdout, thus it would be advisable to pipe stdout with any general compression tool - (gzip, 7zip) just to remove those zeroes from the stream.