AnalyticDB:Xuanwu analytical storage engine

High-throughput real-time writes

AnalyticDB for MySQL reaches very high throughput capacity through a three-layer parallel architecture. The access layer, the storage node layer, and the persistent distributed storage layer all scale out in parallel. Combined with the hybrid row-column storage engine and the asynchronous conversion between incremental data and full data, this architecture delivers high-throughput, high-concurrency real-time writes.

For real-time visibility, AnalyticDB for MySQL combines the Raft consensus protocol with synchronous write apply. Data becomes queryable immediately after a write, and write consistency is guaranteed. The storage engine uses mark-for-delete to support high-throughput real-time updates and deletes, and it uses Multi-Version Concurrency Control (MVCC) to guarantee data atomicity and integrity.

Hybrid row-column storage

The XUANWU storage engine supports a hybrid row-column storage format. This format is based on column storage but also accommodates row storage, similar to the ORC and Parquet formats in Hadoop. The difference is that hybrid row-column storage in XUANWU covers analytical column pruning and high-throughput scanning, and its row alignment also delivers strong random lookup performance. This gives you an outstanding performance advantage when you filter on arbitrary multidimensional indexes.

The following figure shows the hybrid row-column storage format.

Adaptive indexing

OLAP scenarios require queries on arbitrary dimensions, and the single-column or composite indexes of traditional OLTP systems cannot meet this requirement. XUANWU uses adaptive column-level auto index technology. It automatically configures index data structures for column types such as string, number, text, JSON, and vector. Column-level indexes support retrieval on any combination of dimensions and progressive streaming multiway merges, which greatly improves data filtering performance.

The current index types are inverted indexes, BKD-Tree indexes, and Bitmap indexes. Index performance is affected mainly by data distribution characteristics, including cardinality and the ratio of records returned by a range query to the total records in the table. In some cases, such as the condition age > 0 AND age < 100, an index costs more than a scan. XUANWU therefore relies on CBO to intelligently choose between an index and a scan.

The following figure shows the multiway merge lookup process across index types.

Unified structured and unstructured index management

The index manager at the XUANWU storage layer manages structured and unstructured indexes in a unified way. Managed types include BKD indexes for numeric values, inverted indexes for strings, JSON indexes and vector indexes for unstructured data, and full-text indexes for text data. The index manager provides a unified expression to the compute layer, so SQL logic in the compute layer is compatible with heterogeneous data types and is accelerated by indexes. As a result, AnalyticDB for MySQL supports correlation analysis between full-text data and structured tables, and it expresses complex logic in SQL. The following example shows such a query.

SELECT A.customer_id,
       B.score
FROM (
        SELECT customer_id
        FROM dw_databank_crowds_all_100
        WHERE MATCH (item_title, item_desc) AGAINST ('red dress') > 0.5
              AND buy_cnt between 1 and 3
              AND style_prefer_tags in ('slim fit','soft fabric','medium thickness')
              AND ds = 201704
     ) A
JOIN (
        SELECT buyer_id,
               MAX(MATCH (comments) AGAINST ('excellent workmanship,very good quality,elegant,looks great when worn,looks classy -low price')) AS score
        FROM dw_databank_profile_info_0
        WHERE MATCH (mail_addr) AGAINST ('Hangzhou,Zhejiang')
              AND MATCH (comments) AGAINST ('good workmanship,very good quality,elegant,looks great when worn,looks classy -low price')
              AND create_time < '2018-04-31 12:00:00'
              AND create_time > '2017-04-31 12:00:00'
        GROUP BY buyer_id
        ORDER BY score DESC
        LIMIT 100000
     ) B
ON A.customer_id = B.buyer_id
ORDER BY B.score DESC
LIMIT 10000

The query performs correlation analysis on the full-text index result sets returned by the subqueries, sorts the analyzed results by score in descending order, and returns the top 10,000 rows.

Efficient data organization

XUANWU_V2 optimizes how data is organized. Data is written to the real-time engine in append mode. A Flush task then writes the data to the read-friendly full-data engine. Compaction tasks then merge data within a level or across levels of the full-data engine. This keeps partitions below the L0 level ordered and organizes data physically in fixed sizes.

The following figure shows how data is organized.

This data organization ensures that the real-time engine is built into the read-friendly full-data engine in time, which improves query performance. It also significantly reduces read amplification during compaction and further lowers CPU and I/O consumption during compaction. In addition, XUANWU_V2 adaptively splits and merges partition files that are too large or too small, so you no longer need to worry about choosing a partition key.

Efficient storage format

XUANWU_V2 builds on the original column-level I/O blocks that are organized by a fixed number of rows, and it introduces a file format that organizes I/O blocks by fixed size. This fixed-size organization optimizes I/O and memory management and resolves the problems caused by inconsistencies between row alignment and I/O size. It also improves memory reuse efficiency and reduces the overhead of memory allocation and reclamation. By aligning memory with I/O operations, XUANWU_V2 further reduces read amplification and lowers I/O costs.

Better horizontal scaling elasticity

XUANWU_V2 stores all data in OSS, which significantly reduces your storage costs and greatly improves the efficiency of horizontal scaling and node migration. XUANWU_V2 uses ESSD (Enterprise SSD) as the query cache and supports two forms: partition prefetching specified by a DDL statement and automatic caching of queries. This effectively improves query performance.