AnalyticDB:XUANWU_V2 engine

Prerequisites

• An AnalyticDB for MySQL Data Lakehouse Edition cluster is created.

• The minor version of the AnalyticDB for MySQL cluster is 3.2.1 or later.

Background information

AnalyticDB for MySQL uses the XUANWU analytical storage engine to perform high-throughput writes and high-performance queries in real time. The default storage engine is XUANWU. AnalyticDB for MySQL V3.2.0 launches the next-generation storage engine XUANWU_V2 to provide the following features:

• Stores all data on Object Storage Service (OSS) and uses cloud disks as cache. This reduces storage costs, ensures query performance, and provides faster scale-out and higher scaling efficiency.

• Provides the next-generation column-oriented storage based on the original storage format. The new storage format provides finer-grained control over memory usage and disk I/O usage, supports highly concurrent I/O operations, reduces the memory usage, improves query performance, and reduces impact of garbage collection (GC) on service stability.

• Uses the independent compaction service to perform the compaction operations that consume a large amount of resources in the LSM-tree architecture. This improves query and write stability and provides higher compaction throughput and more flexible resource scheduling capabilities.

Limits

The XUANWU_V2 engine does not completely provide the same features as the XUANWU engine. The following features are not supported:

• Specific composite data types and complex index types: JSON, ARRAY, MAP, FULLTEXT index, VECTOR index, and JSON index.

• Backup and restoration.

• Binary logs.

• Spark elastic import.

Billing rules

If you set the table engine to XUANWU_V2, cold data storage fees are incurred. For information about pricing, see Pricing for Data Lakehouse Edition.

Enable the disk cache feature

After you enable the disk cache feature, you can specify the cache type and cache size to improve the random data read performance.

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.

2. In the Configuration Information section of the Cluster Information page, click Configure next to the Cluster Query Acceleration Configuration parameter.

3. In the Cluster Query Acceleration Configuration dialog box, turn on Disk Cache and specify the cache type and cache size.

   Important

   • You must enable the disk cache feature only if you set the table engine to XUANWU_V2.

   • We recommend that you do not disable the disk cache feature to prevent serious performance degradation.

Set the table engine to XUANWU_V2

After you enable the disk cache feature, you can set the table engine to XUANWU_V2 for the table that you want to create.

Sample CREATE TABLE statement:

CREATE TABLE customer (
    customer_id bigint NOT NULL COMMENT 'Customer ID',
    customer_name varchar NOT NULL COMMENT 'Customer name',
    phone_num bigint NOT NULL COMMENT 'Phone number',
    login_time timestamp NOT NULL COMMENT 'Logon time',
    PRIMARY KEY (login_time, customer_id, phone_num)
) 
ENGINE = 'XUANWU_V2'
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Customer information table';

For information about how to create a table, see CREATE TABLE.

Enable the compaction service

The compaction service uses an independent resource pool to perform local compaction operations in an independent process. This reduces resource usage and improves service stability. By default, the compaction service is disabled. After you enable the compaction service, you are charged for the service based on the pay-as-you-go billing method. You can disable the service based on your business requirements. For information about the billing rules of the compaction service, see Pricing for Data Lakehouse Edition.

We recommend that you enable the compaction service in scenarios that meet the following conditions:

• The CPU utilization and the memory usage are high.

• The service performance is significantly affected by scheduled compaction operations.