PolarDB for MySQL IMCI columnar indexes HTAP solution - PolarDB

This topic describes the In-Memory Column Index (IMCI) feature of PolarDB for MySQL.

Background information

PolarDB for MySQL is intended for online transaction processing (OLTP) scenarios that involve online business which produces large amounts of data. However, row store-based PolarDB for MySQL cannot easily meet the query performance requirements of all scenarios. In most cases, to perform complex analytic queries, you need to export data from PolarDB for MySQL and then import the data to an online analytical processing (OLAP) system for analysis and queries. This requires two database systems and increases costs, architectural complexity, and O&M loads.

PolarDB for MySQL provides the IMCI feature for OLAP scenarios that involve complex queries on large amounts of data. With the IMCI feature, PolarDB for MySQL can offer a one-stop hybrid transaction/analytical processing (HTAP) solution, integrating real-time transaction processing and real-time data analysis. As a result, you can meet the requirements of OLTP and OLAP scenarios with only one database system.

Technical Principles

The functional architecture diagram of the Columnstore Index feature in PolarDB for MySQL is as follows:

As shown in the architecture diagram, PolarDB for MySQL designed the Columnstore Index feature with an architecture spanning three layers: storage engine, execution operator, and optimizer:

Storage engine: Supports hybrid row and column storage with real-time, transaction-level consistency.
Execution operator: Provides a vectorized parallel operator for column stores, enabling extremely fast single-table and multi-table queries.
SQL Parser/Optimizer: Features a cost-based optimizer (CBO) for hybrid row and column storage. It automatically selects either row store or column store to execute query requests based on cost.

Under this architecture, PolarDB for MySQL achieves 100% MySQL protocol compatibility and offers query acceleration by several orders of magnitude.

Implementation

To isolate OLAP and OLTP compute resources, IMCIs can only be created on read-only nodes. All OLAP queries run exclusively on read-only nodes. Configure query distribution rules to route OLAP queries to row store or column store read-only nodes. Overview of HTAP-based request distribution among row store and column store nodes.

Core Advantages

PolarDB for MySQL offers the following advantages with the Columnstore Index feature:

100% MySQL compatibility: The column store's data type system is consistent with MySQL. It supports flexible type conversion and is 100% compatible with the MySQL protocol.
Excellent HTAP performance: PolarDB inherently offers strong OLTP performance. The Columnstore Index elevates its OLAP performance to the level of dedicated OLAP database systems.
Hybrid row and column storage reduces costs: It supports both row and column storage formats. It also ensures real-time, transaction-level consistency between rows and columns. Column stores inherently offer lower costs.

Scenarios

The Columnstore Index feature of PolarDB for MySQL provides a one-stop HTAP product experience. It is applicable to various business scenarios:

Scenarios requiring real-time data analysis for online data, such as real-time reports.
Dedicated data warehouse scenarios: You can use PolarDB's massive data storage capabilities to aggregate multiple input data sources and serve as a dedicated data warehouse.
ETL data acceleration compute scenarios: You can leverage PolarDB's powerful and flexible compute capabilities, based on the Columnstore Index, to implement ETL functions using SQL within PolarDB.

Pricing

The IMCI feature is provided free of charge. However, you are charged for read-only column store nodes based on the pricing of common compute nodes. For more information, see Pay-as-you-go prices of compute nodes and Subscription prices of compute nodes. IMCIs also occupy billable storage space. For more information, see Storage pricing.

Supported versions

An Enterprise Edition cluster that meets one of the following requirements:
- PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.22 or later.
- PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.12 or later.
A Standard Edition cluster that uses the X86 CPU architecture and meets one of the following conditions:
- PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.38 or later
- PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.19 and later.

Precautions

The In-Memory Column Index (IMCI) feature does not support Global Database Network (GDN).
IMCI can be used together with the Voting Disk Service (VDS) module of the failover with hot replica feature only under these conditions:
- For a cluster whose revision version is 8.0.1.1.42 and later or 8.0.2.2.23 and later:
  - If a cluster contains a read-only node where the failover with hot replica feature is enabled, you can add read-only column store nodes to the cluster.
  - If a read-only column store node already exists in a cluster, you cannot enable the hot standby feature for any read-only node in the cluster.
- For a cluster whose revision version is earlier than 8.0.1.1.42 or earlier than 8.0.2.2.23, the IMCI feature cannot be used together with failover with hot replica feature.
  - If a cluster contains a read-only node for which the failover with hot replica feature is enabled, you cannot add read-only column store nodes to the cluster.
    
    Note
    If you want to add a read-only column store node to the cluster, contact the technical support to disable VDS. When VDS is being disabled, all nodes in the cluster are automatically restarted.
  - If a read-only column store node already exists in a cluster, you cannot enable the hot standby feature for any read-only node in the cluster.

Performance Improvement

The Columnstore Index feature significantly accelerates SQL query operations, improving query performance by up to hundreds of times. Next, we use standard TPC-H test data tables and SQL queries to demonstrate the query acceleration effect of the Columnstore Index.

Test method: TPC-H is a widely used industry benchmark. The TPC committee developed and released it to evaluate the analytical query capabilities of databases. TPC-H queries include 8 data tables and 22 complex SQL queries. Most queries involve multiple table joins, subqueries, and GROUP BY aggregations.

Note

This TPC-H implementation is based on the TPC-H benchmark. However, it cannot be compared with published TPC-H benchmark results. The tests in this topic do not meet all TPC-H benchmark requirements.

Data volume: 100 GB.
Test results:
- Performance comparison before and after enabling the Columnstore Index
  The following figure compares the query response times for 22 TPC-H query statements executed before and after enabling the Columnstore Index.
- Performance comparison with ClickHouse after enabling the Columnstore Index
  The following figure displays the query response times for 21 TPC-H query statements (excluding Q21, which ClickHouse does not support) after enabling the Columnstore Index. It compares these times with a ClickHouse database of the same data volume and data structure.
Conclusion:
- The Columnstore Index accelerates most complex query operations. Query performance improves significantly, by up to hundreds of times.
- Compared with the traditional OLAP database ClickHouse: After enabling the Columnstore Index, PolarDB for MySQL exhibits both advantages and disadvantages in performance relative to ClickHouse. It performs exceptionally well in scenarios such as single-table scan/aggregation (AGG) and joins. Future Columnstore Index features will continue to be optimized, with breakthroughs expected in areas such as aggregation acceleration and window functions.