The forgotten MySQL Storage Engines

In the recent years a lot of emphasis has been put on the InnoDB storage engine. This is a great thing. This has turned InnoDB from a very limited transactional storage engine to a very powerful piece of the MySQL architecture. InnoDB is now scalable to many cores. It is also very resilient. It supports advanced features(compression, buffer pool saving … long list …). No doubt this was the direction to go. There is still a lot of improvements to come and this is definitely needed for enterprise class applications.

But there are some other part of the server that need some attention and improvement. In the early days of the MySQL plugin architecture some nice concepts have been implemented as storage engines : csv storage engine, federated storage engine, merge storage engine, archive …
In my personal opinion these implementations are very useful but have not been much improved since their first implementation. They can be considered as excellent proof of concept and should have been pushed much further.

If we look at them :

The Merge storage engine implemented the concept of a table composed of multiple underlying table. The first constrain is that all the table have to be in the myISAM format only. With all the improvements innoDB has gone through regarding performance, including read only and complex query execution this is frustrating. The second big limitation is that all the table need to be strictly identical regarding their structure. Third constraint is that there is no parallel execution of the query. This is not a big surprise as there is currently no parallelism at all in MySQL except in marginal areas (slaves apply, innodb full text indexing). One other big limitation of the merge storage engine is that it cannot handle remotely distributed tables.

The Federated storage engine implemented the concept of a MySQL table located on a remote server. The local and remote table must have an identical table structure. The federated storage engine was reengineered into the federatex storage engine which is included in MariaDB. This new design was done to allow in the future to implement a heterogeneous database access like through an ODBC implementation. This never happened. The federated(x) storage engine keeps a lot of limitation. When you issue a select through a federated table a select * is done to access the remote data. There is no condition push down implementation. This works only with MySQL remote database. The limit clause of the sql statement is not pushed to the underlying system. The condition push down mechanism is not used with this engine. These limitations make the federated storage engine of limited use. The Oracle MySQL version of federated is old and does not implement federatedx improvements.

The CSV storage engine implemented the concept of table representing a csv file external from the database. This was a great idea as the csv format is a very common file format. Unfortunately the csv storage engine is very basic and does not implement the limit clause in select. There is no index associated to the file. Condition pushdown is neither implemented.

Leave a Reply




5 − = four

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">