How to prevent performance degradation after MySQL database upgrade

InfoQ 2022-01-26 11:28:32 阅读数:625

prevent performance degradation mysql database
First of all :MySQL Why should the database be upgraded , About how often
First MySQL Each version of has a corresponding Endlife cycle , At this stage MySQL Of Endlife The cycle is as follows :

From the picture above , The current version in the official support life cycle is MySQL 8.0,  among MySQL 5.7 be in Extended Support That means only security updates will be done , Other parties
Front end training
The face is not being processed ,MGR Many of our features are not going to 5.7 Merge in . So the best way at the moment is to upgrade to MySQL 8.0. So let's talk about how often we upgrade . This can be divided into three situations
a.  Positioning upgrade time according to product form
b.  Make decisions based on service performance and cost
c.  Depending on what is used MySQL Characteristics make decisions
Three points above , If you look at it from the perspective of the need to upgrade , You can look at it the other way around . If you look at it from a business perspective .
Let me first describe , We can understand the upgrade time according to the shape of the product , Let me give you an example , For example, the products I experience are :SNS,  Mail system , Lottery system ,IM, Online retailers , payment , Live broadcast and other related services . The mail system is a very special business , He told the DB I don't depend on you very much , For the use of databases , Just record some information about user authentication and mailing list , Concurrent requirements are not high , So now there are still some systems running MySQL4.1 It's still running well on the Internet , Many systems have exceeded 1500 No restart for more than days . For this kind of business , There is basically no need to upgrade , about MySQL Dependence is very light , It's just simple CRUD Use , Upgrading doesn't make a big difference , It adds complexity .
The other type of business belongs to high growth business :SNS, lottery ,IM, Online retailers , payment , This kind of system basically has a strong demand for performance , Good performance means a better user experience . In general, this kind of business suggestion is that large version updates can be upgraded , for example :MySQL 5.5 Than MySQL5.1 Good performance , MySQL5.6 Than MySQL5.5 Good performance ,MySQL 5.7 And than MySQL5.6 Good performance ,MySQL 8.0 It's rich in functions. For development ,DBA Management and other aspects are more friendly , And if your hardware is 32 individual core above , You can also consider upgrading to MySQL8.0, If the hardware is older (cpu core Less than 32,  Not used ssd The above disk ) Can't get upgraded to 8.0 Performance advantages .
Therefore, the general recommendation for business form positioning is to have a large version update ,MySQL After the big version is stable, it can be upgraded , Through time intervals in 1-2 Year's situation , Another kind of business that is not data dependent , No, I didn 't Bug You don't have to upgrade .
Make decisions based on service performance and cost . From current experience MySQL8.0,MySQL 5.7 The performance of the basic is the previous version of the sling , The utilization rate of resources is also higher , So in MySQL 5.7, MySQL 8.0 These two versions can be said to be MySQL Very good version . For both versions, it is recommended to use MySQL 8.0, If the condition ( Machine resources are rotten ) You can also consider using it MySQL 5.7. Usually, the company's business is also developing ,MySQL The new version of the upgrade can always bring positive promotion , It also promotes the company's technical personnel to pay more attention to the development of new knowledge .
Now let's look at how to use MySQL It depends on the characteristics of , And the most important point . Using features mainly refers to :CRUD Use , Copy ,MGR And so on , about CRUD I'm more concerned about MySQL Of SQL analysis , The number of connections ,Buffer The optimization of the , The optimization of index is basically with the increase of version , There will be corresponding optimization ; Now let's talk about replication , at present MySQL We can say that MySQL5.7 It's the perfect way to deal with it , So people who use the copy function , Use MySQL The minimum version of should be MySQL5.7 And open based on writeset Parallel replication of , This feature is available in MySQL8.0 Priority is given to , Later merged into MySQL5.7 in ; Let's move on MGR This feature , If you've already used MGR, You're a tech radical , And congratulations to you now MySQL8.0.22 And beyond MGR Very stable , It's ready to use . but MGR It's kind of MySQL8.0 A new feature in , There will also be some feature updates and Bug Repair , So it is recommended to use MGR Friend, , If you still use 8.0.23 Previous versions , Consider upgrading to 8.0.23 Later version . For using relatively new features , You need to focus on every small version , If there is an important fix in the small version , You can also arrange small versions to test , Upgrade in about a month , This is particularly common among enterprise version customers .

second :  Before upgrade, during upgrade and after upgrade, as well as matters requiring cooperation of business application side
Database upgrade is a work that the whole company needs to work together . Here we can put the work first , Before upgrading ,  Upgrading , Key issues after upgrading and business application side needs to cooperate .
Preparation and related work before upgrading :
1. MySQL It is necessary to be able to compare the version of . Functional verification ,bug verification , Performance verification
2.  Optimal profile determination
3.  New version of functional environment verification , At the same time, I will explain the features of the new version and the precautions for use to the developers
4.  With business ( Core business ) Performance testing , This step needs to be deployed according to the production environment
5.  Confirm the time when the business can be upgraded , Using different upgrade schemes
6.  After upgrading checklist To develop
7.  Expect the contrast point after upgrade

Upgrade and related work :
1.  Night assignment list and schedule
2.  Apply to DB Service interruption handling strategy , Good upgrade plan , You may only need DB The service interruption is below the second level , Let's talk about it in detail in the upgrade plan .
3.  Stop the alarm and alarm upgrade of the corresponding system of the database before the upgrade
4.  Log observation of business during upgrade
5.  database OPS,DML Compare with before upgrade
6.  New online DB The system is added to the monitoring system
7.  Fallback system deployment plan and notification development

The end of the upgrade and related work :
1.  Running report after upgrade
2.  Development side database related log collection and comparison
3.  Fallback system offline
From the above strategy, we can say that we should press more work to the front , In particular, the preparation work before upgrading also takes up a lot of time . At this point DBA We also have a lot of testing work .
For the upgrade scenario , It involves product function verification ,bug verification , Performance verification , The determination of upgrade plan, etc .  When it comes to upgrading, it may involve MySQL There is an improvement in the performance of , Or meet important Bug. In fact, there is a skill that has been ignored : It's officially released mrr test test . As individuals , You can do this by putting the lower version of mrr test  Test in the target version , You can compare the two versions under the same configuration mrr test Time to finish the run , And specific case Time to finish the run , Pass the test from DBA The angle can be used to evaluate the difference between the current version and the target version to be upgraded . This can be done in reverse , Put the higher version of mrr test Run once at low cost , You can see what you've got Bug And the corresponding things , Friendship tips , The job requires a powerful machine , It's also a time-consuming process . It is recommended to read carefully change log Find the corresponding fix , Test the problems concerned with the corresponding repair . And then you can go through sysbench, mysql-tpcc This kind of tool is used to test two versions , Get a better configuration file ,  At the same time, we get two versions in Bug, Guidance on all aspects of digital quantitative upgrade in terms of performance . Later, we can hand it over to the colleagues of function test and performance test .

Third :  How to plan MySQL Upgrade plan
The best solution for the upgrade is less downtime , Minimize the impact on the business . Here is an upgrade scheme that takes into account the fallback scheme , It's also a relatively safe upgrade plan in my opinion . Upgrade with replication technology . The general steps are as follows :

In the 2 Step by step , By adding a slave Library , Only for data synchronization , After confirming that there is no problem, you can enter Proxy or DNS China and foreign countries provide services , If you have problems, you can go offline immediately .
The first 3 Step , You can take one of the original slave Also upgrade to a new version .

The first 4 Step , All read requests can be transferred to the slave library , The original slave version doesn't need to provide services ; Watch two new slave External services .
The first 5 Step ,  adopt HA Switch the original Old Master Switch to slave Library , And provide external services , When there is no problem, upgrade the last one from the library to the latest version
The first 6 Step , Put the original version slave Stop external services , All use the new version to provide services .
The benefits of this upgrade , Basically, it can be used for data preparation during the day , New node creation , Old node replacement, etc , In the low peak period of business , It's just an update Proxy Configure or take a HA Switch , Relatively speaking, the external influence is very small , Fallback processing is also quite easy .

This technology that can be used :
proxy  recommend :ProxySQL
HA Switch recommendation :Orchestrator, Xenon
If you don't want to use Proxy that Zookeeper, Consul,etcd You can also use .

Fourth : How to plan MySQL Upgrade fallback scheme
A good upgrade plan is to bring your own fallback , Advance and attack , Retreat and defend , For example, the above scheme , It's an excellent upgrade plan .  If based on the above scheme , For any point of failure , In the 6 Step forward , It's easy to go back to the old version and provide services , This process may be a waste of one more machine , It can also be said to be an example , In a multi instance environment , It's a very easy solution to deal with . In the customer environment that I deal with, it is usually recommended that they have a unique port number for each of them , So it's easier to deal with multiple instances than to get started .

The fifth :  How to avoid MySQL Performance degradation after upgrade
Most of the time MySQL There will be some performance improvement after the upgrade , But it is also inevitable that the performance will decline . For example, I have a performance degradation problem :MySQL After upgrading, a business timeout is serious ,  The problem was that when we were upgrading the data , Development also updates the application , It's a bit difficult for us to compare this issue . So try to change the business side as much as possible during the upgrade , Introduce fewer variables . How to avoid performance degradation after upgrade , My biggest experience is that I must do well : Performance testing , Performance test of basic business , It can also be said that it is a full link performance test sampling comparison .

The sixth :  Performance degradation problem diagnosis and performance optimization solution after upgrade
The problem can be said to be DBA A key task in our work , It's also a very complicated problem . Try to assume here that you have guaranteed performance testing , A functional test , All the functional tests with business have passed , In the functional environment , The performance environment confirms that the new version has performance improvement . Finally, it's online , There are new performance problems .  Then there is a performance problem at this time , By contrast sys.statement_analysis The content in , See if there is any inconsistency with the functional environment SQL, In addition, you can also combine slow logs to check whether the content is consistent with the previous slow logs , It's easy to compare the two points of performance degradation . If used ProxySQL It can also be in ProxySQL Check out each SQL The response time distribution of , It's easy to check the performance problems . by force of contrast SQL Sample list , It's easier to find out why . Another kind of problem is the failure of statistical information ,show processlist Show a lot of Statics Relevant information , It can be cleared by collecting index information or reorganizing table space ; Another class is a character configuration error , Cause index invalidation .

The seventh : summary
As of today MySQL The technology is very mature , Many companies have put MySQL It is also a persistent service in the micro service architecture , It's easy to use . But for the upgrade part , If you want to reduce problems , Spend as much time as possible testing . We also need to gradually improve the database architecture , In this way, the later upgrade management is more friendly .
If you encounter problems in the upgrade, can you quickly locate , I think there are two abilities , On the one hand is MySQL DBA My basic skills and abilities , The other is the degree of familiarity with the business , This is also what I pay more attention to in the interview or colleague assessment , for example : The core system , What are the core tables , The core SQL What are they , What is the distribution of requests per day , The peak of the core library DML What's the operating volume , How many unique ones are there in the whole business system SQL, If we can have a clear understanding of these problems , So it's quite easy to check the performance problems of the whole system . It can also be said that upgrading is a job with high requirements for all staff , It's also a work that needs cooperation , At the same time, I also hope that 80% Spend more time testing . The real upgrade , Maybe with automatic tools , It can also be done very quickly .
Original author : Wu Bingxi
copyright:author[InfoQ],Please bring the original link to reprint, thank you.