Sometimes there is a need for keeping large amounts of old, rarely used data without investing too much on expensive storage. Very often such data doesn’t need to be updated anymore, or the intent is to leave it untouched. I sometimes wonder what I should really suggest to our Support customers.
For this purpose, the archive storage engine, added in MySQL 4.1.3, seems perfect as it provides excellent compression and the only DML statement it does allow is INSERT. However, does it really work as you would expect?
First of all, it has some serious limitations. Apart from lack of support for DELETE, REPLACE and UPDATE statements (which may be acceptable for some needs), another one is that it does not allow you to have indexes, although you can have an auto_increment column being either a unique or non-unique index. So usually straightforward converting your tables to archive engine will not be possible. See the list of features for reference.
But unfortunately, it does not always work as the manual says, within it’s described limitations. See the following very simple examples.
Problem I
Does the archive storage engine really ensure uniqueness for a primary or unique key?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | mysql> CREATE TABLE `b` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=ARCHIVE; Query OK, 0 rows affected (0.01 sec) mysql> insert into b values (null),(null),(null),(null); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from b; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.01 sec) mysql> repair table b; +--------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+--------+----------+----------+ | test.b | repair | status | OK | +--------+--------+----------+----------+ 1 row in set (0.00 sec) mysql> insert into b values (null),(null); Query OK, 2 row affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from b; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 1 | | 2 | +----+ 6 rows in set (0.01 sec) mysql> show indexes from bG *************************** 1. row *************************** Table: b Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: NONE Comment: Index_comment: 1 row in set (0.00 sec) |
That is really bad – a column being a primary key effectively allows duplicates! And another case exposing the same problem:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | mysql> CREATE TABLE `c` ( `id` int(11) NOT NULL AUTO_INCREMENT, UNIQUE KEY (`id`) ) ENGINE=ARCHIVE; Query OK, 0 rows affected (0.01 sec) mysql> insert into c values (null),(null),(null); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from c; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.01 sec) mysql> optimize table c; +--------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+----------+----------+----------+ | test.c | optimize | status | OK | +--------+----------+----------+----------+ 1 row in set (0.01 sec) mysql> insert into c values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from c; +----+ | id | +----+ | 1 | | 2 | | 3 | | 1 | +----+ 4 rows in set (0.01 sec) |
So even a simple optimize table command does break it completely. After we realize that such operation made our data bad, we won’t be able to easily go back to different engine without sacrificing uniqueness first:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> alter table c engine=innodb; ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'id' mysql> alter table c drop key id; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> alter table c drop key id, add key(id); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> alter table c engine=innodb; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 |
There were already bug reports related to auto_increment feature being broken, but I have filed a new, more specific bug report about this problem.
————–
Problem II
Are we always able to alter a table to use the archive storage engine, even if it is theoretically using supported table definition? Auto increment column issue again…
1 2 3 4 5 6 7 8 9 | mysql> select * from c; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.01 sec) |
We have the same c table using archive. We can change it’s engine to something different:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> alter table c engine=innodb; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from c; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) |
But in some cases, we can’t set it back to archive!
1 2 | mysql> alter table c engine=archive; ERROR 1022 (23000): Can't write; duplicate key in table '#sql-1649_3' |
There is an old bug report about that.
————–
Problem III
And yet another weirdness around auto_increment values. It seems normal that databases allow us to insert explicit values into auto_increment columns, even lower then last inserted maximum, and all other engines – MyISAM, Memory and InnoDB do that:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> CREATE TABLE ai (a int auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> insert into ai values (10); Query OK, 1 row affected (0.00 sec) mysql> insert into ai values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from ai; +----+ | a | +----+ | 1 | | 10 | +----+ 2 rows in set (0.00 sec) |
But it’s not the case for Archive engine:
1 2 3 4 5 6 7 8 | mysql> CREATE TABLE aa (a int auto_increment primary key) ENGINE=Archive; Query OK, 0 rows affected (0.00 sec) mysql> insert into aa values (10); Query OK, 1 row affected (0.00 sec) mysql> insert into aa values (1); ERROR 1022 (23000): Can't write; duplicate key in table 'aa' |
This undocumented behavior was reported here.
Summary
The archive storage engine provides a very good compression and is available in all MySQL variants out of the box. However it does have serious limitations as well as works unreliable and not as expected in some cases.
Related articles worth mentioning here:
In my next blog post, I am going to present simple research on (free) alternatives we do have that can replace the archive storage engine in terms of disk space effectiveness.
There are still use cases for ARCHIVE and I would hate it if it were to go away.
I keep some analytics data and I change the storage engine of the “past” months from InnoDB into Archive (of course after altering the table). This saves me a lot of [expensive] disk space with the trade-off for speed when it comes to computing
newstuff.It is worth mention that replication of an ARCHIVE table with AUTOINCREMENT will most probably cause problems and headaches.
We are using ARCHIVE engine with range columns partitions using month and year columns, and the id column is populated by taken the autoincrement value from the table we want to archive.
CREATE TABLE range_colums_partition (
id INT(11) UNSIGNED DEFAULT NULL,
month TINYINT(2) DEFAULT NULL,
year MEDIUMINT(4) DEFAULT NULL,
date datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (year, month)
(
PARTITION 2014_07 VALUES LESS THAN (2014, 08),
PARTITION 2014_08 VALUES LESS THAN (2014, 09),
PARTITION 2014_09 VALUES LESS THAN (2014, 10),
PARTITION 2014_10 VALUES LESS THAN (2014, 11),
PARTITION 2014_11 VALUES LESS THAN (2014, 12),
PARTITION 2014_12 VALUES LESS THAN (2015, 01),
PARTITION 2015_01 VALUES LESS THAN (2015, 02),
PARTITION 2015_02 VALUES LESS THAN (2015, 03),
PARTITION 2015_03 VALUES LESS THAN (2015, 04),
PARTITION 2015_04 VALUES LESS THAN (2015, 05),
PARTITION 2015_05 VALUES LESS THAN (2015, 06),
PARTITION max VALUES LESS THAN (MAXVALUE, MAXVALUE) ENGINE=ARCHIVE
);
I was hoping to see at least a small mention of the performance benefits of going with ARCHIVE.