MySQL Sharding

Introduction to MySQL

MySQL is a popular Open-Source Relational Database Management System (RDBMS) that uses the Structured Query Language (SQL) to manage Relational Databases (RDBs) in the form of rows and columns across tables. Developed by MySQL AB in 1994, it was later acquired by Sun Microsystems in 2008 and by Oracle, a US tech giant. Despite being free of charge, MySQL offers premium features for customers willing to pay for usage. Despite high competition in the market, MySQL is the preferred database for over 5,000 companies, including Uber, Netflix, Pinterest, Amazon, Airbnb, and Twitter.

Key Features of MySQL

Some of the key features of MySQL are:

Strong Transaction Support: ACID (Atomicity, Consistency, Isolation, Durability) features are implemented to guarantee no data loss or inconsistent data.
Ease of Use: Since it uses SQL to query data, anyone with even rudimentary SQL understanding may readily do the necessary activities.
Security: Makes sure that only authorized people can access sensitive data by implementing a sophisticated data security layer.
Scalable: Because multi-threading is supported, it is considered extremely scalable.
Support for Rollbacks: MySQL allows commits, rollbacks, and crash recovery for every transaction.
High Performance: Contains several quick load tools, distinct memory caches, and table index partitioning to guarantee high performance.

Introduction to Sharding

Monolithic Relational Database Management Systems (RDBMS) often need help with bottlenecks due to increased data volume, causing a decrease in response time due to limited CPU power, memory, storage capacity, and throughput.
Vertical Scaling, while effective, has limitations and diminishes returns after a point. Horizontal Scaling, or Sharding, is the best solution for dealing with high data volumes. This involves partitioning large tables horizontally across multiple servers, reducing the load on each server and reducing response time. Each chunk, known as a Shard, is a horizontal data partition that serves a subset of the original data set, serving only a portion of the total workload.
Sharding also ensures data availability during unplanned outages, as only active Shards can produce the appropriate response. In contrast, unshaded databases have zero data availability if the server goes down, while Sharded databases have multiple Shards that distribute data, ensuring data availability during outages.

What is MySQL Sharding?

MySQL Sharding is a process that distributes workload from a single MySQL Database server to multiple servers, each called a shard, to address performance issues with scaling write data. This method, which involves partitioning data across multiple servers with identical Schema, helps companies manage workload effectively.

Common Auto-Sharding Architecture

"Common Auto-Sharding Architecture" describes a distributed database system's architecture strategy that automatically partitions data into more manageable, smaller segments, or "shards," and distributes those shards over several servers or nodes. This methodology facilitates the attainment of horizontal scalability, enhances performance, and effectively distributes the database workload. Different sharding algorithms are often used in conventional auto-sharding systems; notable techniques include hash sharding, range sharding, and geo-partitioning.

Hash sharding

Description: To decide which shard or partition should hold the data, hash sharding entails applying a hash function to a unique identifier (such as a key or document ID). The hash function evenly distributes data amongst shards based on the hash value.
Advantage: Benefits include decreased hotspots, effective load balancing, and even data distribution.
Challenges: It is easier to query data items if you know where they are in the sharded structure.

Range sharding

Description: Partitioning data according to particular ranges or intervals of a key?for example, time intervals, numerical values, or alphabetical ranges?is known as range sharding. Because each shard is in charge of a predetermined range of data, range queries, and sorted data can be performed on it.
Advantage: Efficient at preserving data order and for range queries.
Challenges: If the data is not dispersed evenly over the ranges, the data may be uneven.

Geo partitioning

Description: Data is distributed through geo-partitioning according to spatial or geographical parameters. Applications requiring geospatial data frequently use it, storing data related to geographic regions in appropriate shards.
Advantage: Effective for applications that depend on geographic data and geospatial searches.
Challenges: It might not be easy to manage geographic data, ensure data is distributed evenly, and deal with variations in volume or regions.

The Challenges of Manual Sharding

Monolithic databases like MySQL, Oracle, PostgreSQL, Amazon Aurora, etc do not support automatic sharding. Shading becomes a new development effort as the complexity of the data rises and is difficult to manage in MySQL. Here are a few more difficulties with manual sharding:

More sharding logic is required to specify how the data should be dispersed and retrieved. It also entails determining which MySQL Sharding strategy to use, how many nodes are required, and how to distribute the load evenly across all the nodes for best performance.
When a business needs change, developers must adapt their data-sharding strategies.
Data sharding by hand can result in unequal shard allocation, leading to unbalanced shards. This means that some shards may be empty while others may be overloaded with data, producing an uneven allocation.
A manual sharding approach makes the operational procedures?maintenance, backups from several data servers, etc.?more difficult. The manual application of all the steps is an uphill task.

Understanding MySQL Sharding

Regretfully, contemporary distributed SQL databases like Amazon Aurora and monolithic databases like PostgreSQL, Oracle, and MySQL do not enable automatic sharding. This implies that implementing anything like MySQL Sharding must be done manually at the Application Layer, requiring a significant amount of engineering bandwidth due to the need to build up the complete Sharding logic that controls how data is received and distributed.

Certain design choices must be made because the implementation must be completed by hand. The following are the decisions that must be made:

Choosing MySQL Sharding Key

The MySQL Sharding Key will govern the distribution of data among Shards. The MySQL Sharding Key should be carefully selected when installing Sharding in MySQL, as selecting the incorrect key could lead to future system inflexibility. For instance, if the parent and child rows are kept on different Shards, Referential Integrity?the parent/child relationship between tables maintained by the Relational Database Management System (RDBMS)?will not be preserved automatically.

The two possible types of MySQL Sharding keys are:

Intelligent MySQL Sharding:
Though it is thought to be more vulnerable to skews, it is more appropriate to prevent joins between shards. To prevent joins, it would make sense to store data on every customer, including interactions, touchpoints, and other details, in a single Shard if the Customer table is Sharded according to the property that represents the Customer ID.
Hashed MySQL Sharding Keys:
Distributes data amongst Shards automatically. This aims to distribute the data evenly and prevent an excessive load on a single Shard. Hashed MySQL Sharding Keys, for instance, make more sense to ensure proper data distribution across Shards if significant client growth is anticipated. Nevertheless, complicated join operations would need to be carried out across numerous Shards to do any meaningful analysis, which is impossible.

Handling Schema Changes

Users of MySQL can modify Table Schemas in the database at any moment after creation. Before any application can use the new Schemas, if MySQL Sharding has been deployed, this update in the Schemas must be applied to all Shards. Application failures or inconsistent data may result if the update is not applied in even one of the Shards. Therefore, once MySQL Sharding is enabled, users should either build a mechanism to verify that Schema changes are implemented across all Shards or refrain from modifying the Schema.

Mapping between Physical Servers, Shards, and MySQL Sharding Keys

Maintaining a mapping between physical servers, shards, and sharding keys is essential in a sharded MySQL architecture. Data retrieval and query routing require this mapping. Using the sharding key as a guide, this metadata helps direct requests to the appropriate shard. It can be not easy to manage this mapping, particularly as the system grows. It's important to track which shard contains what information and how it relates to the physical infrastructure. Additionally, it is critical to have systems in place for updating this mapping and dynamically adding or removing shards.

These three elements are necessary for the MySQL sharding solution to be successful. In a sharded MySQL database environment, data consistency, effective query processing, and scalability are contingent upon carefully evaluating the sharding key, efficiently handling schema changes, and precise mappings between physical servers, shards, and sharding keys.

Limitations of MySQL Sharding

Complex Implementation: Sharding a MySQL database is intricate and requires creating unique query routing and data distribution mechanisms. Database administrators and developers have a steep learning curve because of its complexity. The sharding technique can be resource-intensive to develop and deploy, requiring much engineering work.
Application-Level Logic: Shading requires modifications to the application code to handle sharding keys and direct queries to the right shards. The application code may become more complex because of the close interaction between the sharding strategy and the application layer, making it harder to maintain and write.
Data Distribution Challenges: Distributing data evenly among shards might take much work. Data skew, where certain shards handle a disproportionately large number of data and query traffic while others remain underutilized, can be caused by poorly chosen sharding keys or unequal data growth. A continuing difficulty is balancing query load and data delivery among shards.
Join and Referential Integrity: Sharding can make tasks more difficult, such as preserving referential integrity across databases spread across several shards or combining data across numerous shards. Custom logic is needed to handle these operations, which can affect query performance.
Schema Changes Complexity: Schema updates can be challenging in a shared architecture. To ensure data consistency, all changes made to the Schema must be properly coordinated across all shards. Changes to the Schema, including adding or deleting tables or columns, need careful coordination and preparation.
Data Migration Complexity: Shifting data across shards can be difficult, particularly when shards are added or removed. Careful planning is required for data migration to guarantee data consistency and reduce downtime. Large tables may need to be divided up, data may need to be moved between shards, and data integrity may need to be checked at every stage.
Backup and Recovery Complexity: Backup and recovery processes become increasingly complex in a sharded environment. Coordinating amongst several shards is necessary for data recovery, and making sure point-in-time recovery is possible can be challenging. To back up a shared database, specific techniques must be used to collect data from every shard.
Query Routing Overhead: When a query is routed to the correct shard, query execution incurs additional overhead. Although this overhead is usually negligible, as the system scales, it becomes an issue, and careful design is needed to reduce its effect on query performance.
Scaling Challenges: Scaling is more difficult as the database gets bigger. The application layer may need to be modified to add more shards or resize existing ones, and careful planning is needed to maintain even data distribution during Scaling.
Lack of Built-In Features: Organisations must use bespoke code and tools to enable sharding because traditional MySQL must be supported natively. The absence of pre-installed functionalities like transparent failover or automatic load balancing may increase the company's development and maintenance costs.
Complex Operations: In a sharded environment, monitoring, maintenance, and troubleshooting become more complicated. The sharded database's performance and well-being depend on specialized equipment and knowledge. More work and resources are needed to handle problems like query bottlenecks and hotspots, preserve data consistency, and manage and optimize shard expansion.