Database sharding (scale up to scale out)

From: http://eddysheng.iteye.com/blog/461393

From: http://www.elecfans.com/news/wangluo/20120215260321.html

Sharding is a technology that splits a large database into multiple small databases according to certain rules.

 

What should we do when the data volume of our applications increases and the traffic volume increases? Continue to improve database server performance or adopt a technology to smooth database expansion? Although the performance is getting better and better along with the upgrading of servers, changing more luxurious servers can solve this problem temporarily, it is not satisfactory whether it is cost-effective or controllable. In this case, database sharding is a more feasible solution.

 

Common sharding solutions include the following,

 

1. By function (Vertical Split)

 

Store tables related to different functions in different databases. For example, you can place user management tables on shard 1 and blog tables on shard 2... The benefit of doing so is that it is very intuitive. When you need a user list, I will get it on shard 1 .... In this way, there is also a problem. When the data volume or performance requirements of a certain part of features exceed the controllable range, we need to continue to perform in-depth sharding on them.

 

2. Partition by the range of a field value in the table (horizontal segmentation)

 

When the data size of a table is getting bigger and bigger, it is impossible to bear the data, we need to further split the table. One option is to split the data according to the key range. For example, if userid is 1-10000 00, it is placed on shard 10, and if userid is 20000 to, it is placed on shanrd 11... Such an extension is foreseeable. The other is to divide a field based on its value. For example, based on the first letter of the user name, if it is a-d, it belongs to shard 20, and e-H belongs to shard 21... In this way, there is also imbalance. When a range exceeds the range that shard can afford, it is necessary to continue splitting. Split by date, etc,

 

3. Hash-based splitting

 

Key hash similar to memcachedAlgorithmFirst, determine the number of split databases, and determine which Shard to use through hash modulo. This method can evenly distribute data. However, when the data size increases and needs to be expanded, this method cannot achieve online resizing. Each time a node is added, the hash algorithm needs to be re-computed, and the data needs to be re-cutover.

 

4. Route table-based splitting

 

The previous methods are based on application data to determine the shard operation. Route table-based splitting is a more loose method. It maintains a route table separately and finds the shard used by the route table based on a certain attribute of the user. This method is a more common solution. For example, we maintain a table in the system-(the province to which the user belongs-> shard). In this way, every user knows the province and the Shard to which the table belongs. Because route search is required for each data operation, it is a good way to store the content on an independent cache, such as memcached. This splitting method also brings about another benefit. When shard needs to be added, it can be executed without affecting online applications.ProgramYour design must be applicable to this increase.

 

 

Although the application sharding will bring obvious benefits, it also has some inherent problems that need to be understood. These problems are roughly divided into the following categories,

 

1. Shard resizing

 

When the current shard cannot meet the current application requirements, you need to expand the shard database. Adding shard means that you need to migrate the original shard data. This process is very complex, in addition, it may cause data inconsistency (writing and migration at the same time) or other application problems. Therefore, resizing usually takes effect in the early hours of the morning.

 

2. Join multiple shard table data queries

 

This is an inherent shard problem. When you encounter such a problem, you need to obtain the data of each shard and then summarize the data, most of the time, due to the current relatively developed network speed, this problem can be almost ignored. However, if you want to analyze or mine data, shard may have problems. Generally, in the case that data requirements are not so real-time, the shard data can be synchronized to the summary database. OLAP can be performed on this summary database, which requires timed data synchronization on each shard, this increases the complexity of the program; using the sharding solution would be a devastating blow if real-time access is required.

 

3. Others

 

Our current system is sharding Based on the route table, and we need not so real-time summary data to provide data analysis and mining, at the same time, our basic data is managed in the summary database and copied to the shard node through Oracle's Advanced Replication. When the shard database synchronizes data to the summary database, we implement it through the stored procedure of the Oracle database. This architecture leads to the complexity of the database, and there are some other problems, for example, synchronization will be interrupted for no reason... This requires some other means to maintain data latency consistency.

 

Our sharding is still improving, our shard is still increasing, and we still need to work hard to make our applications more efficient.

 

Sometimes we think our society is like a huge multi-layer sharding solution, such as central, provincial (autonomous region), and municipal...

 

-------------------------------------------------------------

Another database solution is master-slave. A master is mainly responsible for data updates, and then replicates data to each slave node through advanced replication and other means. The slave node is responsible for query. No matter whether the master and slave have all the data, there is a certain delay in the data from the master to the slave. It can be used in combination with the sharding solution.

 

Database sharding technology, as a concept of "new bottled old wine", has been given a new meaning in the new application environment. With the development of cloud computing, sharding has become increasingly popular in recent years. More and more products claim to support sharding. So what is sharding? What benefits does sharding bring to your database application. What are the sharding algorithms available for implementing a sharding system. This article will solve these problems for you.

I. Introduction

1. Background

Database expansion is an eternal topic. Traditional relational databases are scaled up, that is, you can buy better machines and add more resources to achieve better performance (such as hardware upgrades, faster CPU, larger memory, and larger disks ). In the form of parallel databases and distributed databases, the specific details depend on the technology of horizontal or vertical partitions. Through scaleup, relational databases have dominated traditional enterprise application environments for nearly 30 years.

However, in recent years, with the surge in data volume, especially the emergence of cloud computing models, this expansion model is not suitable for some applications, and then there is a scale out model. This method uses some ad-hoc technologies, such as master-slave, replication, and server cache, load Distribution to multiple physical nodes. In addition, sharding technology has gradually developed and attracted the attention of the masses in recent years.

2. What is sharding?

Sharding is an effective way to scale a database out to multiple physical nodes. The word "shard" means "fragments ". If a database is treated as a large glass and broken, each small piece is called a database fragment (databaseshard ). The process of breaking the entire database is called sharding, which can be translated as sharding.

Formally, sharding can be defined as a partitioning scheme that distributes large databases to multiple physical nodes. Each partition contains a certain part of the database, called a shard. The partitioning method can be arbitrary, not limited to the traditional horizontal and vertical partitions. A shard can contain the content of multiple tables or even the content of multiple database instances. Each Shard is placed on a database server. A database server can process one or more shard data. The system requires a server to forward the query route. The server is responsible for forwarding the query to the shard or shards node that contains the data accessed by the query.

3. Comparison between sharding and partition

Sharding is closely related to partitions. The technology they adopt is essentially similar. It can be said that sharding is based on partitions. In some cases, sharding may be a horizontal partition. In addition, fragment is used in some documents (partitions in parallel databases are called partition and fragment in distributed databases ). \ Ref footnote 1

Foot Note 1:

[[

Daniel C. zilio. Physical Database Design demo-algorithms and concurrentreorganization for parallel database systems. PhD thesis 1997.

M. Tamer özsu, Patrick valduriez. Principles ofdistributed database systems, third edition. Springer. 2011

]

But what we call sharding and partitioning are quite different. Below is a list:

(1) Different scaling methods. Sharding belongs to scaleout, while partitioning belongs to the scale up mode.

(2) different purposes. The purpose of partitioning is to process a query in parallel so that all nodes can process a query in parallel. sharding allows each node to process different queries as much as possible.

(3) Application scenarios: partition is applicable to traditional enterprise applications, especially OLAP applications. Basically, each query needs to access most of the data. sharding is applicable to cloud web applications, there are a large number of users and queries, but each query has very few tuples. sharding can distribute the load to multiple physical nodes.

(4) Availability: basically every query in a distributed database requires the participation of all nodes. If some nodes are down, the system will be greatly affected; the sharding process generally involves only a few nodes, so the availability of sharding is better. In addition, the distributed database requires a master node to generate an execution plan and coordinate the execution of relevant nodes, which easily forms a single point of failure.

(5) segmentation granularity: Generally, partitions are only divided within a database. sharding can be divided by database granularity, therefore, it can be used to build a multi-tenant database system ).

4. Advantages of sharding

Sharding has the following advantages:

(1) database scalability is improved. As applications grow, more servers can be added. You only need to put the newly added data and load on the newly added server.

(2) improves Database Availability. After several shard servers are down, the system does not stop the service, but only affects the users who need to access the data on these shard servers.

(3) The query pressure on small databases is relatively small, the query speed is faster, and the performance is better.

(4) The system has better manageability. The system upgrade and configuration can be performed one by one according to Shard, without a major impact on the service.