If you’re like me, you’ve probably heard of the Spider storage engine, but not used it yet.
While it has been available for some years now, I just simply haven’t used it before until now.
I suspect that has to do with ease of installation. Previously, one had to compile it with MySQL in order to use it, which excludes a lot of people. However, in MariaDB 10.0 (as of 10.0.4), it is very easy to add and use.
And with MariaDB 10.0.8 being declared RC, combined with Spider’s sheer usefulness, I only suspect its usage will become more and more widespread.
What is the Spider storage engine, and why will it be useful?
“The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance. It refers to one possible implementation of ISO/IEC 9075-9:2008 SQL/MED. When a table is created with the Spider storage engine, the table links to the table on a remote server. The remote table can be of any storage engine. The table link is concretely achieved by the establishment of the connection from a local MariaDB server to a remote MariaDB server. When more than one Spider storage engine table can be used by the same connection, the connection is shared.”
This is quite flexible, and given that, you can achieve a lot with spider, including federated, sharding, high availability, and background search (the last expected to be fully implemented by the time MariaDB 10.0 is declared GA).
How to install spider?
This is quite simple as the files needed are now included with the MariaDB distribution. In the /share directory, you will see a file named: install_spider.sql
You just need to import this, and there you go:
mysql -uroot -p -P3315 < install_spider.sql
For those interested in the behind-the-scenes, this creates the following 6 system tables (in 'mysql' schema):
spider_link_failed_log spider_link_mon_servers spider_tables spider_xa spider_xa_failed_log spider_xa_member
And it automatically loads the .so or .dll plugin (depending if you're running on Linux or Windows), thus afterward, SHOW ENGINES should report the following:
mysql> show engines; +--------+---------+------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------+---------+------------+--------------+------+------------+ | SPIDER | YES | Spider ... | YES | YES | NO | ...
The most simple example is accessing a table on another instance.
Let's say you have this remote table you want to access:
CREATE TABLE s(id INT NOT NULL AUTO_INCREMENT, code VARCHAR(10), PRIMARY KEY(id));
Then on the local server, where Spider is enabled, issue:
CREATE TABLE s(id INT NOT NULL AUTO_INCREMENT, code VARCHAR(10), PRIMARY KEY(id)) ENGINE=SPIDER COMMENT 'host "127.0.0.1", user "chris", password "xxxxx", port "3314"';
Now you can SELECT from this table, or INSERT into it (which will write to the remote instance), etc.
INSERT INTO s(code) VALUES ('a');
This is very straight-forward, and really it's just the tip of the iceberg. The MariaDB KB article on the Spider storage engine has quite a bit of info and examples, so if you're interested, please see:
Hope this helps.