MySQL Bugs: #74065: Performance hints for mysqlbackup: use innodb compressed to reduce data size

Bug #74065	Performance hints for mysqlbackup: use innodb compressed to reduce data size
Submitted:	25 Sep 2014 8:51	Modified:	7 Sep 2018 17:19
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Backup Documentation	Severity:	S3 (Non-critical)
Version:	3.11.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.11/en/backup-performance.html

As far as I know using InnoDB with row_format=COMPRESSED instead of row_format=COMPACT/etc. would help in reducing both backup and restore times.

It might have negative impact on incremental backup and apply-log operations as documented in "Compression and the InnoDB Redo Log Files"
http://dev.mysql.com/doc/refman/5.6/en/innodb-compression-internals.html

How to repeat:
Read docs

Suggested fix:
Add note(s) to backup_performance.html about compressed InnoDB tables. 
Also add note(s) about compressed MyISAM tables (myisampack).

Thank you for the reasonable doc request.

Although I think users should compress their tables not to make backups smaller and faster, but for better performance of their MySQL installations and also I think it won't add big advantage if compare with compressed backup. But I think it is good to measure impact of having compressed tables on speed of backup and publish these results.

Indeed table compression should mainly be used for 'normal operation' and not soly for backups. However for some users backup and restore times are important and it could help.

A compressed table doesn't need to be compressed during the backup as it can just be copied and it doesn't need to be uncompressed during restore (although after startup InnoDB will uncompress pages to get them into the buffer pool)

I did some simple tests with the employees database from https://dev.mysql.com/doc/index-other.html

This was on a FreeBSD 10.1 VM with MySQL 5.6.22 and MEB 3.11.1

Timings of a compressed full backup:

With LZ4 and REDUNDANT tables: 
10.37 real / 2.21 user / 3.99 sys / 114MB

With LZ4 and all tables COMPRESSED (default key_block_size=8)
7.35 real / 0.60 user / 2.90 sys / 122MB

With zlib (level 9) and REDUNDANT tables:
244.21 real / 234.32 user / 3.21 sys / 82MB

With zlib (level 9) and REDUNDANT tables and salaries compressed with kb=4
60.43 real / 52.51 user / 3.09 sys / 105MB

Posted by developer:
 
Updated the MySQL Enterprise Backup manuals as suggested. 

Changes will be visible soon on https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/backup-performance.html, for MySQL Enterprise Backup 8.0