CPU load foarte mare de la mysql

[ironmanro]

Salut.

De doua zile ma confrunt cu o problema: load foarte mare din cauza mysql. uneori ajunge si la 17

VPS config: 8GB ECC RAM 40GB RAID10 SSD 4x Intel E5 v3 Cores, Debian 7, VestaCP

 

Am gasit vinovaul, un script de "curatare a traficului". Scopul lui e ca fiecare site din db sa fie vizitat o singura data de pe un ip. Scriptul primeste in jur de 200000 - 250000 hituri pe zi.

(M-am gandit sa fac treaba asta folosind cookies dar majoritatea folosesc private browsing si nu cred ca merge)

 

Din ce am vazut cand am loadul mare asta ar fi vinovatul: SELECT * FROM sites WHERE status = '1' AND NOT EXISTS (SELECT idu FROM user_sites WHERE ip_address = '$ip' AND user_sites.siteid = sites.siteid) ORDER BY rand() LIMIT 1

 

Inainte sa radeti si cu fundul de mine vreau sa spun ca sunt incepator la asta, sunt foarte multe chestii pe care nu le stiu.

Va rog sa ma ajutati cu niste sfaturi sau macar sa ma puneti pe directia cea buna

 

Multumesc mult!

 

<?php


$ip = $_SERVER['REMOTE_ADDR'];
$ref = $_SERVER['HTTP_REFERER'];
$ua = $_SERVER['HTTP_USER_AGENT'];
   $proxy_headers = array(
        'HTTP_VIA',
        'HTTP_FORWARDED_FOR',
        'HTTP_X_FORWARDED',
        'HTTP_FORWARDED',
        'HTTP_CLIENT_IP',
        'HTTP_FORWARDED_FOR_IP',
        'VIA',
        'X_FORWARDED_FOR',
        'FORWARDED_FOR',
        'X_FORWARDED',
        'FORWARDED',
        'CLIENT_IP',
        'FORWARDED_FOR_IP',
        'HTTP_PROXY_CONNECTION'
    );

$con=mysqli_connect('localhost','#############','#############','############');


    foreach($proxy_headers as $x){
        if (isset($_SERVER[$x])) $useprxy = 1;
    }



if ($useprxy != 1)
{
$sql = "SELECT * FROM sites WHERE status = '1' AND NOT EXISTS (SELECT idu FROM user_sites WHERE ip_address = '$ip' AND user_sites.siteid = sites.siteid) ORDER BY rand() LIMIT 1";
$result = mysqli_query($con, $sql);

	while ($row = $result->fetch_assoc()){
		$url=$row['url'];
		$siteid=$row["siteid"];
		$method=$row["method"];
		$status=$row["status"];
		$g=$row["g"];
		$t=$row["t"];}


	if ( !empty($siteid)){	

	$sql = "INSERT INTO user_sites (ip_address, siteid) VALUES ('$ip', '$siteid')";
	$insertip = mysqli_query($con, $sql);
	mysqli_close($con);
	$sql = "UPDATE sites SET visited_today = visited_today + 1, visited_total = visited_total + 1 WHERE siteid = $siteid";
	$result = mysqli_query($con, $sql);
	mysqli_close($con);
	
	if ($method == "Google" || $method == "google") 
		{
			header("refresh: 5; url=$g");		
		}
	elseif ($method == "Twitter" || $method == "twitter")
		{			header("refresh: 5; url=$t");		

		}
	elseif ($method == "Blogger" || $method == "blogger")
		{
			header("refresh: 5; url=$burl");		
		}
	else 
		{
			echo '<a href="'.$url.'" rel="noreferrer" id="autoclick"></a>';
			echo "<script>setTimeout(document.getElementById'autoclick').click ),5000);</script>";
		}	


	} else {
		$sql = "UPDATE duplicate SET duplicate = duplicate + 1";
		$result = mysqli_query($con, $sql);
  		$sql1 = "SELECT * FROM duplicate WHERE id = 1";
		$select1 = mysqli_query($con, $sql1);
		while ($row = $select1->fetch_assoc()){
		$durl=$row['url'];}
		echo '<a href="'.$durl.'" rel="noreferrer" id="autoclick"></a>';
		echo "<script>setTimeout(document.getElementById('autoclick').click(),5000);</script>";}	
		mysqli_close($con);	
	}
}
else
{
	$sql = "INSERT INTO proxy (proxy) VALUES ('$ip')";
	$insertip = mysqli_query($con, $sql);
	mysqli_close($con);
			header("refresh: 5; url='https://google.com'");		

}

?>

 

Structura DB:

Sites

	1 	siteid 	int(11) 			No 	None 	AUTO_INCREMENT 	
	2 	site_name 	text 	latin1_swedish_ci 		No 	None 		
	3 	url 	text 	latin1_swedish_ci 		No 	None 		
	4 	method 	text 	latin1_swedish_ci 		No 	None 		
	5 	status 	text 	latin1_swedish_ci 		No 	None 		
	6 	g 	text 	latin1_swedish_ci 		No 	None 		
	7 	t 	text 	latin1_swedish_ci 		No 	None 		
	8 	visited_today 	int(11) 			No 	None 		
	9 	visited_total 	int(11) 			No 	None 		



user_sites

	1 	idu 	int(11) 			No 	None 	AUTO_INCREMENT 	
	2 	ip_address 	text 	latin1_swedish_ci 		No 	None 
	3 	siteid 	int(11) 			No 	None
	4 	expires 	datetime 			No 	None 

Setari mysql din etc/mysql/my.cnf

skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 32M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8

#innodb_use_native_aio = 0
innodb_file_per_table

max_connections=200
max_user_connections=50
wait_timeout=10
interactive_timeout=50
long_query_time=5