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

This article mainly explains "how to optimize MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how to optimize MySQL".

Case background

Case analysis

MySQL master-slave structure

Case solution

Summary

Case background

Suppose your company faces the double 11 promotion and invests a lot of marketing expenses on platform promotion, which brings huge traffic. if you are the technical person in charge of the order system, how do you deal with the sudden read and write traffic?

This is a typical application scenario, and I think many R & D engineers will answer: use Redis as the cache of MySQL, and then when users view the "order center", query the order cache to help MySQL resist most of the query requests.

One of the principles of applying caching is to ensure that the cache hit rate is high enough, otherwise many requests will penetrate the cache and eventually hit the database. However, in scenarios such as "order center", each user's order is different, and unless the database order information is fully cached (which brings architectural complexity), the cache hit rate is still very low.

Therefore, in this scenario, cache can only be used as a pre-protection mechanism for the database, but a lot of traffic will be sent to the database, and with the increasing number of user orders, more and more read and write traffic will be requested on the MySQL. What to do when a single MySQL cannot support a large number of concurrent requests?

Case analysis

The access traffic of most systems on the Internet is more read and write less, and the gap between read and write requests may reach several orders of magnitude, just as the number of views of your products on JD.com must be much larger than the number of orders you place.

So if you want to optimize the database to withstand high query requests, the first thing to do is to distinguish between read and write traffic areas, so as to facilitate separate expansion for read traffic, this process is the "read-write separation" of traffic.

Read-write separation is the preferred solution to improve MySQL concurrency, because when a single MySQL cannot meet the requirements, it can only use a cluster of multiple MySQL instances with the same data to undertake a large number of read and write requests.

MySQL master-slave structure

The premise of MySQL to separate read and write is to split the MySQL cluster into a "master + slave" data cluster, so as to realize the separation of read and write in the program, and the data of the master library and slave database of the MySQL cluster are synchronized through master-slave replication.

Then the interviewer will ask you, "how does the MySQL cluster achieve master-slave replication?" To put it another way, "what does MySQL do when you commit a transaction to the MySQL cluster?" Interviewers will often use this question as a starting point to find out your understanding of the principle of master-slave replication in MySQL clusters, and then simulate a business scenario where you can come up with an architectural design to solve the master-slave replication problem.

So, for the interviewer's routine, you should be prepared for the following:

Master the architecture design ideas under the scenario of reading more and writing less, and know that caching can not solve all problems. "read-write separation" is an important means to improve the concurrency ability of the system.

In-depth understanding of the master-slave replication of the database, master its principles, problems, and solutions.

Proceed from the practice, achieve the cognitive abstraction of technology, and look at the design from the level of methodology.

Case solution

The principle of MySQL master-slave replication, whether it is "how does the MySQL cluster achieve master-slave replication" or "what does the MySQL cluster do when you commit a transaction to the MySQL cluster?" The interviewer mainly asks you: what is the process of master-slave replication of MySQL?

In general, master-slave replication of MySQL depends on binlog, which records all changes on MySQL and saves them on disk in binary form. The process of replication is to transfer the data in the binlog from the master library to the slave library. This process is generally asynchronous, meaning that the thread performing the transaction operation on the main library does not wait for the thread replicating the binlog to complete synchronously.

To facilitate your memory, I sorted out the master-slave replication process of the MySQL cluster into three stages.

Write Binlog: the main library writes binlog logs, commits transactions, and updates locally stored data.

Synchronous Binlog: copy the binlog to all slave libraries, and each slave library writes binlog to the temporary log.

Playback Binlog: play back the binlog and update the stored data.

But you can't just talk about these stages in the interview, you should explain the data synchronization process between the master library and the slave library in as much detail as possible, in order to make the interviewer feel the solidity of your skills (the detailed process is as follows).

After receiving the request from the client to commit the transaction, the MySQL main library will first write to the binlog, then commit the transaction, update the data in the storage engine, and return the response of "successful operation" to the client after the transaction is committed.

A dedicated log dump O thread is created from the slave library, which connects to the log dump thread of the master library to receive the binlog log of the master library, writes the binlog information to the relay log of relay log, and returns the response to the master database "copied successfully".

The slave library will create a thread to play back the binlog, read the relay log relay log, and then play back the data in the binlog update storage engine, resulting in master-slave data consistency.

After the master-slave replication is completed, you can write only the master library when writing the data and read only the slave library when reading the data, so that even if the write request locks the table or the record, it does not affect the execution of the read request.

At the same time, when the read traffic is relatively large, you can deploy multiple slave libraries to share the read traffic. This is the "one master, multi-slave" deployment method, which you can use to resist high concurrent read traffic in vertical e-commerce projects. In addition, the slave library can also be used as a backup library to avoid data loss caused by the failure of the master database.

MySQL has one master and multiple slaves

Of course, once you mention "one master, many followers", it is easy for the interviewer to set a trap to ask you: when there is a lot of traffic, can you resist the concurrent read requests as long as you add a few more slave libraries?

Of course not.

Because of the increase in the number of slave libraries, there are more log dump O threads connected from the library, and the master library has to create the same number of Imax threads to handle replication requests, which consumes a lot of resources and is limited by the network bandwidth of the master library. Therefore, in practical use, one master database generally follows two or three slave databases (one set of databases, one master, two slaves and one master). This is the MySQL cluster structure of one master and multiple slaves.

In fact, you can also find from the MySQL master-slave replication process that MySQL defaults to asynchronous mode: the thread that commits the transaction in the MySQL master library does not wait for the binlog to synchronize to each slave library before returning the client result. In this mode, once the main database goes down, the data will be lost.

At this point, the interviewer will generally ask you, "what other models are there for MySQL master-slave replication?" There are three main types.

Synchronous replication: the transaction thread waits for a successful response from all replications from the library.

Asynchronous replication: the transaction thread does not wait for a successful replication response from the slave library at all.

Semi-synchronous replication: a replication method added after MySQL version 5.7. in between, the transaction thread does not have to wait for all the slave database replications to respond successfully, as long as part of the replication responds successfully, such as a cluster with one master and two slaves. As long as the data is successfully copied to any slave database, the transaction thread of the master database can return to the client.

This semi-synchronous replication method takes into account the advantages of both asynchronous replication and synchronous replication. even if the master database is down, at least one slave database has the latest data, and there is no risk of data loss.

At this point, you have basically mastered the principle of MySQL master-slave replication, but if the interviewer wants to tap your architectural design capabilities, he will also examine how to solve the problem of MySQL master-slave replication delay in terms of architecture design, such as asking you, "what solutions are there to solve the problem of master-slave replication delay in system design?"

Solve the master-slave replication delay from the architecture

Let's design a solution of master-slave replication delay based on the actual case.

In the e-commerce platform, every time users post product comments, they will first call comment review, in order to carry out operations such as speech monitoring, picture porn detection and so on.

After updating the main database, the commodity release module calls the review module asynchronously, and transmits the comment ID to the review module, and then the comment review module uses the comment ID query to get the complete comment information from the library. At this point, if there is a delay in the master-slave database, the comment information will not be available in the slave database, and an exception will occur in the whole process.

Master-slave delay affects the real-time performance of comment reading

This is a query exception caused by master-slave replication delay. There are many solutions. I will provide you with several solutions.

Use data redundancy

When you call the audit module asynchronously, you can not only send the product ID, but also send all the comment information needed by the audit module, so as to avoid re-querying the data from the library (this solution is simple and easy to implement, it is recommended that you choose). But you should pay attention to the parameter size of each call, too large messages will take up network bandwidth and communication time.

Use caching to solve

You can write the comment data to the Redis cache while writing to the main database, so that other threads will query the cache first when getting the comment information, which can also ensure the consistency of the data.

However, this method will cause problems of cache and database consistency. For example, two threads update data at the same time. The steps are as follows:

Thread A first updates the database to 100, when thread B updates the data in the database and cache to 200, and then thread A updates the cache to 100, so that the value 200 in the database is inconsistent with the value 100 in the cache.

In general, when the MySQL master-slave replication delay is resolved by caching, there will be inconsistencies between the database and the cached data.

Directly query the main database

When using this scheme, you must be careful. You should make it clear in advance that the amount of data queried is small, otherwise there will be write requests locking in the main database, which will affect the execution of read requests, and eventually cause great pressure on the main database.

Of course, in addition to examining your understanding of MySQL master-slave replication delay in terms of architecture, the interviewer will also ask you some extended questions, such as: when MySQL separates the master from the slave, the use of the database has changed. in the past, only one database address was needed to operate the database, but now it is necessary to use one master database address and multiple slave database addresses, and to distinguish between write operations and query operations. Then from the engineering code design, how to achieve the master library and slave database data access?

Realize the database access of master library and slave database

A simple way is to configure all data sources in the project in advance, and each data source corresponds to a master library or slave library, then modify the code, judge in the code logic, and send the SQL statement to a specified data source for processing.

This scheme is simple and easy to implement, but SQL routing rules invade the code logic and are not conducive to code maintenance in complex projects.

Another approach is: independently deployed proxy middleware, such as MyCat, this type of middleware is deployed on independent servers, generally using the standard MySQL communication protocol, can proxy multiple databases.

The advantage of this scheme is that the access complexity of the underlying database and the upper application is isolated, and it is more suitable for companies with independent operation and maintenance teams to select the type; the defect is that all SQL statements have to be transmitted across the network twice, resulting in a certain performance loss, and the operation and maintenance middleware is a professional and complex work, which requires a certain amount of technical precipitation.

Summary

Starting from a case, we understand that in the case of more reading and writing of Internet traffic, it is necessary to improve the concurrency ability of the system through "read-write separation", and because the premise of "read-write separation" is to build a "master + slave" data cluster architecture, so we also talked about the principle of master-slave replication and how to solve the delay caused by master-slave replication.

Thank you for your reading, the above is the content of "how to optimize MySQL". After the study of this article, I believe you have a deeper understanding of how to optimize MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.