Shulou(Shulou.com)05/31 Report--

This article shows you how to use partitions and load balancing to get a larger and better MySQL database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The optimization and use of MySQL database is a very complicated task, so how can we make the database bigger and better? This article will show how to make your MySQL bigger and better through partitioning and load balancing solutions.

Larger MySQL

Adding more MySQL instances is an effective way to improve the response speed of your application. If your server has multiple CPU, sufficient memory and fast hard disk, and a considerable part of these resources are idle, in this case, you can run multiple MySQL instances on the server at the same time, because MySQL has only one process and multiple session threads by default. Therefore, the maximum hardware resources that it can really use are limited.

If your server is almost saturated, you must add more servers. Whether your multiple MySQL instances are on one server or on multiple servers, you need to configure a method for your application to let it know which server to send the query to. If you want to modify the data, you should send instructions to the master database instance. Then you can send it to any slave database instance.

1. Data partition and horizontal partition

Because many Web applications identify users through sessions, it makes sense to assign them to different slave database instances through sessions. For example, the database instance of Amura G, HM, O, P, Z may be working, and users can be assigned to different servers through the hash value of the user name, or userid. This is the so-called partition key, so you need to decide carefully when choosing the partition key, because it will affect how you build the database instance, mainly to consider how to make these servers bear the workload evenly, if you do not choose well. If you dump it from the database, it may also cause data interruption.

If you are using this kind of partition, you need to decide which database to use when the program runs, which can be achieved through an intermediate tier such as the MySQL agent, which is still in the Alpha phase, but it has a good idea, and many people have used it in the production environment, it runs on the server and responds to requests on port 3306. These queries are then forwarded to the appropriate back-end server through some logic implemented in high-speed languages such as lua.

Second, you can also specify which servers to send queries to in your application, which is the most flexible way, you can have complete control over the entire decision-making process, or you can use master_pos_wait to check from database instances to see if they have sufficient computing resources. And the language or Web framework you use may also provide this support, if you are not clear, you can consult their documentation.

You can also study Continuent Tungsten,DBIx::DBCluster for Perl and SQLRelay, which support many different programming languages and. Similarly, CMS such as Drupal also supports a variety of read-only slave databases, you only need to enable this feature.

Another thing to consider when using this architecture is whether to use the master database instance and when to use it. In general, all insert, update and delete operations should be done on the master database instance. All query operations are done on the slave database instance. For example, if a user comments on a blog post, it may not be completed if you use the slave database directly, because there is a lag in the MySQL replication schema, and the blog post may not be available from the database at this time.

Checking outdated data is a better way, and if you have a report query that runs at night, it may work well, you just need to make sure that the replication is up to speed.

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

Finally, MySQL provides a function, master_pos_wait, that determines when to update from the database.

2. Functional zoning

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

Make MySQL better through load balancing

If you already have some read-only data from the database, you may need to achieve load balancing and distribute traffic evenly to each slave database, such as random distribution, least connection method, fastest response method, or some kind of 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, LVS project is a good candidate, it has developed quite mature and stable, it provides a load balancing algorithm similar to DNS polling, but implemented in the IP layer, the speed is very fast. In addition, there are many projects based on LVS, including wackamole, which is based on peer-to-peer networks, so there is no single point of failure, and another recommended project is ultramonkey.

MySQL provides many advanced features to achieve unlimited scale expansion, and there are different best solutions for different application environments. Therefore, it is necessary to fully understand the various solutions before using them in the production database, and try to build a test platform equivalent to the load of the production environment for testing.

The above content is how to use partition and load balancing to obtain a larger and better MySQL database. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.