mir_trend.php - www.eclipse.org/downloads - Git at Google

 <?php
 /*******************************************************************************
 * Copyright (c) 2006-2016 Eclipse Foundation and others.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *    Denis Roy (Eclipse Foundation) - initial API and implementation
 *******************************************************************************/

 # This is intended to run on Eclipse servers from a command-line (or cron) facility
 # to gather mirror performance and accuracy trends

 # If launched from httpd we will return our list of URLs so others can gather mirror
 # trend info

   require_once "/home/data/httpd/eclipse-php8-classes/system/dbconnection_rw.class.php";

   # If launched from the cli, we're expecting "downloads.git" and "eclipse.org-common.git"
   # to be checked out at the same level
   if(php_sapi_name() == "cli") {
     $_SERVER['DOCUMENT_ROOT'] = "../";
     $_SERVER['SERVER_NAME'] = "cli";  # satisfy app.class
   }
   else {
     header("Content-type: text/plain");
   }
   require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/classes/mirrors/mirror.class.php");

   # Connect to database
   $dbc = new DBConnectionRW();
   $dbh = $dbc->connect();

   $app = new App();

   $timeout = 30;  # seconds to wait for each fopen

   $mirror = new Mirror();

   # Load up file index
   # We only need one file, but we'll fetch 100 and exit as soon as one is found on disk
   $drop_list = array();
   $valid_file = false;
   $sql = "SELECT file_name, IF(size_disk_bytes BETWEEN 2000000 AND 5000000, 1, 0) AS ideal_size, IF(size_disk_bytes BETWEEN 250000 AND 10000000, 1, 0) AS almost_ideal_size, size_disk_bytes
         FROM download_file_index WHERE timestamp_disk > 0 order by ideal_size*RAND() desc, almost_ideal_size*RAND() desc, size_disk_bytes asc limit 100";
   $rs_idx = mysqli_query($dbh, $sql);
   while($myrow_idx = mysqli_fetch_assoc($rs_idx)) {
     $drop_list['EclipseFull']['filename'] = $myrow_idx['file_name'];
     $drop_list['EclipseFull']['size_disk_bytes'] = $myrow_idx['size_disk_bytes'];

     if(file_exists($app->getDownloadBasePath() . $myrow_idx['file_name']) && !$mirror->isExcluded($myrow_idx['file_name'])) {
       $valid_file = true;
       break;
     }
   }
   if(!$valid_file) {
     die("ERROR: Unable to locate a valid file to trend!");
   }


   # instead of inserting trend records as we go along, we will put the sql queries into an array
   # that way, if all of the fetched files fail, the problem is not the mirrors, it's the file
   $sql_array = array();
   $total_trends = 0;
   $total_errors = 0;


   # Go through mirrors
   $sql = "SELECT DISTINCT MIR.mirror_id, MRP.base_path, DRP.our_path
           FROM mirrors AS MIR INNER JOIN mirror_protocols AS MRP on MRP.mirror_id = MIR.mirror_id
           INNER JOIN (SELECT mirror_id, protocol, drop_id from mirror_drops group by mirror_id, protocol) MRD ON MRD.mirror_id = MIR.mirror_id
           INNER JOIN drops AS DRP ON DRP.drop_id = MRD.drop_id AND MRD.drop_id = 'EclipseFull'
           WHERE MIR.is_internal <> 1 AND MIR.is_advertise = 1 AND MRP.protocol = 'http' AND MIR.create_status IN('active', 'wait') ORDER BY mirror_id";
   $rs = mysqli_query($dbh, $sql);

   while($myrow = mysqli_fetch_assoc($rs)) {
     $mirror_url = $myrow['base_path'] . $drop_list['EclipseFull']['filename'];

     if(php_sapi_name() != "cli") {
       echo $mirror_url . "\n";
     }
     else {
       echo "Processing mirror: " . $myrow['mirror_id'] . " " . $mirror_url . "\n";

       $curl_info = getCurl($mirror_url);

       if(!is_int($curl_info)) {
         echo "Result: Code [" . $curl_info['http_code'] . "]  Speed: [" . $curl_info['speed_download'] . "] Downloaded: [" . $curl_info['size_download'] . "]  Expected: [" . $drop_list['EclipseFull']['size_disk_bytes'] . "]\n";
         $download_speed = $curl_info['speed_download'] / 1024;
         if($curl_info['size_download'] < $drop_list['EclipseFull']['size_disk_bytes']) {
           $download_speed = 0;
           $curl_info['http_code'] = "999";
         }
         $sql = "INSERT INTO mirror_trends (trend_id, mirror_id, sample_date, http_status_code, download_speed_kbps, fetched_url, fetched_size_bytes)
                 VALUES (NULL, " . $myrow['mirror_id'] . ", NOW(), " . $curl_info['http_code'] . ", " . $curl_info['speed_download'] / 1024 . ", '$mirror_url', " . $curl_info['size_download'] . ")";
       }
       else {
         $sql = "INSERT INTO mirror_trends (trend_id, mirror_id, sample_date, http_status_code, download_speed_kbps)
                 VALUES (NULL, " . $myrow['mirror_id'] . ", NOW(), " . $curl_info . ", 0)";
         $total_errors++;
       }
       echo $sql . "\n";
       array_push($sql_array, $sql);
       $total_trends++;

       # Examine all the trends for this mirror
       # mark as dropped if they have 0% success overall
       $sql = "SELECT SUM(IF(http_status_code BETWEEN 200 AND 299, 1, 0)) / COUNT(trend_id) * 100 AS success_rate, COUNT(trend_id) AS trend_count FROM mirror_trends where mirror_id = " . $myrow['mirror_id'];
       $rs_trd = mysqli_query($dbh, $sql);
       if($myrow_trd = mysqli_fetch_assoc($rs_trd)) {
         echo "Overall success rate for this mirror: " . $myrow_trd['success_rate'] . "\n";
         if($myrow_trd['success_rate'] == 0 && $myrow_trd['trend_count'] > 0) {
           echo "Dropping mirror " . $myrow['mirror_id'] . "\n";
           mysqli_query($dbh, "UPDATE mirrors SET create_status = 'dropped' WHERE mirror_id = " . $myrow['mirror_id']);
         }
       }

       # mark as wait if they have 0% success in the last 12 hours
       # or mark back to active if 100%
       # HAVING clause to prevent bug 474570
       $sql = "SELECT /*mir_trend:123 */ SUM(IF(http_status_code BETWEEN 200 AND 299, 1, 0)) / COUNT(trend_id) * 100 AS success_rate
               FROM mirror_trends where mirror_id = " . $myrow['mirror_id']
                  . " AND sample_date > date_sub(now(), interval 12 hour) HAVING success_rate IS NOT NULL";
       $rs_trd = mysqli_query($dbh, $sql);
       if($myrow_trd = mysqli_fetch_assoc($rs_trd)) {
         echo "Last 12h success rate for this mirror: " . $myrow_trd['success_rate'] . "\n";
         if($myrow_trd['success_rate'] == 0) {
           echo "Supending (wait) mirror " . $myrow['mirror_id'] . "\n";
           mysqli_query($dbh, "UPDATE mirrors SET create_status = 'wait' WHERE mirror_id = " . $myrow['mirror_id']);
         }
         elseif ($myrow_trd['success_rate'] == 100) {
           echo "Unsupending (active) mirror " . $myrow['mirror_id'] . " if needed.\n";
           mysqli_query($dbh, "UPDATE mirrors SET create_status = 'active' WHERE mirror_id = " . $myrow['mirror_id'] . " AND create_status = 'wait'");
         }
       }
     }
   }

   # Insert trends if we have good success rate
   if($total_trends > 0) {
     $error_rate = $total_errors/$total_trends * 100;
     if($error_rate < 75) {
       echo "Adding trends to the database\n";
       foreach ($sql_array as $sql) {
         mysqli_query($dbh, $sql);
       }

       # maintenance
       $sql = "DELETE FROM mirror_trends where sample_date < DATE_SUB(NOW(), INTERVAL 1 MONTH)";
       mysqli_query($dbh, $sql);
     }
   }

   $dbc->disconnect();
   $rs = null;
   $dbh = null;
   $dbc = null;

   function getCurl($URL) {
     global $timeout;

     $rValue = false;

     $ch = curl_init($URL);
     curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
     curl_setopt($ch, CURLOPT_TIMEOUT,$timeout);
     curl_setopt($ch, CURLOPT_TIMEOUT,$timeout);

     curl_setopt($ch, CURLOPT_LOW_SPEED_LIMIT, 50*1024);
     curl_setopt($ch, CURLOPT_LOW_SPEED_TIME, 10);
     curl_setopt($ch, CURLOPT_FAILONERROR, 1);


     // Execute
     curl_exec($ch);

     // Check if any error occurred
     if(!curl_errno($ch)) {
       $info = curl_getinfo($ch);
       $rValue = $info;
     }
     else {
       $rValue = curl_errno($ch);
     }

     // Close handle
     curl_close($ch);
     return $rValue;
   }

 ?>
	<?php
	/*******************************************************************************
	* Copyright (c) 2006-2016 Eclipse Foundation and others.
	* All rights reserved. This program and the accompanying materials
	* are made available under the terms of the Eclipse Public License v1.0
	* which accompanies this distribution, and is available at
	* http://www.eclipse.org/legal/epl-v10.html
	*
	* Contributors:
	* Denis Roy (Eclipse Foundation) - initial API and implementation
	*******************************************************************************/

	# This is intended to run on Eclipse servers from a command-line (or cron) facility
	# to gather mirror performance and accuracy trends

	# If launched from httpd we will return our list of URLs so others can gather mirror
	# trend info

	require_once "/home/data/httpd/eclipse-php8-classes/system/dbconnection_rw.class.php";

	# If launched from the cli, we're expecting "downloads.git" and "eclipse.org-common.git"
	# to be checked out at the same level
	if(php_sapi_name() == "cli") {
	$_SERVER['DOCUMENT_ROOT'] = "../";
	$_SERVER['SERVER_NAME'] = "cli"; # satisfy app.class
	}
	else {
	header("Content-type: text/plain");
	}
	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/classes/mirrors/mirror.class.php");

	# Connect to database
	$dbc = new DBConnectionRW();
	$dbh = $dbc->connect();

	$app = new App();

	$timeout = 30; # seconds to wait for each fopen

	$mirror = new Mirror();

	# Load up file index
	# We only need one file, but we'll fetch 100 and exit as soon as one is found on disk
	$drop_list = array();
	$valid_file = false;
	$sql = "SELECT file_name, IF(size_disk_bytes BETWEEN 2000000 AND 5000000, 1, 0) AS ideal_size, IF(size_disk_bytes BETWEEN 250000 AND 10000000, 1, 0) AS almost_ideal_size, size_disk_bytes
	FROM download_file_index WHERE timestamp_disk > 0 order by ideal_sizeRAND() desc, almost_ideal_sizeRAND() desc, size_disk_bytes asc limit 100";
	$rs_idx = mysqli_query($dbh, $sql);
	while($myrow_idx = mysqli_fetch_assoc($rs_idx)) {
	$drop_list['EclipseFull']['filename'] = $myrow_idx['file_name'];
	$drop_list['EclipseFull']['size_disk_bytes'] = $myrow_idx['size_disk_bytes'];

	if(file_exists($app->getDownloadBasePath() . $myrow_idx['file_name']) && !$mirror->isExcluded($myrow_idx['file_name'])) {
	$valid_file = true;
	break;
	}
	}
	if(!$valid_file) {
	die("ERROR: Unable to locate a valid file to trend!");
	}


	# instead of inserting trend records as we go along, we will put the sql queries into an array
	# that way, if all of the fetched files fail, the problem is not the mirrors, it's the file
	$sql_array = array();
	$total_trends = 0;
	$total_errors = 0;


	# Go through mirrors
	$sql = "SELECT DISTINCT MIR.mirror_id, MRP.base_path, DRP.our_path
	FROM mirrors AS MIR INNER JOIN mirror_protocols AS MRP on MRP.mirror_id = MIR.mirror_id
	INNER JOIN (SELECT mirror_id, protocol, drop_id from mirror_drops group by mirror_id, protocol) MRD ON MRD.mirror_id = MIR.mirror_id
	INNER JOIN drops AS DRP ON DRP.drop_id = MRD.drop_id AND MRD.drop_id = 'EclipseFull'
	WHERE MIR.is_internal <> 1 AND MIR.is_advertise = 1 AND MRP.protocol = 'http' AND MIR.create_status IN('active', 'wait') ORDER BY mirror_id";
	$rs = mysqli_query($dbh, $sql);

	while($myrow = mysqli_fetch_assoc($rs)) {
	$mirror_url = $myrow['base_path'] . $drop_list['EclipseFull']['filename'];

	if(php_sapi_name() != "cli") {
	echo $mirror_url . "\n";
	}
	else {
	echo "Processing mirror: " . $myrow['mirror_id'] . " " . $mirror_url . "\n";

	$curl_info = getCurl($mirror_url);

	if(!is_int($curl_info)) {
	echo "Result: Code [" . $curl_info['http_code'] . "] Speed: [" . $curl_info['speed_download'] . "] Downloaded: [" . $curl_info['size_download'] . "] Expected: [" . $drop_list['EclipseFull']['size_disk_bytes'] . "]\n";
	$download_speed = $curl_info['speed_download'] / 1024;
	if($curl_info['size_download'] < $drop_list['EclipseFull']['size_disk_bytes']) {
	$download_speed = 0;
	$curl_info['http_code'] = "999";
	}
	$sql = "INSERT INTO mirror_trends (trend_id, mirror_id, sample_date, http_status_code, download_speed_kbps, fetched_url, fetched_size_bytes)
	VALUES (NULL, " . $myrow['mirror_id'] . ", NOW(), " . $curl_info['http_code'] . ", " . $curl_info['speed_download'] / 1024 . ", '$mirror_url', " . $curl_info['size_download'] . ")";
	}
	else {
	$sql = "INSERT INTO mirror_trends (trend_id, mirror_id, sample_date, http_status_code, download_speed_kbps)
	VALUES (NULL, " . $myrow['mirror_id'] . ", NOW(), " . $curl_info . ", 0)";
	$total_errors++;
	}
	echo $sql . "\n";
	array_push($sql_array, $sql);
	$total_trends++;

	# Examine all the trends for this mirror
	# mark as dropped if they have 0% success overall
	$sql = "SELECT SUM(IF(http_status_code BETWEEN 200 AND 299, 1, 0)) / COUNT(trend_id) * 100 AS success_rate, COUNT(trend_id) AS trend_count FROM mirror_trends where mirror_id = " . $myrow['mirror_id'];
	$rs_trd = mysqli_query($dbh, $sql);
	if($myrow_trd = mysqli_fetch_assoc($rs_trd)) {
	echo "Overall success rate for this mirror: " . $myrow_trd['success_rate'] . "\n";
	if($myrow_trd['success_rate'] == 0 && $myrow_trd['trend_count'] > 0) {
	echo "Dropping mirror " . $myrow['mirror_id'] . "\n";
	mysqli_query($dbh, "UPDATE mirrors SET create_status = 'dropped' WHERE mirror_id = " . $myrow['mirror_id']);
	}
	}

	# mark as wait if they have 0% success in the last 12 hours
	# or mark back to active if 100%
	# HAVING clause to prevent bug 474570
	$sql = "SELECT /mir_trend:123 / SUM(IF(http_status_code BETWEEN 200 AND 299, 1, 0)) / COUNT(trend_id) * 100 AS success_rate
	FROM mirror_trends where mirror_id = " . $myrow['mirror_id']
	. " AND sample_date > date_sub(now(), interval 12 hour) HAVING success_rate IS NOT NULL";
	$rs_trd = mysqli_query($dbh, $sql);
	if($myrow_trd = mysqli_fetch_assoc($rs_trd)) {
	echo "Last 12h success rate for this mirror: " . $myrow_trd['success_rate'] . "\n";
	if($myrow_trd['success_rate'] == 0) {
	echo "Supending (wait) mirror " . $myrow['mirror_id'] . "\n";
	mysqli_query($dbh, "UPDATE mirrors SET create_status = 'wait' WHERE mirror_id = " . $myrow['mirror_id']);
	}
	elseif ($myrow_trd['success_rate'] == 100) {
	echo "Unsupending (active) mirror " . $myrow['mirror_id'] . " if needed.\n";
	mysqli_query($dbh, "UPDATE mirrors SET create_status = 'active' WHERE mirror_id = " . $myrow['mirror_id'] . " AND create_status = 'wait'");
	}
	}
	}
	}

	# Insert trends if we have good success rate
	if($total_trends > 0) {
	$error_rate = $total_errors/$total_trends * 100;
	if($error_rate < 75) {
	echo "Adding trends to the database\n";
	foreach ($sql_array as $sql) {
	mysqli_query($dbh, $sql);
	}

	# maintenance
	$sql = "DELETE FROM mirror_trends where sample_date < DATE_SUB(NOW(), INTERVAL 1 MONTH)";
	mysqli_query($dbh, $sql);
	}
	}

	$dbc->disconnect();
	$rs = null;
	$dbh = null;
	$dbc = null;

	function getCurl($URL) {
	global $timeout;

	$rValue = false;

	$ch = curl_init($URL);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($ch, CURLOPT_TIMEOUT,$timeout);
	curl_setopt($ch, CURLOPT_TIMEOUT,$timeout);

	curl_setopt($ch, CURLOPT_LOW_SPEED_LIMIT, 50*1024);
	curl_setopt($ch, CURLOPT_LOW_SPEED_TIME, 10);
	curl_setopt($ch, CURLOPT_FAILONERROR, 1);


	// Execute
	curl_exec($ch);

	// Check if any error occurred
	if(!curl_errno($ch)) {
	$info = curl_getinfo($ch);
	$rValue = $info;
	}
	else {
	$rValue = curl_errno($ch);
	}

	// Close handle
	curl_close($ch);
	return $rValue;
	}

	?>