Deploy Spider Sharded Topology
Enterprise Server 10.4
Enterprise Server 10.5
Enterprise Server 10.6
Enterprise Server 11.4
Shard tables for horizontal scalability
Spider Node uses Spider storage engine for Sharded Spider Tables
Sharded Spider Table is a partitioned "virtual" table
Spider uses MariaDB foreign data wrapper to query Data Tables on Data Nodes for each partition
This procedure describes the deployment of the Spider Sharded topology with MariaDB Enterprise Server.
This procedure incrementally deploys MariaDB Enterprise Spider on an existing MariaDB Enterprise Server deployment.
In the Spider Sharded topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When a Spider Table is queried in this topology, the Enterprise Spider storage engine uses a MariaDB foreign data wrapper to read from and write to Data Tables on Data Nodes. The data for the Spider Table is partitioned among the Data Nodes using the regular partitioning syntax.
This procedure has 3 steps, which are executed in sequence.
This page provides an overview of the topology, requirements, and deployment procedure.
The topology described is representative of basic product capabilities. MariaDB products can be deployed to form other topologies, leverage advanced product capabilities, or combine the capabilities of multiple topologies.
If you have not yet deployed MariaDB Enterprise Server on the Spider Node and Data Nodes, first deploy a topology containing MariaDB Enterprise Server. Several are documented.
Customers can obtain support by .
The following components are deployed during this procedure:
In the Spider Sharded topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When a Spider Table is queried, the Enterprise Spider storage engine uses a MariaDB foreign data wrapper to read from and write to Data Tables on Data Nodes. The data for the Spider Table is partitioned among the Data Nodes using the regular partitioning syntax.
The Spider Sharded topology consists of:
One MariaDB Enterprise Server node is a Spider Node
One or more MariaDB Enterprise Server nodes are Data Nodes
The Spider Node:
Contains one or more Spider Tables
Uses the plugin for Spider Tables
Uses a MariaDB foreign data wrapper to query the Data Tables on the Data Nodes
The Data Nodes:
Contain Data Tables for one or more partitions of the Spider Table
Uses a non-Spider storage engine for each Data Table, such as or
For additional information, see "".
These requirements are for the Spider Sharded topology when deployed with MariaDB Enterprise Server
One or more MariaDB Enterprise Server nodes must be deployed as Spider Nodes. The Spider Nodes contain Spider Tables.
One or more MariaDB Enterprise Server nodes must be deployed as Data Nodes. The Data Nodes contain Data Tables.
In alignment to the , the Spider Sharded topology with MariaDB Enterprise Server is provided for:
AlmaLinux 8 (x86_64, ARM64)
AlmaLinux 9 (x86_64, ARM64)
Debian 11 (x86_64, ARM64)
Debian 12 (x86_64, ARM64)
Navigation in the procedure "Deploy Spider Sharded Topology":
Next: Step 1: Install Enterprise Spider
A Spider Table is a virtual table that does not store data. When a Spider Table is queried, the uses foreign data wrappers to read from and write to Data Tables on Data Nodes or ODBC Data Sources.
Red Hat Enterprise Linux 8 (x86_64, ARM64)
Red Hat Enterprise Linux 9 (x86_64, PPC64LE, ARM64)
Red Hat UBI 8 (x86_64, ARM64)
Rocky Linux 8 (x86_64, ARM64)
Rocky Linux 9 (x86_64, ARM64)
SUSE Linux Enterprise Server 15 (x86_64, ARM64)
Ubuntu 20.04 LTS (x86_64, ARM64)
Ubuntu 22.04 LTS (x86_64, ARM64)
Ubuntu 24.04 LTS (x86_64, ARM64)
Supports transactions
Enterprise Server 10.3+, Enterprise Spider
Step 1
Step 2
Step 3
Modern SQL RDBMS with high availability, pluggable storage engines, hot online backups, and audit logging.
Storage engine used by Spider Tables to read from and write to Data Tables using the MariaDB foreign data wrapper.
Data Node
A Data Node is a MariaDB Enterprise Server node that contains one or more Data Tables.
Data Table
A Data Table stores data for a Spider Table. When a Spider Table is queried, the Enterprise Spider storage engine uses the MariaDB foreign data wrapper to read from and write to the Data Table on a Data Node. The Data Table must be created on the Data Node with the same structure as the Spider Table. The Data Table must use a non-Spider storage engine, such as InnoDB or ColumnStore.
ODBC Data Source
An ODBC Data Source relies on an ODBC Driver and an ODBC Driver Manager to query an external data source.
ODBC Driver
An ODBC Driver is a library that integrates with a ODBC Driver Manager to query an external data source.
ODBC Driver Manager
An ODBC Driver Manager allows applications to use ODBC Drivers.
Spider Node
A Spider Node is a MariaDB Enterprise Server node that contains one or more Spider Tables.
Spider Node
This page details step 1 of the 3-step procedure "Deploy Spider Sharded Topology".
This step installs the Enterprise Spider storage engine plugin on the Spider Node.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
MariaDB Enterprise Spider depends on interconnect between the Spider Node and all Data Nodes. This may require adjustment to firewall and security settings.
The plugin is not installed with MariaDB Enterprise Server by default. An additional package must be installed.
On the Spider Node, install MariaDB Enterprise Spider:
Install via APT (Debian, Ubuntu)
On the Spider Node, install MariaDB Enterprise Spider:
On the Spider Node, install MariaDB Enterprise Spider:
The plugin must be loaded by MariaDB Enterprise Server
On the Spider Node, use one of the following methods to configure MariaDB Enterprise Server to load the Enterprise Spider storage engine plugin:
On the Spider Node, set the plugin_load_add option to ha_spider in a configuration file. The plugin_load_add option configures MariaDB Enterprise Server to load the Enterprise Spider storage engine plugin. The Spider Node must be restarted to detect the configuration change.
Choose a configuration file for custom changes to system variables and options. It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after. Ensure that your custom changes will be read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name. Some example configuration file paths for different distributions are shown in the following table:
Set the plugin_load_add option in the configuration file.
It must be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].
For example:
Restart MariaDB Enterprise Server:
On the Spider Node, execute the statement with the library name ha_spider. The INSTALL SONAME statement configures MariaDB Enterprise Server to load the Enterprise Spider storage engine plugin. The INSTALL SONAME statement requires the SUPER privilege.
The INSTALL SONAME statement adds the Enterprise Spider storage engine to the system table. When the Spider Node is restarted, MariaDB Enterprise Server reads the system table and reloads the plugin, so the statement only needs to be executed once.
Connect to the Spider Node using MariaDB Client:
Use the INSTALL SONAME statement to install the Enterprise Spider storage engine plugin:
On the Spider Node, confirm that the Enterprise Spider storage engine plugin is loaded by querying the information_schema.PLUGINS table:
When the Enterprise Spider storage engine is loaded, the PLUGIN_NAME column contains the value SPIDER and the PLUGIN_STATUS column contains the value ACTIVE.
Navigation in the procedure "Deploy Spider Sharded Topology":
This page was step 1 of 3.
Next: Step 2: Configure Spider Node and Data Nodes.
Shell
SQL access is not required
SUPER privilege is not required
Configuration file can be version controlled
SQL
Shell access is not required
File system privileges on configuration file are not required
Plugin is included in backup of mysql.plugin system table
Spider Node restart is not required
CentOS
RHEL
Rocky Linux
SLES
/etc/my.cnf.d/z-custom-mariadb.cnf
Debian
Ubuntu
/etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf
$ sudo yum install MariaDB-spider-engine$ sudo apt install mariadb-plugin-spider$ sudo zypper install MariaDB-spider-engine[mariadb]
...
plugin_load_add = "ha_spider"$ sudo systemctl restart mariadb$ sudo mariadbINSTALL SONAME "ha_spider";SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_LIBRARY LIKE "ha_spider%";
+--------------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+--------------------------+---------------+
| SPIDER | ACTIVE |
| SPIDER_ALLOC_MEM | ACTIVE |
| SPIDER_WRAPPER_PROTOCOLS | ACTIVE |
+--------------------------+---------------+This page details step 3 of the 3-step procedure "Deploy Spider Sharded Topology".
This step tests MariaDB Enterprise Spider.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
Use Systemd to test whether the MariaDB Enterprise Server service is running.
This action is performed on the Spider Node and each Data Node.
Check if the MariaDB Enterprise Server service is running by executing the following:
If the service is not running on any node, start the service by executing the following on that node:
Use to test the local connection to the Enterprise Server node.
This action is performed on the Spider Node and each Data Node:
The sudo command is used here to connect to the Enterprise Server node using the root@localhost user account, which authenticates using the unix_socket authentication plugin. Other user accounts can be used by specifying the --user and --password command-line options.
Use to test a client connection to the Data Node from the Spider Node using the Spider user.
This action is performed on the Spider Node:
The host and port of the Data Node can be provided using the --host and --port command-line options. The credentials for the Spider user can be provided using the --user and --password command-line options.
If the Spider user is unable to connect to the Data Node from the Spider Node, check the password for the Spider user account on the Data Node.
For additional information, see "".
Query the information_schema.PLUGINS table to confirm that the Enterprise Spider storage engine is loaded.
This action is performed on the Spider Node.
Execute the following query:
The PLUGIN_STATUS column for each Spider-related plugin should contain ACTIVE.
For additional information, see "".
Write to the Spider Table using an statement to test write operations.
This action is performed on the Spider Node.
Execute the following query:
Read from the Spider Table using a statement to test read operations.
This action is performed on the Spider Node.
Execute the following query:
Use the statement with a statement to determine which shards Spider will read for the query.
This action is performed on the Spider Node.
Execute the following query:
The specific shards read by the query are listed in the partitions column. If partition pruning does not eliminate unnecessary shards for a query with a restrictive filter, then check the partition definitions.
Navigation in the procedure "Deploy Spider Sharded Topology":
This page was step 3 of 3.
This procedure is complete.
This page details step 2 of the 3-step procedure "Deploy Spider Sharded Topology".
This step configures the Spider Node and Data Nodes and creates the Spider Table and Data Tables.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
The data node requires a user account that the Spider Node uses to connect.
On each Data Node, create the Spider user account for the Spider Node using the statement:
Privileges will be granted to the user account in .
On the Spider Node, confirm that the Spider user account can connect to the Data Node using MariaDB Client:
The Spider Node requires connection details for each Data Node.
On the Spider Node, create a server object to configure the connection details for each Data Node using the statement:
Create a Server object to configure the connection details for the Data Node at the headquarters branch:\
Create a server object to configure the connection details for the Data Node at the eastern branch:\
Create a server object to configure the connection details for the Data Node at the western branch:
The Data Node runs MariaDB Enterprise Server, so the FOREIGN DATA WRAPPER is set to mariadb.
Using a server object for connection details is optional. Alternatively, the connection details for the Data Node can be specified in the COMMENT table option of the statement when .
When queries read and write to a Spider Table, Spider reads and writes to the Data Tables for each partition on the on the Data Nodes. The Data Tables must be created on the Data Nodes with the same structure as the .
If your Data Tables already exist, to the Spider user.
On each Data Node, create the Data Tables:
On the Data Node for the headquarters server, create a database and table and add sample data:
The Spider Node reads and writes to the Data Table using the server and user account configured in "". The user account must have .
On the Data Node for the eastern branch of the business, create a database and table and add sample data:\
The Spider Node reads and writes to the Data Table using the server and user account configured in "". The user account must have .\
On the Data Node for the western branch of the business, create a database and table and add sample data:\
The Spider Node connects to the Data Nodes with the user account configured in "".
On each Data Node, grant the Spider user sufficient privileges to operate on the Data Table:
By default, the Spider user also requires the CREATE TEMPORARY TABLES privilege on the database containing the Data Table. The CREATE TEMPORARY TABLES privilege is required, because Spider uses temporary tables to optimize read queries when Spider BKA Mode is 1.
Spider BKA Mode is configured using the following methods:
The session value is configured by setting the system variable on the Spider Node. The default value is -1. When the session value is -1, the value for each is used.
The value for each Spider Table is configured by setting the bka_mode option in the COMMENT table option. When the bka_mode option is not set, the implicit value is 1.
The default value is -1, and the implicit Spider Table value is 1, so the default Spider BKA Mode is 1.
On the Data Node, grant the Spider user the CREATE TEMPORARY TABLES privilege on the database:
The Spider Table must be created on the Spider Node with the same structure as the . The Spider Table must have a partition for each Data Table.
On the Spider Node, create the Spider Table and reference the Data Node in the COMMENT table option:
The COMMENT partition option is used to configure the Data Node and the Data Table for each partition. Set the server option to the server object for the partition configured in "". Set the table option to the for the partition.
An alternative syntax is available. When you don't want to create a server object, the connection details for the Data Nodes can be specified in the COMMENT partition option:
Navigation in the procedure "Deploy Spider Sharded Topology":
This page was step 2 of 3.
Next: Step 3: Test Spider Sharded Topology.
$ systemctl status mariadb$ sudo systemctl start mariadb$ sudo mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 11.4.5-3-MariaDB-Enterprise MariaDB Enterprise Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>$ mariadb \
--host 192.0.2.2 \
--user spider_user \
--password
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 11.4.5-3-MariaDB-Enterprise MariaDB Enterprise Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_LIBRARY LIKE 'ha_spider%';
+--------------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+--------------------------+---------------+
| SPIDER | ACTIVE |
| SPIDER_ALLOC_MEM | ACTIVE |
| SPIDER_WRAPPER_PROTOCOLS | ACTIVE |
+--------------------------+---------------+INSERT INTO spider_hq_sales.invoices
(branch_id, invoice_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES (1, 4, 1, '2021-03-10 12:45:10', 3045.73, 'CREDIT_CARD');SELECT * FROM spider_sharded_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
| 1 | 4 | 1 | 2021-03-10 12:45:10.000000 | 3045.73 | CREDIT_CARD |
| 2 | 1 | 2 | 2020-05-10 12:31:00.000000 | 1351.04 | CREDIT_CARD |
| 2 | 2 | 2 | 2020-05-10 12:45:27.000000 | 162.11 | WIRE_TRANSFER |
| 2 | 3 | 4 | 2020-05-10 13:11:23.000000 | 350.00 | CASH |
| 3 | 1 | 5 | 2020-05-10 12:31:00.000000 | 111.50 | CREDIT_CARD |
| 3 | 2 | 8 | 2020-05-10 12:45:27.000000 | 1509.23 | WIRE_TRANSFER |
| 3 | 3 | 3 | 2020-05-10 13:11:23.000000 | 3301.66 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+EXPLAIN PARTITIONS
SELECT * FROM spider_sharded_sales.invoices
WHERE customer_id = 4;+------+-------------+----------+--------------------------------------------------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------------------------------------------------+------+---------------+------+---------+------+------+-----------------------------------+
| 1 | SIMPLE | invoices | hq_partition,eastern_partition,western_partition | ALL | NULL | NULL | NULL | NULL | 9 | Using where with pushed condition |
+------+-------------+----------+--------------------------------------------------+------+---------------+------+---------+------+------+-----------------------------------+This page is: Copyright © 2025 MariaDB. All rights reserved.
This page is: Copyright © 2025 MariaDB. All rights reserved.
This page is: Copyright © 2025 MariaDB. All rights reserved.
CREATE USER spider_user@192.0.2.1 IDENTIFIED BY "password";$ mariadb --user spider_user --host 192.0.2.2 --passwordCREATE SERVER hq_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.2",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "hq_sales"
);CREATE SERVER eastern_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.3",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "eastern_sales"
);CREATE SERVER western_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.4",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "western_sales"
);CREATE DATABASE hq_sales;
CREATE SEQUENCE hq_sales.invoice_seq;
CREATE TABLE hq_sales.invoices (
branch_id INT NOT NULL DEFAULT (1) CHECK (branch_id=1),
invoice_id INT NOT NULL DEFAULT (NEXT VALUE FOR hq_sales.invoice_seq),
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=InnoDB;
INSERT INTO hq_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD'),
(2, '2020-05-10 14:17:32', 1508.57, 'WIRE_TRANSFER'),
(3, '2020-05-10 14:25:16', 227.15, 'CASH');CREATE DATABASE eastern_sales;
CREATE SEQUENCE eastern_sales.invoice_seq;
CREATE TABLE eastern_sales.invoices (
branch_id INT NOT NULL DEFAULT (2) CHECK (branch_id=2),
invoice_id INT NOT NULL DEFAULT (NEXT VALUE FOR eastern_sales.invoice_seq),
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=InnoDB;
INSERT INTO eastern_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(2, '2020-05-10 12:31:00', 1351.04, 'CREDIT_CARD'),
(2, '2020-05-10 12:45:27', 162.11, 'WIRE_TRANSFER'),
(4, '2020-05-10 13:11:23', 350.00, 'CASH');CREATE DATABASE western_sales;
CREATE SEQUENCE western_sales.invoice_seq;
CREATE TABLE western_sales.invoices (
branch_id INT NOT NULL DEFAULT (3) CHECK (branch_id=3),
invoice_id INT NOT NULL DEFAULT (NEXT VALUE FOR western_sales.invoice_seq),
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=InnoDB;
INSERT INTO western_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(5, '2020-05-10 12:31:00', 111.50, 'CREDIT_CARD'),
(8, '2020-05-10 12:45:27', 1509.23, 'WIRE_TRANSFER'),
(3, '2020-05-10 13:11:23', 3301.66, 'CASH');GRANT ALL PRIVILEGES ON hq_sales.invoices TO 'spider_user'@'192.0.2.1';GRANT CREATE TEMPORARY TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.1';CREATE DATABASE spider_sharded_sales;
CREATE TABLE spider_sharded_sales.invoices (
branch_id INT NOT NULL,
invoice_id INT NOT NULL,
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=Spider
PARTITION BY LIST(branch_id) (
PARTITION hq_partition VALUES IN (1) COMMENT = 'server "hq_server", table "invoices"',
PARTITION eastern_partition VALUES IN (2) COMMENT = 'server "eastern_server", table "invoices"',
PARTITION western_partition VALUES IN (3) COMMENT = 'server "western_server", table "invoices"'
);CREATE TABLE spider_hq_sales.invoices_alternate (
branch_id INT NOT NULL,
invoice_id INT NOT NULL,
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=Spider
COMMENT='table "invoices", host "192.0.2.2", port "5801", user "spider_user", password "user_password", database "hq_sales"';This page is: Copyright © 2025 MariaDB. All rights reserved.