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

This article is about how SQL causes the shopping cart service to become unavailable. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Overview

I have dealt with a shopping cart malfunction before, and I think it is quite classic. I would like to share it with you here. This failure directly led to the failure of operations such as adding a shopping cart at the front end and getting a list of user shopping carts. Shopping cart is the entrance, once there is a problem, the impact is extremely serious.

Temporary treatment

All interfaces of the shopping cart service, which have print response time, are found to be much slower than usual. As the situation was urgent, I had to restart the shopping cart, buffer it, and then use the buffer time to locate the problem.

Problem positioning

The previous shopping cart application based on Spring Cloud micro-service has been running steadily for several months, and it has been tested before launch, and the interface performance is no problem. Why is there a problem all of a sudden? According to past experience, most of the failures are caused by SQL statements, so first export all slow SQL statements in the database (Tencent Cloud has a tool to export slow SQL) and find that most slow queries are SQL statements from inventory queries, and some even take 10 seconds to execute.

Later, take a closer look, inventory slow query sentence, to query the inventory of goods a lot more than usual. If the number of goods is small, this sentence is still very fast, once more, it begins to slow down.

Solution

Due to the historical reasons of inventory calculation system, this SQL is difficult to optimize. The situation is urgent again, and the big boss has been asking what's going on. So temporarily change the code to store the commodity library in Redis cache. The shopping cart service allows inventory data not to be real-time, because the subsequent settlement and payment will calculate the inventory in real time, and will prompt the user when the inventory is insufficient.

Note:

Because the shopping cart is the entrance, the traffic is very large, and from the shopping cart to the settlement page and then to the payment, because there is an operation step, the flow of the settlement page and payment page is not as large as the shopping cart.

Some users have a lot of product data on their shopping carts, but they may not all buy them. Users can also check the items they want to buy and then place orders.

Some users do not have the habit of cleaning up the failed items in the shopping cart, resulting in a lot of goods on the shopping cart.

Ultimate solution

Separate the inventory service, put the commodity inventory data into the cache, and introduce the mechanism of real-time refreshing the inventory data in the cache to keep the data in the cache as fresh as possible. In this way, when querying inventory, most of it can be obtained from the cache and will not penetrate into the database.

Supplement

When we perform pressure testing on the interface, in some scenarios, we need to consider the number of input parameters. We cannot simply pressure the interface with a few data and feel that the performance OK does not matter.

Thank you for reading! This is the end of the article on "what to do if the shopping cart service cannot be used due to SQL". I hope the above content can be of some help to you, so that 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.