📄 adodb-perf-module.inc.php
字号:
<?php/*
V4.65 22 July 2005 (c) 2000-2005 John Lim (jlim@natsoft.com.my). All rights reserved.
Released under both BSD license and Lesser GPL library license.
Whenever there is any discrepancy between the two licenses,
the BSD license will take precedence. See License.txt.
Library for basic performance monitoring and tuning. Modified 23 April 2006 for use with ADOdb Lite by P谩draic Brady Such modifications as listed (c) 2006 P谩draic Brady (maugrimtr@hotmail.com) Modifications: - Moved below methods from adodb_perf class to create a common parent from which all driver specific perfmon modules will extend to prevent duplicate code. - See specific driver module files for other changes
*/eval('class perfmon_parent_EXTENDER extends ' . $last_module . '_ADOConnection { }');class perfmon_parent_ADOConnection extends perfmon_parent_EXTENDER{
var $color = '#F0F0F0';
var $table = '<table style="border: 2px groove #000000; background-color: #FFFFFF;">';
var $titles = '<tr><td><strong>Parameter</strong></td><td><strong>Value</strong></td><td><strong>Description</strong></td></tr>';
var $warnRatio = 90;
var $tablesSQL = false;
var $cliFormat = "%32s => %s \r\n";
var $sql1 = 'sql1'; // used for casting sql1 to text for mssql
var $explain = true;
var $helpurl = '<a href="javascript:void();">LogSQL help</a>';
var $createTableSQL = false;
var $maxLength = 2000; var $settings = false; var $_logsql = false; var $_lastload; /**
* Sets the table name to use for SQL logging. Returns value of current table when called.
* Usage: perfmon_parent_ADOConnection::table('custom_log_sql');
* $currentTable = perfmon_parent_ADOConnection::table();
*
* @access public
* @param string $newtable The name for the table to use; optional.
* @return string
*/
function table($newtable = false)
{
static $_table;
if (!empty($newtable)) $_table = $newtable;
if (empty($_table)) $_table = 'adodb_logsql';
return $_table;
} /**
* Enables SQL logging to database for Performance Monitor use.
* Usage: $oldValue = $db->LogSQL( $enable );
* $enable is optional; defaults to TRUE enabling logging. FALSE disables logging.
*
* @access public
* @param bool $enable
* @return bool
*/ function LogSQL($enable=true)
{
$old = $this->_logsql;
$this->_logsql = $enable; return $old;
}
/**
* Returns an array with information to calculate CPU Load
*
* @access private
* @return mixed
*/
function _CPULoad() {
// Algorithm is taken from
// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/example__obtaining_raw_performance_data.asp
if (strncmp(PHP_OS,'WIN',3)==0) {
if (PHP_VERSION == '5.0.0') return false;
if (PHP_VERSION == '5.0.1') return false;
if (PHP_VERSION == '5.0.2') return false;
if (PHP_VERSION == '5.0.3') return false;
if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737
@$c = new COM("WinMgmts:{impersonationLevel=impersonate}!Win32_PerfRawData_PerfOS_Processor.Name='_Total'");
if (!$c) return false;
$info[0] = $c->PercentProcessorTime;
$info[1] = 0;
$info[2] = 0;
$info[3] = $c->TimeStamp_Sys100NS;
return $info;
}
// Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com)
$statfile = '/proc/stat';
if (!file_exists($statfile)) return false;
$fd = fopen($statfile,"r");
if (!$fd) return false;
$statinfo = explode("\n",fgets($fd, 1024));
fclose($fd);
foreach($statinfo as $line) {
$info = explode(" ",$line);
if($info[0]=="cpu") {
array_shift($info); // pop off "cpu"
if(!$info[0]) array_shift($info); // pop off blank space (if any)
return $info;
}
}
return false;
}
/* NOT IMPLEMENTED */
function MemInfo()
{
}
/**
* Returns CPU Load
*
* @access public
* @return mixed
*/
function CPULoad()
{
$info = $this->_CPULoad();
if (!$info) return false;
if (empty($this->_lastLoad)) {
sleep(1);
$this->_lastLoad = $info;
$info = $this->_CPULoad();
}
$last = $this->_lastLoad;
$this->_lastLoad = $info;
$d_user = $info[0] - $last[0];
$d_nice = $info[1] - $last[1];
$d_system = $info[2] - $last[2];
$d_idle = $info[3] - $last[3];
if (strncmp(PHP_OS,'WIN',3)==0) {
if ($d_idle < 1) $d_idle = 1;
return 100*(1-$d_user/$d_idle);
} else {
$total=$d_user+$d_nice+$d_system+$d_idle;
if ($total<1) $total=1;
return 100*($d_user+$d_nice+$d_system)/$total;
}
}
function Tracer($sql)
{
$perf_table = perfmon_parent_ADOConnection::table();
$saveE = $this->LogSQL(false);
global $ADODB_FETCH_MODE;
$save = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$sqlq = $this->qstr($sql);
$arr = $this->GetArray(
"select count(*),tracer
from $perf_table where sql1=$sqlq
group by tracer
order by 1 desc" );
$s = '';
if ($arr) {
$s .= '\n<h3>Scripts Affected</h3>\n';
foreach($arr as $k) {
$s .= sprintf("%4d",$k[0]).' '.strip_tags($k[1]).'<br />';
}
}
$this->LogSQL($saveE);
return $s;
}
/*
Explain Plan for $sql.
If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the
actual sql.
*/
function Explain($sql, $partial=false)
{
return false;
}
function InvalidSQL($numsql = 10)
{
if (isset($_GET['sql'])) return;
$s = '<h3>Invalid SQL</h3>';
$saveE = $this->LogSQL(false);
$perf_table = perfmon_parent_ADOConnection::table();
$rs =& $this->SelectLimit( "select distinct count(*), sql1, tracer as error_msg from $perf_table where tracer like 'ERROR:%' group by sql1, tracer order by 1 desc" ,$numsql );
$this->LogSQL($saveE);
if ($rs) {
$s .= rs2html($rs,false,false,false,false);
} else {
return "\n<p>$this->helpurl. ".$this->ErrorMsg()."</p>\n";
}
return $s;
}
/*
This script identifies the longest running SQL
*/
function _SuspiciousSQL($numsql = 10)
{
global $ADODB_FETCH_MODE;
$perf_table = perfmon_parent_ADOConnection::table();
$saveE = $this->LogSQL(false);
if (isset($_GET['exps']) && isset($_GET['sql'])) {
$partial = !empty($_GET['part']);
echo '<a name="explain"></a>' . $this->Explain($_GET['sql'], $partial) . "\n";
}
if (isset($_GET['sql'])) return;
$sql1 = $this->sql1;
$save = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$rs =& $this->SelectLimit(
"select avg(timer) as avg_timer, $sql1, count(*), max(timer) as max_timer, min(timer) as min_timer
from $perf_table
where {$this->upperCase}({$this->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 );
$ADODB_FETCH_MODE = $save;
$this->LogSQL($saveE);
if (!$rs) return "<p>$this->helpurl. ".$this->ErrorMsg()."</p>";
$s = "<h3>Suspicious SQL</h3>\n<span style=\"font-size: 8pt;\">The following SQL have high average execution times</span><br />\n<table style=\"border: 2px groove #000000; background-color: #FFFFFF;\">\n<tr>\n<td><strong>Avg Time</strong></td>\n<td><strong>Count</strong></td>\n<td><strong>SQL</strong></td>\n<td><strong>Max</strong></td>\n<td><strong>Min</strong></td>\n</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=\"sql".rand()."\" href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">";
$suffix = "</a>";
if ($this->explain == false || strlen($prefix) > $max) {
$suffix = ' ... <em>String too long for GET parameter: '.strlen($prefix).'</em>';
$prefix = '';
}
$s .= "\n<tr>\n<td>\n" .adodb_round($rs->fields[0],6) ."\n</td><td style='text-align: right;'>\n" .$rs->fields[2] ."\n</td><td>\n<span style=\"font-size: 10pt;\">" .$prefix .htmlentities($sql, ENT_QUOTES, 'UTF-8') .$suffix ."</span>\n</td><td>\n" .$rs->fields[3] ."\n</td><td>\n" .$rs->fields[4] ."\n</tr>";
$rs->MoveNext();
}
return $s."\n</table>\n";
}
function CheckMemory()
{
return '';
}
function SuspiciousSQL($numsql=10)
{
return perfmon_parent_ADOConnection::_SuspiciousSQL($numsql);
}
function ExpensiveSQL($numsql=10)
{
return perfmon_parent_ADOConnection::_ExpensiveSQL($numsql);
}
/*
This reports the percentage of load on the instance due to the most
expensive few SQL statements. Tuning these statements can often
make huge improvements in overall system performance.
*/
function _ExpensiveSQL($numsql = 10)
{
global $ADODB_FETCH_MODE;
$perf_table = perfmon_parent_ADOConnection::table();
$saveE = $this->LogSQL(false);
if (isset($_GET['expe']) && isset($_GET['sql'])) {
$partial = !empty($_GET['part']);
echo "\n<a name=\"explain\"></a>" . $this->Explain($_GET['sql'], $partial) . "\n";
}
if (isset($_GET['sql'])) return;
$sql1 = $this->sql1;
$save = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$rs =& $this->SelectLimit(
"select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
from $perf_table
where {$this->upperCase}({$this->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')
and (tracer is null or tracer not like 'ERROR:%')
group by sql1
having count(*)>1
order by 1 desc" ,$numsql );
$this->LogSQL($saveE);
$ADODB_FETCH_MODE = $save;
if (!$rs) return "<p>$this->helpurl. " . $this->ErrorMsg() . "</p>\n";
$s = "\n<h3>Expensive SQL</h3>\n<span style=\"font-size: 8pt;\">Tuning the following SQL could reduce the server load substantially</span><br />\n<table style=\"border: 2px groove #000000;\">\n<tr>\n<td><strong>Load</strong></td>\n<td><strong>Count</strong></td>\n<td><strong>SQL</strong></td>\n<td><strong>Max</strong></td>\n<td><strong>Min</strong></td>\n</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>\n";
if($this->explain == false || strlen($prefix > $max)) {
$prefix = '';
$suffix = '';
}
$s .= "\n<tr>\n<td>\n" .adodb_round($rs->fields[0],6) ."\n</td><td style='text-align: right;'>\n" .$rs->fields[2] ."\n</td>\n<td><span style=\"font-size: 10pt;\">" .$prefix .htmlentities($sql, ENT_QUOTES, 'UTF-8') .$suffix ."</span>" ."\n</td><td>" .$rs->fields[3] ."\n</td><td>" .$rs->fields[4] ."\n</tr>";
$rs->MoveNext();
}
return $s."\n</table>\n";
}
/*
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->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->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->LogSQL($savelog);
return $ret;
} else {
if (strncmp($sql,'=',1) == 0) {
$fn = substr($sql,1);
return $this->$fn();
}
$sql = str_replace('$DATABASE',$this->database,$sql);
$ret = $this->GetOne($sql);
$this->LogSQL($savelog);
return $ret;
}
}
/*
Warn if cache ratio falls below threshold. Displayed in "Description" column.
*/
function WarnCacheRatio($val)
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -