Feature preview: Compact backups in Percona XtraBackup

Feature preview: Compact backups in Percona XtraBackup:
We continue to improve Percona XtraBackup, and today I would like to give a preview for one feature which comes in next Percona XtraBackup 2.1 release.
This feature is “Compact backups”, and let me explain what it does.

As you may know InnoDB PK (Primary Key) contains all data, and all secondary indexes are only subset of columns of Primary Key. So in theory we can store only PK, and re-build secondary indexes as we need. Well, now it is possible not only in theory.
To create a compact backup you should use

innobackupex --compact

and it will create a backup where all InnoDB tables contain only Primary Keys and not secondary.

It allows to safe some space on a backup storage. How much? Well, it depends on how many indexes you have.

For example for table order_line from tpcc benchmark, 100W.

Original size: 3140M,

Size in compact backup: 2228M.
You may suspect that there is a catch somewhere, and yes, there is.

To recovery a usable database, we need to rebuild indexes, and it is done on prepare stage,

and it takes time.

The command to prepare is:
innobackupex --apply-log --rebuild-indexes /data/backup
As a bonus, secondary indexes are created by sorting, that in general gives much less fragmented indexes, so it may result in an additional space saving.
In fact this --rebuild-indexes can be used on a full backup, and it will result in rebuilt de-fragmented indexes.
I encourage you to try this feature and report your experience.

Right now it is available only in source code from https://code.launchpad.net/~percona-core/percona-xtrabackup/2.1,

but preview binaries should be available soon.
The post Feature preview: Compact backups in Percona XtraBackup appeared first on MySQL Performance Blog.

DIGITAL JUICE