PolarDB:FAQ about X-Engine

What is X-Engine? What are the benefits of X-Engine?

X-Engine is developed by Alibaba Cloud based on the log-structured merge-tree (LSM tree). X-Engine provides powerful data compression capabilities that allow you to archive data at a low cost. X-Engine uses the LSM tree structure and the Zstandard (ZSTD) data compression algorithm to increase the data compression ratio. Compared with InnoDB, X-Engine can reduce storage usage by up to 70%. For more information, see X-Engine.

What compression efficiency can X-Engine achieve?

By default, the data compression feature is enabled for X-Engine. Compared with InnoDB, X-Engine can reduce storage usage by up to 70%, depending on the type of data. You can query the estimated compression ratio of X-Engine for specific data, which is calculated based on sampled data.

The following SQL statement queries the estimated compression ratio for a table named test1 in a database named test: Sample statement:

SELECT 
    TABLE_NAME,
    SUBTABLE_NAME,
	SUM(EXTENTS*APRROX_CMP_RATIO)/SUM(EXTENTS)
FROM 
	INFORMATION_SCHEMA.XENGINE_SUBTABLE 
WHERE TABLE_NAME = 'test.test1' AND SUBTABLE_NAME = 'PRIMARY' AND LEVEL in (1, 2);

The following table describes the schema information of the XENGINE_SUBTABLE table in the SQL statement.

Why is a low compression ratio reached after data is imported to X-Engine in batch?

X-Engine uses the background asynchronous compression technology to gradually reorganize and compress data to achieve an ideal compression ratio without affecting your business. In most cases, reorganization and background compression operations are performed during off-peak hours. Therefore, after the batch import, data storage space may be close to the level before compression, and the space will be gradually released after a period of time.

What are the advantages of X-Engine over RocksDB and TokuDB?

X-Engine is similar to RocksDB and TokuDB in terms of the compression ratio. However, TokuDB is deprecated. All data of TokuDB users of Alibaba Cloud is migrated to X-Engine. Compared with RocksDB and MyRocks, X-Engine provides large-scale storage, high elasticity, and uninterrupted schema change. For more information, see Benefits of X-Engine.

How do I purchase a PolarDB cluster that uses X-Engine?

PolarDB for MySQL clusters that run MySQL 8.0 support X-Engine. To purchase a cluster that use X-Engine, you can set the Database Engine parameter to MySQL 8.0.1 or MySQL 8.0.2 and the Storage Engine parameter to InnoDB & X-Engine on the cluster buy page of the PolarDB console.

Can I enable X-Engine for existing PolarDB clusters?

Yes, you can enable X-Engine if your PolarDB cluster runs the latest MySQL version. To enable X-Engine, you can perform the following operations: Log on to the PolarDB console and click the ID of the cluster that you want to manage. Then, choose Settings and Management > Data Lifecycle in the left-side navigation pane of the cluster details page. Finally, click Enable on the X-Engine (Warm Data) tab.

To dynamically enable (X-Engine), your cluster must run one of the following MySQL versions:

• PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.31 or later.

• PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.12 or later.

For more information, see Prerequisites.

How do I use X-Engine?

When you create a table, you can complete the settings of ENGINE=xengine. For existing tables, you can execute the ALTER statement to change the storage engines of the tables to X-Engine. For more information, see Usage notes.

How do I adjust the memory resource proportion of X-Engine?

To adjust the memory resource proportion of X-Engine based on your business requirements, you can perform the following steps: 1. Log on to the PolarDB and then click the ID of the cluster that you want to manage. 2. On the cluster details page, choose Settings and Management > Data Lifecycle. 3. On the page that appears, click the X-Engine (Warm Data tab. For more information, see Adjust the proportion of memory resources.

Does X-Engine have any success stories?

X-Engine is used within Alibaba Group and by users of Alibaba Cloud. PB-level data in a total of more than 3,000 clusters is stored in X-Engine. For more information, see Best practices.

How is X-Engine billed?

The billing rules of X-Engine are the same as those of InnoDB. You are not charged additional fees if you use X-Engine. For example, if your data is 100 GB in size in InnoDB and is compressed to 30 GB when stored in X-Engine, you are charged for 30 GB of storage space.

What are the benefits of PolarDB X-Engine over ApsaraDB RDS X-Engine?

PolarDB X-Engine provides up to 500 TB of storage space. PolarDB X-Engine uses an architecture with storage and compute resources decoupled. This way read-only nodes can be individually added or released. A copy of data can be replicated to up to 16 read-only nodes. PolarDB X-Engine uses distributed storage. PolarDB X-Engine does not rely on binary logs to ensure the high availability of data, which improves data write performance.

Do PolarDB for MySQL clusters that run MySQL 5.6 or 5.7 support X-Engine?

No.

Does X-Engine support transactions?

Yes.

Does X-Engine support online DDL operations?

Yes. The DDL operations do not block the other transactions when the operations are performed.

Does X-Engine support parallel DDL operations?

Yes.

Does X-Engine support row locks?

Yes. Similar to InnoDB, X-Engine supports concurrency control at the row level and deadlock detection.

What isolation types does X-Engine support?

X-Engine supports the read-committed and repeatable-read isolation types. The read-committed isolation is used by default.

Does X-Engine support large transactions?

No. When the number of rows that are modified in a transaction is greater than or equal to 10,000, X-Engine enables the commit in middle feature. Then, X-Engine internally commits the transaction and starts a sub-transaction to continue the transaction.

Does X-Engine support partitioned tables?

Yes. Your cluster must run one of the following MySQL versions:

• PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.34 or later.

• PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.16 or later.

Does X-Engine support in-memory column indexes (IMCIs)?

No.

What are limits on X-Engine?

As a MySQL storage engine, X-Engine supports only common features. For more information, see Limits.

Does X-Engine support the separated storage of hot and cold data on different storage media?

No.

Does X-Engine support parallel queries?

Yes. Parallel queries are supported only on clusters of MySQL 8.0.1 whose revision version is 8.0.1.1.34 or later.

Does X-Engine support the serverless feature?

The serverless feature can be enabled for clusters with fixed specifications that have X-Engine enabled and run qualified minor versions. For more information about version requirements, see Minor version update. X-Engine cannot be enabled for serverless clusters.

How is the performance of X-Engine?

X-Engine organizes data in LSM trees, which delivers better write performance than read performance. By default, data compression is enabled for X-Engine. Therefore, the performance of X-Engine is lower than that of InnoDB by 10% to 20% in Sysbench tests. For more information, see Performance comparison between X-Engine Edition and Cluster Edition in PolarDB for MySQL 8.0.1.

How do I view the size of storage space occupied by X-Engine tables?

Use a primary cluster endpoint to connect to your PolarDB cluster. Specify a database name and a table name to query the TABLE_SPACE_ID value of the XENGINE_SUBTABLE table. Then, view the number of extents used by the XENGINE_TABLE_SPACE table of INFORMATION_SCHEMA based on the TABLE_SPACE_ID value. Calculate the size of storage space occupied by the table based on the number of extents.

The following SQL statement queries the size of storage space occupied by a table named test1 in a database named test.

SELECT 
 	SUM(TOTAL_EXTENT_COUNT)/512 AS SIZE_GB,
  SUM(USED_EXTENT_COUNT)/512 AS USED_SIZE_GB,
  SUM(FREE_EXTENT_COUNT)/512 AS FREE_SIZE_GB
FROM 
	INFORMATION_SCHEMA.XENGINE_TABLE_SPACE
WHERE TABLE_SPACE_ID IN (
  SELECT TABLE_SPACE_ID 
FROM 
	INFORMATION_SCHEMA.XENGINE_SUBTABLE 
WHERE TABLE_NAME = 'test.test1');

XENGINE_TABLE_SPACE calculates the size of storage space based on the files. The following table describes the parameters that you can configure.

XENGINE_SUBTABLE calculates the size of storage space based on the LSM levels. The following table describes the parameters that you can configure.