Hi Team,
What would be the reason for Xtrabackup not to compressing all the ibd files?
Xtrabackup version
[Xtrabackup] recognized server arguments: --datadir=/mysqldata --innodb_flush_method=O_DIRECT --innodb_log_files_in_group=2 --innodb_log_file_size=1G --innodb_flush_log_at_trx_commit=1 --innodb_file_per_table=1 --innodb_buffer_pool_size=16G --log_bin=mysql-bin --server-id=1 --open_files_limit=65535
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
Command used:
xtrabackup --backup --compress --target-dir=/backups/databases/physical/sunday_full/
Log file output:
2024-06-03T12:51:12.805799+02:00 2 [Note] [MY-011825] [Xtrabackup] Compressing ./delta_sap_schweppes/customer.ibd to /backups/databases/physical/sunday_full/delta_sap_schweppes/customer.ibd
2024-06-03T12:51:12.926955+02:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing ./delta_sap_schweppes/customer.ibd to /backups/databases/physical/sunday_full/delta_sap_schweppes/customer.ibd
Since 8.0.34 Xtrabackup, by default, Xtrabackup uses the ZSTD compression technique, and the older compression qp has been removed. You can now see the compressed file extension with .zst.
-
Ensure you have zstd package installed on the server.
-
Please try the command with --compress-threads=4
xtrabackup --backup --compress --compress-threads=4 \
--target-dir=/data/compressed/
- Please try to mention another compression technique --compress=lz4 explicitly
xtrabackup --backup --compress=lz4 --target-dir=/data/backup
Hi Abhinav,
Compression is working but only for some ibd files, for example
as_stock_parent_deduct.ibd
as_stock_slot_variance_reasons.ibd
as_stock_slots.ibd
as_stock_snapshot.ibd.zst
as_stock_take.ibd.zst
I’ve tried both --compress-threads=4 and --compress=lz4, still the same outcome.
Hi @Danie_Le_Roux Can you please run PXB with additional logging and/or debug output? Is this 100% repeatable? Is it the same tables that do not get compressed or is it different tables each time you run PXB?
Hi Matthew,
What is the flag for extra logging?
It seems that none of the ibd files gets compressed, only the mysql and xtrabackup files.
For example, the below files is compressed
-rw-r----- 1 root root 258930 Jun 5 09:06 ib_buffer_pool.zst
-rw-r----- 1 root root 40504413 Jun 5 09:06 ibdata1.zst
-rw-r----- 1 root root 114 Jun 5 09:06 mysql-bin.000695.zst
-rw-r----- 1 root root 32 Jun 5 09:06 mysql-bin.index.zst
-rw-r----- 1 root root 66800994 Jun 5 09:06 mysql.ibd.zst
-rw-r----- 1 root root 22081136 Jun 5 09:06 undo_001.zst
-rw-r----- 1 root root 15940185 Jun 5 09:06 undo_002.zst
-rw-r----- 1 root root 34 Jun 5 09:06 xtrabackup_binlog_info.zst
None of the ibd files is compressed, example below
-rw-r----- 1 root root 128K Jun 5 09:06 as_stock_slots.ibd
-rw-r----- 1 root root 1.7G Jun 5 09:06 as_stock_snapshot.ibd
-rw-r----- 1 root root 192K Jun 5 09:06 as_stock_take.ibd
-rw-r----- 1 root root 224K Jun 5 09:06 as_stock_take_items.ibd
Are these tables encrypted? I see you have another post about Vault encryption. Could there be some issue with the two?
Hi Matthew,
Yes, tables are encrypted.
Not sure if this is related to my Vault issue in the other post
Since these tables that are not getting compressed are encrypted AND you are having issues with encrypted tables in general, I suggest we focus efforts on solving the Vault issue first and once that is all good, if the tables are still not compressing, we can look more.
Hi Matthew,
I’ve changed my encryption from Vault to local File Key component based and PXB is now working on 85+ databases with over 1.5T, however I’m still facing the issues of my ibd files not compressing. I’ve switch off encryption on some of the tables and noticed that they get compressed if I do that. Does that mean that encrypted tables does not get compressed?
Table testa = Encrypted
Jun 13 13:33 db.opt.lz4
Jun 13 13:33 testa.ibd
Jun 13 13:33 testb.MYD.lz4
Jun 13 13:33 testb.MYI.lz4
Jun 13 13:33 testb_11898.sdi.lz4
Table testa = Not Encrypted
Jun 13 13:39 db.opt.lz4
Jun 13 13:39 testa.ibd.lz4
Jun 13 13:39 testb.MYD.lz4
Jun 13 13:39 testb.MYI.lz4
Jun 13 13:39 testb_11898.sdi.lz4
Hello @Danie_Le_Roux,
I could not find it documented, but Percona’s CTO confirmed that encrypted tables are not compressed:
Vadim T: there is no benefit from compressing encrypted data. typically there is about 0% compression can be achieved