![Page 1: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/1.jpg)
Scaling (sharding) MariaDB
with Spider Storage Engine
Spiral-Arms
Kentoku SHIBA
![Page 2: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/2.jpg)
What is the Spider Storage Engine?
Spider is a Storage Engine.
Spider Storage Engine is a plugin of
MariaDB/MySQL. Spider tables can be
used on remote MariaDB/MySQL/OracleDB
tables as local tables. And Spider can
create database sharding by using table
partitioning feature.
![Page 3: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/3.jpg)
What is Spider Storage Engine
Spider is bundled in MariaDB
from 10.0.4.
![Page 4: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/4.jpg)
Using Spider
dividing huge data into multiple servers
1. High traffic processing(DB sharding)
2. Parallel processing
You can use
3. multiple backend databases for different application
as one database through Spider.
For example, Spider is used for
- 200 Billion records of data analytics platform
- 2440 database instances of social game data platform
![Page 5: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/5.jpg)
SPIDER (MySQL/MariaDB)
SPIDER (MySQL/MariaDB)
Spider structure sample of database sharding
DB1
tbl_a1
1.Request
2.Just connect to spider
3.Response
DB2 DB3
AP
SPIDER (MySQL/MariaDB)
An application can use all databases
by only connecting to one database.
AP AP AP AP
tbl_a2 tbl_a3
![Page 6: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/6.jpg)
How to use Spider (1/5)
1. Install Spider bundled with
MariaDB/MySQL.
2. Login MariaDB/MySQL then
install Spider as a plugin.
(execute install_spider.sql)
3. Create Spider table.
![Page 7: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/7.jpg)
How to use Spider (2/5)
Create one to one Spider table.
CREATE TABLE t1(
c1 int,
c2 varchar(100),
PRIMARY KEY(c1)
)ENGINE=spider DEFAULT CHARSET=utf8
COMMENT '
table "rt1", database "test", port "3306",
host "host name of data node",
user "user name for data node",
password "password for data node"
';
Set engine name to “Spider” and write connect
information (and parameter) in the comment.
![Page 8: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/8.jpg)
How to use Spider (3/5)
You can create Spider tables without column definitions in
MariaDB. In this case Spider gets the column definition from
data node.
CREATE TABLE t1
ENGINE=spider DEFAULT CHARSET=utf8
COMMENT '
table "rt1", database "test", port "3306",
host "host name of data node",
user "user name for data node",
password "password for data node"
';
![Page 9: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/9.jpg)
How to use Spider (4/5)
Create one to many (sharding) Spider table
CREATE TABLE t1(
c1 int,
c2 varchar(100),
PRIMARY KEY(c1)
)ENGINE=spider DEFAULT CHARSET=utf8
COMMENT 'table "rt1", database "test", port "3306",
user "user name for data node", password "password for data node"'
PARTITION BY RANGE(c1) (
PARTITION p0 VALUES LESS THAN (100000) COMMENT 'host "h1"',
PARTITION p1 VALUES LESS THAN (200000) COMMENT 'host "h2"',
PARTITION p2 VALUES LESS THAN (300000) COMMENT 'host "h3"',
PARTITION p3 VALUES LESS THAN MAXVALUE COMMENT 'host "h4"'
);
Write shared connect information to table comment,
shard specific connect information to partition comment.
![Page 10: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/10.jpg)
How to use Spider (5/5)
You can use “CREATE SERVER” statement for defining
connection information.
CREATE SERVER srv1
FOREIGN DATA WRAPPER mysql
HOST 'host name of data node',
DATABASE 'test',
USER 'user name for data node',
PASSWORD 'password for data node',
PORT 3306
;
You can use create server definition by writing “server” parameter
into table/partition comment.
CREATE TABLE t1(
c1 int,
c2 varchar(100),
PRIMARY KEY(c1)
)ENGINE=spider DEFAULT CHARSET=utf8
COMMENT 'table "rt1", server "srv1"';
![Page 11: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/11.jpg)
Spider’s other features
![Page 12: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/12.jpg)
Spider’s other features
Redundancy
You can choose redundant level per table/partition.
Fault Tolerance
You can use not only Spider’s fault tolerant feature
but also other MySQL fault tolerance solutions.
Fulltext/Geo search feature
(with table partitioning, available for patched MariaDB)
You can use backend Fulltext/Geo search feature
transparently.
![Page 13: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/13.jpg)
Spider’s other features
NoSQL feature (not available for MariaDB yet)
You can use HandlerSocket for Spider.
OracleDB connecting
You can use OracleDB for data node.
Note: You need to build from source code
for using this feature
Parallel searching
(available for patched MariaDB)
You can search sharded table by parallel.
![Page 14: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/14.jpg)
Spider’s other features
Direct updating (available for patched MariaDB)
Improve updating performance.
Direct aggregating (available for patched MariaDB)
Improve aggregating(group by) performance.
Engine condition pushdown (with table partitioning, available for patched MariaDB)
Improve searching with full-scan performance.
![Page 15: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/15.jpg)
Spider’s other features
Multi Range Read
(include Batched Key Access) (with table partitioning, available for patched MariaDB)
Improve searching with join performance.
![Page 16: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/16.jpg)
Introducing other plugin
which is combined with Spider in many cases
![Page 17: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/17.jpg)
1. Vertical Partitioning Storage Engine
2. Mroonga Storage Engine
Other plugins
![Page 18: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/18.jpg)
Vertical Partitioning Storage Engine
Vertical Partitioning (VP) Storage Engine’s
main features
1. Column level partitioning.
2. Works like a view of one to one relation tables.
Possible to direct access to child tables.
But, VP table can use as a table including
insert statement.
3. For using different sharding (horizontal
partitioning) rules case by case.
4. Support online copying data between child tables.
![Page 19: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/19.jpg)
Structure sample of using different sharding rules (1/2)
DB2
tbl_a
1.Request
3.Response
DB3
tbl_a
DB4
tbl_a
AP AP AP AP AP
DB1
tbl_a (vp)
DB5
tbl_a
Partition
by col_a
tbl_a1(spider)
Partition
by col_b
tbl_a2(spider)
select … from tbl_a where col_a = 1
![Page 20: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/20.jpg)
Structure sample of using different sharding rules (2/2)
DB2
tbl_a
1.Request
3.Response
DB3
tbl_a
DB4
tbl_a
AP AP AP AP AP
DB1
tbl_a (vp)
DB5
tbl_a
Partition
by col_a
tbl_a1(spider)
Partition
by col_b
tbl_a2(spider)
select … from tbl_a where col_b = 1
![Page 21: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/21.jpg)
1. Vertical Partitioning Storage Engine
2. Mroonga Storage Engine
Other plugins
![Page 22: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/22.jpg)
Mroonga Storage Engine (1/2)
Mroonga Storage Engine’s main features
1. Offering high-speed FULLTEXT search.
2. Offering high-speed GEO search.
3. High-speed updating with multiple thread
searching. (lock-free model)
4. Support CJK languages.
![Page 23: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/23.jpg)
Mroonga Storage Engine (2/2)
Mroonga Storage Engine’s main features
5. You can use to add FULLTEXT/GEO search for
other storage engines.
6. Offering FULLTEXT/GEO search to sharded
servers by combining with Spider.
Mroonga’s documentation is available.
http://mroonga.github.com/
![Page 24: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/24.jpg)
The feature roadmap
of Spider
![Page 25: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/25.jpg)
The feature roadmap of Spider
Spring 2016
- Recovering statistics information at startup tables.
(Improving startup phase of MariaDB) DONE
binary
http://spiderformysql.com/downloads/spider-3.3/mariadb-10.1.12-
spider-3.3.8-vp-1.1-linux-x86_64-glibc25b.tgz
source code
http://spiderformysql.com/downloads/spider-3.3/mariadb-10.1.12-
spider-3.3.8-vp-1.1b.tgz
- Reducing number of threads for collecting statistics
information from data nodes. (Reduce memory usage)
![Page 26: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/26.jpg)
The feature roadmap of Spider
Summer 2016
- Direct join on data node part1. (for simple joined SQL) (not
include partitioned table and redundant table)
- Auto repair broken spider system table.
- Direct join on data node part2. (for simple joined SQL)
(include partitioned table and not include redundant table)
Autumn 2016
- Auto XA recovery and commit/rollback on startup phase of
MariaDB.
- Direct join on data node part3. (for simple joined SQL)
(include partitioned table and redundant table)
![Page 27: Scaling (sharding) MariaDB with Spider Storage Engine](https://reader034.vdocuments.us/reader034/viewer/2022052116/5879cc921a28abb42a8b79f3/html5/thumbnails/27.jpg)
http://spiderformysql.com
Kentoku SHIBA (kentokushiba [at] gmail [dot] com)
Any Questions?
You can see me later!
Come to visit me!!
Thank you for taking your time!