Official Mafiree Blogs

Keeping you informed about Databases

Scaling Databases Horizontally: A Solution for High-Concurrency Systems
By Sukan   |   September 20, 2023   |   Posted in : TiDB
Blog post on how TiDB handles the OLAP requests without compromising OLTP

6 Interesting Features In MongoDB 6.0
By Thivakar   |   June 19, 2023   |   Posted in : MongoDB
Upgrade to MongoDB 6.0 for improved index management, error handling, powerful aggregation, replicaset enhancements, time series collection improvements, and embedded field exclusion. Experience efficient database management.

Access Control & Account Management In MySQL 8
By Sukan   |   June 08, 2023   |   Posted in : MySQL
Discover how MySQL 8 enhances security through features like password expiry, password validation, account lock/unlock, privilege management, and dual passwords. Safeguard your valuable data and streamline access control in your MySQL environment.

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.

 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