Downloads

Blog

Overview of MySQL Alternative Storage Engines

March 26, 2021

Author

Sri Sakthivel

Insight for DBAs

Insight for Developers

MySQL

Share this Post:

MySQL Alternative Storage Engines For MySQL, MyISAM and InnoDB storage engines are very popular. Currently, we are mostly using InnoDB engines for high reliability and high performance. Apart from those engines, we also have some other alternative engines and they have some nice features in them. In this blog, I am going to explain some of those engines, which I have listed below.

FEDERATED Storage Engine

Merge or MRG_MyISAM Engine

Blackhole Engine

CSV Engine

FEDERATED Storage Engine

Overview:

FEDERATED Storage Engine allows you to access the data remotely without replication and cluster technologies.

Using the FEDERATED tables, you can scale your server load. Queries for the given table will be sent over the network to another MySQL instance. In this case, to scale the DB, you can use many MySQL instances without changing the application code.

FEDERATED tables are a security concern because you will need to save the host and user information in the table. It can be viewed using SHOW CREATE TABLE command.

Query optimization is limited and JOINs are slow.

Doing the bulk transaction may crash the local server.

By default, FEDERATED Storage Engine support is disabled. To enable it, you need to manually enable the variable “federated = ON” in the MySQL config file and restart the MySQL service.

mysql> select * from information_schema.engines where engine='federated'G<br>*************************** 1. row ***************************<br>      ENGINE: FEDERATED<br>     SUPPORT: NO<br>     COMMENT: Federated MySQL storage engine<br>TRANSACTIONS: NULL<br>          XA: NULL<br>  SAVEPOINTS: NULL<br>1 row in set (0.00 sec)<br><br>#vi /etc/my.cnf <br>[mysqld]<br>federated = ON<br><br>[root@mass ~]# service mysqld restart<br>Redirecting to /bin/systemctl restart mysqld.service<br>[root@mass ~]#<br><br>mysql> select * from information_schema.engines where engine='federated'G<br>*************************** 1. row ***************************<br>      ENGINE: FEDERATED<br>     SUPPORT: YES<br>     COMMENT: Federated MySQL storage engine<br>TRANSACTIONS: NO<br>          XA: NO<br>  SAVEPOINTS: NO<br>1 row in set (0.00 sec)

mysql> select * from information_schema.engines where engine='federated'G *************************** 1. row *************************** ENGINE: FEDERATED SUPPORT: NO COMMENT: Federated MySQL storage engine TRANSACTIONS: NULL XA: NULL SAVEPOINTS: NULL 1 row in set (0.00 sec) #vi /etc/my.cnf [mysqld] federated = ON [root@mass ~]# service mysqld restart Redirecting to /bin/systemctl restart mysqld.service [root@mass ~]# mysql> select * from information_schema.engines where engine='federated'G *************************** 1. row *************************** ENGINE: FEDERATED SUPPORT: YES COMMENT: Federated MySQL storage engine TRANSACTIONS: NO XA: NO SAVEPOINTS: NO 1 row in set (0.00 sec)

How Does it work?

FEDERATED tables need to be created on a local server and the remote table needs to be created on a remote server.

Make sure that you have the MySQL port and user access between the local and remote servers.

Remote tables can be created as MyISAM or InnoDB storage engines.

The FEDERATED table will not store any data. Data will be stored on the remote server.

Both local and remote servers should have the same columns and structure.

You can execute the query on both local or remote servers to modify or retrieve the data.

Example

I have two servers:

172.28.128.16 (local server)

172.28.128.17 (remote server)

On the local server, I am creating the FEDERATED table:

mysql> create table fed_source(id int, name varchar(16)) engine=federated connection="mysql://fed:Fede4!i&1@172.28.128.17/percona/fed_destination";<br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql> show create table fed_sourceG<br>*************************** 1. row ***************************<br>       Table: fed_source<br>Create Table: CREATE TABLE `fed_source` (<br>  `id` int(11) DEFAULT NULL,<br>  `name` varchar(16) DEFAULT NULL<br>) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://fed:Fede4!i&1@172.28.128.17/percona/fed_destination'<br>1 row in set (0.00 sec)

mysql> create table fed_source(id int, name varchar(16)) engine=federated connection="mysql://fed:Fede4!i&[email protected]/percona/fed_destination"; Query OK, 0 rows affected (0.02 sec) mysql> show create table fed_sourceG *************************** 1. row *************************** Table: fed_source Create Table: CREATE TABLE `fed_source` ( `id` int(11) DEFAULT NULL, `name` varchar(16) DEFAULT NULL ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://fed:Fede4!i&[email protected]/percona/fed_destination' 1 row in set (0.00 sec)

Syntax is:

 "connection=mysql://<user>:<password>@<remote_host_ip>/<remote_database>/<remote_table>"

1	"connection=mysql://<user>:<password>@<remote_host_ip>/<remote_database>/<remote_table>"

On the remote server, I am creating the table with InnoDB engine:

mysql> create table fed_destination(id int, name varchar(16));<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> show create table fed_destinationG<br>*************************** 1. row ***************************<br>       Table: fed_destination<br>Create Table: CREATE TABLE `fed_destination` (<br>  `id` int(11) DEFAULT NULL,<br>  `name` varchar(16) DEFAULT NULL<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1<br>1 row in set (0.00 sec)

mysql> create table fed_destination(id int, name varchar(16)); Query OK, 0 rows affected (0.00 sec) mysql> show create table fed_destinationG *************************** 1. row *************************** Table: fed_destination Create Table: CREATE TABLE `fed_destination` ( `id` int(11) DEFAULT NULL, `name` varchar(16) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

As I mentioned earlier, the data will be physically stored on the remote server. FEDERATED tables will not store the data. From the below example, you can see the data file (.ibd) was created on the remote server and the local server just has the table structure file ( .frm ).

Local server:

[root@mass percona]# pwd<br>/var/lib/mysql/percona<br>[root@mass percona]# ls -lrth<br>total 16K<br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:00 fed_source.frm

1	[root@mass percona]# pwd<br>/var/lib/mysql/percona<br>[root@mass percona]# ls -lrth<br>total 16K<br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:00 fed_source.frm

Remote server:

root@repl percona]# pwd<br>/var/lib/mysql/percona<br>[root@repl percona]# ls -lrth<br>total 112K<br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:00 fed_destination.frm<br>-rw-r-----. 1 mysql mysql  96K Mar 19 18:01 fed_destination.ibd

1	root@repl percona]# pwd<br>/var/lib/mysql/percona<br>[root@repl percona]# ls -lrth<br>total 112K<br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:00 fed_destination.frm<br>-rw-r-----. 1 mysql mysql 96K Mar 19 18:01 fed_destination.ibd

Let’s do this experiment. On the local server, I am inserting the record.

mysql> insert into fed_source values (1,'herc');<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> select * from fed_source;<br>+------+------+<br>| id   | name |<br>+------+------+<br>|    1 | herc |<br>+------+------+<br>1 row in set (0.00 sec)

1	mysql> insert into fed_source values (1,'herc');<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> select * from fed_source;<br>+------+------+<br>\| id \| name \|<br>+------+------+<br>\| 1 \| herc \|<br>+------+------+<br>1 row in set (0.00 sec)

And on the remote server:

mysql> select * from fed_destination;<br>+------+------+<br>| id   | name |<br>+------+------+<br>|    1 | herc |<br>+------+------+<br>1 row in set (0.00 sec)

1	mysql> select * from fed_destination;<br>+------+------+<br>\| id \| name \|<br>+------+------+<br>\| 1 \| herc \|<br>+------+------+<br>1 row in set (0.00 sec)

Now, I am going to update the data on a remote server.

mysql> update fed_destination set name='hercules7sakthi' where name='herc';<br>Query OK, 1 row affected (0.00 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql> select * from fed_destination;<br>+------+-----------------+<br>| id   | name            |<br>+------+-----------------+<br>|    1 | hercules7sakthi |<br>+------+-----------------+<br>1 row in set (0.00 sec)

mysql> update fed_destination set name='hercules7sakthi' where name='herc'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from fed_destination; +------+-----------------+ | id | name | +------+-----------------+ | 1 | hercules7sakthi | +------+-----------------+ 1 row in set (0.00 sec)

At the local server:

mysql> select * from fed_source;<br>+------+-----------------+<br>| id   | name            |<br>+------+-----------------+<br>|    1 | hercules7sakthi |<br>+------+-----------------+<br>1 row in set (0.00 sec)

1	mysql> select * from fed_source;<br>+------+-----------------+<br>\| id \| name \|<br>+------+-----------------+<br>\| 1 \| hercules7sakthi \|<br>+------+-----------------+<br>1 row in set (0.00 sec)

It seems that you can execute the query on both local and remote servers. The FEDERATED Engine is mostly supported for data manipulation languages (INSERT/UPDATE/DELETE/TRUNCATE).

Merge or MRG_MyISAM Engine

Overview:

The collection of identical MyISAM tables can be used as a single table for better performance.

Only supported for MyISAM tables.

Merge tables will use more file descriptors.

You can’t perform the FULL TEXT SEARCH using the merge tables.

Merge tables used extremely rare since partitions came around.

How Does it work?

It works only for the MyISAM tables.

The columns order, index, data types should be the same on all the tables.

Example

I have created two tables:

mysql> create table merge_1(id int, name varchar(16)) engine = myisam;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> create table merge_2(id int, name varchar(16)) engine = myisam;<br>Query OK, 0 rows affected (0.01 sec)

1	mysql> create table merge_1(id int, name varchar(16)) engine = myisam;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> create table merge_2(id int, name varchar(16)) engine = myisam;<br>Query OK, 0 rows affected (0.01 sec)

Inserting some data on both tables:

mysql> insert into merge_1 values (1,'herc'),(2,'sakthi'),(3,'sri');<br>Query OK, 3 rows affected (0.00 sec)<br>Records: 3  Duplicates: 0  Warnings: 0<br><br>mysql> insert into merge_2 values (4,'jc'),(5,'xxx'),(3,'yyy');<br>Query OK, 3 rows affected (0.00 sec)<br>Records: 3  Duplicates: 0  Warnings: 0

1	mysql> insert into merge_1 values (1,'herc'),(2,'sakthi'),(3,'sri');<br>Query OK, 3 rows affected (0.00 sec)<br>Records: 3 Duplicates: 0 Warnings: 0<br><br>mysql> insert into merge_2 values (4,'jc'),(5,'xxx'),(3,'yyy');<br>Query OK, 3 rows affected (0.00 sec)<br>Records: 3 Duplicates: 0 Warnings: 0

Now, creating the merge table:

mysql> create table merge_1_and_2 (id int, name varchar(16)) engine = mrg_myisam union=(merge_1,merge_2);<br>Query OK, 0 rows affected (0.01 sec)

1	mysql> create table merge_1_and_2 (id int, name varchar(16)) engine = mrg_myisam union=(merge_1,merge_2);<br>Query OK, 0 rows affected (0.01 sec)

Let’s query the merge table:

mysql> select * from merge_1_and_2;<br>+------+--------+<br>| id   | name   |<br>+------+--------+<br>|    1 | herc   |<br>|    2 | sakthi |<br>|    3 | sri    |<br>|    4 | jc     |<br>|    5 | xxx    |<br>|    3 | yyy    |<br>+------+--------+<br>6 rows in set (0.00 sec)

1	mysql> select * from merge_1_and_2;<br>+------+--------+<br>\| id \| name \|<br>+------+--------+<br>\| 1 \| herc \|<br>\| 2 \| sakthi \|<br>\| 3 \| sri \|<br>\| 4 \| jc \|<br>\| 5 \| xxx \|<br>\| 3 \| yyy \|<br>+------+--------+<br>6 rows in set (0.00 sec)

It seems, when I query the merge table, it merges both the tables (merge_1, merge_2) and displays the results.

Physically, the MERGE table will not occupy any disk space. When querying the table, it will just merge the data from the configured tables and display the result.

[root@mass percona]# ls -lrth<br>total 72K<br><br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:44 merge_1.frm<br>-rw-r-----. 1 mysql mysql 1.9K Mar 19 18:51 merge_1.MYD<br>-rw-r-----. 1 mysql mysql 1.0K Mar 19 18:51 merge_1.MYI<br><br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:44 merge_2.frm<br>-rw-r-----. 1 mysql mysql 1.6K Mar 19 18:51 merge_2.MYD<br>-rw-r-----. 1 mysql mysql 1.0K Mar 19 18:51 merge_2.MYI<br><br>-rw-r-----. 1 mysql mysql 8.4K Mar 19 18:48 merge_1_and_2.frm<br>-rw-r-----. 1 mysql mysql   16 Mar 19 18:48 merge_1_and_2.MRG

[root@mass percona]# ls -lrth total 72K -rw-r-----. 1 mysql mysql 8.4K Mar 19 18:44 merge_1.frm -rw-r-----. 1 mysql mysql 1.9K Mar 19 18:51 merge_1.MYD -rw-r-----. 1 mysql mysql 1.0K Mar 19 18:51 merge_1.MYI -rw-r-----. 1 mysql mysql 8.4K Mar 19 18:44 merge_2.frm -rw-r-----. 1 mysql mysql 1.6K Mar 19 18:51 merge_2.MYD -rw-r-----. 1 mysql mysql 1.0K Mar 19 18:51 merge_2.MYI -rw-r-----. 1 mysql mysql 8.4K Mar 19 18:48 merge_1_and_2.frm -rw-r-----. 1 mysql mysql 16 Mar 19 18:48 merge_1_and_2.MRG

Blackhole Engine

Overview:

Blackhole Engine will accept the data from SQL. The accepted data will not be stored, whenever you are querying the data it will give the empty result.

Can be used for SQL syntax checking purposes.

Can be used for the replication filter purpose.

You have to be very careful when you use the table in a replication environment. Because the SQL will be logged in the binary log.

How Does it work?

Example

Creating the blackhole table:

mysql> create table black_hole (id int, name varchar(16)) engine = blackhole;<br>Query OK, 0 rows affected (0.00 sec)

1	mysql> create table black_hole (id int, name varchar(16)) engine = blackhole;<br>Query OK, 0 rows affected (0.00 sec)

Inserting and retrieving the data:

mysql> insert into black_hole values (1,'sri'),(2,'jc');<br>Query OK, 2 rows affected (0.00 sec)<br>Records: 2  Duplicates: 0  Warnings: 0<br><br>mysql> select * from black_hole;<br>Empty set (0.00 sec)

1	mysql> insert into black_hole values (1,'sri'),(2,'jc');<br>Query OK, 2 rows affected (0.00 sec)<br>Records: 2 Duplicates: 0 Warnings: 0<br><br>mysql> select * from black_hole;<br>Empty set (0.00 sec)

The data will be stored on the binary logs:

# at 23445<br>#210319 19:19:15 server id 10  end_log_pos 23497 CRC32 0x36e22a05       Write_rows: table id 115 flags: STMT_END_F<br>### INSERT INTO `percona`.`black_hole`<br>### SET<br>###   @1=1 /* INT meta=0 nullable=1 is_null=0 */<br>###   @2='sri' /* VARSTRING(16) meta=16 nullable=1 is_null=0 */<br>### INSERT INTO `percona`.`black_hole`<br>### SET<br>###   @1=2 /* INT meta=0 nullable=1 is_null=0 */<br>###   @2='jc' /* VARSTRING(16) meta=16 nullable=1 is_null=0 */<br># at 23497<br>#210319 19:19:15 server id 10  end_log_pos 23573 CRC32 0x4d79cba4       Query   thread_id=5     exec_time=0     error_code=0<br>SET TIMESTAMP=1616181555/*!*/;<br>COMMIT

# at 23445 #210319 19:19:15 server id 10 end_log_pos 23497 CRC32 0x36e22a05 Write_rows: table id 115 flags: STMT_END_F ### INSERT INTO `percona`.`black_hole` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='sri' /* VARSTRING(16) meta=16 nullable=1 is_null=0 */ ### INSERT INTO `percona`.`black_hole` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jc' /* VARSTRING(16) meta=16 nullable=1 is_null=0 */ # at 23497 #210319 19:19:15 server id 10 end_log_pos 23573 CRC32 0x4d79cba4 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1616181555/*!*/; COMMIT

Syntax Checking Purposes

If you want to check any syntax of the SQL statements, you can directly execute them against the blackhole tables as it is not going to do anything with the data.

Replication Filter Purpose

Let’s consider that I have a source-replica setup. At the source, I have created the below table.

mysql> create table test_blackhole(id int, name varchar(16)) engine=innodb;<br>Query OK, 0 rows affected (0.01 sec)

1	mysql> create table test_blackhole(id int, name varchar(16)) engine=innodb;<br>Query OK, 0 rows affected (0.01 sec)

I don’t want to replicate this table to replica nodes. In this case, I just converted the table to BLACKHOLE engine on the replica node.

At replica node:

mysql> alter table test_blackhole engine=blackhole;<br>Query OK, 0 rows affected (0.00 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql> show create table test_blackholeG<br>*************************** 1. row ***************************<br>       Table: test_blackhole<br>Create Table: CREATE TABLE `test_blackhole` (<br>  `id` int(11) DEFAULT NULL,<br>  `name` varchar(16) DEFAULT NULL<br>) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1<br>1 row in set (0.00 sec)

mysql> alter table test_blackhole engine=blackhole; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test_blackholeG *************************** 1. row *************************** Table: test_blackhole Create Table: CREATE TABLE `test_blackhole` ( `id` int(11) DEFAULT NULL, `name` varchar(16) DEFAULT NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

Now, at source, I am inserting some records:

mysql> insert into test_blackhole values (1,'aaa');<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> select * from test_blackhole;<br>+------+------+<br>| id   | name |<br>+------+------+<br>|    1 | aaa  |<br>+------+------+<br>1 row in set (0.00 sec)

1	mysql> insert into test_blackhole values (1,'aaa');<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> select * from test_blackhole;<br>+------+------+<br>\| id \| name \|<br>+------+------+<br>\| 1 \| aaa \|<br>+------+------+<br>1 row in set (0.00 sec)

At replica, the data is not available. The data has been ignored as the table was converted to the blackhole engine.

mysql> select * from test_blackhole;<br>Empty set (0.00 sec)

1	mysql> select * from test_blackhole;<br>Empty set (0.00 sec)

CSV Engine

Overview:

The CSV storage engine stores data in csv files.

If you need the data into a CSV file, you can just copy the table physical file and use it. No need to export the data using the command SELECT INTO OUTFILE.

It will not support nullable columns.

The data will store comma-separated values.

Example

Creating the CSV table:

mysql> create table csv_test (id int not null, name varchar(16) not null, cur_time datetime default current_timestamp not null) engine = csv;<br>Query OK, 0 rows affected (0.00 sec)

1	mysql> create table csv_test (id int not null, name varchar(16) not null, cur_time datetime default current_timestamp not null) engine = csv;<br>Query OK, 0 rows affected (0.00 sec)

Inserting data:

mysql> insert into csv_test (id,name) values (1,'jc'),(2,'sri'),(3,'herc');<br>Query OK, 3 rows affected (0.00 sec)<br>Records: 3  Duplicates: 0  Warnings: 0<br><br>mysql> select * from csv_test;<br>+----+------+---------------------+<br>| id | name | cur_time            |<br>+----+------+---------------------+<br>|  1 | jc   | 2021-03-19 19:40:40 |<br>|  2 | sri  | 2021-03-19 19:40:40 |<br>|  3 | herc | 2021-03-19 19:40:40 |<br>+----+------+---------------------+<br>3 rows in set (0.00 sec)

mysql> insert into csv_test (id,name) values (1,'jc'),(2,'sri'),(3,'herc'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from csv_test; +----+------+---------------------+ | id | name | cur_time | +----+------+---------------------+ | 1 | jc | 2021-03-19 19:40:40 | | 2 | sri | 2021-03-19 19:40:40 | | 3 | herc | 2021-03-19 19:40:40 | +----+------+---------------------+ 3 rows in set (0.00 sec)

Physically, you can see the data is stored as the .csv file. You can view the data from the file itself.

[root@mass percona]# ls -lrth | grep -i csv<br>-rw-r-----. 1 mysql mysql 8.5K Mar 19 19:38 csv_test.frm<br>-rw-r-----. 1 mysql mysql   90 Mar 19 19:40 csv_test.CSV<br>-rw-r-----. 1 mysql mysql   35 Mar 19 19:40 csv_test.CSM<br><br>[root@mass percona]# cat csv_test.CSV<br>1,"jc","2021-03-19 19:40:40"<br>2,"sri","2021-03-19 19:40:40"<br>3,"herc","2021-03-19 19:40:40"

[root@mass percona]# ls -lrth | grep -i csv -rw-r-----. 1 mysql mysql 8.5K Mar 19 19:38 csv_test.frm -rw-r-----. 1 mysql mysql 90 Mar 19 19:40 csv_test.CSV -rw-r-----. 1 mysql mysql 35 Mar 19 19:40 csv_test.CSM [root@mass percona]# cat csv_test.CSV 1,"jc","2021-03-19 19:40:40" 2,"sri","2021-03-19 19:40:40" 3,"herc","2021-03-19 19:40:40"

As you see, MySQL alternative engines are having some good features. Based on my point of view, I would not suggest having them on production until finding a valid reason. But, it is still good to know about those engines and understand their features.

Percona Distribution for MySQL: An enterprise-grade solution for your most critical business applications.