Mastering MariaDB Spider: A Complete Guide to Distributed Database Sharding and Deployment

Introduction

The Spider storage engine, integrated in MariaDB (latest 3.2.37), provides transparent sharding, partitioning, and XA‑transaction support for MySQL/MariaDB. It creates logical links between local Spider tables and remote backend tables, routing queries automatically based on table and server rules.

Typical DBA Scenarios

Multiple tables residing on different instances need to be joined or aggregated.

A large or heavily accessed table must be split across several instances to overcome capacity or performance limits.

Solution Overview

Common approaches (middleware, MySQL partitioning, Galera, multi‑source replication, federated tables) have limitations for small‑to‑medium deployments. Spider offers a built‑in, protocol‑compatible alternative.

Spider Engine Features

Business‑level transparency – no application changes required.

Logical sharding without DBA concern for physical distribution.

Horizontal scaling beyond a single MySQL node.

Backend storage engine agnostic.

Supports hash, range, and list partitioning algorithms.

Fully compatible with MySQL protocol and plugins.

No query cache or full‑text index on Spider tables (define on backends).

Physical backups must be performed on each backend instance.

Spider itself is a single point; HA requires external solutions (e.g., VIP).

Additional network hop introduces latency, especially for cross‑partition queries.

Usage Scenarios

Vertical Partitioning

Spider can map different logical tables to distinct backend servers. Example: user_info on HostA, user_msg on HostB. Queries are routed automatically based on the mapping.

Horizontal Partitioning

Spider supports hash, range, and list partitioning. A range example partitions IDs 0‑100000 to HostA, 100000‑200000 to HostB, etc. Queries are forwarded to the appropriate backend.

Installation & Deployment

Spider is bundled with MariaDB starting from 10.0.0.4.

Install MariaDB on the Spider server and all backend MySQL servers.

Execute the engine installation script: mysql -uroot -p < install_spider.sql or source /path/install_spider.sql The script creates system tables such as spider_link_failed_log, spider_link_mon_servers, spider_tables, spider_xa, spider_xa_failed_log, and spider_xa_member.

Verify installation with

SHOW ENGINES LIKE 'SPIDER';

Practical Test Setup

Assume two backend DB servers (10.128.128.60 and 10.128.128.88) and a Spider server (10.128.128.91).

Grant access for the Spider server:

GRANT ALL ON *.* TO spider_db_all@'10.128.128.91' IDENTIFIED BY 'spider_db_all';

Create server objects:

CREATE SERVER backend1 FOREIGN DATA WRAPPER mysql OPTIONS (host '10.128.128.60', database 'test', user 'spider_db_all', password 'spider_db_all', port 3306);

CREATE SERVER backend2 FOREIGN DATA WRAPPER mysql OPTIONS (host '10.128.128.88', database 'test', user 'spider_db_all', password 'spider_db_all', port 3306);

Base table on each backend (run on both backends):

CREATE TABLE test_spider (id INT, username VARCHAR(20), address VARCHAR(128), PRIMARY KEY (id), KEY (username)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'spider test base table';

Vertical Table Example

CREATE TABLE test_spider (id INT, username VARCHAR(20), address VARCHAR(128), PRIMARY KEY (id), KEY (username)) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='server "backend1"';

CRUD operations on this table are routed to backend1.

Hash Partitioned Table

CREATE TABLE test_spider (id INT, username VARCHAR(20), address VARCHAR(128), PRIMARY KEY (id), KEY (username)) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "test_spider"' PARTITION BY HASH (id) (PARTITION pt1 COMMENT='srv "backend1"', PARTITION pt2 COMMENT='srv "backend2"');

Range Partitioned Table

CREATE TABLE test_spider (id INT, username VARCHAR(20), address VARCHAR(128), PRIMARY KEY (id), KEY (username)) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "test_spider"' PARTITION BY RANGE COLUMNS (id) (PARTITION pt1 VALUES LESS THAN (100000) COMMENT='srv "backend1"', PARTITION pt2 VALUES LESS THAN (200000) COMMENT='srv "backend2"');

List Partitioned Table

CREATE TABLE test_spider (id INT, username VARCHAR(20), address VARCHAR(128), PRIMARY KEY (id), KEY (username)) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "test_spider"' PARTITION BY LIST COLUMNS (id) (PARTITION pt1 VALUES IN (1,3,5,7,9) COMMENT='srv "backend1"', PARTITION pt2 VALUES IN (2,4,6,8,10) COMMENT='srv "backend2"');

After each DDL, perform INSERT/SELECT/UPDATE/DELETE to verify that operations affect the intended backend. Dropping the Spider table does not delete the underlying backend tables.

Performance Testing

Performance can be measured with sysbench, comparing Spider against a single MySQL instance and a multi‑node backend configuration. Detailed methodology and results are available in the MariaDB documentation.

References

https://mariadb.com/kb/en/mariadb/spider-storage-engine-overview/

https://mariadb.com/kb/en/mariadb/spider/

https://mysqlstepbystep.com/2015/04/03/spider-for-mysql-overview/