Does InnoDB data compression help with short disk space?

Because we are a bit short off disk space on one of our servers I had the idea to try out the MySQL feature Data Compression for InnoDB. This feature is useful if you have tables with VARCHAR, BLOB or TEXT attributes.

To not make it not too simple our table is partitioned as well. Our table looks like this:

CREATE TABLE `history_str` (
  `itemid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) unsigned NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (clock)
(PARTITION p2012_kw05 VALUES LESS THAN (1328482800) ENGINE = InnoDB,
 PARTITION p2012_kw06 VALUES LESS THAN (1329087600) ENGINE = InnoDB,
 PARTITION p2012_kw07 VALUES LESS THAN (1329692400) ENGINE = InnoDB,
 PARTITION p2012_kw08 VALUES LESS THAN (1330297200) ENGINE = InnoDB,
 PARTITION p2012_kw09 VALUES LESS THAN (1330902000) ENGINE = InnoDB,
 PARTITION p2012_kw10 VALUES LESS THAN (1331506800) ENGINE = InnoDB,
 PARTITION p2012_kw11 VALUES LESS THAN (1332111600) ENGINE = InnoDB,
 PARTITION p2012_kw12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

And the partitions use the following space on disk (not really much, I know!):

-rw-rw---- 1 mysql mysql 184549376 Mar  7 00:43 history_str#P#p2012_kw05.ibd
-rw-rw---- 1 mysql mysql 209715200 Mar 14 00:11 history_str#P#p2012_kw06.ibd
-rw-rw---- 1 mysql mysql 234881024 Mar 21 00:47 history_str#P#p2012_kw07.ibd
-rw-rw---- 1 mysql mysql 226492416 Mar 23 16:39 history_str#P#p2012_kw08.ibd
-rw-rw---- 1 mysql mysql 234881024 Mar 19 18:22 history_str#P#p2012_kw09.ibd
-rw-rw---- 1 mysql mysql 289406976 Mar 19 18:22 history_str#P#p2012_kw10.ibd
-rw-rw---- 1 mysql mysql 281018368 Mar 23 16:39 history_str#P#p2012_kw11.ibd
-rw-rw---- 1 mysql mysql 213909504 Mar 23 17:23 history_str#P#p2012_kw12.ibd

After the table was compressed with the following values:

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

The space on disk was used as follows:

-rw-rw---- 1 mysql mysql   7340032 Mar 23 17:33 history_str#P#p2012_kw05.ibd
-rw-rw---- 1 mysql mysql   7340032 Mar 23 17:34 history_str#P#p2012_kw06.ibd
-rw-rw---- 1 mysql mysql   8388608 Mar 23 17:36 history_str#P#p2012_kw07.ibd
-rw-rw---- 1 mysql mysql  75497472 Mar 23 17:49 history_str#P#p2012_kw08.ibd
-rw-rw---- 1 mysql mysql 104857600 Mar 23 17:44 history_str#P#p2012_kw09.ibd
-rw-rw---- 1 mysql mysql 125829120 Mar 23 17:51 history_str#P#p2012_kw10.ibd
-rw-rw---- 1 mysql mysql 125829120 Mar 23 17:57 history_str#P#p2012_kw11.ibd
-rw-rw---- 1 mysql mysql 134217728 Mar 23 18:11 history_str#P#p2012_kw12.ibd

So we got a reduction of used disk space by 40 - 60%! Not too bad.

But we also want to see what impact it has on memory:

SHOW GLOBAL STATUS LIKE 'innodb_buffer%';
+---------------------------------------+----------------------+
| Variable_name                         | Value                |
+---------------------------------------+----------------------+
| Innodb_buffer_pool_pages_data         | 10769                |
| Innodb_buffer_pool_pages_dirty        | 6613                 |
| Innodb_buffer_pool_pages_free         | 644                  |
| Innodb_buffer_pool_pages_misc         | 18446744073709549802 |
| Innodb_buffer_pool_pages_total        | 9599                 |
+---------------------------------------+----------------------+

Those numbers do not really make sense. We also hit a known MySQL Bug #59550: Innodb_buffer_pool_pages_misc goes wrong.

Some fancy graphs

InnoDB Buffer Pool activity

Because our InnoDB Buffer pool was too big we have reduced it a bit. For enabling the Barracuda file format we restarted the database afterwards. An then the numbers went amok...

InnoDB compression time

The first time we can see InnoDB compression time in our Monitor... \o/

InnoDB Row operations

And here you can find out how we solved it technically... :-)