ApsaraDB RDS:Usage notes

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.

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.

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.

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

Running status metrics

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