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

This article is about how mysql select count (*) has led to an increase in CPU usage. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

#

Msyq version: mysql 5.5

Operating system: CentOS 5

2019-06-05

#

Recently, the monitoring alarm shows that the CPU utilization rate has intermittently risen to more than 95%. Through show processlist; inspection, it is found that there are more than 60 active session executing the same SQL: select count (*) from xxxxx (there is no where clause here).

When you see this, you may think, Oh, the problem has been found, because this SQL has led to an increase in CPU usage. Is such a judgment a bit arbitrary?

Is the increase in CPU usage a cause or an effect? Or is it because of the increase in CPU usage or the increase in CPU usage due to the fact that there are multiple session executing the same SQL in the database at the same time?

Later, the R & D team cached the result set of the SQL on the application, and the problem was solved. This shows that it is the SQL that causes the increase in CPU usage, but I have a question: operations of the count type, without sorting, should not take up more of the resources of the count O?

With this question, I Baidu did not find the answer, and then I did a test: check the values of Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads through show status before and after select count (*) from xxxx. It turns out that the SQL requests disk IO very few times, indicating that a large number of data retrieval is in memory. This explains why queries of type count (*) cause CPU to rise.

Thank you for reading! This is the end of the article on "how mysql select count (*) leads to the increase in CPU usage". 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.