MySQL Enterprise Backup (MEB) Compress Option

I have used compression tools before and was interested to understand a little more about MEB's use of compression.  Jumping straight in, here are a couple of points to consider:
  • the backup compression feature only applies to InnoDB tables.  
  • when InnoDB tablespace files are compressed during backup, they receive the extension .ibz rather than the usual .ibd extension.  
  • you can only use the --compress option for full backups, not for incremental backups.

How does it work
MySQL Enterprise backup has three conceptual steps:

  • Backup - backup the database
  • Apply - prepare for restore by bring the backup up to date
  • Restore - restore the database
Using MEB you can combine Backup and Apply or they can be two separate steps.  Compression works on the Backup step which involves running the Backup and Apply steps separately,  if you try to use --compress with the backup-and-apply-log option you wil get an error:
mysqlbackup: ERROR: Incompatible option found. Current Operation:backup-and-apply-log
Specified option:--compress

How do you use it:
mysqlbackup -u root --backup-dir=<backup location> --compress  backup

Whilst the backup is running the files will be compressed and the extension changed to .ibz.  This allows automatic space savings whilst the backup is running.   The downside is compression (and uncompress) uses a lot of CPU resource.  Compared to a standard MEB backup using compression uses at least double the amount of CPU.  The compression rate is very good, I tested it on partition tables as well as standard tables, my 47g instance compressed to 8.1gb.  At the end of the backup you will get a some specific compression information:
130528 10:19:28 mysqlbackup: INFO: Compressed 39646 MB of data files to 8402847 kbytes (compression 79.30%).
130528 10:19:28 mysqlbackup: INFO: Compress Backup operation completed successfully.

Compression biggest benefit is it relives space issues but is intensive to run and if you need this backup for a restore you will need the full space of the backup because before you restore it you need to uncompress it, you can not uncompress 'on the fly'.

Restoring a Compressed Backup
Once the compressed backup is required for restore you need to run an Apply and uncompress to ensure the backup files up to date and ready to restore:
mysqlbackup -u root --backup-dir=<backup location> --uncompress apply-log;
This process will uncompress the backup in its current location and the backup will then be it's regular size, so it is worth remembering that extra space is required in your backup area for restoring a compressed backup.  Also as with the compress option uncompress uses a lot of CPU.

Once the Apply process has run the backup is ready for a standard restore.

It's also useful to know timings.  In my tests, a 41gb database took 11 mins to perform a normal backup.  A compressed backup took 8/9 mins, so it is fast.  I then decided to run the Apply step and this took 6/7 mins.  I should note here that I was using MEB 3.8.  I was running it with the default settings (3 read-threads, 3 process-threads, 3 write-threads), so they may differ from MEB 3.7.

If you want to use the compression feature it only works on innodb table.   It compresses 'on the fly' and it is surprisingly quick.  The downside is it does require a lot of CPU.  I wouldn't run this with nightly batch jobs.  There are some extra steps to consider if you need to restore.  If you need to restore you will need the full backup space to uncompress the backup.   I can definitely see a use for this espcially as we write backups off to tape nightly.  This will help us with amount we transfer to tape cutting down the amount of tape space used.

No comments:

Post a Comment