All Products
Search
Document Center

ApsaraDB RDS:Usage notes

Last Updated:Oct 24, 2023

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

Introduction

X-Engine is an online transaction processing (OLTP) database-oriented storage engine that is developed by the Database Products Business Unit of Alibaba Cloud to suit the needs of PolarDB. This storage engine is widely used in various business systems of Alibaba Group to reduce costs. These systems include the transaction history database and DingTalk chat history database. X-Engine is also 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 achievements that X-Engine has made in the storage engine field. This paper was accepted by the Industrial Track of SIGMOD 2019 in 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 benefits:

  • 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 RDS High-availability Edition or RDS 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 in the Basic Configurations step and select X-Engine (High Compression Rate) for Default Storage Engine in the Instance Configuration step when you create an RDS instance. For more information about other parameters, see Create an ApsaraDB RDS for MySQL instance.

Note

Create an X-Engine table

If you select X-Engine when you create an RDS instance, the table that is created within the RDS instance uses X-Engine by default. Execute the following statement to view the default engine used by an RDS 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 create a table, data is stored in X-Engine.

Note

You can create tables that use the InnoDB engine on an RDS instance with X-Engine. If you use Data Transmission Service (DTS) to migrate an InnoDB table to an RDS instance with X-Engine, the destination table may still use InnoDB. For more information, see the "Solution 2" section in Convert tables from InnoDB, TokuDB, or MyRocks to X-Engine.

Limits

  • Limits on resource allocations when X-Engine and InnoDB are used together

    When you use X-Engine for your RDS 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 RDS instance that uses X-Engine. Otherwise, the X-Engine performance may be compromised due to a low cache hit ratio. If your RDS instance runs MySQL 8.0, we recommend that you use X-Engine or InnoDB for all tables within the RDS instance.

  • 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

    Partitioned 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 are 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 RAW format. The stmt and mixed formats may cause 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); // T12,000 rows are 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 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 meet 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 in 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 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.

This parameter cannot be modified.

Locks

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 a lock wait.

This parameter cannot be modified.

Running status metrics

The following table describes the status metrics of X-Engine.

Names of metrics

Description

xengine_rows_deleted

The number of deleted rows.

xengine_rows_inserted

The number of inserted rows.

xengine_rows_read

The number of read rows.

xengine_rows_updated

The number of updated rows.

xengine_system_rows_deleted

The number of deletion operations on a system table that uses X-Engine.

xengine_system_rows_inserted

The number of insert operations on a system table that uses X-Engine.

xengine_system_rows_read

The number of read operations on a system table that uses X-Engine.

xengine_system_rows_updated

The number of updates on a system table that uses X-Engine.

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_miss metric + The value of the index_miss metric.

xengine_block_cachecompressed_miss

The number of misses in the compressed block cache.

xengine_bytes_read

The number of bytes that are read from a physical disk.

xengine_bytes_written

The number of bytes that are written in a physical disk.

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 a superversion is applied for references.

xengine_number_superversion_cleanups

The number of times that a 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 returned due to snapshot version conflicts at the RR isolation level.

xengine_wal_bytes

The size of redo logs that are written 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.