MySQL database CPU, MEM High utilization resolution case

Fault:

At about 7 o'clock in the evening, received the Broadcast center complaint, said the video playback is slow, loading for a long time not come out. At first, brother thought it was Tomcat service and hung up. So see the next Catalina.out output log on the Tomcat server. But did not find the task error message.

Analysis:

Think, video loading slow, can it be a database problem? Decisively on the MySQL database (from the library) look at the top below:

PID USER PR NI VIRT RES SHR s%cpu%MEM time+ COMMAND 37258 mysql 0 17.2g 12g 5032 s 769.5 81.3 4383:29 mysqld

I didn't expect the CPU to reach 769%!

Then go to the directory under MySQL's slow query statement and look at the Slow.log

Select COUNT (*) as col_0_0_ from Card_received cardreceiv0_ where (cardreceiv0_.statuscode= ' 1 ' or cardreceiv0_. Statuscode= ' 2 ') and (cardreceiv0_.ownercardnum= ' 8757003738566209 ' or cardreceiv0_.ownerphonenum= ' 13724689717 ') and Cardreceiv0_.readstatus=0\g;

It took about 5 seconds to find the query, but it was all in Slow.log. So I think it's suspicious.

And then look at it with explain analysis.

Mysql> explain select count (*)  as col_0_0_ from card_received  cardreceiv0_ where  (cardreceiv0_.statuscode= ' 1 '  or cardreceiv0_.statuscode= ' 2 ')  and   (cardreceiv0_.ownercardnum= ' 8757003738566209 '  or cardreceiv0_.ownerphonenum= ' 13724689717 ')  and cardreceiv0_.readstatus=0\g;  explain Results: ************************** 1. row  ***************************           id: 1            select_type: SIMPLE            table: cardreceiv0_            type: refpossible_keys: readstatus,ownercardnum,statuscode, ownerphonenum           key: readstatus            key_len: 5           ref:  const           rows: 2394190            Extra: Using where1 row in set  ( 0.00 SEC)  error: no query specified

Actually scanned: 2394190 lines ....

Finally, after communication with the development, because this table data is not important. So will some old data situation, said has cleared 2 million lines! Finally, the CPU of MySQL finally lowered ...

However, to completely solve the problem, you still need to refine the statement, to build the index. Otherwise there will be more data, there must be a problem.

OK, this fault is only to provide some ideas to solve the problem. The specific problem still needs to be analyzed concretely.

This article is from the "it--Brother" blog, please make sure to keep this source http://402753795.blog.51cto.com/10788998/1761719

MySQL database CPU, MEM High utilization resolution case