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

This article will explain in detail what are the pits in the programming of MYSQL, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Recently, a BUG of ORACLE 12C has occupied the official account of Wechat. If you take a closer look, it's actually all right. Every kind of database will have problems after the new version, in fact, not only the database, but every software contains BUG, and how to deal with it becomes important after discovering BUG.

Take MYSQL as an example, using a test library of MGR of percona 5.7.23, the disk environment is not very ideal, this is what we know earlier, I am slow. Then there is such a design, because the information of the customer is to be processed and the information is sent to the bank, and in the process of verifying the user, the original design is to verify and insert in batches, and it is found that there is a problem with the information of a customer. It is directly called back to this batch of information, and later, due to business requirements, it is said to come one by one, so that the matching customer information in this package will not be delayed. (business considerations are reasonable.)

Then, just after the program was modified, the servers in MYSQL's MGR cluster began to show the following NOTE, and it was obvious that there was a problem with replication.

The NOTE in the image above is not an ERROR, but it is clearly a rising number of waited at clock conflicts and the number after the when workers occupied (milliseconds).

After communicating with the programmer, I found that it was originally a batch insert, but now it is a single insert. Database ERS knows that any database inserts tend to insert and commit in batches. (please don't misunderstand the meaning of batch, batch refers to a batch of comprehensive transactions like stored procedures, such as a bad design such as ORACLE updating hundreds of thousands of statements to write a UPATE statement). This is related to the principle of the database itself. The consumption caused by batch insertion is not the same as the pressure of a single fast circular insert on the database's I pump O system, and not only insert but also insert a CHECK on the unique index of the inserted table, so tables with unique indexes consume more performance when inserting at a high speed. (in fact, there are many things to consider in the design of a program, and sometimes it is very simple, but the limitations of the business and the system itself put forward higher requirements for the developers of the program. At the same time, a different order of magnitude also directly affects the overall design of the system.)

In fact, we should also think of a combination of application-level monitoring and system performance monitoring, often with poor system performance, often accompanied by just changing the system code, adding new functions, and so on. How to build a monitoring system that can reflect the details of the system becomes very important.

As a result, programmers try to modify programs that do not conform to the principles used by the database, and then look at them.

On the MYSQL program design of which holes to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.