{"id":486,"date":"2019-10-21T10:00:37","date_gmt":"2019-10-21T03:00:37","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=486"},"modified":"2020-04-11T23:24:00","modified_gmt":"2020-04-11T16:24:00","slug":"mariadb-storage-engines","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-storage-engines\/","title":{"rendered":"MariaDB Storage Engines"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MariaDB storage engines including InnoDB, TokuDB, MyISAM, Aria, and MyRocks.<\/p>\n\n\n\n

Storage engines handle data at the physical level. Storage engines are designed to efficiently manage data files, the data, and the index caches.<\/p>\n\n\n\n

To show all available storage engines in a MariaDB server, you use the show engines statement:<\/p>\n\n\n

show<\/span> engines<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Or you can query the supported storage engines from the information_schema.engines<\/code> table:<\/p>\n\n\n
select<\/span> *\nfrom<\/span> information_schema.engines;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
The output includes a support<\/code> column that indicates whether the engine is available.<\/p>\n\n\n\n
When you create a new table<\/a>, you need to specify a storage engine. If you don’t, MariaDB will use a default storage engine for the table.<\/p>\n\n\n
create<\/span> table<\/span> table_name(\n    ...\n)engine<\/span>=storage_engine;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To show the current storage engine, you use the storage_engine<\/code> system variable:<\/p>\n\n\n
select<\/span> @@global.storage_engine;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
MariaDB has made the InnoDB as the default storage engine since version 5.5. In the earlier version, it used Aria as the default storage engine.<\/p>\n\n\n\n
If you want to find the storage engine of a table, you can query the engine<\/code> column from the table information_schema.tables<\/code>:<\/p>\n\n\n
select<\/span> \n    table_name,\n    engine<\/span> \nfrom<\/span> \n    information_schema.tables\nwhere<\/span> \n    table_schema='nation'<\/span>\n    table_name = 'countries'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
InnoDB storage engine<\/h2>\n\n\n\n
InnoDB is a high-performance, general-purpose storage engine that supports transactions with savepoints, XA transactions, and foreign keys. Savepoints are points in the middle of a transaction to which can be restored to if necessary. XA is a special type of transaction designed for transactions that may involve multiple resources, not only SQL databases.<\/p>\n\n\n\n
In most cases, the performance of InnoDB is better than other engines, therefore, it is a default storage engine.<\/p>\n\n\n\n
TokuDB storage engine<\/h2>\n\n\n\n
Similar to InnoDB, the TokuDB supports transactions with savepoints and XA transactions. However, it does not support foreign keys and full-text indexes.<\/p>\n\n\n\n
Unlike the InnoDB, the TokuDB supports a special structure for indexes called the factual trees. In addition, another important feature of TokuDB is data compression.<\/p>\n\n\n\n
The fractal trees and data compression make TokuDB suitable for datasets that are too big to store entirely in memory. In this situation<\/p>\n\n\n\n
MyISAM and Aria storage engines<\/h2>\n\n\n\n
MyISAM was the default storage engine for MySQL and MariaDB before version 5.5. MyISAM is a simple storage engine that is optimized for read-heavy, not write-heavy operations. MyISAM is good for websites and reporting systems which require heavy-read workloads.<\/p>\n\n\n\n
Aria is MyISAM’s successor. Aria uses logs that enable data recovery after crashes. Aria is better than MyISAM is the environments that have concurrency.<\/p>\n\n\n\n
Note that both MyISAM and Aria do not support transactions and foreign keys. Each statement in Aria is considered as a transaction.<\/p>\n\n\n\n
MyRocks storage engine<\/h2>\n\n\n\n
MyRocks storage engine is based on RocksDB which was originally developed by Facebook. MyRocks storage engine is good for workloads that require high compression and I\/O efficiency.<\/p>\n\n\n\n
MyRocks storage engine uses a Log Structured Merge (LSM) architecture that has better compression than the B-tree used by InnoDB. It is a write-optimized and has faster data loading and replication.<\/p>\n\n\n\n
MyRocks storge engine supports read committed and repeatable read isolated levels. However, it doesn’t support serializable.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t