Storage engine refers to the type of table, which determines how tables are stored in computers. This document describes the storage engines supported by TencentDB for MySQL.
InnoDB engine
The InnoDB engine is the most commonly used OLTP storage engine, employing Multi-Version Concurrency Control (MVCC) and row-level locking technology to deliver high-performance and reliable transaction processing. Compared to other MySQL storage engines, InnoDB supports superior data integrity, including foreign keys and rollback, and provides advanced query capabilities. Meanwhile, Tencent Cloud has implemented extensive kernel optimizations for InnoDB, enhancing its performance advantages, and it is widely applied in high-performance, high-concurrency use cases.
TencentDB for MySQL supports the InnoDB engine by default. In MySQL 5.6 and above versions, it no longer supports the MyISAM engine and Memory engine. The main reasons are as follows:
In the current MySQL version, TencentDB has implemented numerous kernel optimizations for InnoDB, already delivering significant performance advantages.
MyISAM uses a table-level locking mechanism, while InnoDB employs a row-level locking mechanism. Typically, InnoDB offers higher write efficiency.
Note:
Table-level locking is the coarsest granularity lock in MySQL, which means applying a lock to the entire table being operated on.
Row-level locking is the finest granularity lock in MySQL, which means applying a lock only to the row being operated on.
MyISAM has defects in protecting data integrity, and these defects can lead to corruption or even loss of database data. Moreover, many of these defects are design issues that cannot be fixed without breaking compatibility.
Migrating from MyISAM and Memory to InnoDB incurs low costs. For most applications, the migration only requires modifying the table creation code.
The development of MyISAM is transitioning to InnoDB. In the latest official MySQL 8.0 version, system tables have all adopted InnoDB.
Memory cannot guarantee data integrity. When an instance restarts or a primary-secondary switch occurs, all data in the table will be lost. We recommend that you migrate to InnoDB as soon as possible.
RocksDB Engine
RocksDB is a highly popular high-performance persistent KV (key-value) store. TXRocks is a transactional storage engine developed by Tencent's TXSQL team based on this. The TXRocks transactional storage engine benefits from the RocksDB LSM Tree storage structure, which reduces both the half-full pages and fragmentation waste in InnoDB, and enables compact format storage. Consequently, TXRocks maintains performance close to that of InnoDB while saving half or even more storage space compared to InnoDB, making it more suitable for businesses that demand high transaction read/write performance and have large data storage volumes.
More storage-efficient
Compared to the B+Tree index structure used by InnoDB, LSM Tree can save a significant proportion of storage space.
Lower write amplification
InnoDB uses an In-Place modification approach, where even modifying a single row may require writing an entire page to the disk, resulting in higher write amplification and random writes. In contrast, RocksDB adopts an Append-Only approach, which offers lower write amplification.
LibraDB Engine
The LibraDB engine, also known as the read-only analysis engine, is a new feature supported by TencentDB for MySQL. This feature is provided based on read-only instances. Its pluggable engine architecture enables flexible creation and termination, while providing users with the capability to process massive volumes of data and perform efficient, real-time complex analysis. The LibraDB engine primarily serves efficient analytical queries. It is an extended read-only analysis component that provides customers with real-time and high-performance complex SQL processing. Leveraging the columnar storage capability, vectorized parallel execution engine, and optimizer extended for distributed parallel execution of the LibraDB engine, customers can easily experience efficient analysis capabilities directly within the database. Additionally, LibraDB's columnar storage has been specifically optimized for high-QPS updates and ACID transactions, ensuring the real-time nature and consistency of query data.
Massively Parallel Processing MPP
Supports the MPP (Massively Parallel Processing) architecture, which enhances data processing performance by distributing workloads across multiple nodes.
Vectorized execution engine
In the LibraDB engine, a vectorized execution mode has been implemented. This mode processes in-memory columnar data in batches, invoking SIMD instructions once per batch, which reduces function call overhead and minimizes cache misses.
Supports columnar storage in high-speed update scenarios
The LibraDB engine ensures data consistency for changes in high-concurrency scenarios by leveraging optimizations and support at the storage layer. This prevents missed analysis opportunities caused by data latency from frequent updates in read-write instances.
Specified data loading capability
The LibraDB engine supports the capability to specify objects for loading. You can specify the LibraDB objects to be loaded either through the data load console settings or via command-line SQL.
Supported Editions and Architectures for Each Engine
|
InnoDB engine | MySQL 5.6,5.7,8.0 | Single-node, Two-node, Three-node, Cluster edition |
RocksDB Engine | MySQL 5.7,8.0 | Two-node |
LibraDB Engine | MySQL 5.7,8.0 | Two-node, Three-node |