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
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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.