MySQL Bugs: #100243: Native cloning causes sparse files to grow to their full size

Bug #100243	Native cloning causes sparse files to grow to their full size
Submitted:	17 Jul 2020 7:49	Modified:	24 Aug 2020 14:04
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Clone Plugin	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	page_compression

Description:
Tables with page compression (CREATE TABLE ... COMPRESSION="zlib") use hole punching to create sparse files.

With replication this works ok, the size as reported with `ls -lhs` are the same on replica and source.

However when cloning with `CLONE INSTANCE FROM...` the sparse property seems to get lost.

Files on source:
total 5.4G
1.9G -rw-r--r-- 1 mysql mysql 1.9G Jul 14 13:18 no_compression.ibd
489M -rw-r--r-- 1 mysql mysql 1.9G Jul 14 13:21 page_compression_lz4.ibd
489M -rw-r--r-- 1 mysql mysql 1.9G Jul 14 15:06 page_compression_zlib.ibd
1.9G -rw-r--r-- 1 mysql mysql 1.9G Jul 14 13:15 source.ibd
836M -rw-r--r-- 1 mysql mysql 836M Jul 14 13:19 table_compression_8k.ibd

Files on replica:
total 8.1G
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 17 09:40 no_compression.ibd
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 17 09:39 page_compression_lz4.ibd
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 17 09:39 page_compression_zlib.ibd
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 17 09:39 source.ibd
836M -rw-r----- 1 mysql mysql 836M Jul 17 09:39 table_compression_8k.ibd

How to repeat:
This is with CentOS 8 on the replica and and CentOS 7 on the source/donor. Both use XFS.

Suggested fix:
Make sure page compression and native cloning work together to keep the files having the same size after cloning (so with 'holes').

Hello Daniël,

Thank you for the report and feedback.
I tried to reproduce the reported issue at my end but not seeing any issues i.e Donor and Recipient files having the same size after cloning(Only diff being that donor is on OEL7 and recipient on CentOS8, and default configuration used).  
I'm attaching the worklog for your reference, please let me know if I'm missing anything here. Thank you!

Sincerely,
Umesh

MySQL Server 8.0.21 test results

Attachment: 100243_8.0.21.results.txt (text/plain), 19.16 KiB.

I tried this again, and it looked like it worked fine, but on the original set of hosts it still shows the problem.

I've tried to set sunit/swith on fs creation, but that doesn't seem to matter. So it looks like MIN-IO and OPT-IO as reported by lsblk is the real difference.

Weirdly enough the issue is not XFS specific, it also happens with tmpfs.

If on the machine were the file was growing while cloning, I first do a ALTER TABLE...FORCE then everything works ok.

root@dvaneeden-testdb-1002 [(none)]> CLONE LOCAL DATA DIRECTORY '/run/test/local_test3';
Query OK, 0 rows affected (53.09 sec)

[dvaneeden@dvaneeden-testdb-1002 /run/test]$ sudo ls -lhs /run/test/local_test3/compression_test/ /mysql/db/data/compression_test/
/mysql/db/data/compression_test/:
total 5.7G
1.9G -rw-r--r-- 1 mysql mysql 1.9G Jul 14 13:18 no_compression.ibd
749M -rw-r----- 1 mysql mysql 2.1G Jul 22 00:12 page_compression_lz4.ibd
489M -rw-r--r-- 1 mysql mysql 1.9G Jul 14 15:06 page_compression_zlib.ibd
1.9G -rw-r--r-- 1 mysql mysql 1.9G Jul 14 13:15 source.ibd
836M -rw-r--r-- 1 mysql mysql 836M Jul 14 13:19 table_compression_8k.ibd

/run/test/local_test3/compression_test/:
total 7.0G
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 22 00:12 no_compression.ibd
747M -rw-r----- 1 mysql mysql 2.1G Jul 22 00:12 page_compression_lz4.ibd   <- ALTER TABLE ... FORCE, before CLONE.
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 22 00:12 page_compression_zlib.ibd  <- Gown to full size
1.9G -rw-r----- 1 mysql mysql 1.9G Jul 22 00:12 source.ibd
836M -rw-r----- 1 mysql mysql 836M Jul 22 00:12 table_compression_8k.ibd

Thank you so much Daniël for the detailed test results.
I'll try to repeat again on few other boxes and come back to you.
I'm sorry but I've marked your results*.txt update as private for now(host details etc visible). If you think okay to make them public then I'll publish them. Please let me know

Sincerely,
Umesh

Hi, any luck with reproducing this? or do you need more info?

Hello Daniël,

I tried to repro this on as many as 8-10 times(physical boxes, VMs etc) but still not seeing the discrepancy on cloned instance. I'll give it a try again later today and still no luck then will discuss with developer and come back to you.

Sincerely,
Umesh

Thank you so much Daniël all your help and pointers.
Finally, I'm able to repro on CentOS(Donor CentOS7, Recipient CentOS8).

regards,
Umesh

MySQL Server 8.0.21 test results - D/R CentOS platform

Attachment: 100243_CentOS_8.0.21.results.txt (text/plain), 42.00 KiB.

Posted by developer:
 
Fixed as of the upcoming 8.0.22 release, and here's the proposed changelog entry from the documentation team:

A page-compressed table was cloned as an uncompressed table. The
associated tablespace object, which includes a compression flag, was not
initialized prior to the cloning operation.