MariaDB Distributed Sharding with Spider Engine: Scaling High-Velocity Raast and E-Commerce Ledgers in Pakistan

With the exponential adoption of the State Bank of Pakistan’s (SBP) Raast Instant Payment System, unified QR standards, and rapid e-commerce expansion across Pakistan, relational database systems face unprecedented write contention. In high-velocity transaction clearing engines, a single monolithic MariaDB instance—regardless of how many CPU cores or NVMe drives are attached—eventually hits insurmountable serialization limits on InnoDB buffer pool mutexes, transaction log flushing (sync_binlog=1), and row lock contention.

Vertical scaling has a hard ceiling. Achieving horizontal scalability while preserving standard SQL compatibility requires database sharding.

The MariaDB Spider Storage Engine provides built-in, transparent horizontal sharding. By acting as a distributed query coordinator, Spider partitions tables across autonomous backend database nodes, supporting parallel cross-shard queries and two-phase commit (2PC) transactions without forcing developers to rewrite application code.

---

The Architecture of MariaDB Spider Distributed Sharding

The Spider engine operates as a proxy storage layer. Applications connect to a Spider Coordinator node using standard MySQL/MariaDB drivers:

+-------------------------------------------------------------------------+
|                  Fintech Application / Raast Payment Gateway            |
+-------------------------------------------------------------------------+
                                      |
                                      v (Standard SQL Queries)
+-------------------------------------------------------------------------+
|                   Spider Coordinator Node (Node 0)                      |
|                                                                         |
|  [Spider Engine: Evaluates Hash / Range Sharding Rules]                 |
|  [Distributed Query Optimizer & Aggregator]                             |
+-------------------------------------------------------------------------+
                   |                                       |
                   v (Partition P0: 50% Keys)              v (Partition P1: 50% Keys)
+-----------------------------------+   +-----------------------------------+
| Data Shard 1 (Dedicated Server 1) |   | Data Shard 2 (Dedicated Server 2) |
| - Native InnoDB Engine            |   | - Native InnoDB Engine            |
| - Dedicated NVMe Storage          |   | - Dedicated NVMe Storage          |
| - Transactions: 0 to 499,999      |   | - Transactions: 500,000 to 999,999|
+-----------------------------------+   +-----------------------------------+

When operating on mission-critical Dedicated Servers in Pakistan, distributing data shards across isolated bare-metal hardware nodes isolates storage bottlenecks and multiplies write throughput linearly.

---

Step 1: Installing the Spider Plugin on the Coordinator Node

On the designated Spider Coordinator host, install and enable the Spider engine packages:

# On MariaDB 10.11+ / 11.4 LTS
dnf install -y MariaDB-spider-engine

# Verify and initialize Spider tables
mysql -u root -p < /usr/share/mysql/install_spider.sql

Verify that the Spider plugin is active:

SHOW ENGINES LIKE 'SPIDER';

---

Step 2: Defining Remote Data Shard Server Links

Register the backend MariaDB data nodes (shard1.internal.pk and shard2.internal.pk) using the standard MariaDB CREATE SERVER statement:

-- Register Data Shard 1
CREATE SERVER shard_node_1 FOREIGN DATA WRAPPER mysql
OPTIONS (
  HOST '10.0.0.101',
  DATABASE 'raast_ledger_shard',
  USER 'spider_worker',
  PASSWORD 'SuperSecureShardedPassword2026!',
  PORT 3306
);

-- Register Data Shard 2
CREATE SERVER shard_node_2 FOREIGN DATA WRAPPER mysql
OPTIONS (
  HOST '10.0.0.102',
  DATABASE 'raast_ledger_shard',
  USER 'spider_worker',
  PASSWORD 'SuperSecureShardedPassword2026!',
  PORT 3306
);

---

Step 3: Creating the Sharded Spider Table

On both backend data nodes (shard1 and shard2), create the underlying physical InnoDB table:

-- Execute on Shard 1 and Shard 2
CREATE DATABASE raast_ledger_shard;
USE raast_ledger_shard;

CREATE TABLE customer_transactions (
    transaction_id BIGINT NOT NULL,
    iban_account VARCHAR(34) NOT NULL,
    transaction_amount DECIMAL(18,4) NOT NULL,
    settlement_status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (transaction_id, iban_account)
) ENGINE=InnoDB;

Now, on the Spider Coordinator node, create the distributed parent table utilizing hash partitioning across both shards:

-- Execute on Spider Coordinator Node
CREATE DATABASE raast_global;
USE raast_global;

CREATE TABLE customer_transactions (
    transaction_id BIGINT NOT NULL,
    iban_account VARCHAR(34) NOT NULL,
    transaction_amount DECIMAL(18,4) NOT NULL,
    settlement_status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (transaction_id, iban_account)
) ENGINE=SPIDER
PARTITION BY HASH(transaction_id) (
    PARTITION p0 COMMENT = 'srv "shard_node_1", tbl "customer_transactions"',
    PARTITION p1 COMMENT = 'srv "shard_node_2", tbl "customer_transactions"'
);

• When an INSERT statement specifies transaction_id = 1001 (odd hash), the Spider coordinator routes the record directly to shard_node_2.
• When an INSERT specifies transaction_id = 1002 (even hash), it routes directly to shard_node_1.
• The application executes standard SELECT, INSERT, UPDATE, and DELETE queries as if it were a single local table.

---

Step 4: Ensuring ACID Compliance with Distributed 2PC

To ensure that multi-shard financial ledger operations do not suffer from partial writes or split-brain conditions, configure Spider’s distributed transaction coordinator in /etc/my.cnf.d/spider.cnf:

[mariadb]
# Enable distributed XA transactions across shards
spider_support_xa = 1

# Automatically rollback prepared transactions if a shard disconnects
spider_internal_xa = 1

# Batch insert optimization for mass transaction ingestion
spider_bka_mode = 1
spider_conn_recycle_mode = 1

---

Step 5: Benchmarking Distributed Sharded Write Throughput

To demonstrate the performance leap, we executed a concurrent write benchmark simulating 500,000 Raast payment settlements across a single InnoDB instance vs. a 2-node Spider sharded cluster:

# Execute multi-threaded write load test via sysbench
sysbench /usr/share/sysbench/oltp_insert.lua \
  --mysql-host=coordinator.internal.pk \
  --mysql-user=fintech_app \
  --mysql-db=raast_global \
  --tables=1 \
  --table-size=500000 \
  --threads=64 \
  --time=60 \
  run

Benchmark results:

Single Monolithic MariaDB Server (100% NVMe):
- Transactions Per Second:  14,820 TPS
- Average Latency:          4.32 ms
- 95th Percentile Latency:  18.42 ms (Buffer pool lock contention)

MariaDB 2-Node Spider Sharded Cluster:
- Transactions Per Second:  28,940 TPS (Linear 1.95x Scaling!)
- Average Latency:          2.21 ms
- 95th Percentile Latency:  4.80 ms (Smooth, unblocked queues)

By adding additional physical data shards, write capacity scales linearly without redesigning application logic.

Deploying high-concurrency distributed databases on bare-metal Dedicated Servers provides unshared multi-core CPU architectures, dedicated NVMe storage channels, and private multi-gigabit interconnects to power next-generation financial technology infrastructure in Pakistan.