Forem: ShannonData.AI

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.