Storage engines 1/2

1/2

ARIA 1 .5 version

ARIA is a storage engine for MySQL and MariaDB. It supports full text search, OpenGIS datatypes, and generated columns. It sets a recovery mechanism in case of crash during writing, based on logs.

ARIA does not supports the constraints on foreign keys and is not a transactionnal engine yet, but it’s planned to become one in the next 2.0 version.

This upcoming version will also supports the row based lock instead of the current table based lock

ARCHIVE

As indicated by it’s name, the ARCHIVE engine is first intended to data archivage.

The ARCHIVE table’s footprint is reducted because the data experiences a zlib type compression, it’s still allowed to insert new data at table’s end.

On the other hand, it’s not possible to delete or modify the existing data and ARCHIVE does not manage the indexes, only a primary key.

BLACKHOLE

The BLACKHOLE engine is to MySQL what /dev/null is to POSIX system : the SQL insertions queries are actually executed but they don’t have any consequences on BLACKHOLE based tables. This engine has it’s interest in STATEMENT type replications, because if tables are empty, the queries are actually saved on binary logs and taken by slave servers.

So, a BLACKHOLE based server can function as a PROXY SQL, especially since there’s no much latency between master and slaves since data are not really written.

1. BLACKHOLE cannot be used as a PROXY in a ROW or MIXED typed replication because it can’t historicize no modification or insertions in the tables which, by definition, stay perpetually empty.

CSV

The CSV engine stores data in text format with comma as separator.

The creation of a CSV table has the effect of creating three files : .frm .CSV and CSM who contains respectivly the table definition, it’s data, it’s state and it’s record number.

The .CSV file can be directly opened and modified by a spreadsheet.

Copy a conventionnal table to a CSV table is a fast and simple method to export data :

mysql> CREATE TABLE EXPORT_STORE ENGINE=’CSV’ SELECT * FROM STORE;

In the {datadir}/eni folder, the EXPORT_STORE.CSV file contains the data of STORE table : it is ready to be opened by a spreadsheet.

FEDERATED

A FEDERATED engine allows to manipulate the distant server’s table as if it were a local table. The connexion can be set-up by two ways.

Connexion string

The connexion to the distant server’s table can be set up this way:

mysql> CREATE TABLE my_federated_table ( … definition of the same table to the reference table ) ENGINE=FEDERATED CONNECTION=’mysql ://mylogin :mypasswd@localhost :port/ mtdb/mytable’

Distant server creation

To not waste time if we have to create several tables, it’s possible to declare the distant server this way :

mysql> CREATE SERVER my_distant_srv FOREIGN DATA WRAPPER mysql OPTIONS (USER ‘remote_user’, HOST ‘remote_host’, PORT 3307, DATABASE ‘federated’);

Since then, the my_distant_srv server can be used as a reference :

mysql> CREATE TABLE my_federated_table ( … definition of the same table to the reference table ) ENGINE=FEDERATED CONNECTION=’my_distant_srv/table1’;

The FEDERATED engine is convenient to send queries on various distant server’s tables, providing not to take care of performances.