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.

Delayed Flushing & Its Role in MySQL SQL Performance Tuning

Fact: delayed flushing may actually lead to reduced load. It may sound hard to believe, but if you ask an expert about the reasons behind systems delaying flushing changes to durable storage, you might get one or both of these answers:

1. In order to complete the task at a more convenient time
2. To have less work overall

In this post, we will examine the second reason and whether it helps MySQL SQL performance tuning.

Higher Peak & Average IO Rates with Adaptive Flush

The total number of I/O operations performed definitely improves with adaptive flushing. What happens here is, the workload gets smoothed out by adaptive flushing, which results in more work getting done.

Why does this happen? As you might recall, InnoDB works on 16kb pages at a time. Suppose one of the users manipulates the data of one of the rows. If InnoDB flushes constantly, it will flush the whole 16kb page right then. Just after this flush, a different row undergoes changes on the same page, which causes one more page flush.

If the first flush were to be delayed, the two flushes would be performed as a single flush. Delaying a flush to perform along with other flushes as one flush is known as write combining in MySQL database and SQL.

In certain workloads, the same rows may end up getting updated numerous times, which is where delaying and allowing write combining may dramatically reduce the quantity of I/O operations.

Does Recovery Time Affect this Result?

Some of us might think that recovery time is a significant factor for MySQL SQL performance tuning, and that enabling this option is not really necessary if that isn’t the case. However, it actually depends on the workload, and if each flush takes up a resource that is also required by another task.

If the data is able to fit in the buffer pool, and no read operations are taking place at that moment - which means the disk isn’t required for any other operations - the disk would only be in use for write operations. Also, if everything is ready at the OS and RAID controller layers, a read operation won’t be needed for a write either, helping Oracle database performance tuning.

An important point to remember is that other than a mutex contention inside MySQL or InnoDB, write operations typically run in the background - so they don’t block foreground tasks.