The ARCHIVE Storage Engine is a storage engine that uses gzip to compress rows. It is mainly used for storing large amounts of data, without indexes, with only a very small footprint.

An ARCHIVE table is stored in two files on disk. There's a table definition file with an extension of .frm, and a data file with the extension .ARZ. At times during optimization, a .ARN file will appear.

New rows are inserted into a compression buffer and are flushed to disk when needed. SELECTs cause a flush. Sometimes, rows created by multi-row inserts are not visible until the statement is complete.

ARCHIVE allows a maximum of one key. The key must be on an AUTO_INCREMENT column, and can be a PRIMARY KEY or a non-unique key. However, it has a limitation: it is not possible to insert a value which is lower than the next AUTO_INCREMENT value.

Installation

The ARCHIVE Storage Engine was enabled by default until MariaDB 10.0. From MariaDB 10.1, the plugin needs to be specifically enabled, for example:

INSTALL SONAME 'ha_archive';

Characteristics

  • Supports INSERT and SELECT, but not DELETE, UPDATE or REPLACE.
  • Data is compressed with zlib as it is inserted, making it very small.
  • Data is slow the select, as it needs to be uncompressed, and, besides the query cache, there is no cache.
  • Supports AUTO_INCREMENT (since MariaDB/MySQL 5.1.6), which can be a unique or a non-unique index.
  • Since MariaDB/MySQL 5.1.6, selects scan past BLOB columns unless they are specifically requested, making these queries much more efficient.
  • Does not support spatial data types.
  • Does not support transactions.
  • Does not support foreign keys.
  • Does not support virtual columns.
  • No storage limit.
  • Supports row locking.
  • Supports table discovery, and the server can access ARCHIVE tables even if the corresponding .frm file is missing.
  • OPTIMIZE TABLE and REPAIR TABLE can be used to compress the table in its entirety, resulting in slightly better compression.
  • With MariaDB, it is possible to upgrade from the MySQL 5.0 format without having to dump the tables.
  • INSERT DELAYED is supported.
  • Running many SELECTs during the insertions can deteriorate the compression, unless only multi-rows INSERTs and INSERT DELAYED are used.
  • No items found.
Loading