How to Use MariaDB Spider for Horizontal & Vertical Sharding

What Is Spider?

Spider is a pluggable storage engine built into MariaDB that acts as a middleware proxy between the application server and remote backend MySQL/MariaDB instances, enabling horizontal and vertical scaling, range/list/hash partitioning, XA distributed transactions, and cross‑database joins without changing application code.

Typical Use Case – Tencent Games

In production, Tencent Games stores 100 TB of data across 396 Spider nodes, which in turn distribute the data to 2 800 MySQL nodes.

Vertical Sharding Scenario

When historical tables become large and are archived to backup servers, BI queries may still need temporary access. Using Spider, a remote table can be mapped as a local “soft link,” allowing immediate queries without moving data back to the primary server.

Installation

Run the following command on the target server (MariaDB 10 required):

mysql -uroot -p < /usr/local/mysql/share/install_spider.sql

Verify the engine is loaded:

SELECT engine, support, transactions, xa FROM information_schema.engines;

Configuration – Vertical Sharding

Define a backend server and create a table that points to the remote table using the COMMENT clause.

CREATESERVER backend1
  FOREIGN DATA WRAPPER mysql
  OPTIONS(
    HOST '192.168.143.205',
    DATABASE 'test',
    USER 'user_readonly',
    PASSWORD '123456',
    PORT 3306
  );

CREATETABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql",table "sbtest",srv "backend1"';

Configuration – Horizontal Partitioning

The same table definition can be extended with LIST partitioning to achieve sharding across multiple backends.

CREATETABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql",table "sbtest"'
PARTITION BY LIST (mod(id,2))
(
  PARTITION p0 VALUES IN (0) COMMENT='srv "backend1"' ENGINE=SPIDER,
  PARTITION p1 VALUES IN (1) COMMENT='srv "backend2"' ENGINE=SPIDER
);

Tunable Parameters

spider_conn_recycle_mode=1 – enables connection reuse similar to a pool.

optimizer_switch='engine_condition_pushdown=on' – pushes predicates to the remote backend for aggregation.

Load‑Balancing Architecture

Because Spider stores only routing information, multiple Spider instances can be deployed behind a load balancer for high availability. Backend MySQL servers can be protected with MHA for automatic failover.

Performance Observations

Official benchmarks show a 70 % slowdown for pure sharding and a 40 % slowdown for vertical splitting due to the overhead of 2‑phase commit and cross‑network traffic. The current RC version (3.2.37) should be used cautiously in production.

References

https://m17.mariadb.com/session/deep-dive-supporting-distributed-data-spider

https://www.percona.com/live/17/sessions/using-spider-sharding-production

https://www.percona.com/blog/2017/04/12/percona-live-2017-many-technical-mariadb-server-sessions/