Comments on: The ARCHIVE Storage Engine – does it do what you expect?

By: Vael Victus

Vael Victus — Sat, 26 Mar 2016 20:16:39 +0000

I was hoping to see at least a small mention of the performance benefits of going with ARCHIVE.

By: Diego

Diego — Tue, 16 Jun 2015 16:49:57 +0000

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

By: Truls Bergskaug

Truls Bergskaug — Wed, 08 Jan 2014 09:22:31 +0000

It is worth mention that replication of an ARCHIVE table with AUTOINCREMENT will most probably cause problems and headaches.

By: eRadical

eRadical — Tue, 07 Jan 2014 14:57:28 +0000

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 new stuff.