This topic describes the X-Engine storage engine supported by ApsaraDB RDS for MySQL. This engine can process transactions and reduce disk usage.
X-Engine is an online transaction processing (OLTP) database storage engine developed by the Database Products Business Unit of Alibaba Cloud to suit the needs of ApsaraDB PolarDB. This storage engine is widely used in many business systems of Alibaba Group to reduce costs. These systems include the transaction history database and DingTalk chat history database. In addition, X-Engine is a crucial database technology that empowers Alibaba Group to withstand bursts of traffic that may surge to hundreds of times greater than usual during the Double 11 shopping festival in China.
X-Engine is optimized for large-scale e-commerce transaction processing. The paper X-Engine: An Optimized Storage Engine for Large-scale E-Commerce Transaction Processing written by the X-Engine R&D team describes the pioneering work X-Engine has achieved in the database engine field. This paper was accepted by the Industrial Track of SIGMOD 2019.
Unlike the InnoDB storage engine, X-Engine adopts the log-structured merge-tree (LSM tree) architecture for tiered storage. LSM tree has the following significant advantages:
- The small size of hotspot datasets that require indexes improves write performance.
- The bottom-layer persistent data pages are read-only. In addition, they are stored in a compact format and are compressed by default to reduce storage costs.
In addition to the advantages of LSM tree, X-Engine brings the following innovations in engineering implementation:
- Continuously optimized write performance: Continuous optimization allows X-Engine to deliver write performance that is over 10 times higher than the write performance of RocksDB that runs in the LSM tree architecture.
- Data reuse at the storage layer: Data reuse optimizes the performance of compaction operations and reduces the impact of compaction operations on system resources in the traditional LSM tree architecture. This allows you to keep system performance stable.
- Hybrid storage: You can deploy various storage media, such as SSDs and HDDs. These storage media provide different I/O capabilities on the same RDS instance. The hybrid storage architecture works with the tiered storage architecture of X-Engine to intelligently store hot and cold data separately. This allows you to reduce overall costs without compromising performance.
- Multi-level caching, refilling, and prefetching: These allow X-Engine to use the fine-grained access mechanism and cache technology to make up for the read performance shortcomings of the engines that adopt the LSM tree architecture.
The preceding optimizations make X-Engine an alternative to the traditional InnoDB storage engine. In addition to supporting transactions, X-Engine can also reduce occupied storage space by up to 90% and thus lower storage costs. X-Engine is especially suitable for businesses that have a large data volume and require high read/write performance.
Your RDS instance runs MySQL 8.0 on High-availability Edition or Basic Edition.
Purchase an RDS instance that uses X-Engine
If you want to use X-Engine for your RDS instance, select MySQL 8.0 for Database Engine on the Basic Configuration page and select X-Engine (Low Cost) for Storage Engine on the Instance Configuration page when you purchase an RDS instance. For more information about other parameters, see Create an ApsaraDB RDS for MySQL instance.
- If you want to use X-Engine for an RDS instance that runs MySQL 5.5, 5.6, or 5.7, you must migrate the data of the RDS instance to a new RDS instance that runs MySQL 8.0. For more information, see Migrate data between RDS instances.
- If you want to convert the storage engine of an RDS instance to X-Engine, see Convert tables from InnoDB, TokuDB, or MyRocks to X-Engine.
Create X-Engine tables
If you select X-Engine when you create an instance, the table created within the instance uses X-Engine by default. Execute the following statement to view the default engine used by an instance:
show variables like '%default_storage_engine%';
If the default engine is X-Engine, you do not need to specify the storage engine in the table creation statement.
After you have created a table, data is stored in X-Engine.
- Limits on resource allocations if X-Engine and InnoDB are used together
When you use X-Engine for an instance, 95% of the memory is used as the write cache and block cache to speed up reading and writing. The InnoDB buffer pool does not occupy much memory. Do not use tables that use InnoDB to store a large volume of data within an instance that uses X-Engine. Otherwise, the X-Engine performance may deteriorate due to a low cache hit ratio. We recommend that you use only a single engine type for tables within an ApsaraDB for RDS instance that runs MySQL 8.0.
- Limits on engine features
X-Engine has some limits on features. Some features are in development. Other features that are not listed in the following table are the same as those of InnoDB.
Category Feature X-Engine Remarks SQL features Foreign key Not supported. None Temporary table Not supported. None Partition table Not supported. X-Engine does not support the creation, addition, deletion, modification, or query of partitions. None Generated Column Not supported. None Handler API Not supported. None Column properties
Maximum column size
32 MB None GIS data type Not supported. X-Engine does not support the following GIS data types: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEMOMETRYCOLLECTION. None Indexes Hash index Not supported. None Spatial index Not supported. X-Engine does not support the creation and use of full-text indexes. None Transactions Transaction isolation level Two isolation levels are provided:
- Read Committed (RC)
- Repeatable Read (RR)
None Maximum transaction size 32 MB Support for larger transactions is under development. Savepoint Not supported. None XA transaction Not supported. Support for XA transactions is under development. Locks Lock granularity
- Table-level locks supported.
- Row-level locks supported.
- Gap locks not supported.
Not supported. None Character sets Character sets supported by non-indexed columns Supported. None Character sets supported by indexed columns
- Latin1 (latin1_bin)
- GBK (gbk_chinese_ci and gbk_bin)
- UTF-8 (utf8_general_ci and utf8_bin)
- UTF-8MB4 (utf8mb4_0900_ai_ci, utf8mb4_general_ci, and utf8mb4_bin)
None Primary/secondary replication Binary log formats stmt/row/mixedNote The default binary log format is the row-based format. The statement-based and row-based log formats may lead to data security issues in specific concurrency scenarios. None
- Limits on large transactions
X-Engine does not support large transactions. If a transaction modifies a large number of rows, X-Engine uses the commit in middle feature. For example, if you use a transaction to modify more than 10,000 rows, X-Engine commits this transaction and starts a new transaction. However, the commit in middle feature cannot strictly follow atomicity, consistency, isolation, durability (ACID). Exercise caution when you use the commit in middle feature. Examples:
- Start a transaction to insert more than 10,000 rows. During the insertion, a portion of the committed data can be queried by other requests.
- Start a transaction to modify more than 10,000 rows. If a portion of the data is committed in the middle of the transaction, you cannot roll the transaction back.
drop table t1; create table t1(c1 int primary key , c2 int)ENGINE=xengine; begin; call insert_data(12000); // 12,000 rows is inserted, and a commit in middle operation is triggered. As a result, the first 10,000 rows of data are committed. rollback;// Only the last 2,000 rows can be rolled back. select count(*) from t1; // The committed 10,000 rows of data can be queried. +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec)
- Start a transaction to delete or modify more than 10,000 rows. Some rows are omitted.
drop table t1; create table t1(c1 int primary key , c2 int)ENGINE=xengine; call insert_data(10000); begin; insert into t1 values(10001,10001), (10002,10002); delete from t1 where c1 >= 0;// The deletion triggers a commit in middle operation, and the two rows of data inserted by the current transaction are not deleted. commit; select * from t1; +-------+-------+ | c1 | c2 | +-------+-------+ | 10001 | 10001 | | 10002 | 10002 | +-------+-------+ 2 rows in set (0.00 sec)
|Performance||xengine_arena_block_size||The unit used when a memory table requests new memory from the operating system and the external memory management system of jemalloc.||Read-only after startup.|
|xengine_batch_group_max_group_size||The maximum number of groups of a transaction pipeline.||Read-only after startup.|
|xengine_batch_group_max_leader_wait_time_us||The maximum wait time of a transaction pipeline.||Read-only after startup.|
|xengine_batch_group_slot_array_size||The maximum batch size of a transaction pipeline.||Read-only after startup.|
|Memory||xengine_block_cache_size||The size of the read block cache.||This parameter cannot be modified.|
|xengine_row_cache_size||The size of the row cache.||This parameter cannot be modified.|
|xengine_write_buffer_size||The maximum size of a single memory table.||This parameter cannot be modified.|
|xengine_block_size||The size of the data block on a disk.||
Read-only after initialization.
Read-only after startup.
|xengine_db_write_buffer_size||The maximum size of the active memory tables in all subtables.||This parameter cannot be modified.|
|xengine_db_total_write_buffer_size||The maximum size of the active memory tables and immutable memory tables in all subtables.||This parameter cannot be modified.|
|xengine_scan_add_blocks_limit||The number of blocks that can be added to the block cache during each range-based scan request.||This parameter cannot be modified.|
|compaction||xengine_flush_delete_percent_trigger||If the number of records in a memory table exceeds the value of this parameter, the xengine_flush_delete_record_trigger parameter takes effect on the memory table.||None|
|Lock||xengine_max_row_locks||The maximum number of rows that can be locked in a single SQL request.||This parameter cannot be modified.|
|xengine_lock_wait_timeout||The timeout period of lock wait.||This parameter cannot be modified.|
Running status metrics
The following table shows the running status metrics of X-Engine.
|xengine_rows_deleted||The number of rows deleted.|
|xengine_rows_inserted||The number of rows written.|
|xengine_rows_read||The number of rows read.|
|xengine_rows_updated||The number of rows updated.|
|xengine_system_rows_deleted||The number of deletion operations on an X-Engine system table.|
|xengine_system_rows_inserted||The number of insert operations on an X-Engine system table.|
|xengine_system_rows_read||The number of read operations on an X-Engine system table.|
|xengine_system_rows_updated||The number of updates on an X-Engine system table.|
|xengine_block_cache_add||The number of add operations on the block cache.|
|xengine_block_cache_data_hit||The number of hits in read data blocks.|
|xengine_block_cache_data_miss||The number of misses in read data blocks.|
|xengine_block_cache_filter_hit||The number of hits in filter blocks.|
|xengine_block_cache_filter_miss||The number of misses in filter blocks.|
|xengine_block_cache_hit||The number of hits in the block cache. The value of this metric is calculated by using the following formula: The value of this metric= The value of the data_hit metric + The value of index_hit metric.|
|xengine_block_cache_index_hit||The number of hits in index blocks.|
|xengine_block_cache_index_miss||The number of misses in index blocks.|
|xengine_block_cache_miss||The number of misses in the block cache. The value of this metric is calculated by using the following formula: The value of this metric= The value of the data_hit metric + The value of the index_hit metric.|
|xengine_block_cachecompressed_miss||The number of misses in the compressed block cache.|
|xengine_bytes_read||The number of bytes on the read physical disk.|
|xengine_bytes_written||The number of bytes that are added to the block cache.|
|xengine_memtable_hit||The number of hits in memory tables.|
|xengine_memtable_miss||The number of misses in memory tables.|
|xengine_number_block_not_compressed||The number of uncompressed blocks.|
|xengine_number_keys_read||The number of times that keys are read.|
|xengine_number_keys_updated||The number of times that keys are updated.|
|xengine_number_keys_written||The number of times that keys are written.|
|xengine_number_superversion_acquires||The number of times that Superversion is applied for references.|
|xengine_number_superversion_cleanups||The number of times that Superversion is cleared. If Superversion is not referenced, it is cleared.|
|xengine_number_superversion_releases||The number of times that the referenced Superversion is released. If Superversion is not referenced, it is cleared.|
|xengine_snapshot_conflict_errors||The number of times that an error is reported due to snapshot version conflicts at the RR isolation level.|
|xengine_wal_bytes||The size of redo logs that are flushed into the disk. Unit: bytes.|
|xengine_wal_group_syncs||The number of times that GroupCommit is executed by redo logs.|
|xengine_wal_synced||The number of times that redo logs are synchronized.|
|xengine_write_other||The number of times that a follower commits transactions in a transaction pipeline.|
|xengine_write_self||The number of times that a leader commits transactions in a transaction pipeline.|
|xengine_write_wal||The number of times that redo logs are written.|