DEV Community: ShannonData.AI

Upgraded ShannonBase Released Now

ShannonData.AI — Tue, 08 Sep 2026 10:44:10 +0000

Today, We are happy to announce that the core codebase line of ShannonBase is upgraded from MySQL 8.4.8 to MySQL 8.4.11(The latest version of MySQL), and the 9.7 LTS is on the way.

The first round benkmark result Of TPC-H is also release at: TPC-H benchmark

From the results, we get the maximum about more than 60.9× speedup. That is not the final result of TPC-H. The more optimization approaches are still going.
and The Docker image will be update at shannonbase docker image in a week.

Thanks,

⭐ Star the repo
🧩 Submit PRs
🐞 Open Issues
💬 Join Discussion

Why ShannonBase Doesn’t Just Embed DuckDB

ShannonData.AI — Tue, 01 Sep 2026 02:22:35 +0000

⭐ Star the repo
🧩 Submit PRs
🐞 Open Issues
💬 Join Discussion

On engine boundaries, transaction boundaries, and what a bolted-on OLAP engine structurally cannot do.

Every few weeks someone asks me the same question, and it is a fair one:

DuckDB is fast, permissively licensed, embeddable, and already has a vectorized executor, a compressed columnar format, morsel-driven parallelism, and a mature optimizer. Alibaba shipped it inside AliSQL. There is pg_duckdb for Postgres and MyDuck for MySQL. Why are you writing an entire columnar engine, an entire vectorized executor, and an entire optimizer integration by hand?

It is the highest-leverage question anyone can ask about ShannonBase, so it deserves a real answer rather than a slogan. This post is that answer. I will make the case for the DuckDB route as strongly as I can, then explain the specific architectural property it cannot deliver — and why that property is the whole product for us.

The case for MySQL + DuckDB is genuinely strong Let me not strawman it.

If your goal is “make analytical queries on MySQL data 100x faster with the smallest possible engineering team,” embedding DuckDB is close to the optimal move. You inherit, for free:

a battle-tested compressed columnar storage format with per-type compression selection
a vectorized execution engine that is competitive with ClickHouse on single-node workloads
morsel-driven parallelism, spilling, and out-of-core execution
a cost-based optimizer with real join ordering
an MIT license that permits commercial use without restriction
and — critically — an upstream team that will keep improving all of the above whether you fund it or not
Alibaba’s RDS MySQL DuckDB analytical read-only instance is the most serious production instance of this design. Their published architecture is clean: the instance is a read-only replica that follows the primary over binlog; InnoDB retains only metadata and system tables while all user data lives in DuckDB; a query is parsed by the MySQL server layer, handed to DuckDB for execution, and the result set is converted back into MySQL wire protocol. They report roughly 200x speedup on TPC-H SF100 with storage footprint around 20% of the row-store primary.

Those are excellent numbers, and they are excellent numbers achieved by a small fraction of the engineering effort that building an engine from scratch requires. If ShannonBase’s mission were “MySQL, but TPC-H is fast,” I would have taken this route, and I would tell you to take it too.

That is not the mission. The rest of this post is about why.

The seam is the story Integrating DuckDB into MySQL does not give you one database with two storage layouts. It gives you two databases that agree to cooperate at a well-defined boundary. DuckDB brings its own storage manager, its own catalog, its own transaction manager and MVCC implementation, its own type system, its own parser, and its own optimizer. Every one of those is now duplicated in your process.

Duplication is not automatically bad. It becomes bad in specific, predictable places, and every team that has walked this path has hit the same four.

2.1 Two transaction managers
DuckDB does not implement two-phase commit. This is not an oversight — it is a reasonable design choice for an embedded analytics engine — but it means you cannot use XA to make binlog GTID position and DuckDB’s committed state atomically consistent, and you cannot make a DDL statement atomic across InnoDB’s data dictionary and DuckDB’s catalog.

Alibaba’s engineers describe exactly how they worked around it: they reworked the commit and binlog-apply paths to make replay idempotent, so a crash-restart converges to a consistent state by replaying rather than by rolling back a prepared transaction.

That is solid engineering. It is also a downgrade in the strength of your correctness argument. With 2PC, consistency is a property you prove once at the protocol level. With idempotent replay, consistency is a property you must maintain across every interleaving of DML replay, DDL replay, checkpoint, and crash — forever, for every new feature. The invariant moves from “the protocol guarantees it” to “we tested it.”

There is a second consequence. DuckDB is optimized for large transactions and performs poorly on high-frequency small ones, which produces severe replication lag under an OLTP write pattern. The published fix is batched replay: accumulate many binlog transactions and apply them as one. It works — they report keeping up with sysbench load — but note what batching means semantically. Visibility of committed TP data on the AP side is now deliberately deferred to a batch boundary. You have traded freshness for throughput, and you had no choice, because the engine underneath you was never designed for a stream of 200-byte updates.

2.2 Two type systems and two parsers
MySQL’s observable semantics are not the SQL standard. They are the SQL standard plus thirty years of specific behavior: utf8mb4_0900_ai_ci collation ordering, DECIMAL arithmetic with MySQL’s exact scale-propagation rules, zero dates and NO_ZERO_IN_DATE, ENUM/SET ordinal comparison, implicit string-to-number coercion, sql_mode variations, the ONLY_FULL_GROUP_BY boundary cases, spatial reference system handling, JSON path semantics.

To run MySQL SQL on DuckDB, all of that must be re-implemented in DuckDB’s dialect. The AliSQL team extended DuckDB’s parser to accept MySQL-specific syntax and rewrote or added a large number of functions to match MySQL behavior, then validated with roughly 170,000 SQL statements in an automated compatibility harness, reporting about 99% compatibility.

99% is a genuinely impressive number. It is also, in an architecture with no fallback path, a hard 99%. The remaining 1% is not “slower” — it is “does not run.” And compatibility here is a treadmill, not a milestone: every MySQL point release and every new DuckDB version can move a semantic edge in the translation layer.

Contrast the secondary-engine model. In ShannonBase, parsing, type resolution, collation, sql_mode, and function semantics all happen in the MySQL server layer, exactly once, using MySQL’s own code. Rapid never sees a LIKE expression — it sees an already-resolved Item tree with MySQL’s semantics baked in. Compatibility is not something I test my way toward; it is a structural property.

I want to be honest about the flip side: the compatibility cost does not disappear, it relocates. It moves from “semantic translation” to “engine integration,” and it shows up as a different class of bug. Real examples from my own commit history: ENUM predicate pushdown breaking because the code consulted field->type() instead of field->real_type(); filesort returning wrong rows on wide varlen columns because m_last_returned_rowid wasn’t assigned in populate_row_from_chunks; the hypergraph optimizer wrapping an already-complete aggregation in a spurious two-phase plan; a STREAM AccessPath falling through to the default branch in translate_access_path. None of these are semantic translation bugs. They are all “my engine did not honor a contract the server layer assumed.” Different failure mode, comparable total cost.

2.3 Two optimizers
This is the difference that matters most for query capability.

In the DuckDB-as-replacement-engine design, the MySQL optimizer is essentially bypassed. A statement is routed to DuckDB and DuckDB plans it end to end. That gives you DuckDB’s excellent plans — and an all-or-nothing execution model. A query either runs entirely on the columnar engine or it does not run there at all. There is no per-operator decision, no partial offload, no fallback.

In the secondary-engine design, the columnar engine participates in a plan that the MySQL optimizer owns. The cost model can decide, for this query and increasingly for this operator, whether the row store or the column store is cheaper, and can compose them. A large scan and aggregate goes to Rapid; a point lookup on a secondary index stays in InnoDB; a query that Rapid cannot support degrades to a correct row-store plan instead of failing.

That composability is what makes the next section possible at all.

2.4 Two catalogs
Some MySQL DDL has no DuckDB equivalent — reordering columns, for instance. AliSQL handles this with a Copy DDL mechanism: build a replacement table and swap it, parallelized across threads to cut the cost. Again: correct, pragmatic, and a permanent maintenance surface. Every DDL feature MySQL adds is a potential new divergence between two catalogs that must be kept in lockstep across an asynchronous replication channel.

What ShannonBase needs that the seam forbids Everything above is a cost argument, and cost arguments alone would not justify writing an engine. The actual justification is capability. There are three things ShannonBase is built to do that the integration architecture cannot do — not “does not do yet,” but cannot, without dismantling the boundary that made it cheap.

3.1 Analytics inside the transaction boundary
Rapid/IMCS is a fractured mirror maintained in the same process, fed by a dual channel: redo log application plus direct DML notification. Column store visibility is keyed on the same InnoDB transaction IDs as the row store. An analytical query executes against the same MVCC snapshot as the transactional statement that preceded it in the same session — including reading the session’s own uncommitted writes.

The replica architecture cannot offer this at any price. Its AP data lives on a different instance, behind an asynchronous channel, deliberately batched. “Write, then immediately analyze, in one transaction” is outside its universe. For a reporting workload that is fine. For an operational workload where a decision is made from an aggregate and then written back, it is not.

3.2 AI operators that share the snapshot and the buffers
This is the part that made the decision for me.

ShannonBase runs ONNX Runtime and LightGBM inference inside the kernel, and exposes vector search over an ART-indexed structure as a first-class access path. The design goal is that an inference or an embedding-similarity search is an operator in the execution plan, consuming the same columnar buffers that a SUM would consume, under the same snapshot, inside the same transaction.

Put that requirement against the replica architecture and it dissolves:

The inference operator would score data that is stale by at least one replay batch.
It would run outside the transaction that will act on its output, so you cannot make “predict, then update” atomic.
It would sit on the far side of a process boundary from the row store, so a model that needs both a hot row lookup and a columnar scan pays a network hop.
And the agent layer makes it sharper still. ShannonBase’s embedded agent runtime (JerryScript, with an in-process SQL bridge via execute_sql_internal) executes a ReAct loop where the model reads, proposes, waits for human approval, and writes. That loop is only coherent if reads and writes share a transaction manager. Bolt an OLAP replica onto the side and the agent is reading a different database than the one it is about to modify — which is precisely the failure mode the whole design exists to eliminate.

If you want a one-sentence version of this post: the DuckDB integration route optimizes the analytical query; ShannonBase optimizes the transaction that contains the analytical query. Those lead to different architectures, and only one of them has room for an AI operator in the plan.

3.3 Semantics by construction, not by test suite
Covered above, but it compounds with the previous two. When an agent generates SQL, “99% of statements run” is a materially worse guarantee than it sounds, because the agent will generate the unusual 1% — odd casts, GROUP BY shapes, date arithmetic — far more often than a hand-written application will.

What this costs me, stated plainly I am not going to pretend this is free. It is the most expensive decision in the project.

Writing a columnar engine means owning every bug in it. From my own history: an off-by-one in ring buffer sequence release that livelocked after 262,144 entries; a vectorized scan snapshot’s VarlenReference discarded during BLOB resolution; CU::read passing normalized length as buffer size and silently truncating TEXT; a freelist bucket migration bug in the varlen data pool; a lock gap in ARTIterator::find_position_ge() that released the tree mutex before traversal; SIMD aggregate overflow in the decimal path. DuckDB’s team fixed the equivalent class of bug years ago, and I paid for each of mine in production incidents and debugging weeks.

There are two places where the integration route is simply better, and I would tell an honest evaluator so:

Resource isolation. A separate read-only instance gives you physical TP/AP isolation for free. My populate threads, background worker pool, and SIMD aggregation contend with OLTP for CPU and memory in the same process. I can mitigate this with CPU quotas on the populate/compact thread groups, a separate memory arena for Rapid with admission control — but the ceiling of a shared-process design is real and lower.

Dataset size. Rapid/IMCS is memory-resident. DuckDB is disk-based with strong compression and spills gracefully; SF100 on a 32-core / 128 GB box is comfortable for it. Above the memory line, the integration route wins outright today. Closing this requires cold-partition spill with zone-map skipping over existing chunk metadata, and that work is ahead of me, not behind me.

I will also say the thing that follows from all of this: racing a mature vectorized engine on pure scan-and-aggregate throughput is not a winnable long-term strategy for a small team. DuckDB’s executor will keep getting faster on a budget I do not have. If ShannonBase’s pitch were a TPC-H number, the DuckDB route would beat me on cost and eventually on performance. The pitch is not a TPC-H number.

A decision rule, not a verdict I do not think MySQL + DuckDB is the wrong answer. I think it is the right answer to a different question. Here is the rule I would give someone choosing between them:

Choose the DuckDB integration route if your analytics are reporting-shaped — dashboards, BI, batch aggregation — and seconds-to-minutes of staleness is acceptable; if your datasets exceed memory; if you need hard TP/AP isolation; and if your compatibility surface is a fixed set of hand-written queries you can validate once.

Choose a native secondary-engine route if analytical results must be consistent with the transaction that produced them; if you need per-query or per-operator planning with a correct fallback rather than all-or-nothing routing; if arbitrary generated SQL must work with full MySQL semantics; or if you intend to put inference, embedding, or vector search into the execution plan rather than beside the database.

ShannonBase exists because the second column is our workload. The engine is expensive because the boundary I refused to accept is exactly the boundary that made the cheap version cheap.

ShannonBase is an open-source, MySQL 8.4-compatible HTAP database with native AI and agent capabilities. Technical details of the AliSQL DuckDB integration referenced here are drawn from Alibaba Cloud’s published documentation and engineering write-ups on the RDS MySQL DuckDB analytical instance.

ShannonBase: The Lightweight Semantic Layer for Enterprise AI SQL

ShannonData.AI — Sun, 24 May 2026 09:32:45 +0000

ShannonBase: The Lightweight Semantic Layer for Enterprise AI SQL

⭐ Star the repo
🧩 Submit PRs
🐞 Open Issues
💬 Join Discussion

Enterprise AI agents are entering a new phase.

The first generation of AI data products focused on a single capability:

Natural Language → SQL

And for a while, that worked surprisingly well.

Projects like Text2SQL, NL2SQL systems, and many AI BI tools proved that LLMs can generate SQL from plain English with impressive accuracy.

But once these systems entered real enterprises, a deeper problem emerged:

The hardest part of enterprise analytics is not SQL generation.

It’s business semantics.

That is exactly where ShannonBase is evolving differently.

The Problem with Traditional NL2SQL

Most current AI SQL systems follow the same architecture:

Natural Language
    ↓
LLM
    ↓
SQL

ShannonBase itself originally started from this direction.

Current ShannonBase capabilities already include:

Automatic schema metadata reading
Table/column comment understanding
Prompt-based SQL generation
SQL validation and retry repair
Fully-qualified table enforcement
Multi-schema/table scope control
Multi-model support:
- DeepSeek
- Qwen
- Llama
- OpenAI-compatible models

Architecturally, it is a strong:

Schema-aware NL2SQL system

This works well for simple analytical queries.

But enterprise environments are never simple.

Why Enterprise Analytics Breaks NL2SQL

1. Metrics Are Not Columns

In enterprise systems:

GMV
Revenue
Active Users
New Customers
Retention

…are almost never raw fields.

They are combinations of:

aggregation logic
filters
business rules
status conditions
time windows

For example:

GMV != SUM(order_amount)

It may require:

paid orders only
excluding refunds
excluding test users
timezone normalization
order status filtering

An LLM cannot reliably infer this from schema metadata alone.

2. Time Semantics Are Business Logic

Users ask questions like:

“this month”
“year to date”
“QoQ”
“YoY”
“up to now”

But every enterprise defines time differently.

Examples:

fiscal calendars
delayed revenue recognition
T+1 ingestion
timezone cutoffs
business-day alignment

Pure NL2SQL systems usually hallucinate these rules.

3. Join Paths Become Unmanageable

Real enterprise databases contain:

hundreds or thousands of tables
historical schemas
bridge tables
denormalized layers
legacy data marts

LLMs frequently produce:

incorrect joins
missing joins
duplicated joins
unstable join paths

Even if the SQL is syntactically correct,
the business answer can still be wrong.

4. Enterprise Schemas Are Messy

In reality:

naming conventions drift
comments are missing
tables become obsolete
duplicate datasets exist
historical migrations accumulate

Relying only on schema metadata inevitably creates hallucinations.

This is not a prompt engineering problem.

It is an architecture problem.

The Industry’s Response: Semantic Layers

To solve this, many companies moved toward semantic-layer architectures.

Examples include:

Cube Semantic Layer
LookML
Enterprise BI Semantic Engines

Their architecture looks like this:

Natural Language
    ↓
Semantic IR / LogicForm
    ↓
Semantic Runtime
    ↓
SQL / API / URL

These systems introduce:

metric registries
business glossaries
entity modeling
join graphs
semantic planners
query compilers
ontology systems

The idea is powerful:

Move business understanding out of the LLM.

But there is a major problem.

Why Most Semantic Layer Projects Fail

Full semantic systems are extremely heavy.

They often become:

expensive
slow to deploy
difficult to maintain
dependent on specialized teams

Eventually, many organizations realize:

They are rebuilding a BI Operating System.

This starts to resemble previous generations of:

data governance platforms
enterprise data middle platforms
metadata governance systems

Projects become multi-year initiatives.

Adoption slows.

Maintenance explodes.

Many eventually stall or fail entirely.

ShannonBase’s Direction: Lightweight Semantic Layer

ShannonBase believes there is a better path.

Not:

“Build a giant semantic operating system.”

But:

“Inject business semantics directly into the NL2SQL workflow.”

The new architecture becomes:

Natural Language
    +
Schema Metadata
    +
Business Semantic Context
    ↓
LLM
    ↓
SQL

This approach keeps the system:

lightweight
flexible
deployable
developer-friendly

While dramatically improving enterprise accuracy.

Introducing ShannonBase Lightweight Semantic Layer

Instead of forcing companies to build complex ontology systems,
ShannonBase introduces a simple but powerful concept:

System Semantic Tables

Example:

sys.nl_sql_semantics

Users can maintain semantic definitions directly inside the database.

This includes:

metric definitions
join relationships
business terminology
synonyms
time semantics
filtering rules

Example:

Metric: GMV
Definition:
SUM(order_amount)
WHERE order_status = 'paid'
AND is_test = false

Or:

Term: Active User
Definition:
Distinct users with at least one login event in 30 days

Or:

Join Rule:
orders.user_id -> users.id
Preferred Join Type: LEFT JOIN

The LLM no longer guesses business logic.

It retrieves semantic context before SQL generation.

Why This Matters

This architecture changes the role of the LLM.

Instead of asking the model to:

infer business definitions
invent joins
guess metrics

We let the model focus on what it does best:

reasoning
query composition
language understanding

And we externalize business truth into manageable semantic context.

This dramatically improves:

SQL stability
metric consistency
enterprise trust
explainability
maintainability

Without introducing a massive semantic platform.

The Key Insight

The future is probably not:

Pure NL2SQL

Nor:

Massive Semantic Operating Systems

The future is likely:

Lightweight Semantic-Augmented AI Data Agents

Systems that combine:

LLM flexibility
schema awareness
business semantic grounding

Without overwhelming operational complexity.

That is the direction ShannonBase is building toward.

Why Developers Like This Approach

ShannonBase keeps the developer experience simple.

You do not need to build:

ontology graphs
DSL compilers
semantic runtimes
massive metadata platforms

You only need to gradually define:

critical metrics
important joins
business vocabulary

This creates an incremental adoption model.

Teams can start small:

define GMV
define Active Users
define fiscal calendar rules

And improve accuracy immediately.

No “big bang” semantic migration required.

The Bigger Vision

AI Data Agents are evolving beyond SQL generation.

The next generation will require:

semantic grounding
business consistency
reliable execution
enterprise trust

But enterprises do not want another heavyweight platform.

ShannonBase aims to bridge that gap.

Not by replacing SQL.

Not by replacing BI systems.

But by making AI truly understand enterprise data semantics —
in a lightweight, practical, deployable way.

Final Thoughts

The core insight behind ShannonBase is simple:

Enterprise analytics problems are rarely SQL problems.

They are semantic problems.

And solving semantics does not necessarily require building a massive semantic operating system.

Sometimes, a lightweight semantic layer is enough to unlock reliable AI analytics at scale.

That is the direction ShannonBase is pursuing.

ShannonBase Live Demos

ShannonData.AI — Mon, 04 May 2026 03:32:10 +0000

1：to show how the native agent in ShannonBase does, Using natural language to do query...

2：to demonstrate how do ml training in ShannonBase

3：To illustrate how to inference locally with local LLM.

4：to show how embedding a text in ShannonBase

5：To demonstrate how to do RAG in ShannonBase with ml_rage function.

⭐ Star the repo
🧩 Submit PRs
🐞 Open Issues
💬 Join Discussion

ShannonBase Javascript Engine

ShannonData.AI — Mon, 27 Apr 2026 03:48:40 +0000

The image shows a discussion where someone complained after the release of MySQL 9.7. It can open the feature of supporting JS routine.

But don’t worry — ShannonBase already supports a JS engine, allowing you to write and execute JavaScript programs inside ShannonBase just like writing SQL stored procedures, and you can directly manipulate the local database within the JS program.

The choice to integrate a JavaScript (JS) engine is mainly to lower the development barrier, expand the database’s processing capabilities, and improve support for modern data formats (especially JSON). By embedding a lightweight JS engine (based on GraalVM) into the database, developers can write stored procedures and functions directly in JavaScript, rather than being limited to traditional SQL or stored procedure languages.

Expanding complex logic processing capabilities: SQL is a set-based declarative language, making it difficult to implement complex business logic or procedural processing. JavaScript, as an imperative programming language, is better suited for data transformations, custom algorithms, and other complex logic.

Bridging the “last mile” of JSON support: JSON data is naturally compatible with JavaScript. Using JavaScript to handle stored procedures allows developers to manipulate in-memory JSON data in a more native and efficient way.

Seamless compatibility with existing infrastructure: JavaScript stored programs work seamlessly with traditional SQL stored programs, InnoDB, Lakehouse, and the Rapid engine. This means you can write logic in JS while still leveraging Rapid’s high-performance computing capabilities.

Wider developer ecosystem: JavaScript is one of the most popular programming languages. Using JS allows a large number of frontend or backend developers to easily perform advanced development inside the database without learning complex SQL procedural languages.

More options: With the introduction of generative AI features, the JS engine provides flexibility for handling unstructured data and calling external APIs.

In summary, with the JS engine, ShannonBase achieves “processing logic where the data resides,” avoiding the latency of moving data out of the database just to handle logic, thus improving overall analysis and development efficiency.

Unlike Heatwave, ShannonBase does not use GraalVM as its JS engine; instead, it uses JerryScript. Compared to GraalVM, JerryScript offers the following advantages:

Extremely lightweight and low resource usage: JerryScript is designed to run in less than 64 KB of RAM and 200 KB of ROM. For a database, this means the JS engine’s memory overhead can be kept minimal, leaving most memory resources for core tasks like data processing, query caching, and connection management. In contrast, GraalVM is designed for general-purpose applications and microservices, with a relatively large base runtime that significantly increases the database’s memory burden.
Small code size, easy integration and maintenance: JerryScript is written in C99, and its compiled binary is very small (e.g., only about 258 KB when compiled for ARM Thumb-2). This makes it easy to embed into ShannonBase’s C++ codebase without significantly increasing the size of the installation package or binary files. Moreover, as a pure interpreter without complex JIT frameworks, its behavior and resource consumption are more predictable when executing user-provided JS code within the database process, making sandboxing easier.
Fast startup and no JIT warm-up overhead: JerryScript has no JIT mechanism, so there is no “warm-up” process — code always starts being interpreted immediately. This is ideal for database scenarios, where stored procedures or functions are typically short and called frequently. JerryScript’s “zero startup cost” avoids CPU and memory spikes caused by JIT compilation, ensuring stable database performance and predictable response times.
Mature embedded API and snapshot support: JerryScript provides a mature C API, making it easy for applications to call and embed directly. Its unique snapshot support allows JavaScript source code to be precompiled into bytecode. These snapshots can be preloaded in the database, further reducing runtime parsing and compilation overhead and significantly improving execution efficiency.

With this lightweight JS engine, ShannonBase now supports JavaScript stored functions. Since JerryScript itself, as a lightweight JS engine, does not include database connection APIs like ODBC or DAO in its core standard library, ShannonBase extends its capabilities by providing the sys.exec_sql interface to execute SQL queries within JS functions. Through sys.exec_sql, you can directly manipulate the database from inside a JS function. The sys.exec_sql function returns results in JSON format by default.

DELIMITER *
CREATE FUNCTION query_table() RETURNS TEXT
LANGUAGE JAVASCRIPT AS $$
function query() {
    return sys.exec_sql("select * from test");
}
return query();
$$*
DELIMITER ;

Additionally, exec_sql inherits all the states of the current connection.
Example execution:

mysql> DELIMITER *
mysql> CREATE FUNCTION query_table() RETURNS TEXT
    -> LANGUAGE JAVASCRIPT AS $$
    $> function query() {
    $>     return sys.exec_sql("select * from test");
    $> }
    $> return query();
    $> $$*
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select query_table();
+---------------------------------------------------------------------------------------------------------------------------------------------+
| query_table()                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| [{"score":1.1,"name":"n1","id":1,"gender":"m"},{"score":2.2,"name":"n2","id":2,"gender":"f"},{"score":3.3,"name":"n3","id":3,"gender":"m"}] |
+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (2.95 sec)

You can also use other features:

DELIMITER *
CREATE FUNCTION IS_EVEN (VAL INT) RETURNS INT
LANGUAGE JAVASCRIPT AS $$
function isEven(num) {
    return num % 2 == 0;
}
return isEven(VAL);
$$*
DELIMITER ;

If you like the project, please give me a star or submit a PR.

⭐ Star the repo
🧩 Submit PRs
🐞 Open Issues
💬 Join Discussion

ShannonBase HTAP Architecture Analysis: From MySQL Optimizer to Vectorized Execution Engine — Overview

ShannonData.AI — Mon, 27 Apr 2026 03:43:18 +0000

This article is the overview in a series introducing ShannonBase’s query optimization and execution. Subsequent articles will provide detailed introductions to

each module, systematically analyzing how ShannonBase implements its HTAP capabilities.

In the modern database field, HTAP (Hybrid Transactional/Analytical Processing) capability is a core metric for evaluating high-performance databases.

ShannonBase, through its Rapid engine (commonly referred to as IMCS — In-Memory Column Store), builds an efficient columnar vectorized execution flow on top of MySQL.

The HTAP execution flow of ShannonBase can be summarized in the following five key stages.

I. Plan Capture & Translation
ShannonBase does not operate completely independently of MySQL; instead, it deeply integrates (Hooks) into MySQL’s optimizer workflow.

Intervention Timing: In the current version, after MySQL’s optimizer generates the original AccessPath, ShannonBase’s optimizer begins its intervention. Future versions will advance this intervention to an earlier stage, intervening during MySQL’s optimization process to more quickly and accurately determine if the current join order is optimal.

Translation Process: Once MySQL completes its optimization, the translate function in ShannonBase's Rapid engine optimizer is responsible for converting MySQL's optimized AccessPath tree into ShannonBase's internal QueryPlan.

Node Conversion: It converts MySQL AccessPath nodes into query nodes supported by Rapid. For example, a MySQL aggregation path is converted into ShannonBase’s Aggregate node, and a filter path is converted into a Filter node.

Fallback Mechanism: For complex operators that ShannonBase cannot yet handle, it encapsulates them using the MySQLNative class (see query_plan.h). This ensures the query can fall back to execution by the native MySQL engine, guaranteeing system robustness. The advantage of this approach is that it allows for an iterative process, first handling the query plans with the greatest impact on performance without affecting overall execution.

ShannonBase HTAP Architecture Analysis: From MySQL Optimizer to Vectorized Execution Engine — Overview

ShannonData.AI — Thu, 26 Feb 2026 02:49:31 +0000

This article is the overview in a series introducing ShannonBase’s query optimization and execution. Subsequent articles will provide detailed introductions to each module, systematically analyzing how ShannonBase implements its HTAP capabilities.

In the modern database field, HTAP (Hybrid Transactional/Analytical Processing) capability is a core metric for evaluating high-performance databases.

ShannonBase, through its Rapid engine (commonly referred to as IMCS — In-Memory Column Store), builds an efficient columnar vectorized execution flow on top of MySQL.

The HTAP execution flow of ShannonBase can be summarized in the following five key stages.

I. Plan Capture & Translation

ShannonBase does not operate completely independently of MySQL; instead, it deeply integrates (Hooks) into MySQL’s optimizer workflow.

Plan Optimizer::translate_access_path(OptimizeContext *ctx, THD *thd, AccessPath *path, const JOIN *join) {
  if (!path) return nullptr;
  switch (path->type) {
    case AccessPath::TABLE_SCAN:
    case AccessPath::INDEX_SCAN:
    case AccessPath::INDEX_RANGE_SCAN: {
      auto scan = std::make_unique<ScanTable>();
      scan->original_path = path;
      TABLE *table{nullptr};
      if (path->type == AccessPath::INDEX_SCAN) {
        table = path->index_scan().table;
        scan->scan_type = ScanTable::ScanType::INDEX_SCAN;
      } else if (path->type == AccessPath::INDEX_RANGE_SCAN) {
        const auto &irs = path->index_range_scan();
        if (irs.used_key_part != nullptr && irs.num_used_key_parts > 0 && irs.used_key_part[0].field != nullptr)
          table = irs.used_key_part[0].field->table;
        scan->scan_type = ScanTable::ScanType::INDEX_SCAN;
      } else {
        table = path->table_scan().table;
        scan->scan_type = ScanTable::ScanType::FULL_TABLE_SCAN;
      }
      assert(table);
      auto share = ShannonBase::shannon_loaded_tables->get(table->s->db.str, table->s->table_name.str);
      auto table_id = share ? share->m_tableid : 0;
      scan->rpd_table = (share->is_partitioned) ? Imcs::Imcs::instance()->get_rpd_parttable(table_id)
                                                : Imcs::Imcs::instance()->get_rpd_table(table_id);
      assert(scan->rpd_table);
      scan->estimated_rows = path->num_output_rows();
      scan->source_table = table;
      return scan;
    } break;
    case AccessPath::HASH_JOIN: {
      auto hashjoin_node = std::make_unique<HashJoin>();
      hashjoin_node->original_path = path;
      auto &param = path->hash_join();
      // Recursively convert children
      hashjoin_node->children.push_back(translate_access_path(ctx, thd, param.outer, join));
      hashjoin_node->children.push_back(translate_access_path(ctx, thd, param.inner, join));
      // Extract Join Conditions
      if (param.join_predicate) {
        for (auto *cond : param.join_predicate->expr->equijoin_conditions) {
          hashjoin_node->join_conditions.push_back(cond);
        }
        // Handle other conditions...
      }
      hashjoin_node->allow_spill = param.allow_spill_to_disk;
      hashjoin_node->estimated_rows = path->num_output_rows();
      return hashjoin_node;
    } break;
    case AccessPath::NESTED_LOOP_JOIN: {
      auto nestloop_node = std::make_unique<NestLoopJoin>();
      nestloop_node->original_path = path;
      auto &param = path->nested_loop_join();
      // Recursively convert children
      nestloop_node->children.push_back(translate_access_path(ctx, thd, param.outer, join));
      nestloop_node->children.push_back(translate_access_path(ctx, thd, param.inner, join));
      nestloop_node->pfs_batch_mode = param.pfs_batch_mode;
      nestloop_node->already_expanded_predicates = param.already_expanded_predicates;
      // Extract Join Conditions
      nestloop_node->source_join_predicate = param.join_predicate;
      if (param.join_predicate) {
        for (auto *cond : param.join_predicate->expr->equijoin_conditions) {
          nestloop_node->join_conditions.push_back(cond);
        }
        // Handle other conditions...
      }
      nestloop_node->equijoin_predicates = param.equijoin_predicates;
      return nestloop_node;
    } break;
    case AccessPath::AGGREGATE: {
      auto agg = std::make_unique<LocalAgg>();
      agg->original_path = path;
      auto param = path->aggregate();
      agg->olap = param.olap;
      agg->children.push_back(translate_access_path(ctx, thd, param.child, join));
      fill_aggregate_info(agg.get(), join);
      return agg;
    } break;
    case AccessPath::LIMIT_OFFSET: {
      auto limit = std::make_unique<Limit>();
      limit->original_path = path;
      auto param = path->limit_offset();
      limit->limit = (param.limit - param.offset);  // mysql limit is (sql limit + sql offset)
      limit->offset = param.offset;
      limit->count_all_rows = param.count_all_rows;
      limit->reject_multiple_rows = param.reject_multiple_rows;
      limit->send_records_override = (param.send_records_override) ? *param.send_records_override : 0;
      limit->children.push_back(translate_access_path(ctx, thd, param.child, join));
      return limit;
    } break;
    case AccessPath::FILTER: {
      auto filter = std::make_unique<Filter>();
      filter->original_path = path;
      auto param = path->filter();
      filter->condition = param.condition;
      filter->children.push_back(translate_access_path(ctx, thd, param.child, join));
      filter->predict = Optimizer::convert_item_to_predicate(thd, param.condition);
      return filter;
    } break;
    case AccessPath::SORT: {
      auto param = path->sort();
      // only it has `limit` clause, can be converted to `TopN`, such as `select xxx from xxx order by xx limit xx`.
      if (param.limit > 0 && param.limit != HA_POS_ERROR) {
        auto topn = std::make_unique<TopN>();
        topn->original_path = path;
        topn->order = param.order;
        topn->limit = param.limit;
        topn->filesort = param.filesort;
        topn->children.push_back(translate_access_path(ctx, thd, param.child, join));
        topn->estimated_rows = std::min(topn->children[0]->estimated_rows, (ha_rows)param.limit);
        return topn;
      } else {
        // without `LIMIT` clause, keep it as `order by`
        auto sort = std::make_unique<Sort>();
        sort->original_path = path;
        sort->order = param.order;
        sort->filesort = param.filesort;
        sort->limit = HA_POS_ERROR;
        sort->children.push_back(translate_access_path(ctx, thd, param.child, join));
        sort->estimated_rows = sort->children[0]->estimated_rows;
        sort->remove_duplicates = param.remove_duplicates;
        sort->unwrap_rollup = param.unwrap_rollup;
        sort->force_sort_rowids = param.force_sort_rowids;
        sort->tables_to_get_rowid_for = param.tables_to_get_rowid_for;
        return sort;
      }
      assert(false);
      return nullptr;
    } break;
    case AccessPath::EQ_REF: {
      auto param = path->eq_ref();
      // Check if this is a dynamic join condition (not a constant lookup)
      bool dynamic_lookup{false};
      for (uint i = 0; i < param.ref->key_parts; i++) {
        Item *item = param.ref->items[i];
        if (!item) continue;
        // Check if this item references fields from other tables or is a non-constant expression
        if (!item->const_item()) {
          dynamic_lookup = true;
          break;
        }
      }
      if (!dynamic_lookup) {
        auto scan = std::make_unique<ScanTable>();
        scan->original_path = path;
        scan->source_table = param.table;
        scan->scan_type = ScanTable::ScanType::EQ_REF_SCAN;
        auto share = ShannonBase::shannon_loaded_tables->get(scan->source_table->s->db.str,
                                                             scan->source_table->s->table_name.str);
        auto table_id = share ? share->m_tableid : 0;
        scan->rpd_table = (share->is_partitioned) ? Imcs::Imcs::instance()->get_rpd_parttable(table_id)
                                                  : Imcs::Imcs::instance()->get_rpd_table(table_id);
        assert(scan->rpd_table);
        // If this is a dynamic join condition, we cannot convert it to a static Predicate. Return nullptr
        // to indicate this should be handled as a join condition during execution.
        // This is a JOIN condition like "c.customer_id = o.customer_id"
        // It should be handled by the join executor, not converted to a static filter predicate
        scan->prune_predicate = Optimizer::convert_item_to_predicate(thd, param.ref, param.table);
        return scan;
      } else {  // if it's dynamic join condition(such as a.id = b.id), the join condition cannot be pushed down.
        auto eq_ref = std::make_unique<MySQLNative>();
        eq_ref->original_path = path;
        return eq_ref;
      }
      assert(false);
      return nullptr;  // not reach forever.
    } break;
    default: {
      // if Rapid can not handle, then re-encapsulate to a Fallback node
      auto original = std::make_unique<MySQLNative>();
      original->original_path = path;
      original->estimated_rows = path->num_output_rows();
      // no need to transalte anymore, because it's a MySQL AccessPath.
      return original;
    }
  }
}

II. Core Optimization Rules

Once the internal QueryPlan is generated, the Optimizer applies a series of optimization rules. The core optimizations include:


void Optimizer::AddDefaultRules() {
  // becareful the order of rules. The rules be applied in the order of added.
  // Make predicates available
  m_optimize_rules.emplace_back(std::make_unique<PredicatePushDown>());
  // Use predicates for IMCU pruning
  m_optimize_rules.emplace_back(std::make_unique<StorageIndexPrune>());
  // After predicates clarify needed columns
  m_optimize_rules.emplace_back(std::make_unique<ProjectionPruning>());
  // Before aggregation changes structure
  m_optimize_rules.emplace_back(std::make_unique<TopNPushDown>());
  // aggregation push down to lower level operators
  m_optimize_rules.emplace_back(std::make_unique<AggregationPushDown>());
  // Re-run after structure changes
  m_optimize_rules.emplace_back(std::make_unique<ProjectionPruning>());
  // Final reordering with all optimizations
  m_optimize_rules.emplace_back(std::make_unique<JoinReOrder>());
  m_registered.store(true, std::memory_order_relaxed);
}
Plan Optimizer::Optimize(const OptimizeContext *context, const THD *thd, const JOIN *join) {
  if (!m_registered.load()) AddDefaultRules();
  if (m_optimize_rules.empty()) return nullptr;
  QueryPlan plan;
  plan.root = get_query_plan(const_cast<OptimizeContext *>(context), const_cast<THD *>(thd), const_cast<JOIN *>(join));
  for (auto &rule : m_optimize_rules) {
    Timer rule_timer;
    rule->apply(plan.root);
  }
  return std::move(plan.root);
}

Predicate PushDown: Pushes filter conditions as far down as possible towards the storage layer.

Storage Index Prune: This is key to HTAP performance improvement. With the Storage Index (see writable_access_path.inc), the engine can use statistical information (such as Max/Min) to exclude non-matching data blocks before reading them, significantly reducing I/O pressure.

Projection Pruning: A key advantage of columnar storage is reading only the required columns. The optimizer calculates projected_columns to ensure the execution engine loads only the relevant column data.

Aggregation PushDown: When possible, aggregation operations are completed directly during the scan process, avoiding the generation of a large number of intermediate row data.

III. Vectorized AccessPath Reconstruction

Once the optimization is complete, the QueryPlan needs to be remapped back into an execution framework that MySQL can understand, but at this point, the path is already equipped with “vectorized” capabilities.
The RapidAccessPath structure is defined in access_path.h:

  ...
  bool m_vectorized{false}; 
  ...
};

The ToAccessPath method within it is responsible for completing this conversion and setting the m_vectorized flag to true. This marks that the query path is now ready to enter ShannonBase's high-performance execution channel.

IV. Vectorized Operator Instantiation (Iterator Creation)

This is the core part of HTAP execution. The PathGenerator class is responsible for instantiating the AccessPath into a concrete RowIterator. ShannonBase retains MySQL's execution engine. The advantage of this approach is that it maximizes the reuse of the original execution mechanisms, related concepts, data structures, and so on.

Vectorized Operator Library: Rapid implements a series of vectorized operators (currently completed operators include TableScanIterator, HashJoinIterator, AggregateIterator, etc., with more vectorized execution operators to be provided in the future).

Execution Mode Selection: If path->vectorized is true, the PathGenerator will prioritize creating ShannonBase's own fast operators.

Data Flow Direction: These operators no longer process data in the traditional MySQL way of “one row at a time” (Iterative Row-at-a-time), but rather process data in “one batch/vector at a time,” fully leveraging the acceleration of modern CPU SIMD instruction sets.

unique_ptr_destroy_only<RowIterator> PathGenerator::CreateIteratorFromAccessPath(THD *thd, MEM_ROOT *mem_root,
                                                                                 OptimizeContext *context,
                                                                                 AccessPath *top_path, JOIN *top_join,
                                                                                 bool top_eligible_for_batch_mode) {
  unique_ptr_destroy_only<RowIterator> ret;
  Mem_root_array<IteratorToBeCreated> todo(mem_root);
  todo.push_back({top_path, top_join, top_eligible_for_batch_mode, &ret, {}});
  // The access path trees can be pretty deep, and the stack frames can be big
  // on certain compilers/setups, so instead of explicit recursion, we push jobs
  // onto a MEM_ROOT-backed stack. This uses a little more RAM (the MEM_ROOT
  // typically lives to the end of the query), but reduces the stack usage
  // greatly.
  //
  // The general rule is that if an iterator requires any children, it will push
  // jobs for their access paths at the end of the stack and then re-push
  // itself. When the children are instantiated and we get back to the original
  // iterator, we'll actually instantiate it. (We distinguish between the two
  // cases on basis of whether job.children has been allocated or not; the child
  // iterator's destination will point into this array. The child list needs
  // to be allocated in a way that doesn't move around if the TODO job list
  // is reallocated, which we do by means of allocating it directly on the
  // MEM_ROOT.)
  while (!todo.empty()) {
    IteratorToBeCreated job = todo.back();
    todo.pop_back();
    AccessPath *path = job.path;
    JOIN *join = job.join;
    bool eligible_for_batch_mode = job.eligible_for_batch_mode;
    if (job.join != nullptr) {
      assert(!job.join->needs_finalize);
    }
    unique_ptr_destroy_only<RowIterator> iterator;
    ha_rows *examined_rows = nullptr;
    if (path->count_examined_rows && join != nullptr) {
      examined_rows = &join->examined_rows;
    }
    switch (path->type) {
      case AccessPath::TABLE_SCAN: {
        const auto &param = path->table_scan();
        std::unique_ptr<Imcs::Predicate> predicate{nullptr};
        std::vector<uint32_t> projection;
        ha_rows limit{HA_POS_ERROR};
        ha_rows offset{0};
        bool use_storage_index{false};
        if (path->secondary_engine_data) {
          auto rapid_scan_param = static_cast<RapidScanParameters *>(path->secondary_engine_data);
          predicate = std::move(rapid_scan_param->prune_predicate);
          projection = std::move(rapid_scan_param->projected_columns);
          limit = rapid_scan_param->limit;
          offset = rapid_scan_param->offset;
          use_storage_index = true;
          rapid_scan_param->~RapidScanParameters();
#ifndef NDEBUG
          if (predicate) {
            DBUG_PRINT("rapid_optimizer",
                       ("TABLE_SCAN: Passing predicate to iterator: %s", predicate->to_string().c_str()));
          }
          if (!projection.empty()) {
            DBUG_PRINT("rapid_optimizer", ("TABLE_SCAN: Column projection enabled (%zu columns)", projection.size()));
          }
#endif
        }
        // Here param.table maybe a temp table/in-memory temp table.)
        if (path->vectorized && param.table->s->table_category == enum_table_category::TABLE_CATEGORY_USER) {
          assert(param.table->s->is_secondary_engine());
          iterator = NewIterator<ShannonBase::Executor::VectorizedTableScanIterator>(
              thd, mem_root, param.table, path->num_output_rows(), examined_rows, std::move(predicate), projection,
              limit, offset, use_storage_index);
        } else
          iterator = NewIterator<TableScanIterator>(thd, mem_root, param.table, path->num_output_rows(), examined_rows);
        break;
      }
      ...

namespace ShannonBase {
namespace Executor {
/**
 * VectorizedTableScanIterator - A vectorized table scan iterator that processes data in batches
 *
 * This iterator implements a vectorized execution model for table scanning, where data is
 * processed in batches rather than row-by-row. It leverages columnar storage and SIMD
 * optimizations to improve cache locality and CPU efficiency.
 */
class VectorizedTableScanIterator : public TableRowIterator {
 public:
  VectorizedTableScanIterator(THD *thd, TABLE *table, double expected_rows, ha_rows *examined_rows,
                              std::unique_ptr<Imcs::Predicate> predicate = nullptr,
                              const std::vector<uint32_t> &projection = {}, ha_rows limit = HA_POS_ERROR,
                              ha_rows offset = 0, bool use_storage_index = false);
  bool Init() override;
  int Read() override;
  /**
   * Set a filter function to be applied during scanning
   * @param filter Filter function that returns true for rows to keep
   */
  void set_filter(filter_func_t filter) { m_filter = filter; }
  size_t GetCurrentBatchSize() const { return m_batch_size; }
 private:
  size_t EstimateRowSize() const;
  /**
   * Calculate the optimal batch size based on system characteristics
   * Considers cache line size, row size, and expected row count
   * @param expected_rows Estimated number of rows to process
   * @return Optimal batch size in number of rows
   */
  size_t CalculateOptimalBatchSize(double expected_rows);
  void CacheActiveFields();
  /**
   * Preallocate memory for column chunks to avoid runtime allocations
   * Sets up the columnar storage buffers for batch processing
   */
  void PreallocateColumnChunks();
  int ReadNextBatch();
  inline void ClearBatchData() {
    for (auto &chunk : m_col_chunks) chunk.clear();
  }
  void UpdatePerformanceMetrics(std::chrono::high_resolution_clock::time_point start_time);
  /**
   * Adapt batch size based on runtime performance characteristics
   * Implements dynamic batch size adjustment for optimal performance
   */
  void AdaptBatchSize();
  /**
   * Populate the current row from batch data to MySQL row format
   * @return 0 on success, error code on failure
   */
  int PopulateCurrentRow();
  /**
   * Process field data from column chunk to MySQL field format
   * Dispatches to specialized handlers based on field type
   * @param field MySQL field structure
   * @param col_chunk Column chunk containing the data
   * @param rowid Row index within the current batch
   */
  inline void ProcessFieldData(Field *field, const ShannonBase::Executor::ColumnChunk &col_chunk, size_t rowid) {
    if (Utils::Util::is_string(field->type()) || Utils::Util::is_blob(field->type())) {
      ProcessStringField(field, col_chunk, rowid);
    } else {
      ProcessNumericField(field, col_chunk, rowid);
    }
  }
  ...
  }

  int VectorizedTableScanIterator::Read() {
  int result{ShannonBase::SHANNON_SUCCESS};
  if (m_batch_exhausted && !m_eof_reached) {
    result = ReadNextBatch();
    if (result) {
      if (result == HA_ERR_END_OF_FILE && m_curr_batch_size == 0) return HandleError(HA_ERR_END_OF_FILE);
      if (result != HA_ERR_END_OF_FILE) return HandleError(result);
    }
  }
  if (m_curr_row_in_batch >= m_curr_batch_size) {
    if (m_eof_reached) {
      return HandleError(HA_ERR_END_OF_FILE);
    } else {  // read the next batch.
      m_batch_exhausted = true;
      return Read();
    }
  }
  // fill up the data to table->field
  result = PopulateCurrentRow();
  if (result) return HandleError(result);
  // move to the next row.
  m_curr_row_in_batch++;
  m_metrics.total_rows++;
  if (m_curr_row_in_batch >= m_curr_batch_size) {
    m_batch_exhausted = true;
    if (!m_eof_reached) m_curr_row_in_batch = 0;
  }
  return result;
}
int VectorizedTableScanIterator::ReadNextBatch() {
  auto batch_start = std::chrono::high_resolution_clock::now();
  ClearBatchData();
  size_t read_cnt = 0;
  int result = m_cursor->next(m_batch_size, m_col_chunks, read_cnt);
  if (result != 0) {
    if (result == HA_ERR_END_OF_FILE) {
      m_eof_reached = true;
      if (read_cnt) {
        m_batch_exhausted = false;
        m_metrics.total_batches++;
        UpdatePerformanceMetrics(batch_start);
      }
      m_curr_batch_size = read_cnt;
      m_curr_row_in_batch = 0;
      return HA_ERR_END_OF_FILE;
    }
    if (++m_metrics.error_count > 10) return HA_ERR_GENERIC;
    if (result == HA_ERR_RECORD_DELETED && !thd()->killed) return ReadNextBatch();
    return result;
  }
  if (read_cnt == 0) {  // no data read, therefore set to EOF.
    m_eof_reached = true;
    return HA_ERR_END_OF_FILE;
  }
  m_curr_batch_size = read_cnt;
  m_curr_row_in_batch = 0;
  m_batch_exhausted = false;
  m_metrics.total_batches++;
  UpdatePerformanceMetrics(batch_start);
  AdaptBatchSize();
  return ShannonBase::SHANNON_SUCCESS;
}

V. Storage Engine Integration

Finally, the execution flow interfaces with the storage layer through ha_shannon_rapid.cc.

Handler Management: The ha_rapid plugin opens tables via the open interface. If it is a partitioned table, it acquires rpd_parttable.

Columnar Scan: Execution operators interact with the In-Memory Columnar Units (IMCU) in memory via the RapidCursor.

HTAP Synchronization Mechanism: The SelfLoadManager and Populator mentioned in the code are responsible for synchronously loading/real-time streaming row-based data from InnoDB into the columnar storage space of the Rapid engine, ensuring that HTAP queries can see the latest transactional data.

Conclusion

ShannonBase’s HTAP execution flow follows a model of “deep integration, smooth enhancement.” It retains MySQL’s consistent external interface, but internally achieves its capabilities through:

Intelligent Translation Layer: Seamlessly translate MySQL plans.
High-Performance Optimization Rules: Implementing index pruning and predicate pushdown.

Vectorized Execution Engine: Leveraging columnar storage and batch processing or vector processing to achieve a leap in analytical performance.

This design allows users to directly gain the ability to handle large-scale analytical tasks without modifying business SQL, truly realizing the integration of OLTP and OLAP.

ShannonBase is designed for data + AI
• MySQL-compatible SQL engine
• Built-in Vector & ML capabilities
• HTAP / IMCS for AI-native workloads
⭐ Star the repo
🧩 Submit PRs
🐞 Open Issues
💬 Join Discussion

Partition-Based Parallel Data Loading: An Architectural Practice for Improving Mass Data Import Efficiency

ShannonData.AI — Thu, 16 Oct 2025 03:46:01 +0000

In modern database systems, data volume is growing exponentially. How to efficiently load data from primary storage (such as InnoDB) to secondary engines (such as in-memory analytical engines, columnar engines, etc.) has become a critical performance challenge. Traditional serial loading methods often become a system bottleneck when dealing with large tables, especially partitioned tables. This article will delve into a parallel data loading scheme for partitioned tables, providing a detailed analysis of its design philosophy, implementation details, and the benefits it brings.

I. Why Parallel Loading? Drawbacks of the Traditional Approach

Traditional data loading typically employs a single-threaded sequential scan (Serial Scan). A worker thread reads each partition of the source data table in order, processes rows one by one, and then writes them to the target engine. The drawbacks of this model become glaringly obvious as data scales:

Wasted Hardware Resources: Modern servers are commonly equipped with multi-core CPUs. The single-threaded model utilizes only one core, leaving a significant amount of CPU resources idle and failing to harness the hardware’s full potential.
Significant Performance Bottleneck: The loading speed is entirely limited by the processing power of a single core and the throughput of a single I/O stream. When data volume reaches the terabyte level, the loading process can take hours, severely impacting data availability and business real-time requirements.
Poor Scalability: Even if the server is upgraded with more CPU cores, the performance of serial loading cannot be improved. This architecture lacks horizontal scalability and cannot cope with future, faster data growth demands.
Disregard for Partitioned Table Advantages: Partitioned tables physically split data into independent, individually manageable data blocks. This provides a natural foundation for parallel processing. However, traditional serial loading methods completely ignore this advantage, still treating the table as a single, massive entity.
Therefore, to break the single-point performance bottleneck, fully utilize system resources, and embrace the architectural advantages brought by data partitioning, adopting a parallel loading solution is imperative.

II. Key Architectural Design Points for Parallel Loading

Our goal is to decompose the massive monolithic task of data loading into a set of smaller tasks that can be executed in parallel. Based on this concept, we have designed the following core points:

Task Granularity: A single partition within a partitioned table is treated as the smallest unit of work for parallel processing. Loading the data of each partition is considered an independent task, making task division clear and natural.
unsigned int num_threads = std::thread::hardware_concurrency() * 0.8; if (num_threads == 0) num_threads = SHANNON_PARTS_PARALLEL;
Concurrency Model: A thread pool model is introduced. The system creates a group of worker threads based on hardware configuration (e.g., std:🧵:hardware_concurrency()). These threads atomically fetch (fetch-and-add) pending partition tasks from a shared task list, achieving dynamic load balancing.
Resource Isolation & Context Management: This is the most critical and complex aspect of the parallel design. In a database kernel environment, multiple threads operating in parallel can easily cause race conditions and state pollution. Therefore, it is essential to provide a completely isolated execution context for each worker thread. This includes:

bool PartitionLoadThreadContext::initialize(const Rapid_load_context *context) {
  // Create THD
  m_thd = new THD;
  if (!m_thd) return true;

  m_thd->set_new_thread_id();
  m_thd->thread_stack = (char *)this;
  m_thd->set_command(COM_DAEMON);
  m_thd->security_context()->skip_grants();
  m_thd->system_thread = NON_SYSTEM_THREAD;
  m_thd->store_globals();
  m_thd->lex->sql_command = SQLCOM_SELECT;

  // Open table from source table share.
  TABLE_SHARE *share = context->m_table->s;
  m_table = (TABLE *)m_thd->mem_root->Alloc(sizeof(TABLE));
  if (!m_table) return true;
  // get a copy of source TABLE object with its table share. TABLE will be used for feteching data from part tables.
  // we will clone a new handler for using multi-cursor. The invoker[mysql_secodary_load_unload] hold the refcnt
  // of shhare, here, we dont need to warry about its be released.
  if (open_table_from_share(m_thd, share, share->path.str, 0, SKIP_NEW_HANDLER, 0, m_table, false, nullptr)) {
    return true;
  }

  m_table->in_use = m_thd;
  m_table->alias_name_used = context->m_table->alias_name_used;
  m_table->read_set = context->m_table->read_set;
  m_table->write_set = context->m_table->write_set;

  return false;
}

bool PartitionLoadThreadContext::clone_handler(ha_innopart *file, const Rapid_load_context *context,
                                               std::mutex &clone_mutex) {
  std::lock_guard<std::mutex> lock(clone_mutex);
  THD *original_thd = context->m_table->in_use;
  context->m_table->in_use = m_thd;
  m_handler = static_cast<ha_innopart *>(file->clone(context->m_table->s->normalized_path.str, m_thd->mem_root));
  context->m_table->in_use = original_thd;

  if (!m_handler) return true;

  m_handler->change_table_ptr(m_table, m_table->s);
  m_table->file = m_handler;

  // Note: ha_open() is not needed because:
  // 1. ha_innopart::clone() inherits the open state from the source handler
  // 2. change_table_ptr() updates internal pointers while preserving the open state
  // 3. Partition-level operations (rnd_init_in_part/rnd_next_in_part) work directly

  return false;
}

Robust Error Handling Mechanism: In a parallel system, the failure of any single task can affect the whole. We designed a “Fast-Fail” mechanism:

Safe Resource Management: Utilize C++’s RAII (Resource Acquisition Is Initialization) idiom to ensure correct resource release. For example, the PartitionLoadHandlerLock class acquires the external lock for InnoDB in its constructor and automatically releases it in its destructor. This ensures the lock is always released, regardless of whether an exception is thrown during execution, thus avoiding deadlocks.
III. Introduction to Core Implementation Details

Based on the above design, let’s look at the specific implementation in the code:

Task Creation and Distribution: The load_innodbpart_parallel function first iterates through context->m_extra_info.m_partition_infos, creating a partition_load_task_t task structure for each partition and storing it in a tasks vector. An atomic counter std::atomic_size_t task_idx serves as the shared task index, allowing all worker threads to safely claim tasks.

unsigned int num_threads = std::thread::hardware_concurrency() * 0.8;
  if (num_threads == 0) num_threads = SHANNON_PARTS_PARALLEL;
  std::vector<partition_load_task_t> tasks;
  tasks.reserve(context->m_extra_info.m_partition_infos.size());
  for (auto &[part_name, part_id] : context->m_extra_info.m_partition_infos) {
    partition_load_task_t task;
    task.part_id = part_id;
    task.part_key = part_name + "#" + std::to_string(part_id);
    task.result = ShannonBase::SHANNON_SUCCESS;
    task.rows_loaded = 0;
    tasks.push_back(std::move(task));
  }

Thread Pool and Work Unit: A temporary thread pool is created via std::vectorstd::thread workers_pool. The core logic executed by each thread is encapsulated in the worker_func lambda expression.
Thread Context Initialization (PartitionLoadThreadContext): At the beginning of worker_func, each thread creates an instance of PartitionLoadThreadContext. Within its initialize() and clone_handler() methods, the crucial operations of creating an independent THD and cloning the ha_innopart handler are performed, ensuring that the execution environments of threads do not interfere with each other.
Single Partition Loading Logic (load_one_partition): This lambda function is the actual data mover. It receives a partition task and executes the following steps:

auto load_one_partition = [&](partition_load_task_t &task,
                                ha_innopart *task_handler) -> int {  // Lambda: load a partition.
    int result{ShannonBase::SHANNON_SUCCESS};
    task.rows_loaded = 0;
    if (task_handler == nullptr) {
      std::lock_guard<std::mutex> lock(error_mutex);
      task.error_msg = "Handler clone is null for partition " + std::to_string(task.part_id);
      task.result = HA_ERR_GENERIC;
      return HA_ERR_GENERIC;
    }
    Rapid_load_context::extra_info_t::m_active_part_key = task.part_key;
    ....
    if (task_handler->inited == handler::NONE && task_handler->rnd_init_in_part(task.part_id, true)) {
       ....
    }
    part_initialized = true;
    int tmp{HA_ERR_GENERIC};
    std::unique_ptr<uchar[]> rec_buff = std::make_unique<uchar[]>(context->m_table->s->rec_buff_length);
    memset(rec_buff.get(), 0, context->m_table->s->rec_buff_length);
    while ((tmp = task_handler->rnd_next_in_part(task.part_id, rec_buff.get())) != HA_ERR_END_OF_FILE) {
      if (tmp == HA_ERR_KEY_NOT_FOUND) break;
      ....
      auto partition_ptr = part_tb_ptr->get_partition(task.part_key);
      ...  
      // parttable is shared_ptr/unique_ptr to PartTable
      if (partition_ptr->write(context, rec_buff.get(), context->m_table->s->reclength, col_offsets.data(),
                               context->m_table->s->fields, null_byte_offsets.data(), null_bitmasks.data())) {
        std::lock_guard<std::mutex> lock(error_mutex);
        task.error_msg = "load data from " + sch_name + "." + table_name + " to imcs failed";
        task.result = HA_ERR_GENERIC;
        return HA_ERR_GENERIC;
      }
      memset(rec_buff.get(), 0, context->m_table->s->rec_buff_length);
      task.rows_loaded++;
      if (tmp == HA_ERR_RECORD_DELETED && !context->m_thd->killed) continue;
    }
    task.result = ShannonBase::SHANNON_SUCCESS;
    return result;
  };

Lock and Transaction Management (PartitionLoadHandlerLock): Within worker_func, the instantiation of PartitionLoadHandlerLock automatically performs the ha_external_lock(m_thd, F_RDLCK) operation, applying the necessary lock for data reading. When worker_func ends, the lock is automatically released via its destructor.
IV. Summary

The partition-based parallel loading scheme introduced in this article successfully transforms a time-consuming serial task into an efficient parallel workflow. Through carefully designed task granularity, thread model, resource isolation, and error handling mechanisms, this implementation not only significantly improves data loading performance and fully unleashes the potential of modern multi-core hardware but also ensures operational stability and security within the database environment.

Practice has proven that this architecture has excellent scalability: as the number of CPU cores and table partitions increases, the throughput of data loading can grow almost linearly. It serves as a typical example of combining modern C++ parallel programming techniques with database kernel knowledge, providing a mature, efficient, and reliable engineering solution for the rapid import of massive data.

ShannonBase — The Next-Gen HTAP Database for the AI Era

ShannonData.AI — Tue, 16 Sep 2025 02:28:51 +0000

ShannonBase (by Shannon Data AI) is a MySQL-compatible HTAP (Hybrid Transactional/Analytical Processing) database designed and optimized for modern big-data and AI workloads. Think of it as "MySQL for the AI era": it keeps familiar SQL and operational semantics while adding native embedding/vector support, built-in machine learning, a columnar in-memory engine, and a lightweight JavaScript runtime. The result is a unified platform where OLTP, OLAP, vector search and ML workflows can run together with minimal data movement.

https://github.com/Shannon-Data/ShannonBase

Key Design Principles

Zero Data Movement: keep data, embeddings, models and inference as close to storage as possible - inside the database - to reduce latency, cost and operational complexity.
Native ML & Vector Support: provide first-class vector types, embedding pipelines and in-DB ML training/inference primitives.
HTAP with Intelligent Routing: combine row and column engines and route work to the best engine via cost-based and ML-driven decisions.
SQL-First Developer Experience: enable data scientists and application engineers to use SQL (and optional JS stored procedures) rather than stitching many systems together.

Architecture Overview

InnoDB + Rapid (IMCS Column Store) InnoDB (row store): handles transactional, write-heavy OLTP workloads and durability.
Rapid (IMCS - In-Memory Column Store): a columnar, memory-optimized engine for analytics, aggregations and vector/semantic search.
Intelligent Workload Routing: queries are dispatched to InnoDB or Rapid using a cost model and optional ML models that learn which engine yields better performance for a given query pattern.
Version Linking & MVCC: Rapid supports MVCC via a version-linking mechanism so analytical reads observe consistent snapshots while writes occur in InnoDB.
Redo-log Synchronization: InnoDB changes are applied to Rapid by replaying redo logs (synchronously), keeping both engines consistent without ETL.

Multimodal Data Types

Structured data: classic SQL columns and indexing.
JSON: efficient JSON storage and querying (MySQL-style semantics, extended where needed).
GIS: spatial types and ST_* functions for location queries.
VECTOR: native vector column type and helper functions (eg. Distance, etc.) for embeddings and similarity search.

Native Machine Learning

Embedded model runtime: integrates LightGBM (and optionally XGBoost or other engines) so you can train and infer inside the DB. Stored procedures for ML: sys.ML_TRAIN, sys.ML_PREDICT_ROW, sys.ML_MODEL_LOAD, etc. - train models on DB tables and run predictions in place.
Model imports: load pre-trained models to save training time and standardize inference.
ONNX/ONNXRuntime support: run portable models (including small LLMs or local ONNX LLMs) where appropriate.

Retrieval-Augmented Generation (RAG) & Embeddings

Embedding routines: built-in embedding APIs and stored procedures to create and manage embedding tables.
Vector stores & RAG helpers: utilities to embed tables, perform ANN / nearest-neighbor searches, and assemble RAG inputs for LLMs (local or remote).
LLM integration: run generation via integrated routines or attach an ONNX LLM runtime for on-prem inference.

Multilingual Stored Procedures: JerryScript

JavaScript engine: JerryScript embedded for writing stored procedures and UDFs in JavaScript as an alternative to pure SQL, lowering the barrier for custom logic and preprocessing.

Benefits & Target Use Cases

Benefits
Unified platform for OLTP, OLAP, vector search and ML - reduces system sprawl.
Low latency for inference and semantic search because embeddings and models live next to the data.
Familiar SQL surface with optional JS for custom logic.
HTAP efficiency via in-memory column engine for analytics and row engine for transactional integrity.

Target Use Cases

Enterprise knowledge bases and RAG systems Real-time personalization and recommendation (online training & inference) Interactive analytics and BI over fresh transactional data Spatial analytics and location-aware services Simplified MLOps for small/medium in-DB models and feature stores

Conclusion
ShannonBase brings together a pragmatic set of features to address the needs of modern AI and analytics applications: an HTAP architecture with intelligent routing, native vector and embedding support, in-database ML training and inference, and a small JavaScript runtime for programmable extensions. For organizations that want to reduce data movement, simplify the analytics + ML stack, and deliver low-latency semantic search and in-place inference, ShannonBase offers a compelling, SQL-centric path forward.