Official Mafiree Blogs

Keeping you informed about Databases

Significance of using Invisible Primary key (GIPK) with MySQL 8.0
By Sukan   |   February 21, 2023   |   Posted in : MySQL
How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

SQL Server On Linux
By Sujith   |   March 30, 2019   |   Posted in : SQL Server
SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Types of SQL Server Replication
By Sujith   |   February 19, 2018   |   Posted in : SQL Server
Understanding on the types of replication in SQL Server and the type apt for your traffic pattern.

Data File Splitting in SQL Server
By Murali   |   March 15, 2021   |   Posted in : SQL Server
Huge data blocks resides under single MDF file might cause a performance of the query and impact the application services. This blog help you to understand how we can the split the single MDF file into multiple data files.

 Home  /  MySQL  
What is Spider Storage Engine
By Murali   |   July 10, 2019   |   Posted in : MySQL

Spider storage engine was introduced in Mariadb 10.0.4 as an alpha version. After a long wait, spider engine become stable from Mariadb 10.3.7.

Spider storage engine comes with inbuilt sharding features which supports partitioning and xa transactions. When a table is created with Spider, the table creates links to the remote server (MySQL / MariaDB).

For example,

If the table is created on the Spider Node(10.100.10.10) with Spider Engine, then the table creates a link to connect the Backend Server (10.100.10.15).



It supports both MySQL and MariaDB servers as backend servers.

Currently Spider Storage Engine has two types:

  • Federation

  • Sharding

1) Federation:

Entire data will be kept on remote data nodes.



To use spider, you need a minimum of two or more instances of MariaDB or MySQL, typically running on separate hosts.

In federation, application sends traffic to Spider node and spider decides on which data node it has to get the data. The data nodes are the MariaDB servers that actually store the table data and in our case, 10.100.10.15 and 10.100.10.18.

In order for this to work, you need to configure the data nodes to accept queries from the Spider node.

Using Federation,

  1. Read queries, Spider perform load balancing to the data nodes.

  2. Writes queries are mirrored to the data nodes.

  3. Full copy of the table on each node.

2) Sharding:

This feature will keep the table data partitioned across different remote backend data nodes (Shards).





As architecture shows, When a table is created in spider node using sharding, data will get split across data nodes as per the partitioned mentioned in the spider node.

Sharding in spider is splitting up (or horizontally partition) big tables among several isolated DB instances, called a database shard. We can divide huge tables and load share the traffic to multiple shard servers by using spider.How to shard the data is really interesting. You just mention the native partition in spider node. Lets say, we created Table1 with 2 hash partition in spider node. Part 1 of data will store in 10.100.10.15 and Part 2 of data will get stored in 10.100.10.18 server. You can use all partitioning rules(key, range, hash, and so on).

Using Sharding,

  • Ability to store large amounts of data.

  • Quick result during the retrieval process as spider uses certain methods of parallelization.

In what cases spider is useful for you?
  • If your data set is huge and you want to scale horizontally.

  • If you want to split the write traffic across nodes.

  • Different set of storage engines across data nodes.

  • When you need parallel execution and data consistency.-

In the next blog, we would explain a use case where we have faced issue with huge data set and how we have achieved horizontal sharding using Spider storage engine.




0 Comments


Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree