Cases of high CPU usage and memory usage in MySQL

Cases of high CPU usage and memory usage in MySQL

Faults:
At about seven o'clock, I received a complaint from the playback center that the video was playing slowly and could not be loaded for a long time. At first, he thought it was the tomcat service that went down again. View the output logs of catalina. out on the tomcat server. But no task error message is found.

Analysis:
After thinking about the slow loading of videos, will it be a database problem? Check the top of the mysql database (from the database) as follows:
Pid user pr ni virt res shr s % CPU % mem time + COMMAND
37258 mysql 20 0 17.2g 12g 5032 S 769.5 81.3 4383: 29 mysqld

I didn't expect the cpu to reach 769%!
Go to the mysql slow query statement directory and check slow. log.
Select count (*) as col_0_0 _ from card_received cardreceiv0 _ where (cardreceiv0 _. statusCode = '1' or cardreceiv0 _. statusCode = '2') and (cardreceiv0 _. ownerCardNum = '000000' or cardreceiv0 _. ownerPhoneNum = '000000') and cardreceiv0 _. readStatus = 0 \ G;

It is found that this query statement takes about 5 seconds, but slow. log contains all this statement. So I think it is suspicious.
Use the explain command to analyze the problem.
Mysql> explain select count (*) as col_0_0 _ from card_received cardreceiv0 _ where (cardreceiv0 _. statusCode = '1' or cardreceiv0 _. statusCode = '2') and (cardreceiv0 _. ownerCardNum = '000000' or cardreceiv0 _. ownerPhoneNum = '000000') and cardreceiv0 _. readStatus = 0 \ G;


Explain result:
* ************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: cardreceiv0 _
Type: ref
Possible_keys: readStatus, ownerCardNum, statusCode, ownerPhoneNum
Key: readStatus
Key_len: 5
Ref: const
Rows: 2394190
Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

Actually scanned: 2394190 rows ....
After communication with developers, the table data is not important here. Therefore, some old data is cleared, indicating that 2 million rows have been cleared! At last, the cpu of mysql was reduced...
However, to completely solve the problem, you still need to optimize the statement and create an index .. Otherwise, there will certainly be problems if there is more data.
Well, this fault only provides some ideas for solving the problem. The specific problem still needs to be analyzed.

This article permanently updates the link address: