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