The company has several web sites on the United States virtual host, the server's MySQL service almost every day will suddenly not know when to hang up, and then resumed after a while, suspicion is exceeded the CPU usage limit and is automatically ended, but actually the traffic on the server is very small. So the earlier time contacted the server provider of India, the third customer service, want to see if other users to make a lot of harm to everyone died together, ah three searched after, vowed to pat the breasts of the chest guarantee is not their problem, things have not been resolved. Hanging is not a thing, had to check their own, fortunately can access to the INFORMATION_SCHEMA library, looked at, no words, user_statistics inside the data show one of our MySQL users in busy_time,cpu_ Time and other indicators are high to no, their own things, fortunately three o did not find. So hurriedly check the program, before this site program is not done by me, but know that there are a lot of problems, the architecture to the implementation of the problem, but the page is not more general, the code is mixed with HTML, look at the past is not dead, (this time, especially the idea of MVC is wonderful), usually can make a run on it, Anyway, there's not much to visit.
Since it is the heavy burden of MySQL, then look for this, the local to engage a site image run, in the My.ini to modify the addition
Copy CodeThe code is as follows:
[Mysqld]
Log= "D:/temp/mysql.log"
Log_slow_queries= "D:/temp/mysql_slow.log"
Long_query_time=1
This directory is already in existence. Restart the MySQL service and you can log it.
Look at the SQL records surprised, the number of queries is amazing, a random page, SQL queries are in dozens of, many have thousands!
To the Forum, a page of the database query number is 10 times, with the cache can be lower. In this case, it is equivalent to the original dozens of times times the burden, can not hang?
Who can not have the perseverance to write down hundreds of queries Ah, so must be circular query. The SQL statement also shows this. Know the reason to change, find the relevant page, get rid of the circular query, for example, there is a page, to show all the regional classification and the number of articles under the classification, first of all, regardless of the structure of the database optimization, in terms of the program, the original is probably such a son
Copy CodeThe code is 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;
}
First of all, regardless of code fault tolerance, to see the realization, he first read the user's relevant articles and the number of groups and statistics by region ID, and then each region of each region to export the region name, so, if there are 1w regions, here to query 1w times. Put on a timed code, looked at, about the memory consumption of 6M, the execution time 16 seconds, the cumulative query 1001 times
In fact, here is just a sentence of SQL can be done, do not need to loop.
Copy CodeThe code is 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 can be solved. Rerun, memory consumption is similar, query 1 times, CPU execution time is only 647 milliseconds, and the original difference of 26 times times! Look again, found this pz_content table, records are relatively more, there are often to be divided by regional query, such as, but the original index did not. By adding an index to the aid, the execution time is reduced to 432 milliseconds.
This page of things forget, first come here, next time continue.
http://www.bkjia.com/PHPjc/322364.html www.bkjia.com true http://www.bkjia.com/PHPjc/322364.html techarticle The company has several websites on the United States virtual host, the server's MySQL service almost every day will suddenly do not know when to hang up, and then after a while back again, suspicion is beyond ...
