Bug #53226 Innobackup compression level 0 and 1 does not make any diff in size data files

• Submitted: 27 Apr 2010 22:17
• Modified: 12 Oct 2011 18:31
• Reporter: Hema Sridharan
• Status: Closed
• Category: MySQL Enterprise Backup
• Severity: S3 (Non-critical)
• Version: 3.5
• OS: Any
• CPU Architecture: Any

[27 Apr 2010 22:17] Hema Sridharan

Description:
* When doing backup using innobackup, use compression option (--compress)
* Use different levels for compression options like 0 and 1
* The execution of innobackup --compress=0{or 1} gives the same level of compression for both 0 and 1
* The manuals specify that
"Compression level is an integer between 0 and 9: 1 gives fastest compression, 9 gives best compression, and 0 means no compression"

* But in reality, even --compress=0 gives the same level of compression  as that of --compress=1 
* Ideally there should not be any compression for --compress=0 as stated in InnoDB Hot Backup manuals.

How to repeat:
We will repeat the same test for different levels of compression

1. without compression option
2. with --compress=1
3. with --compress=0

CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE db1.t1(id INT)ENGINE=INNODB;
CREATE TABLE db2.t2(a CHAR(10))ENGINE=INNODB;

INSERT INTO db1.t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15);
INSERT INTO db2.t2 VALUES
('a'),('b'),('c'),('d'),('e'),('f'),
('g'),('h'),('i'),('j'),('k'),('l');

INSERT INTO db1.t1 SELECT * FROM db1.t1;
INSERT INTO db1.t1 SELECT * FROM db1.t1;
INSERT INTO db1.t1 SELECT * FROM db1.t1;
INSERT INTO db1.t1 SELECT * FROM db1.t1;
INSERT INTO db1.t1 SELECT * FROM db1.t1;

INSERT INTO db2.t2 SELECT * FROM db2.t2;
INSERT INTO db2.t2 SELECT * FROM db2.t2;
INSERT INTO db2.t2 SELECT * FROM db2.t2;
INSERT INTO db2.t2 SELECT * FROM db2.t2;
INSERT INTO db2.t2 SELECT * FROM db2.t2;

1. Without any compression
=============================
[hs221732@fimafeng10]/export/home2/tmp/mysql-5.1-ihb: perl innobackup hema-inno.cnf mysql-test/backup/ --socket=/export/home2/tmp/mysql-5.1-ihb/mysql-test/var/tmp/mysqld.1.sock --user=root --ibbackup=/export/home2/tmp/mysql-5.1-ihb/ibbackup

The data file size shows,

ls -lh 2010-04-27_23-43-13/
total 11M
-rw-r--r-- 1 hs221732 wheel  358 Apr 27 23:43 backup-my.cnf
drwxr-x--- 2 hs221732 wheel 4.0K Apr 27 23:43 db1
drwxr-x--- 2 hs221732 wheel 4.0K Apr 27 23:43 db2
-rw-r--r-- 1 hs221732 wheel   16 Apr 27 23:43 ibbackup_binlog_info
-rw-r----- 1 hs221732 wheel 4.0K Apr 27 23:43 ibbackup_logfile
-rw-r----- 1 hs221732 wheel  10M Apr 27 23:43 ibdata1
drwxr-xr-x 2 hs221732 wheel 4.0K Apr 27 23:43 mtr
drwxr-xr-x 2 hs221732 wheel 4.0K Apr 27 23:43 mysql
-rw-r--r-- 1 hs221732 wheel    0 Apr 27 23:43 mysql-stderr
-rw-r--r-- 1 hs221732 wheel  526 Apr 27 23:43 mysql-stdout
drwxr-xr-x 2 hs221732 wheel 4.0K Apr 27 23:43 test

2. With --compress=1
====================

 perl innobackup --compress=1 hema-inno.cnf mysql-test/backup/ --socket=/export/home2/tmp/mysql-5.1-ihb/mysql-test/var/tmp/mysqld.1.sock --user=root --ibbackup=/export/home2/tmp/mysql-5.1-ihb/ibbackup

ls -lh mysql-test/backup/2010-04-27_23-47-40/ibdata1.ibz

-rw-r----- 1 hs221732 wheel 62K Apr 27 23:52 mysql-test/backup/2010-04-27_23-47-40/ibdata1.ibz

3. With --compress=0
====================

perl innobackup --compress=0 hema-inno.cnf mysql-test/backup/ --socket=/export/home2/tmp/mysql-5.1-ihb/mysql-test/var/tmp/mysqld.1.sock --user=root --ibbackup=/export/home2/tmp/mysql-5.1-ihb/ibbackup

ls -lh mysql-test/backup/2010-04-27_23-49-58/

-rw-r----- 1 hs221732 wheel  62K Apr 27 23:50 ibdata1.ibz

From the above we can notice that both --compress=0 and 1 gives the same level of compression. 

Suggested fix:
Ideally there should be no compression for --compress=0 option.

[28 Apr 2010 19:26] Sveta Smirnova

Thank you for the report.

Verified as described using version 3.5

[24 May 2010 5:20] Ritheesh Vedire

I think it is not a bug and it's as expected. 
--compress 0 option generates .ibz file which may have compression headers and other required information though the compression level is 0. 
If that is the case, obviously file sizes do not match.

[26 May 2010 17:06] Hema Sridharan

The manual states that --compress=0 produces no compression. When level 0 is used, then compression headers should not be used and should not produce any compression. If at all it produces compression, documentation needs to clarify this.

[12 Oct 2011 18:31] Sveta Smirnova

Bug is fixed in version 3.6.0  The --compress-level=0 and 1 does
not give same level of compression. Infact when --compress-level=0 is used,
no compression takes place which is expected.