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

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

Recently, we have made an online switch of the cluster service, switching the original master-slave environment. Of course, the processing work of the back-end is relatively complex, involving the online migration and hardware changes of the master-slave server.

Overall, the read latency after handover is about 0.4 milliseconds lower than the original. For a quarter-sensitive business with delay, 0.4 milliseconds is a very high ratio, which is almost optimized by 25-30% according to the established proportion rule.

So which part of the 0.4 milliseconds saved is optimized? We did some discussion and analysis, not only secretly sighed, but fortunately it was optimized, and if the delay increased by 30%, it would be very stressful to analyze quickly.

The difficulty of analyzing this problem lies in the existence of a series of dynamic variables, which leads to the lack of some benchmarks and metrics in the comparison before and after service switching.

We simply refine the following: the optimization work before service switching is as follows:

1) the new slave library uses a new hardware configuration, and the CPU model is higher, with a performance improvement of up to 30%

2) different brands are used in the storage of SATA-SSD used in the master-slave library.

3) before the master-slave switch, the data table is completely defragmented in the slave database.

4) the system disk of the original server uses SAS, which is configured according to the SATA-SSD class in the new slave library.

5) the middleware server has not been restarted for nearly 2 years, and this maintenance has been restarted.

In addition, there are some uncertain factors, such as differences in business volume, which are unlikely to be fully analyzed according to the standard of 100% equivalence, so we try to analyze them through the following logic.

Initially, we think that if we clean up the fragmentation of the data table, the effect of this improvement will be relatively high, because the fragmentation rate is nearly 20% before switching and nearly 20% is released after cleaning. It was originally thought that such a set of indicators system is relatively evolved, but after comparing the business delays in recent days, there is a business data that does not make sense.

We assume that it is business 2, and the data of business 2 represents the daily table, which means that there is no so-called fragmentation in this table, because a new data table is written to the data every day. Therefore, it makes sense that there should be no change or slight difference in the delay of Business 2, but it can be clearly seen here that the delay has increased by nearly 30%, which does not make sense. So the benefits brought by simple debris cleaning are not as high as expected.

We analyze the load of the system, randomly select a shard node, and view the load data before and after handover.

Before switching

After switching

Overall, the average load has increased by 0.03, which is a very low proportion and can be ignored for the time being.

Let's take a look at the utilization of CPU. Because the performance of the new server CPU has improved by nearly 30%, we have made an overall comparison of the load situation. We can find that this business is not strongly computing-related. Therefore, no matter how well CPU is configured, the benefits brought here are not as obvious as expected.

After switching

Before switching

Let's take a look at the disk. There are many metrics at the disk level. Let's take a look at the util metrics:

After switching:

Before switching:

The overall util after switching has increased from 16% to 18%, so it is difficult to give an objective conclusion.

For the analysis of disk read-write level, we can see that the delay part of waiting is not an order of magnitude.

After switching

Before switching

There may be a misunderstanding here, that is, the index before switching is not accurate enough, which is 0. We can enlarge the index, that is, select one of the indicators, and we can see the relatively accurate data of the index on the right.

After switching:

Before switching:

At this point, we can see that the delay indicator is still very different for logical volumes and different partitions. Although the improvement of a single indicator is about 10%, all indicators are slightly higher.

Of course, defragmentation has improved, but the proportion is not that high compared with the improvement at the disk level.

The next problem is further verification, which needs to analyze some differences and stability test data between different SSD products.

Generally speaking, the whole process of analysis can provide an analytical idea, not just a preliminary conclusion.

Thank you for reading, the above is the content of "how to understand MySQL cluster optimization". After the study of this article, I believe you have a deeper understanding of how to understand MySQL cluster optimization, 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.