Hilft die InnoDB Datenkompression bei wenig Diskplatz?

Sat, 2012-03-24 10:06 — oli

Weil wir auf einem unserer Server etwas knapp an Diskplatz sind, hatte ich die Idee, das MySQL Feature Datenkompression für InnoDB auszuprobieren. Dieses Feature ist nützlich, wenn Tabellen mit VARCHAR, BLOB oderr TEXT Attributen vorhanden sind.

Um es nicht allzu einfach zu machen ist unsere Tabelle auch noch partitioniert. Sie sieht wie folgt aus:

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);

Und die Partitionen verbrauchen wie folgt Diskplatz (nicht wirklich viel, ich weiss!):

-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

Die Tabelle wurde mit den folgenden Werten komprimiert:

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

Anschliessend schaut die Lage wie folgt aus:

-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

Wir erhalten somit eine Platzersparnis von 40 - 60%! Nicht allzu schlecht.

Aber spannend wäre auch noch den Einfluss auf den Speicher zu sehen:

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                 |
+---------------------------------------+----------------------+

Diese Zahlen machen nicht wirklich Sinn. Zudem sind wir noch auf einen bekannten MySQL Bug #59550: Innodb_buffer_pool_pages_misc goes wrong gestossen.

Ein paar nette Grafen

InnoDB Buffer Pool activity

Weil unser InnoDB Buffer Pool zu gross war, haben wir ihn etwas kleiner gemacht. Um das Barracuda File Format einzuschalten, haben wir anschliessend die Datenbank neu gestartet. Und dann sind die Zahlen amok gelaufen...