Use partition and server load balancer to obtain a larger and better MySQL database _ MySQL

Using partition and server load balancer to achieve greater and better MySQL database optimization and use is a very complicated issue. so how can we make the database bigger and better? This article will show you how to use partition and load balancing solutions to make your MySQL bigger and better.

Larger MySQL

Adding more MySQL instances is an effective way to speed up Application Response. if your server has multiple CPUs, sufficient memory, and fast hard disks, A considerable portion of these resources are idle. in this case, multiple MySQL instances can be run on the server at the same time, because MySQL has only one process and multiple session threads by default, therefore, the maximum hardware resources it can use are limited.

If your server is saturated, you must add more servers, no matter whether multiple MySQL instances are on one server or multiple servers, you need to configure a method for the application to know which server the query is sent to. if you want to modify the data, you should send the command to the master database instance, if it is only a query operation, you can send it to any slave database instance.

1. data partition and horizontal partition

Because many Web applications recognize users through sessions, it makes sense to allocate them to different slave database instances through sessions. For example, a A-G, a H-O database instance may be working, and users can be assigned to different servers through the hash of the user name or userid, which is called the partition key, when selecting the partition key, you need to be careful when deciding, because it will affect how you build slave database instances, mainly to consider how to make these servers take on average workload, if the choice is not good, if the database is dumped, data may be interrupted.

If you are using this type of partition, you need to decide which database to use when the program runs. this can be achieved through a middle layer such as the MySQL proxy. although it is still in the Alpha stage, it has a good idea, many people have used it in the production environment. it runs on the server and responds to requests on port 3306, then, some logic implemented by these queries through high-speed languages such as lua is forwarded to the appropriate backend servers.

Second, you can also specify the server to which the query is sent in the application. this is also the most flexible method. you can completely control the entire decision-making process, or you can use master_pos_wait to check the slave database instance, check whether they have enough computing resources. The programming language or Web framework you are using may also provide support in this regard. if you are not clear about it, you can query their documents.

You can also study Continuent tunsten, DBIx: DBCluster for Perl and SQLRelay. they support many different programming languages and databases. Similarly, CMS, such as Drupal, also supports multiple read-only slave databases. you only need to enable this function.

Another thing to consider when using this architecture is whether to use the primary database instance and when to use it. generally, all the insert, update, and delete operations should be completed on the primary database instance, all query operations are performed on the slave database instance. For example, if a user comments a blog post and directly uses the slave database, it may not be completed because the MySQL replication architecture lags behind, at this time, the blog post may not be available from the database.

Checking outdated data is a better way. if you have a report query that runs at night, this method may work well. you just need to make sure that the replication can catch up with the progress.

Another method is to track database changes by version number and determine whether the data is the latest version before reading the data.

Finally, MySQL provides the master_pos_wait function to determine the time from which the database is updated.

2. function partitioning

You may have used functional partitions. when using functional partitions, you need to create a copy of the production database for different purposes, such as one for the data warehouse and report, and the other for text search.

Use server load balancer to make MySQL better

If your slave database already has some read-only data, you may need to implement load balancing and evenly distribute the traffic to each slave database. there are multiple implementation methods, such as random allocation and least connection, the fastest response speed method or a weighted average method. although some hardware load balancing devices can provide load balancing functions, they are often designed to balance network traffic, database-related balancing functions are not provided.

Fortunately, there are many software solutions, and the LVS project is a good candidate. it has developed quite mature and stable. It provides load balancing algorithms similar to DNS round robin, however, the implementation speed at the IP layer is very fast. In addition, many projects are built on LVS, including wackamole, which is based on peer-to-peer networks. therefore, no single point of failure (SPOFs) will occur. a recommended project is ultramonkey.

Summary

MySQL provides many advanced features to achieve unlimited scaling. different application environments have different best solutions. Therefore, before using MySQL for production databases, we recommend that you first fully understand the various solutions and try to build a testing platform with the same load as the production environment for testing.