adodb-perf.inc.php

Cacti是一套基于PHP,MySQL,SNMP及RRDTool开发的网络流量监测图形分析工具。 它通过snmpget来获取数据

			$sql1 = $this->sql1;
			$save = $ADODB_FETCH_MODE;
			$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
			$rs =& $this->conn->SelectLimit(
			"select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
				from $perf_table
				where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5))  not in ('DROP ','INSER','COMMI','CREAT')
				and (tracer is null or tracer not like 'ERROR:%')
				group by sql1
				order by 1 desc",$numsql);
			
			$this->conn->fnExecute = $saveE;
			$ADODB_FETCH_MODE = $save;
			if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
			$s = "<h3>Expensive SQL</h3>
<font size=1>Tuning the following SQL will reduce the server load substantially</font><br>
<table border=1 bgcolor=white><tr><td><b>Load</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n";
			$max = $this->maxLength;
			while (!$rs->EOF) {
				$sql = $rs->fields[1];
				$raw = urlencode($sql);
				if (strlen($raw)>$max-100) {
					$sql2 = substr($sql,0,$max-500);
					$raw = urlencode($sql2).'&part='.crc32($sql);
				}
				$prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">";
				$suffix = "</a>";
				if($this->explain == false || strlen($prefix>$max)) {
					$prefix = '';
					$suffix = '';
				}
				$s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
					"<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
				$rs->MoveNext();
			}
			return $s."</table>";
	}
	
	/*
		Raw function to return parameter value from $settings.
	*/
	function DBParameter($param)
	{
		if (empty($this->settings[$param])) return false;
		$sql = $this->settings[$param][1];
		return $this->_DBParameter($sql);
	}
	
	/*
		Raw function returning array of poll paramters
	*/
	function &PollParameters()
	{
		$arr[0] = (float)$this->DBParameter('data cache hit ratio');
		$arr[1] = (float)$this->DBParameter('data reads');
		$arr[2] = (float)$this->DBParameter('data writes');
		$arr[3] = (integer) $this->DBParameter('current connections');
		return $arr;
	}
	
	/*
		Low-level Get Database Parameter
	*/
	function _DBParameter($sql)
	{
		$savelog = $this->conn->LogSQL(false);
		if (is_array($sql)) {
		global $ADODB_FETCH_MODE;
		
			$sql1 = $sql[0];
			$key = $sql[1];
			if (sizeof($sql)>2) $pos = $sql[2];
			else $pos = 1;
			if (sizeof($sql)>3) $coef = $sql[3];
			else $coef = false;
			$ret = false;
			$save = $ADODB_FETCH_MODE;
			$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
			$rs = $this->conn->Execute($sql1);
			$ADODB_FETCH_MODE = $save;
			if ($rs) {
				while (!$rs->EOF) {
					$keyf = reset($rs->fields);
					if (trim($keyf) == $key) {
						$ret = $rs->fields[$pos];
						if ($coef) $ret *= $coef;
						break;
					}
					$rs->MoveNext();
				}
				$rs->Close();
			}
			$this->conn->LogSQL($savelog);
			return $ret;
		} else {
			if (strncmp($sql,'=',1) == 0) {
				$fn = substr($sql,1);
				return $this->$fn();
			}
			$sql = str_replace('$DATABASE',$this->conn->database,$sql);
			$ret = $this->conn->GetOne($sql);
			$this->conn->LogSQL($savelog);
			
			return $ret;
		}
	}
	
	/*
		Warn if cache ratio falls below threshold. Displayed in "Description" column.
	*/
	function WarnCacheRatio($val)
	{
		if ($val < $this->warnRatio) 
			 return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>';
		else return '';
	}
	
	/***********************************************************************************************/
	//                                    HIGH LEVEL UI FUNCTIONS
	/***********************************************************************************************/

	
	function UI($pollsecs=5)
	{
	global $HTTP_GET_VARS,$HTTP_SERVER_VARS,$HTTP_POST_VARS;
	
    $perf_table = adodb_perf::table();
	$conn = $this->conn;
	
	$app = $conn->host;
	if ($conn->host && $conn->database) $app .= ', db=';
	$app .= $conn->database;
	
	if ($app) $app .= ', ';
	$savelog = $this->conn->LogSQL(false);	
	$info = $conn->ServerInfo();
	if (isset($HTTP_GET_VARS['clearsql'])) {
		$this->conn->Execute("delete from $perf_table");
	}
	$this->conn->LogSQL($savelog);
	
	// magic quotes
	
	if (isset($HTTP_GET_VARS['sql']) && get_magic_quotes_gpc()) {
		$_GET['sql'] = $HTTP_GET_VARS['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$HTTP_GET_VARS['sql']);
	}
	
	if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10;
	else  $nsql = $_SESSION['ADODB_PERF_SQL'];
	
	$app .= $info['description'];
	
	
	if (isset($HTTP_GET_VARS['do'])) $do = $HTTP_GET_VARS['do'];
	else if (isset($HTTP_POST_VARS['do'])) $do = $HTTP_POST_VARS['do'];
	 else if (isset($HTTP_GET_VARS['sql'])) $do = 'viewsql';
	 else $do = 'stats';
	 
	if (isset($HTTP_GET_VARS['nsql'])) {
		if ($HTTP_GET_VARS['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $HTTP_GET_VARS['nsql'];
	}
	echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>";
	if ($do == 'viewsql') $form = "<td><form># SQL:<input type=hidden value=viewsql name=do> <input type=text size=4 name=nsql value=$nsql><input type=submit value=Go></td></form>";
	else $form = "<td>&nbsp;</td>";
	
	$allowsql = !defined('ADODB_PERF_NO_RUN_SQL');
	
	if  (empty($HTTP_GET_VARS['hidem']))
	echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2>
	<b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td>
	<a href=?do=stats><b>Performance Stats</b></a> &nbsp; <a href=?do=viewsql><b>View SQL</b></a>
	 &nbsp; <a href=?do=tables><b>View Tables</b></a> &nbsp; <a href=?do=poll><b>Poll Stats</b></a>",
	 $allowsql ? ' &nbsp; <a href=?do=dosql><b>Run SQL</b></a>' : '',
	 "$form",
	 "</tr></table>";

	 
	 	switch ($do) {
		default:
		case 'stats':
			echo $this->HealthCheck();
			//$this->conn->debug=1;
			echo $this->CheckMemory();
			break;
		case 'poll':
			echo "<iframe width=720 height=80% 
				src=\"{$HTTP_SERVER_VARS['PHP_SELF']}?do=poll2&hidem=1\"></iframe>";
			break;
		case 'poll2':
			echo "<pre>";
			$this->Poll($pollsecs);
			break;
		
		case 'dosql':
			if (!$allowsql) break;
			
			$this->DoSQLForm();
			break;
		case 'viewsql':
			if (empty($HTTP_GET_VARS['hidem']))
				echo "&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
			echo($this->SuspiciousSQL($nsql));
			echo($this->ExpensiveSQL($nsql));
			echo($this->InvalidSQL($nsql));
			break;
		case 'tables': 
			echo $this->Tables(); break;
		}
		global $ADODB_vers;
		echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>";
	}
	
	/*
		Runs in infinite loop, returning real-time statistics
	*/
	function Poll($secs=5)
	{
		$this->conn->fnExecute = false;
		//$this->conn->debug=1;
		if ($secs <= 1) $secs = 1;
		echo "Accumulating statistics, every $secs seconds...\n";flush();
		$arro =& $this->PollParameters();
		$cnt = 0;
		set_time_limit(0);
		sleep($secs);
		while (1) {

			$arr =& $this->PollParameters();
			
			$hits   = sprintf('%2.2f',$arr[0]);
			$reads  = sprintf('%12.4f',($arr[1]-$arro[1])/$secs);
			$writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs);
			$sess = sprintf('%5d',$arr[3]);
			
			$load = $this->CPULoad();
			if ($load !== false) {
				$oslabel = 'WS-CPU%';
				$osval = sprintf(" %2.1f  ",(float) $load);
			}else {
				$oslabel = '';
				$osval = '';
			}
			if ($cnt % 10 == 0) echo " Time   ".$oslabel."   Hit%   Sess           Reads/s          Writes/s\n"; 
			$cnt += 1;
			echo date('H:i:s').'  '.$osval."$hits  $sess $reads $writes\n";
			flush();
			
			if (connection_aborted()|| connection_timeout()) return;
			
			sleep($secs);
			$arro = $arr;
		}
	}
	
	/*
		Returns basic health check in a command line interface
	*/
	function HealthCheckCLI()
	{
		return $this->HealthCheck(true);
	}
	
		
	/*
		Returns basic health check as HTML
	*/
	function HealthCheck($cli=false)
	{
		$saveE = $this->conn->fnExecute;
		$this->conn->fnExecute = false;	
		if ($cli) $html = '';
		else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles;
		
		$oldc = false;
		$bgc = '';
		foreach($this->settings as $name => $arr) {
			if ($arr === false) break;
			
			if (!is_string($name)) {
				if ($cli) $html .= " -- $arr -- \n";
				else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> &nbsp;</td></tr>";
				continue;
			}
			
			if (!is_array($arr)) break;
			$category = $arr[0];
			$how = $arr[1];
			if (sizeof($arr)>2) $desc = $arr[2];
			else $desc = ' &nbsp; ';
			
			
			if ($category == 'HIDE') continue;
			
			$val = $this->_DBParameter($how);
			
			if ($desc && strncmp($desc,"=",1) === 0) {
				$fn = substr($desc,1);
				$desc = $this->$fn($val);
			}
			
			if ($val === false) {
				$m = $this->conn->ErrorMsg();
				$val = "Error: $m"; 
			} else {
				if (is_numeric($val) && $val >= 256*1024) {
					if ($val % (1024*1024) == 0) {
						$val /= (1024*1024);
						$val .= 'M';
					} else if ($val % 1024 == 0) {
						$val /= 1024;
						$val .= 'K';
					}
					//$val = htmlspecialchars($val);
				}
			}
			if ($category != $oldc) {
				$oldc = $category;
				//$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color;
			}
			if (strlen($desc)==0) $desc = '&nbsp;';
			if (strlen($val)==0) $val = '&nbsp;';
			if ($cli) {
				$html  .= str_replace('&nbsp;','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc)));
				
			}else {
				$html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n";
			}
		}
		
		if (!$cli) $html .= "</table>\n";
		$this->conn->fnExecute = $saveE;
			
		return $html;	
	}
	
	function Tables($orderby='1')
	{
		if (!$this->tablesSQL) return false;
		
		$savelog = $this->conn->LogSQL(false);
		$rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby);
		$this->conn->LogSQL($savelog);
		$html = rs2html($rs,false,false,false,false);
		return $html;
	}
	

	function CreateLogTable()
	{
		if (!$this->createTableSQL) return false;
		
		$savelog = $this->conn->LogSQL(false);
		$ok = $this->conn->Execute($this->createTableSQL);
		$this->conn->LogSQL($savelog);
		return ($ok) ? true : false;
	}
	
	function DoSQLForm()
	{
	global $HTTP_SERVER_VARS,$HTTP_GET_VARS,$HTTP_POST_VARS,$HTTP_SESSION_VARS;
	
		$HTTP_VARS = array_merge($HTTP_GET_VARS,$HTTP_POST_VARS);
		
		$PHP_SELF = $HTTP_SERVER_VARS['PHP_SELF'];
		$sql = isset($HTTP_VARS['sql']) ? $HTTP_VARS['sql'] : '';

		if (isset($HTTP_SESSION_VARS['phplens_sqlrows'])) $rows = $HTTP_SESSION_VARS['phplens_sqlrows'];
		else $rows = 3;
		
		if (isset($HTTP_VARS['SMALLER'])) {
			$rows /= 2;
			if ($rows < 3) $rows = 3;
			$HTTP_SESSION_VARS['phplens_sqlrows'] = $rows;
		}
		if (isset($HTTP_VARS['BIGGER'])) {
			$rows *= 2;
			$HTTP_SESSION_VARS['phplens_sqlrows'] = $rows;
		}
		
?>

<form method="POST" action="<?php echo $PHP_SELF ?>">
<table><tr>
<td> Form size: <input type="submit" value=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " name="BIGGER">
</td>
<td align=right>
<input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>
</td></tr>
  <tr>
  <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>
  </td>
  </tr>
 </table>
</form>

<?php
		if (!isset($HTTP_VARS['sql'])) return;
		
		$sql = $this->undomq(trim($sql));
		if (substr($sql,strlen($sql)-1) === ';') {
			$print = true;
			$sqla = $this->SplitSQL($sql);
		} else  {
			$print = false;
			$sqla = array($sql);
		}
		foreach($sqla as $sqls) {

			if (!$sqls) continue;
			
			if ($print) {
				print "<p>".htmlspecialchars($sqls)."</p>";
				flush();
			}
			$savelog = $this->conn->LogSQL(false);
			$rs = $this->conn->Execute($sqls);
			$this->conn->LogSQL($savelog);
			if ($rs && is_object($rs) && !$rs->EOF) {
				rs2html($rs);
				while ($rs->NextRecordSet()) {
					print "<table width=98% bgcolor=#C0C0FF><tr><td>&nbsp;</td></tr></table>";
					rs2html($rs);
				}
			} else {
				$e1 = (integer) $this->conn->ErrorNo();
				$e2 = $this->conn->ErrorMsg();
				if (($e1) || ($e2)) {
					if (empty($e1)) $e1 = '-1'; // postgresql fix
					print ' &nbsp; '.$e1.': '.$e2;
				} else {
					print "<p>No Recordset returned<br></p>";
				}
			}
		} // foreach
	}
	
	function SplitSQL($sql)
	{
		$arr = explode(';',$sql);
		return $arr;
	}
	
	function undomq(&$m) 
	{
	if (get_magic_quotes_gpc()) {
		// undo the damage
		$m = str_replace('\\\\','\\',$m);
		$m = str_replace('\"','"',$m);
		$m = str_replace('\\\'','\'',$m);
	}
	return $m;
}
}



?>