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


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...

1 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.

  • many thx, for the detailed answer. I did my google research on the topic, with no result (so I asked here) I am wondering you are the member of the development team, because as I can imagine the only way to know this is knowing the source code... (or my google research was not effective enough) Jan 26, 2023 at 15:33
  • 1
    Yes, I'm member of development team. I even dealt with this specific question back in the day. I tried my google chops though. If I google for "mariabackup page compression", a see one relevant entry (bug related to XFS), as entry # 3 . I have better results, giving more technical "mariabackup punch hole" , this gives the original bug jira.mariadb.org/browse/MDEV-13023, which was fixed back in 2017 the way I described in the answer. Except 2 entries in the bug database, there is not much info about it, right. Jan 26, 2023 at 16:35
  • many thanks, very authentic. Punch hole did not came in my mind, my excuse I've tried "mariabackup sparse". Jan 27, 2023 at 4:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.