Xtrabackup not compressing all files

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.

  1. Ensure you have zstd package installed on the server.

  2. Please try the command with --compress-threads=4

xtrabackup --backup --compress --compress-threads=4 \
  1. 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


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