Official Mafiree Blogs

Keeping you informed about Databases

 Home  /  MySQL  
How to achieve horizontal sharding using spider storage engine
By Murali   |   August 02, 2019   |   Posted in : MySQL

Managing Tables / DBs more than 500G / 1T will be hard for any DBAs.  Spider storage engine makes life simpler using its sharding technique.

If you are new to Spider Storage Engine, I would suggest to read my previous
blog (What is Spider Storage Engine) to understand more about spider.

Problems with big tables are,

  • Adding index / columns will be slower. 

  • Reporting queries would take hours to run.

  • Disk / IO / CPU utilization would spike. 

With MySQL replication, we could address Scaling of read queries but we may not be able to scale write queries.  

With partitioning, we can improve the write performance but there are cases where it can also fail. 

MariaDB trying to fill this gap by horizontal Sharding using Spider Storage Engine.

How to?

Create partition on Spider node of MariaDB. The partitioned data will be stored on multiple database instances as shown in the below diagram.

The application will only need to connect to the Spider instance. And the spider instance will get the required data from the backend data instance. All the metadata will be maintained on the spider instance. 

 

Let me give a simple example of this partition with Student_details table.

  • In the spider instance, I have created partitioned with,

    • Year 2016 as P1 partition.

    • Year 2017 as P2 partition.

    • Year 2018 as P3 partition.

  • Data of 2016 (P1) will be on Backend #1.

  • Data of 2017 (P2) will be on Backend #2.

  • Data of 2018 (P3) will be on Backend #3.

                            

 

So, when you query 2017 year data (Hitting Partition2 condition) then the host information for Backend server #2 will be selected and the data will be retrieved from server #2. By the way, this is not only for selects but also for all DMLs (insert, update and delete statements).

 

In the next blog, I shall explain how to achieve HA in spider.

 




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