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

What this article shares with you is about how to analyze the bottleneck caused by a select sentence. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Situational restoration:

On the second day after the launch of a new project of the company, it was found that there was a bottleneck in the network IO between the database server and the IIS server, and the network bandwidth of 1GB occupied 70%, 100%, that is, 700MB-1GB per second, and the memory of the database was as high as 21GB. The CPU utilization of IIS servers often explodes to 80% Murray 90%, resulting in frequent connection timeouts on websites.

Reason: at night, I had to temporarily shut down the website, maintain the server, and do a comprehensive check and tracking. I found that a Select sentence led to: Select * From Table1 this sentence, the syntax is fine, but there is a problem in the application. Table1 stores more than 100,000 rows of data, and the table data grows by tens of thousands every day. In order to count the total number of rows, this statement is called frequently, refreshing no less than 1000 times per second. As a result, there is a bottleneck in the network.

Solution: the Select statement was modified later.

The code is as follows: Select Count (*) from Table1

The problem can be solved, the network IO data will immediately drop below 10MB, and the database memory usage will remain in the expected range of 12GB. It seems like a very simple question, but in fact it is not. It takes 6 hours to solve this problem, 1 hour to check the problem, and 5 hours to modify the code.

The above is how to analyze the bottleneck caused by a select sentence. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.