How to rsync InnoDB page compressed datafiles

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi all,
I’m using innodb page compression (lz4 algorithm) in my database. Increased performance is really significant so overall I’m super-excited about change from table-level compression. Now I’m trying to resolve how to rsync datafile to another host, having compression still in place.

I tried “rsync -av --progress --sparse --delete” but files were stored on target server without any compression(du equals ls). After starting service on target server, I double checked that information_schema.innodb_tablespaces table is really showing “Compressed” on all tables. Medicine for me = running “optimize table” on all tables to rebuild physically data on disk based on tables definition. But that’s not feasible for me (time + disk space). During my discoveries around I found Vadim’s blog (good, bad, ugly) but nothing more.

Any idea how can I preserve innodb page compression while copying (in general, maybe rsync is not the right tool) datafiles into other server is highly appreciated! Also any experiences appreciated too, I just can’t stand that performance is so good and this last step… is not achievable :slight_smile:

Server env: Debian Buster, kernel 5.4.48, xfsprogs 4.20.0-1, rsync 3.1.3-6, percona server 8.0.22

2 Likes
2

Hello @Jiri_Sula,
rsync is certainly not the correct tool here. You should be using xtrabackup to make streaming backups to your storage server. Be sure to add --parallel 4 (or greater) and use --stream xbstream for increased network throughput.

1 Like