The worst sharding system I ever maintained was the one we wrote ourselves. A routing table in application config, a shard-resolver library someone had written in a hurry, and an unbreakable rule that no query was ever allowed to cross shards — right up until finance needed a report that crossed shards. We ended up with a cron job that dumped five databases nightly and merged them in a script, and every schema change became five coordinated deployments plus a prayer. A few years later I inherited a MariaDB estate that achieved the same result with the Spider storage engine and exactly zero lines of routing code, and I finally understood why people tolerate its sharp edges.
Spider is MariaDB's answer to "one table, many servers": a storage engine that stores nothing itself. A Spider table is a local definition whose partitions point at real tables on remote MariaDB backends, and the server routes reads and writes to the right backend over ordinary client connections. This post is the operator's version: how to set it up, when it beats sharding in the application, the limits that bite in production, and how to watch a topology that actively hides its own problems.
What Spider actually is
Spider has shipped with MariaDB since the 10.0 era, bundled but disabled. You load it as a plugin, declare each backend once as a foreign server, and then create a partitioned table where every partition's COMMENT names the server that owns it. The rows themselves live in perfectly ordinary InnoDB tables on the backends — Spider holds no data, only metadata and connections.
INSTALL SONAME 'ha_spider';
CREATE SERVER shard1
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST '10.0.1.11', PORT 3306, DATABASE 'app', USER 'spider', PASSWORD 's3cret');
CREATE SERVER shard2
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST '10.0.1.12', PORT 3306, DATABASE 'app', USER 'spider', PASSWORD 's3cret');
CREATE TABLE orders (
order_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
KEY (customer_id)
) ENGINE=SPIDER
COMMENT 'wrapper "mysql", table "orders"'
PARTITION BY HASH (customer_id MOD 2) (
PARTITION p0 COMMENT 'server "shard1"',
PARTITION p1 COMMENT 'server "shard2"'
);Two things to notice in that example. First, Spider does not create the backend tables for you: app.orders must already exist on each backend, in InnoDB, with a compatible definition. If the shapes drift apart you get runtime errors or, worse, silent column mismatches. Second, the credentials live in the server definition in plain view of anyone who can read the data dictionary, so use a dedicated low-privilege account that can reach only the tables Spider needs, and lock down who can CREATE SERVER on the spider node.
How a query actually flows
When a query includes the partition key, the partition pruning machinery does its usual job and Spider forwards the statement to the one backend that owns the matching partition, rewrites it against the remote table name, and streams the rows back. A point lookup by customer costs one network round trip plus the backend's normal execution time — an added hop, not an added order of magnitude. WHERE conditions and LIMIT are pushed down to the backends, so the spider node is not dragging full tables across the wire just to filter them locally.
Queries without the partition key fan out to every partition, and current Spider versions can run those partition scans in parallel rather than serially — but only if you ask for it: background search is off by default (spider_bgs_mode=0), so parallel partition reads need spider_bgs_mode=1. With it enabled, a cross-shard report can genuinely finish faster than the same scan on a single overloaded server. The catch is what happens after the rows arrive: grouping, sorting, and joining are assembled on the spider node from whatever came back. Simple filtered scans scale with the backends; anything that needs a global view materializes intermediate rows on the spider node, with the memory and temp-table costs that implies.
Writes follow the same map. An insert routes to the owning partition's backend and commits there. A transaction that touches several shards is where honesty is required: with spider_support_xa enabled — it defaults to on — Spider wraps the backend writes in XA and commits them with two-phase commit, which gives you real atomicity across shards. (The related spider_internal_xa knob only chooses where the XA coordination lives; it is not the on/off switch.) It also inherits XA's classic failure mode — a backend that dies between prepare and commit leaves a prepared transaction sitting on that server, and sooner or later you will be staring at XA RECOVER deciding whether to commit or roll back by hand. Without XA, a multi-shard write is just a row of independent commits, and a mid-transaction failure leaves some shards written and some not. Pick your poison deliberately.
Where Spider beats app-level sharding
Having run both, I would reach for Spider in these situations:
- A monolith that expects one database. If the application was never written to route, sharding underneath it with Spider avoids the router library, the config maps, and the years of "which shard is customer 9182 on" debugging. The app keeps one connection string.
- Reporting that must span shards. App-level sharding answers cross-shard questions with dump-and-merge scripts. Spider answers them with SQL. The query may be slower than a purpose-built pipeline, but it exists the day you need it.
- Federation of existing servers. Read-mostly tables that live on legacy instances can be surfaced through one Spider node without migrating anything first, which is a fine way to strangle a monolith gradually.
- Resharding and migration windows. Because the spider node is the constant endpoint, you can move backend tables, add a shard, and re-partition with the application pointing at the same place the whole time.
The counterargument is just as real: app-level sharding gives you explicit control, no single SQL chokepoint, and failure domains you can reason about. If you are building a new system with a small team that owns the data layer end to end, routing in the app is often the more honest design. Spider shines when the app already exists and cannot be rewritten on your schedule.
The limits nobody puts in the README
- Cross-shard joins are expensive. There is no co-located join pushdown magic: joining two tables sharded on different keys means pulling rows from multiple backends to the spider node and joining them there. A join that touches a hundred million rows across shards moves that data over the network. Shard by the key your hot joins use.
- No global uniqueness. AUTO_INCREMENT values come from whichever backend owns the partition, so the same id can legitimately appear on two shards. Use application-generated ids, UUIDs, or a dedicated id service — do not let backend auto-increment be your identity scheme.
- DDL does not propagate. An ALTER on the Spider table changes the local definition only. The backend tables must be altered yourself, per shard, which is exactly the multi-deployment problem Spider was supposed to solve — the spider_direct_sql UDF helps push statements to backends, but the coordination is still yours.
- The spider node is a single point of failure and a latency adder. Every query pays at least one extra network hop, and if the spider node dies, every shard is unreachable through it. High availability at the spider layer is your problem too.
- Backups lose their global meaning. Each backend backs up independently, and a consistent cross-shard snapshot needs GTID coordination or a quiet window. Point-in-time recovery across shards is a research project, not a checkbox.
Monitoring a topology that hides its own problems
The failure mode that makes Spider operationally tricky is that the spider node's counters can look perfectly healthy while a backend is dying. A "slow query on the spider" is almost always a slow query on one backend plus merge overhead, and the only place that truth exists is on the backend itself — its processlist will show the forwarded SQL arriving from the spider user, which is where your slow query logging and lock monitoring belong. Every backend is a first-class MariaDB instance with its own buffer pool, its own replication lag if it has replicas, its own disk: monitor it like one, not like a partition.
On the spider node itself, watch the Spider_* status counters, connection failures to backends in the error log, and the timeout knobs (spider_conn_wait_timeout and friends) that decide how long a sick backend gets to stall a query before the error surfaces. A backend that flaps its network connection will show up as intermittent query errors on the spider long before it shows up as a down host anywhere else.
Watching N+1 servers that pretend to be one
Every lesson in this post lands on the same operational rule: no backend goes ungraphed. The spider node's counters can look serene while a shard is dying, so per-backend slow logs, Spider_* counters, and backend connection failures in the error log are the real early warning. MonPG, the product I work on, monitors PostgreSQL today; MariaDB support is coming soon — in active development now, and multi-instance shapes like Spider's are the reason it trends every backend as its own server instead of averaging them into one healthy-looking line. If PostgreSQL shares your fleet, that per-server discipline already exists there — the PostgreSQL overview shows the workflow, and the engine comparison plus the blog carry the rest of the field notes.