Leveraging Apache Ignite as a Memory‑Centric Data Platform to Replace MySQL for High‑Performance Data Synchronization

Introduction Ignite is a memory‑centric data platform that offers strong consistency, high availability, and standard SQL capabilities. Since joining the Apache project in 2015, Ignite has progressed to version 2.7, with the article recommending version 2.6 (which supports the COPY keyword). The Ignite compute grid is used to replace MySQL join queries, thereby reducing pressure on the business database and improving platform efficiency.

1. Application Scenario – Data Cloud and Caching Layer As business databases grow, full‑extract updates and dimension‑table joins on MySQL cause severe pressure, with some SQL joins taking more than 20 minutes. By redesigning the data processing logic and introducing Ignite, minute‑level data synchronization is achieved, cutting the sync interval from hourly to 15 minutes and boosting performance by four times while relieving MySQL load.

2. Architecture Comparison

Previous Architecture

MySQL join queries caused >20‑minute latency.

File read/write: Spark converted CSV to ORC and wrote to Hive tables.

Data distribution: Hive wrote to HDFS, BulkLoad loaded data into HBase, and Phoenix performed business joins.

Optimized Ignite Architecture

MySQL join queries are moved to Ignite, whose in‑memory joins outperform MySQL.

File I/O is eliminated; MySQL binlog is streamed into Kafka to update Ignite tables in real time, avoiding CSV‑to‑ORC conversion.

Ignite data is also distributed to Hive and Phoenix for business queries.

3. Detailed Architecture Diagram

4. Ignite SQL Data‑Grid Advantages

Distributed Query Ignite reuses the H2 SQL parser, optimizer, and execution planner. H2 executes localized queries on specific nodes, and results are aggregated by the Ignite SQL engine. Data and indexes reside on the Ignite data grid, which executes distributed, fault‑tolerant SQL queries.

Ignite SQL Grid Execution For REPLICATED caches, Ignite assumes all data is local and hands the query to H2 for simple execution. For PARTITIONED caches, the query is split into multiple map queries and a reduce query; each node processes its portion locally, streams sorted results for ORDER BY/GROUP BY, and the reducer merges them without loading the full result set into memory.

Transactional Support Ignite provides atomic, cross‑cache, and near‑cache transactions, two‑phase commit (2PC), various isolation levels, pessimistic and optimistic modes, dead‑lock detection, and lock‑free transactions.

5. Existing Shortcomings and Solutions

SQL function and syntax differences from MySQL (e.g., collect_set ) require rewriting or custom functions.

Enabling multiple backups and disk storage reduces performance; disabling disk sync improves speed but raises data‑safety concerns, prompting the development of efficient data‑loading modules for faster recovery.

Metadata management between MySQL and Ignite, and Kafka task orchestration, are addressed by using a data‑cloud metadata service and building task‑management modules (start, monitor, auto‑restart).

Batch import/export: version 2.6 supports the COPY command for initial data load; binlog parsing via Kafka updates Ignite; client‑side export to CSV uses !outputformat csv .

6. Future Planning Ignite’s current stability does not yet match that of widely adopted data‑warehouse architectures, limiting large‑scale deployment. The roadmap includes building a 10‑node Ignite cluster, expanding cluster size, and gradually supporting more complex, larger‑scale business scenarios.