Official Mafiree Blogs

Keeping you informed about Databases

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.

Dynamic query routing with ProxySQL
By Sabareesh   |   March 15, 2021   |   Posted in : Proxysql
How ProxySQL helps in achieving query routing without making a application changes.

 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.

MySQL replication, we could address Scaling of read queries but we may not be able to scale write queries. 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.

And,

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

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

  3. 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