The company has several sites in the United States on the virtual mainframe, the MySQL service on the server almost every day will suddenly not know when to hang up, and then recover after a while, suspect is beyond the CPU usage limit and is automatically ended, but in fact the traffic on the server is very small. So earlier when contacted the server provider of India's three customer service, want to see if other users to do a lot of harm to the people die together, a three look after, vowed to pat the chest of long hair is not their problem, things did not solve. Hanging is not a thing, had to check their own, but can access to the INFORMATION_SCHEMA library, look, no words, user_statistics inside the data shows us a MySQL user in busy_time,cpu_ Time and other indicators are high to no, their own things, fortunately O did not find. So hurry to check the program, the previous Web site procedures are not made by me, but know that there are a lot of problems, architecture to implement have problems, but the page is not more general, code mixed with HTML, all see the past is not dead, (this time is particularly the feeling of MVC more wonderful), usually can make do with the operation on it, Anyway, no traffic.
Since it is the burden of MySQL, then find this, local to engage in a Web site mirroring run under the My.ini to modify the add
Copy Code code as follows:
[Mysqld]
Log= "D:/temp/mysql.log"
Log_slow_queries= "D:/temp/mysql_slow.log"
Long_query_time=1
This directory is to be already in existence. Restart the MySQL service and you can log it.
Look at the SQL record after a surprise, the number of inquiries is staggering, random page, SQL query in dozens of, many thousands!
To the Forum, a page of the database query number is 10 times, the use of the cache can be lower. In this way, it is equivalent to dozens of times times the original burden, can not hang?
Who can not have perseverance to write down hundreds of inquiries ah, so it must be circular query. The SQL statement also shows this. Know why a good change, find the relevant page, get rid of circular query, for example, there is a page, to show the classification of all regions and the number of articles under the classification, regardless of the structure of the database optimization, in terms of procedures, the original is probably such a
Copy Code code as follows:
$sql 1= "Select Aid,count (*) as CC from Pz_content WHERE uid= $uid Group by aid";
$rs 1= $db->query ($sql 1);
if (Is_array ($rs 1)) {
foreach ($rs 1 as $r 1) {
Output...
Echo id2name ($r 1->aid);
}
}
............
function Id2name ($aid)
{
$sql = "Select ename from Pz_area_a where aid_a=". $id;
$result =mysql_query ($sql);
$row =mysql_fetch_object ($result);
return $row->ename;
}
Regardless of the code fault-tolerant, see the implementation, he read the relevant articles of the user and by region ID for the group and statistics, and then each region to output the region name, so, if there are 1w regions, here will be queried 1w times. Put on a timed code, looked at, probably consumed memory 6M, execution time 16 seconds, cumulative query 1001 times
In fact, here is what you can do with just one sentence of SQL, you don't need to loop.
Copy Code code as follows:
$sql 1= Select pz_area.aid,pz_area.ename,tb1.cc from Pz_area right join (select Aid,count (*) as CC from Pz_content WHERE UI d= $uid GROUP BY-Aid) as TB1 on Pz_area.aid=tb1.aid ";
$rs 1= $db->query ($sql 1);
if (Is_array ($rs 1)) {
foreach ($rs 1 as $r 1) {
Output...
Echo $r 1->ename;
}
}
The problem will be solved. Rerun, the memory consumes almost, query 1 times, CPU execution time only 647 milliseconds, and the original difference 26 times times! Look again, found that this pz_content table, records are relatively many, there are often to query by region and so on, but what the original index did not. By adding an index to the aid, the execution time is shortened to 432 milliseconds.
This page of things to forget, first here, next time to continue.
