Shulou(Shulou.com)06/01 Report--

The following brings you about how to effectively optimize the Mysql bottleneck, if you are interested, let's take a look at this article. I believe it will be of some help to you after reading how to effectively optimize the Mysql bottleneck.

Mysql bottleneck optimization (the transition from small business to large business) assume that a website (discuz) starts with a small number of visitors to tens of millions of visits per day, let's speculate on the evolution of its mysql server architecture. 1. In the first stage, the daily pv volume of website visits is less than 1w. A single machine runs web and db without the need for architectural layer tuning (for example, no need to increase the memcached cache). At this time, the data is often cold backup every day, but sometimes if you consider the data security, you will build a mysql master and slave. two。 In the second stage, the daily pv of website visits reached tens of thousands. At this point, a single machine has a bit of a load, so we need to separate web from db, and we need to build a memcached service as a cache. In other words, at this stage, we can also use a single machine to run mysql to undertake the entire site data storage and query. If you do mysql master and slave, the purpose is also for data security. 3. In the third stage, the daily pv of website visits reached hundreds of thousands. Although a single machine can also support, but the required machine configuration is much better than the previous machine. If funds permit, you can buy highly configured machines to run mysql services, but it does not mean that the configuration doubles and the performance doubles. At a certain stage, the increase in configuration can no longer bring about an increase in performance. So, at this stage, we will think of clusters that do mysql services, that is, we can run mysql with multiple machines. However, the mysql cluster is different from the web cluster, and we need to consider the consistency of the data, so we cannot simply apply the way of web cluster (lvs,nginx proxy). The architecture that can be done is that mysql master and slave, one master and multiple slaves. In order to ensure the robustness of the architecture and the integrity of the data, there can only be one master and multiple slaves. There is another problem that we need to think about, that is, in the front-end web layer, our program specifies the ip of the mysql machine, so how to configure it in the program when there are multiple mysql machines? Discuz, in fact, has a feature that supports mysql read-write separation. That is, we can run mysql with multiple machines, one of which is written, and the other is read. We just need to configure the read and write ip into the program, and the program will automatically distinguish between machines. Of course, if we don't use the configuration that comes with discuz, we can also reference a software called mysql-proxy, which can be used to achieve read-write separation. It supports the mode of one master and multiple slaves. 4. The fourth phase of the website visits pv to millions of people a day. The previous one-master and multi-slave mode has encountered a bottleneck, because when the number of visits to the website becomes larger and larger, the amount of reading the database will become larger and larger, we need to add more slaves, but when the number of slaves increases to dozens, because the master needs to distribute all the bin-log to all slaves, then the process itself is a very tedious thing, coupled with frequent reading, it is bound to cause great delay in synchronizing data from the top. Therefore, we can do an optimization to change the original one master and multiple slaves of mysql into one master and one slave, and then as the master of other slaves, while the former master is only responsible for the writing of the website business, while the latter is never responsible for any business of the website, only responsible for synchronizing the bin-log to other slaves. In this way, you can continue to stack several more slave libraries. 5. In the fifth phase, when the daily visit volume of the website pv reached 10 million, we found that the amount of writing to the website was very large, and there was only one master in our previous architecture, and the master here has become a bottleneck. Therefore, we need to make further adjustments. For example, we can divide the business into modules, separate the user-related ones, separate permissions, points, and so on, and run a separate library, and then do master and slave, that is, the so-called sub-library. Of course, you can also change a latitude, separate tables with large visits or writes, run on a server, or divide a table into multiple small tables. This step involves some procedural changes, so you need to communicate and design with your development colleagues in advance. In short, what we need to do in this step is to divide the database and table.

After further development, we can continue to divide the big tables into small ones. The amount of data on the domestic Ali Taobao website is huge, their databases are all mysql, and their mysql architecture follows the principle of sub-database and sub-table, but their division rules will have many latitudes, such as division according to region, division according to buyers and sellers, division according to time, and so on.

Read the above details on how to effectively optimize the bottleneck of Mysql, whether there is any gain. If you want to know more about it, you can continue to follow our industry information section.

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.