Uses for MariaDB and the Spider Storage Engine

Spider is a storage engine for the MariaDB Platform that allows you to build distributed databases from a standard MariaDB setup. The technology is not complicated, although the implementation is. This blog will explain how the Spider storage engine works, what it does and will also show some of the use cases.

MariaDB Storage Engines

Before we look at the Spider storage engine, let's have a quick look at the storage engine concept. A storage engine is the implementation of code that manages the low level of data access in MariaDB. The storage engine handles things such as reading and writing data, row-level locking, if supported, multi-versioning and transaction management, among other things.

Storage engines are defined on a table-by-table basis, and once a table is created and defined to use a particular storage engine (using the  ENGINE  Table attribute) it will generally be treated just like any other tables. Tables using different engines, once created, are handled as equal and can be joined, have data selected from one into another, etc. Tables may also have the storage engine changed after the table has been created.

You may also enjoy: The State of the Storage Engine

Spider From a High Level

What Spider provides, first and foremost, is a way to access a table on one MariaDB Server from another MariaDB Server. The MariaDB Server that holds the actual table data does not have any specific Spider code on it at all; it is a normal MariaDB Server. The MariaDB Server that is configured to access that data then uses the Spider storage engine to access the data on the other server using the usual MariaDB protocol.

As can be seen, Spider is only active on the referencing node; the target node does not need to have Spider installed on it. Creating a "spider table" means that we define a table that contains the same, or a subset of, the columns in the target table and referencing the target server.

Also, note that there is no data for these tables on the "spider node" and there is no duplication of data, all data resides on the target node.

Installing the Spider Storage Engine

Spider is included with MariaDB Server and there is also an installation script that installs the Spider engine and also some utilities are supporting objects and it is recommended to install spider using this script. If you have installed MariaDB as an RPM this spider installation script is in /usr/share/mysql and is called install_spider.sql. To run it use the MariaDB MySQL command-line tool and use the source command, like this.

$ mysql -u root

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2835

Server version: 10.4.6-MariaDB-log MariaDB 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> source /usr/share/mysql/install_spider.sql

After running the script check if Spider is installed using the  SHOW ENGINES  command:

xxxxxxxxxx

MariaDB> SHOW ENGINES;

+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+

| SPIDER | YES | Spider storage engine | YES | YES | NO |

| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |

| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |

| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |

| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| CSV | YES | Stores tables as CSV files | NO | NO | NO |

+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.001 sec)

We are now ready to start using the Spider engine.

Single Table Link with Spider

Let's look at an example, a very basic one. On the "target" server, we create a table. Note that on this server, Spider isn't necessary; this is only required on the server that accesses remote data, and we will get to that next. So now we create a table on the "target" server, let's call that "Server 2:"

xxxxxxxxxx

$ mysql -u root -S /tmp/mariadb2.sock -u root

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 11

Server version: 10.4.8-MariaDB MariaDB 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)]> CREATE DATABASE spidertest;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use spidertest;

Database changed

MariaDB [spidertest]> CREATE TABLE customer(

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(200) NOT NULL,

address VARCHAR(255) NOT NULL);

Query OK, 0 rows affected (0.539 sec)

Following this, let's insert some data into this table:

xxxxxxxxxx

MariaDB [spidertest]> INSERT INTO customer VALUES(NULL, 'John Doe', '1 Main Street');

Query OK, 1 row affected (0.309 sec)

MariaDB [spidertest]> INSERT INTO customer VALUES(NULL, 'Bob Smith', '45 Elm Street');

Query OK, 1 row affected (0.092 sec)

MariaDB [spidertest]> INSERT INTO customer VALUES(NULL, 'Jane Jones',

'18 Second Street');

Query OK, 1 row affected (0.094 sec)

I admit that this wasn't the most exciting experiment since Marie Curie invented radium, but we are not done yet. What we are going to do now is to access this table from another instance of MariaDB Server. As Spider connects to the remote server as a normal user, we need to create a user and grant it some access to the table we created above, on the same MariaDB Server instance as above:

xxxxxxxxxx

MariaDB [spidertest]> CREATE USER 'spider'@'192.168.0.11' IDENTIFIED BY 'spider';

Query OK, 0 rows affected (0.236 sec)

MariaDB [spidertest]> GRANT ALL ON spidertest.* TO 'spider'@'192.168.0.11';

Query OK, 0 rows affected (0.238 sec)

MariaDB [spidertest]> GRANT ALL ON mysql.* TO 'spider'@'192.168.0.11';

Query OK, 0 rows affected (0.238 sec)

The next step is to create a  SERVER . If you haven't used Spider you have probably not used the command before, but what it does is that it defines the parameters that are used to connect to another MariaDB Server instance, so the server is defined on the MariaDB Server instance that is to access the table we create above (let's call that Server1).

xxxxxxxxxx

r$ mysql -u root -S /tmp/mariadb1.sock -u root

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 12

Server version: 10.4.8-MariaDB MariaDB 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]> CREATE SERVER Server2 FOREIGN DATA WRAPPER mysql

OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10482,

USER 'spider', PASSWORD 'spider');

Query OK, 0 rows affected (0.233 sec)

With this in place, let's create a link using Spider from Server1 to Server2. Note that we do not have to use all the fields in the target table.

xxxxxxxxxx

$ mysql -u root -S /tmp/mariadb1.sock -u root

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 33

Server version: 10.4.8-MariaDB MariaDB 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)]> DROP DATABASE IF EXISTS spidertest;

Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> CREATE DATABASE spidertest;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use spidertest;

Database changed

MariaDB [spidertest]> CREATE TABLE customer(

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(200) NOT NULL) ENGINE=Spider

COMMENT = 'wrapper "mysql", srv "Server2"';

Query OK, 0 rows affected (0.132 sec)

OK, no errors, then try it by  SELECTing some data:

xxxxxxxxxx

MariaDB [spidertest]> SELECT * FROM customer;

+----+------------+

| id | name |

+----+------------+

| 1 | John Doe |

| 2 | Bob Smith |

| 3 | Jane Jones |

+----+------------+

3 rows in set (0.006 sec)

Yes, this seems to work. Still Marie Curie beats us in the excitement department, but we are getting there.

Uses for a Single Table Link

There are quite a few uses for even a single table link such as the one described above. In some cases it can be used to replace replication where it is just a single table that is to be replicated. Maybe you have a single table that is maintained in one database instance such as a customer table or something and then you want a reference to a customer ID from a completely different application running in some other MariaDB Server instance.

One needs to understand that there are drawbacks to this kind of setup; for example, performance can be an issue, because for every statement that uses a spider table a connection is made to the connected server. A join between a local table and a Spider table can also be slow, but that depends on a few things. In general, Spider is pretty performant and is not a bottleneck per se and there is quite a bit of tuning possible.

A third option is when we have a single table, for example a log table, that I don't want to mix with any other tables. Then a Spider table might be a way to achieve that.

Using Spider for Status Across Multiple Servers

If you have a MariaDB Cluster running, with a primary and several secondaries for example, then there is probably a need to see the status of all the servers in the cluster as one unit.  Spider comes in handy. Here we are to look at how that can be done. We have two servers, moe and homer, that we want to monitor. Let us assume that moe is the "main" server and that we from that box want to look at the global status on both servers.

On homer we create a view for  GLOBAL_STATUS  which is based on the GLOBAL STATUS table in  INFORMATION_SCHEMA , but adds a column with the name of the server and put that in the MySQL database.

xxxxxxxxxx

CREATE OR REPLACE VIEW global_status_homer

AS SElECT 'homer' host, gs.variable_name, gs.variable_value

FROM information_schema.global_status gs;

And then we do a similar thing on moe.

xxxxxxxxxx

CREATE OR REPLACE VIEW global_status_moe

AS SElECT 'moe' host, gs.variable_name, gs.variable_value

FROM information_schema.global_status gs;

The next step is to create a link from what we consider the master (moe) to the other server (homer). So on moe, we execute this command:

xxxxxxxxxx

CREATE OR REPLACE SERVER homer FOREIGN DATA WRAPPER mysql

OPTIONS(HOST '192.168.0.11', DATABASE 'mysql', PORT 10482, USER 'spider',

PASSWORD 'spider');

With this, we are ready to create a view on moe for viewing the status on homer.

xxxxxxxxxx

CREATE OR REPLACE TABLE global_status_homer(host varchar(2048), variable_name VARCHAR(64), variable_value VARCHAR(64)) ENGINE=Spider

COMMENT='wrapper "mysql", srv "homer"';

On homer, we now have one table and one view, one for each server and with similar schema, that represent the status across the cluster. Let's combine these two into a single VIEW.

xxxxxxxxxx

CREATE OR REPLACE VIEW global_status_all AS

SELECT host, variable_name, variable_value

FROM global_status_homer

UNION

SELECT host, variable_name, variable_value

FROM global_status_moe;

And then a  VIEW  that shows the summary status across the cluster.

xxxxxxxxxx

CREATE OR REPLACE VIEW global_status_total AS

SELECT variable_name, SUM(variable_value) sum, MAX(variable_value) max,

MIN(variable_value) min

FROM global_status_all

GROUP BY variable_name;

Let's give this a shot now and see how it works:

xxxxxxxxxx

MariaDB [mysql]> SELECT * FROM global_status_total WHERE variable_name LIKE 'open%';

+--------------------------+------+------+------+

| variable_name | sum | max | min |

+--------------------------+------+------+------+

| OPENED_FILES | 629 | 477 | 152 |

| OPENED_PLUGIN_LIBRARIES | 1 | 1 | 0 |

| OPENED_TABLES | 112 | 75 | 37 |

| OPENED_TABLE_DEFINITIONS | 125 | 95 | 30 |

| OPENED_VIEWS | 85 | 43 | 42 |

| OPEN_FILES | 132 | 76 | 56 |

| OPEN_STREAMS | 0 | 0 | 0 |

| OPEN_TABLES | 77 | 46 | 31 |

| OPEN_TABLE_DEFINITIONS | 83 | 49 | 34 |

+--------------------------+------+------+------+

9 rows in set (0.029 sec)

I think this is useful, although this is a simple example. With more servers than these two it is a lot more useful.

Combining Multiple Server Tables in the General Case

In the above example, we look at how tables with the same structure with similar content can be combined into one single view, using Spider. We look at a specific example there, but in general, there are uses for this. Let's say that you have an application that runs in multiple instances, say some ERP application that is used by multiple departments. If you then want to do reporting across the departments from a reporting server, then you can use this model to access all the instances of this application.

The alternative to using Spider is to use multi-source replication, but that requires a lot more data to be stored in the reporting server, so Spider does have some advantages.

Sharding with Spider

Sharding is the most common use case for Spider. The examples I described above are less common, but still useful. In the cases above, I mapped a table on one server to a table on another machine. With Spider used for a partitioned table on one server, each partition exists on a separate server. Except for this, there aren't many differences in practice, although the partitioning use case allows Spider to do some interesting things and Spider has some extra performance-enhancing tricks up its sleeve, for this particular use case.

To show how sharding with Spider works, let's show a very simple example. We are going to show how to set up sharding with two shards only, just to show the principle. Let us use the customer table where we have a total of three servers, two "data" servers with the data for two shards and one server than is the "Spider" server that hold no actual data for the table we are working with, but instead points to data that resides on the other two servers.

Let's start at the bottom, which is to create the tables we are going to use in MariaDB Servers Server2 and Server3 (this looks similar to the customer table above, but not exactly so). This is executed of both of these two Servers as user root

xxxxxxxxxx

CREATE DATABASE IF NOT EXISTS spidertest;

CREATE TABLE spidertest.customer(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(200) NOT NULL,

address VARCHAR(255) NOT NULL);

Now we have two shards created, so let's then create links so we can reach them from MariaDB Server1, so on Server1 execute this SQL statement, replacing my port, host, username, and password with something relevant in your case.

xxxxxxxxxx

CREATE OR REPLACE SERVER Server2 FOREIGN DATA WRAPPER mysql

OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10482,

USER 'spider', PASSWORD 'spider');

CREATE OR REPLACE SERVER Server3 FOREIGN DATA WRAPPER mysql

OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10483,

USER 'spider', PASSWORD 'spider');

Let us then tie it together with a partitioned table, and note that you can use any reasonable partitioning scheme here, I just picked a simple one to make a point.

xxxxxxxxxx

CREATE TABLE spidertest.customer(id INT NOT NULL PRIMARY KEY,

name VARCHAR(200) NOT NULL,

address VARCHAR(255) NOT NULL) ENGINE=Spider

COMMENT 'wrapper "mysql", table "customer"'

PARTITION BY RANGE(id) (

PARTITION p0 VALUES LESS THAN (1000) COMMENT = 'srv "Server2"',

PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'srv "Server3"');

We can now on Server1 insert some data and see it appear as appropriate on Server2 and Server3.

xxxxxxxxxx

INSERT INTO customer VALUES(1, 'Larry', 'Main Street 1');

INSERT INTO customer VALUES(2, 'Ed', 'Main Street 1');

INSERT INTO customer VALUES(3, 'Bob', 'Main Street 1');

INSERT INTO customer VALUES(1001, 'Monty', 'Main Street 1');

INSERT INTO customer VALUES(1002, 'David', 'Main Street 1');

INSERT INTO customer VALUES(1003, 'Allan', 'Main Street 1');

Based on our partitioning set up, the three first row goes onto Server2 and the last three to Server3. I would like to point out before I finish this section that the individual shards, as they are common MariaDB instances, without the Spider engine, so they can be accessed shard by shard by connecting to, in this case, Server2 or Server3.

Uses for Sharding with Spider

The most obvious advantage of sharding is to increase performance when working with large datasets. But with Spider there are other advantages, most coming from the facts that the individual shards are plain MariaDB Servers that can be accessed individually, so the dataset can be looked at shard by shard, without any performance bottleneck, or you can look at it as a whole, using Spider, at the same time.

Conclusion

This blog has presented some uses for the Spider Storage Engine, all useful in their own right. Most documentation around Spider has focused on the sharding use case, so the first two uses might be surprising to some.

Happy SQL'ing
/Karlsson

Further Reading