MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
. Users can use this analytic storage engine to create a table that is “archive” only. Data cannot be deleted from this table, only added. The Archive engine uses a compression strategy based on the zlib
Zlib
zlib is a software library used for data compression. zlib was written by Jean-Loup Gailly and Mark Adler and is an abstraction of the DEFLATE compression algorithm used in their gzip file compression program. Zlib is also a crucial component of many software platforms including Linux, Mac OS X,...
library and it packs the rows using a bit header to represent nulls and removes all whitespace for character type fields. When completed, the row is inserted into the compression buffer and flushed to disk by an explicit flush table, a read, or the closing of the table.
One of the current restrictions of Archive tables is that they do not support any indexes, so you will be looking at a table scan for any SELECT tasks. MySQL is examining index support for Archive tables in upcoming releases, but for now, one thing you can depend on to help table scan times is the fact that Archive tables are supported by the MySQL Query Cache, which can dramatically reduce response times for Archive table queries that are repetitively issued.
In computer science, ACID is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction...
compliant. Unlike OLTP engines, it uses a "stream" format to disk with no block boundaries. The head of the Archive file generated is a byte array representing the data format and contents of that file. In MySQL 5.1, a copy of the MySQL FRM file is stored in the header of each Archive file. The FRM file, which represents the definition of a table, allows an Archive file to be restored to a MySQL server if the Archive file is copied to the server.
zlib is a software library used for data compression. zlib was written by Jean-Loup Gailly and Mark Adler and is an abstraction of the DEFLATE compression algorithm used in their gzip file compression program. Zlib is also a crucial component of many software platforms including Linux, Mac OS X,...
, archive files are not compatible with gzio, the basis of the gzip
Gzip
Gzip is any of several software applications used for file compression and decompression. The term usually refers to the GNU Project's implementation, "gzip" standing for GNU zip. It is based on the DEFLATE algorithm, which is a combination of Lempel-Ziv and Huffman coding...
tools. It uses its own azio system that is a fork of gzio.
Archive differs from the other MySQL analytical engine, MyISAM
MyISAM
MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5 . It is based on the older ISAM code but has many useful extensions. The major deficiency of MyISAM is the absence of transactions support...
, by being a row level locking engine and by keeping a constant version snapshot throughout a single query (making it MVCC
Multiversion concurrency control
Multiversion concurrency control , in the database field of computer science, is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory.For instance, a database will...
). This means that Archive does not lock for concurrent bulk inserts. For bulk inserts it performs an interlaced INSERT, so unlike MyISAM, order is not guaranteed.
Users can use the archive_reader tool to take an online snapshot of a table and to change the characteristics of an archive file.
To create an Archive table, specify the following engine string:
create table t1 (
a int,
b varchar(32))
ENGINE=ARCHIVE
The MySQL Archive Storage Engine was authored and is maintained by Brian Aker
Brian Aker
Brian Aker, born August 4, 1972 in Lexington, Kentucky, USA is an open-source hacker who has worked on various Apache modules, the Slash system, and numerous storage engines for the MySQL database. Aker was Director of Architecture at MySQL AB until it was acquired by Sun Microsystems. He led...
