Optimization of SQL Queries: Cons of Multiple Storage Engines

Among the big advantages advertised for MySQL includes support for several storage engines. This seems like an excellent advantage at first glance because users gain the advantage of using the same high-level SQL interface, enabling them to save their information in numerous ways.   

Though it may seem quite useful, this advantage comes at a high expense in terms of performance, growth, and operational intricacy. In this post, we will discuss just a few challenges of this particular feature.  

Cons of Multiple Storage Engines in Oracle\MySQL Databases 

Consider the following setbacks due to the support provided for multiple storage engines in Oracle queries - 

• Conversions – Every storage engine contains its unique format for storing data, so MySQL performance tuning requires plenty of copying and conversions when it needs to access the information. This has a substantial impact on performance in comparison with a “zero-copy” design, where users may stream information from memory when it can be accommodated there. Optimizer and Execution Storage engines are not created equal especially if you look at In-Memory storage engines vs Distributed ones. 

• Managing many different instances in query optimizer - Certain tasks and cases like clustering by The primary key in Innodb, the memory-based characteristics of the MEMORY storage engine and the distributed nature of NDB tend to get more complicated than necessary. This is due to the need to cater to several use cases that keep MySQL from exploiting the proper performance potential of either of the storage engines. 

• Integration and Synchronization - The upper-most level at the MySQL side that includes .frm and binary log files require synchronization with storage engine transactions. This, in turn, takes up significant performance overhead and gets rather complicated. Also, despite MySQL doing numerous fsync() calls for each transaction commit just to remain on the safe side, many tasks still aren’t completely safe. For example, .frm files can be retrieved from the internal data dictionary in case of a crash at an inopportune moment. 

• Transaction Assistance - There are several combinations of locks that users need to manage on higher levels and inside storage engines. They also have to deal with varying locking strategies owned by storage engines, which can lead to plenty of surprises if they choose to venture into the cross storage engine transactions.

• Backup - Backing up data across storage engines can be frustrating due to their differences. While some of these storage engines are distributed, others use the memory, which means even versatile approaches such as LVM backup could fail in some situations.  

Query Optimization & Multiple Storage Engines - Final Thoughts

It is interesting to note that a single storage engine is more than sufficient for over ninety-five percent of applications that use SQL tuning for Oracle. Moreover, most users already prefer to use only one storage engine instead of mixing multiple of them because of the hindrances mentioned above. 

At the same time, this doesn’t mean users should give up flexibility altogether. For instance, administrators can enable InnoDB tables that don’t log changes, which will increase the rate of update operations. They may also lock those tables in memory to make sure the in-memory performance doesn’t get unpredictable.