Search the Community

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