Storage engine, literally “storage motor,” or affectionately just “motor” as we commonly say in Brazil, can be roughly understood as the table type. It is a unique and exclusive feature of MySQL/MariaDB. Why not say it’s one of its charms?
For those new to the world of databases, it might be relatively easier to understand and accept how storage engines work. More experienced DBAs, especially those coming from Oracle & DB2 backgrounds, might find it harder to accept their behavior than to understand it.
In Oracle, DB2, PostgreSQL, Sybase, MS-SQL—in all relational databases (RDBMS)—when you create a table, it is always transactional and always comes with a fixed set of features and qualities. So, we can say they all use the same “engine,” meaning they always have the same performance.
When using high-end servers with great processing power and memory, it’s no problem that all tables are transactional, thus heavier. Remember that transactional tables must handle locks, write a lot more to REDO and UNDO logs, among other traits typical of transactional tables.
In other words, if you’re only going to run on a smooth road (a powerful server) with excellent infrastructure and have plenty of money, why not drive a Ferrari? But if you’ll be on a bumpy road, rough terrain, and have little budget, it would be better to drive a Niva 4×4… or even a bicycle, why not? Time… Time… Do you know what a Niva is? It’s a shabby little Russian jeep. Ugly as hell, but good on dirt roads (databases are also culture).
That’s what a storage engine is. It’s choosing the right car (sorry, if Mr. Lula uses football metaphors, I can’t use cars?), or better, the most appropriate table type for the table’s purpose. It means adding features or removing unnecessary ones, shedding weight from the table, making it lighter and even faster.
If you still don’t get the core of it, here’s another gem: Imagine that every table you create in other databases is always a Ferrari, with a powerful engine, fast, full of luxury items—some even unnecessary—oops, the red color of the Ferrari reminds us of a certain database. Now imagine you want to cross a completely congested avenue. Question: Who will get there faster, the Ferrari or a bicycle?
That’s the big insight of the storage engine. By choosing a certain engine/storage engine, you add or remove features that can make tables faster, for example. With MySQL/MariaDB, you can either drive a Ferrari (transactional tables) or pedal a bicycle (non-transactional tables).
Main features, resources, or functionalities inherent to storage engines:
a) Transactional capability: The ability of the table to handle multiple accesses (by multiple users/applications) with minimal collision and locking, so one user does not interfere with another. It means executing commands in blocks (transactions) instead of one SQL command at a time. It follows the ACID model (Atomic, Consistent, Isolated, Durable).
b) Storage method: This is fantastic. In other databases, all tables read and write data in a single, standard way. In MySQL/MariaDB, depending on the chosen engine, the table can be stored 100% in memory—yes, no disk at all. Data can be stored in a tablespace, like in Oracle. You can use a very fast 1970s technology called ISAM for rapid data storage and retrieval. Another engine type can store data compressed like a ZIP file, saving disk space. There’s also CSV format for easy integration with network and telephony equipment. Additionally, data can be spread across multiple computers to create a high-availability, high-performance cluster.
c) Indexes: Depending on the engine, there are B-TREE, B+TREE, RTREE (spatial), or FULLTEXT indexes (which index words instead of entire columns or fields, allowing Google-like searches with words out of order).
d) Referential integrity: Foreign keys (FK). Some engines support them, some don’t. Like all features discussed, depending on the application or table purpose, it may not be necessary—and this feature can slow down the database. Sometimes lacking this feature can be an advantage in speed.
e) Locking type: The ability to lock a single row, multiple rows, or the entire table. Each engine offers one or more locking types for the table.
f) BOL – Backup Online: It’s great to have online backups with everyone working, without needing to stop the database. Beautiful? Sure, but costly. If your application does not run 24/7, you have backup windows. Wouldn’t it be better to give up this feature to speed up reads and writes? Welcome to the world of storage engines.
g) Auto Recovery: Some engines require you to take the database offline to run a basic REPAIR TABLE command if, for example, an index corruption happens. Others simply notify you via the error log that the server found and fixed an inconsistency.
In summary, a storage engine is much more than just a table type. It is a unique and exclusive MySQL/MariaDB feature that lets you choose a predefined set of features that best meets the requirements and needs of your table, considering the available hardware.
Don’t miss Part II of this article.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
