This topic describes the X-Engine storage engine supported by ApsaraDB RDS for MySQL. This engine can process transactions and reduce disk usage.

Introduction

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.

Note For more information about the use scenarios of X-Engine, see Best practices of X-Engine.

Prerequisites

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.

Basic resourcesInstance configurations
Note

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%';
View the default engine

If the default engine is X-Engine, you do not need to specify the storage engine in the table creation statement.

Create a table

After you have created a table, data is stored in X-Engine.

Note You can create tables that use the InnoDB engine in an instance that uses X-Engine. If you use Data Transmission Service (DTS) to migrate an InnoDB table to an X-Engine instance, the destination table also uses InnoDB. For more information, see Solution 2 in Convert the storage engine from InnoDB, TokuDB, or MyRocks to X-Engine.

Limits

  • 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

    (LONGBLOB/LONGTEXT/JSON)

    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.
    None

    Skip Locked

    Lock Nowait

    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/mixed
    Note 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)

Parameters

Note When you create an RDS instance, you can select X-Engine as the default storage engine. You can also adjust the parameter template based on the parameters described in the following table to suit your business requirements. For more information, see Create an ApsaraDB RDS for MySQL instance.
Category Parameter Description Remarks
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. You can view the metrics on the Monitoring page.

Metric Description
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.