Documentation TYPO3 par Ameos

adodb-perf.inc.php

00001 <?php
00002 /* 
00003 V4.80 8 Mar 2006  (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. See License.txt. 
00007   Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   Library for basic performance monitoring and tuning.
00012   
00013   My apologies if you see code mixed with presentation. The presentation suits
00014   my needs. If you want to separate code from presentation, be my guest. Patches
00015   are welcome.
00016   
00017 */
00018 
00019 if (!defined('ADODB_DIR')) include_once(dirname(__FILE__).'/adodb.inc.php');
00020 include_once(ADODB_DIR.'/tohtml.inc.php');
00021 
00022 define( 'ADODB_OPT_HIGH', 2);
00023 define( 'ADODB_OPT_LOW', 1);
00024 
00025 // returns in K the memory of current process, or 0 if not known
00026 function adodb_getmem()
00027 {
00028         if (function_exists('memory_get_usage'))
00029                 return (integer) ((memory_get_usage()+512)/1024);
00030         
00031         $pid = getmypid();
00032         
00033         if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) {
00034                 $output = array();
00035         
00036                 exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output); 
00037                 return substr($output[5], strpos($output[5], ':') + 1);
00038         } 
00039         
00040         /* Hopefully UNIX */
00041         exec("ps --pid $pid --no-headers -o%mem,size", $output);
00042         if (sizeof($output) == 0) return 0;
00043         
00044         $memarr = explode(' ',$output[0]);
00045         if (sizeof($memarr)>=2) return (integer) $memarr[1];
00046         
00047         return 0;
00048 }
00049 
00050 // avoids localization problems where , is used instead of .
00051 function adodb_round($n,$prec)
00052 {
00053         return number_format($n, $prec, '.', '');
00054 }
00055 
00056 /* return microtime value as a float */
00057 function adodb_microtime()
00058 {
00059         $t = microtime();
00060         $t = explode(' ',$t);
00061         return (float)$t[1]+ (float)$t[0];
00062 }
00063 
00064 /* sql code timing */
00065 function& adodb_log_sql(&$conn,$sql,$inputarr)
00066 {
00067         
00068     $perf_table = adodb_perf::table();
00069         $conn->fnExecute = false;
00070         $t0 = microtime();
00071         $rs =& $conn->Execute($sql,$inputarr);
00072         $t1 = microtime();
00073 
00074         if (!empty($conn->_logsql)) {
00075                 $conn->_logsql = false; // disable logsql error simulation
00076                 $dbT = $conn->databaseType;
00077                 
00078                 $a0 = split(' ',$t0);
00079                 $a0 = (float)$a0[1]+(float)$a0[0];
00080                 
00081                 $a1 = split(' ',$t1);
00082                 $a1 = (float)$a1[1]+(float)$a1[0];
00083                 
00084                 $time = $a1 - $a0;
00085         
00086                 if (!$rs) {
00087                         $errM = $conn->ErrorMsg();
00088                         $errN = $conn->ErrorNo();
00089                         $conn->lastInsID = 0;
00090                         $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250);
00091                 } else {
00092                         $tracer = '';
00093                         $errM = '';
00094                         $errN = 0;
00095                         $dbg = $conn->debug;
00096                         $conn->debug = false;
00097                         if (!is_object($rs) || $rs->dataProvider == 'empty') 
00098                                 $conn->_affected = $conn->affected_rows(true);
00099                         $conn->lastInsID = @$conn->Insert_ID();
00100                         $conn->debug = $dbg;
00101                 }
00102                 if (isset($_SERVER['HTTP_HOST'])) {
00103                         $tracer .= '<br>'.$_SERVER['HTTP_HOST'];
00104                         if (isset($_SERVER['PHP_SELF'])) $tracer .= $_SERVER['PHP_SELF'];
00105                 } else 
00106                         if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.$_SERVER['PHP_SELF'];
00107                 //$tracer .= (string) adodb_backtrace(false);
00108                 
00109                 $tracer = (string) substr($tracer,0,500);
00110                 
00111                 if (is_array($inputarr)) {
00112                         if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr);
00113                         else {
00114                                 // Quote string parameters so we can see them in the
00115                                 // performance stats. This helps spot disabled indexes.
00116                                 $xar_params = $inputarr;
00117                                 foreach ($xar_params as $xar_param_key => $xar_param) {
00118                                         if (gettype($xar_param) == 'string')
00119                                         $xar_params[$xar_param_key] = '"' . $xar_param . '"';
00120                                 }
00121                                 $params = implode(', ', $xar_params);
00122                                 if (strlen($params) >= 3000) $params = substr($params, 0, 3000);
00123                         }
00124                 } else {
00125                         $params = '';
00126                 }
00127                 
00128                 if (is_array($sql)) $sql = $sql[0];
00129                 $arr = array('b'=>strlen($sql).'.'.crc32($sql),
00130                                         'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6));
00131                 //var_dump($arr);
00132                 $saved = $conn->debug;
00133                 $conn->debug = 0;
00134                 
00135                 $d = $conn->sysTimeStamp;
00136                 if (empty($d)) $d = date("'Y-m-d H:i:s'");
00137                 if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') {
00138                         $isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";
00139                 } else if ($dbT == 'odbc_mssql' || $dbT == 'informix' || $dbT == 'odbtp') {
00140                         $timer = $arr['f'];
00141                         if ($dbT == 'informix') $sql2 = substr($sql2,0,230);
00142 
00143                         $sql1 = $conn->qstr($arr['b']);
00144                         $sql2 = $conn->qstr($arr['c']);
00145                         $params = $conn->qstr($arr['d']);
00146                         $tracer = $conn->qstr($arr['e']);
00147                         
00148                         $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";
00149                         if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);
00150                         $arr = false;
00151                 } else {
00152                         $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)";
00153                 }
00154 
00155                 $ok = $conn->Execute($isql,$arr);
00156                 $conn->debug = $saved;
00157                 
00158                 if ($ok) {
00159                         $conn->_logsql = true; 
00160                 } else {
00161                         $err2 = $conn->ErrorMsg();
00162                         $conn->_logsql = true; // enable logsql error simulation
00163                         $perf =& NewPerfMonitor($conn);
00164                         if ($perf) {
00165                                 if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr);
00166                         } else {
00167                                 $ok = $conn->Execute("create table $perf_table (
00168                                 created varchar(50),
00169                                 sql0 varchar(250), 
00170                                 sql1 varchar(4000),
00171                                 params varchar(3000),
00172                                 tracer varchar(500),
00173                                 timer decimal(16,6))");
00174                         }
00175                         if (!$ok) {
00176                                 ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");
00177                                 $conn->_logsql = false;
00178                         }
00179                 }
00180                 $conn->_errorMsg = $errM;
00181                 $conn->_errorCode = $errN;
00182         } 
00183         $conn->fnExecute = 'adodb_log_sql';
00184         return $rs;
00185 }
00186 
00187         
00188 /*
00189 The settings data structure is an associative array that database parameter per element.
00190 
00191 Each database parameter element in the array is itself an array consisting of:
00192 
00193 0: category code, used to group related db parameters
00194 1: either
00195         a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'", 
00196         b. array holding sql string and field to look for, e.g. array('show variables','table_cache'),
00197         c. a string prefixed by =, then a PHP method of the class is invoked, 
00198                 e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue',
00199 2: description of the database parameter
00200 */
00201 
00202 class adodb_perf {
00203         var $conn;
00204         var $color = '#F0F0F0';
00205         var $table = '<table border=1 bgcolor=white>';
00206         var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>';
00207         var $warnRatio = 90;
00208         var $tablesSQL = false;
00209         var $cliFormat = "%32s => %s \r\n";
00210         var $sql1 = 'sql1';  // used for casting sql1 to text for mssql
00211         var $explain = true;
00212         var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>";
00213         var $createTableSQL = false;
00214         var $maxLength = 2000;
00215         
00216     // Sets the tablename to be used            
00217     function table($newtable = false)
00218     {
00219         static $_table;
00220 
00221         if (!empty($newtable))  $_table = $newtable;
00222                 if (empty($_table)) $_table = 'adodb_logsql';
00223         return $_table;
00224     }
00225 
00226         // returns array with info to calculate CPU Load
00227         function _CPULoad()
00228         {
00229 /*
00230 
00231 cpu  524152 2662 2515228 336057010
00232 cpu0 264339 1408 1257951 168025827
00233 cpu1 259813 1254 1257277 168031181
00234 page 622307 25475680
00235 swap 24 1891
00236 intr 890153570 868093576 6 0 4 4 0 6 1 2 0 0 0 124 0 8098760 2 13961053 0 0 0 0 0 0 0 0 0 0 0 0 0 16 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
00237 disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320)
00238 ctxt 66155838
00239 btime 1062315585
00240 processes 69293
00241 
00242 */
00243                 // Algorithm is taken from
00244                 // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/example__obtaining_raw_performance_data.asp
00245                 if (strncmp(PHP_OS,'WIN',3)==0) {
00246                         if (PHP_VERSION == '5.0.0') return false;
00247                         if (PHP_VERSION == '5.0.1') return false;
00248                         if (PHP_VERSION == '5.0.2') return false;
00249                         if (PHP_VERSION == '5.0.3') return false;
00250                         if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737
00251                         
00252                         @$c = new COM("WinMgmts:{impersonationLevel=impersonate}!Win32_PerfRawData_PerfOS_Processor.Name='_Total'");
00253                         if (!$c) return false;
00254                         
00255                         $info[0] = $c->PercentProcessorTime;
00256                         $info[1] = 0;
00257                         $info[2] = 0;
00258                         $info[3] = $c->TimeStamp_Sys100NS;
00259                         //print_r($info);
00260                         return $info;
00261                 }
00262                 
00263                 // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com)
00264                 $statfile = '/proc/stat';
00265                 if (!file_exists($statfile)) return false;
00266                 
00267                 $fd = fopen($statfile,"r");
00268                 if (!$fd) return false;
00269                 
00270                 $statinfo = explode("\n",fgets($fd, 1024));
00271                 fclose($fd);
00272                 foreach($statinfo as $line) {
00273                         $info = explode(" ",$line);
00274                         if($info[0]=="cpu") {
00275                                 array_shift($info);  // pop off "cpu"
00276                                 if(!$info[0]) array_shift($info); // pop off blank space (if any)
00277                                 return $info;
00278                         }
00279                 }
00280                 
00281                 return false;
00282                 
00283         }
00284         
00285         /* NOT IMPLEMENTED */
00286         function MemInfo()
00287         {
00288                 /*
00289 
00290         total:    used:    free:  shared: buffers:  cached:
00291 Mem:  1055289344 917299200 137990144        0 165437440 599773184
00292 Swap: 2146775040 11055104 2135719936
00293 MemTotal:      1030556 kB
00294 MemFree:        134756 kB
00295 MemShared:           0 kB
00296 Buffers:        161560 kB
00297 Cached:         581384 kB
00298 SwapCached:       4332 kB
00299 Active:         494468 kB
00300 Inact_dirty:    322856 kB
00301 Inact_clean:     24256 kB
00302 Inact_target:   168316 kB
00303 HighTotal:      131064 kB
00304 HighFree:         1024 kB
00305 LowTotal:       899492 kB
00306 LowFree:        133732 kB
00307 SwapTotal:     2096460 kB
00308 SwapFree:      2085664 kB
00309 Committed_AS:   348732 kB
00310                 */
00311         }
00312         
00313         
00314         /*
00315                 Remember that this is client load, not db server load!
00316         */
00317         var $_lastLoad;
00318         function CPULoad()
00319         {
00320                 $info = $this->_CPULoad();
00321                 if (!$info) return false;
00322                         
00323                 if (empty($this->_lastLoad)) {
00324                         sleep(1);
00325                         $this->_lastLoad = $info;
00326                         $info = $this->_CPULoad();
00327                 }
00328                 
00329                 $last = $this->_lastLoad;
00330                 $this->_lastLoad = $info;
00331                 
00332                 $d_user = $info[0] - $last[0];
00333                 $d_nice = $info[1] - $last[1];
00334                 $d_system = $info[2] - $last[2];
00335                 $d_idle = $info[3] - $last[3];
00336                 
00337                 //printf("Delta - User: %f  Nice: %f  System: %f  Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle);
00338 
00339                 if (strncmp(PHP_OS,'WIN',3)==0) {
00340                         if ($d_idle < 1) $d_idle = 1;
00341                         return 100*(1-$d_user/$d_idle);
00342                 }else {
00343                         $total=$d_user+$d_nice+$d_system+$d_idle;
00344                         if ($total<1) $total=1;
00345                         return 100*($d_user+$d_nice+$d_system)/$total; 
00346                 }
00347         }
00348         
00349         function Tracer($sql)
00350         {
00351         $perf_table = adodb_perf::table();
00352                 $saveE = $this->conn->fnExecute;
00353                 $this->conn->fnExecute = false;
00354                 
00355                 global $ADODB_FETCH_MODE;
00356                 $save = $ADODB_FETCH_MODE;
00357                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00358                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00359                                 
00360                 $sqlq = $this->conn->qstr($sql);
00361                 $arr = $this->conn->GetArray(
00362 "select count(*),tracer 
00363         from $perf_table where sql1=$sqlq 
00364         group by tracer
00365         order by 1 desc");
00366                 $s = '';
00367                 if ($arr) {
00368                         $s .= '<h3>Scripts Affected</h3>';
00369                         foreach($arr as $k) {
00370                                 $s .= sprintf("%4d",$k[0]).' &nbsp; '.strip_tags($k[1]).'<br>';
00371                         }
00372                 }
00373                 
00374                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00375                 $ADODB_CACHE_MODE = $save;
00376                 $this->conn->fnExecute = $saveE;
00377                 return $s;
00378         }
00379 
00380         /* 
00381                 Explain Plan for $sql.
00382                 If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the 
00383                         actual sql.
00384         */
00385         function Explain($sql,$partial=false)
00386         {       
00387                 return false;
00388         }
00389         
00390         function InvalidSQL($numsql = 10)
00391         {
00392         
00393                 if (isset($_GET['sql'])) return;
00394                 $s = '<h3>Invalid SQL</h3>';
00395                 $saveE = $this->conn->fnExecute;
00396                 $this->conn->fnExecute = false;
00397         $perf_table = adodb_perf::table();
00398                 $rs =& $this->conn->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);//,$numsql);
00399                 $this->conn->fnExecute = $saveE;
00400                 if ($rs) {
00401                         $s .= rs2html($rs,false,false,false,false);
00402                 } else
00403                         return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
00404                 
00405                 return $s;
00406         }
00407 
00408         
00409         /*
00410                 This script identifies the longest running SQL
00411         */      
00412         function _SuspiciousSQL($numsql = 10)
00413         {
00414                 global $ADODB_FETCH_MODE;
00415                 
00416             $perf_table = adodb_perf::table();
00417                         $saveE = $this->conn->fnExecute;
00418                         $this->conn->fnExecute = false;
00419                         
00420                         if (isset($_GET['exps']) && isset($_GET['sql'])) {
00421                                 $partial = !empty($_GET['part']);
00422                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
00423                         }
00424                         
00425                         if (isset($_GET['sql'])) return;
00426                         $sql1 = $this->sql1;
00427                         
00428                         $save = $ADODB_FETCH_MODE;
00429                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00430                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00431                         //$this->conn->debug=1;
00432                         $rs =& $this->conn->SelectLimit(
00433                         "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
00434                                 from $perf_table
00435                                 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')
00436                                 and (tracer is null or tracer not like 'ERROR:%')
00437                                 group by sql1
00438                                 order by 1 desc",$numsql);
00439                         if (isset($savem)) $this->conn->SetFetchMode($savem);
00440                         $ADODB_FETCH_MODE = $save;
00441                         $this->conn->fnExecute = $saveE;
00442                         
00443                         if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
00444                         $s = "<h3>Suspicious SQL</h3>
00445 <font size=1>The following SQL have high average execution times</font><br>
00446 <table border=1 bgcolor=white><tr><td><b>Avg Time</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n";
00447                         $max = $this->maxLength;
00448                         while (!$rs->EOF) {
00449                                 $sql = $rs->fields[1];
00450                                 $raw = urlencode($sql);
00451                                 if (strlen($raw)>$max-100) {
00452                                         $sql2 = substr($sql,0,$max-500);
00453                                         $raw = urlencode($sql2).'&part='.crc32($sql);
00454                                 }
00455                                 $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">";
00456                                 $suffix = "</a>";
00457                                 if ($this->explain == false || strlen($prefix)>$max) {
00458                                         $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>';
00459                                         $prefix = '';
00460                                 }
00461                                 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
00462                                         "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
00463                                 $rs->MoveNext();
00464                         }
00465                         return $s."</table>";
00466                 
00467         }
00468         
00469         function CheckMemory()
00470         {
00471                 return '';
00472         }
00473         
00474         
00475         function SuspiciousSQL($numsql=10)
00476         {
00477                 return adodb_perf::_SuspiciousSQL($numsql);
00478         }
00479 
00480         function ExpensiveSQL($numsql=10)
00481         {
00482                 return adodb_perf::_ExpensiveSQL($numsql);
00483         }
00484 
00485         
00486         /*
00487                 This reports the percentage of load on the instance due to the most 
00488                 expensive few SQL statements. Tuning these statements can often 
00489                 make huge improvements in overall system performance. 
00490         */
00491         function _ExpensiveSQL($numsql = 10)
00492         {
00493                 global $ADODB_FETCH_MODE;
00494                 
00495             $perf_table = adodb_perf::table();
00496                         $saveE = $this->conn->fnExecute;
00497                         $this->conn->fnExecute = false;
00498                         
00499                         if (isset($_GET['expe']) && isset($_GET['sql'])) {
00500                                 $partial = !empty($_GET['part']);
00501                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
00502                         }
00503                         
00504                         if (isset($_GET['sql'])) return;
00505                         
00506                         $sql1 = $this->sql1;
00507                         $save = $ADODB_FETCH_MODE;
00508                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00509                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00510                         
00511                         $rs =& $this->conn->SelectLimit(
00512                         "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
00513                                 from $perf_table
00514                                 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5))  not in ('DROP ','INSER','COMMI','CREAT')
00515                                 and (tracer is null or tracer not like 'ERROR:%')
00516                                 group by sql1
00517                                 having count(*)>1
00518                                 order by 1 desc",$numsql);
00519                         if (isset($savem)) $this->conn->SetFetchMode($savem);
00520                         $this->conn->fnExecute = $saveE;
00521                         $ADODB_FETCH_MODE = $save;
00522                         if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
00523                         $s = "<h3>Expensive SQL</h3>
00524 <font size=1>Tuning the following SQL could reduce the server load substantially</font><br>
00525 <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";
00526                         $max = $this->maxLength;
00527                         while (!$rs->EOF) {
00528                                 $sql = $rs->fields[1];
00529                                 $raw = urlencode($sql);
00530                                 if (strlen($raw)>$max-100) {
00531                                         $sql2 = substr($sql,0,$max-500);
00532                                         $raw = urlencode($sql2).'&part='.crc32($sql);
00533                                 }
00534                                 $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">";
00535                                 $suffix = "</a>";
00536                                 if($this->explain == false || strlen($prefix>$max)) {
00537                                         $prefix = '';
00538                                         $suffix = '';
00539                                 }
00540                                 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
00541                                         "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
00542                                 $rs->MoveNext();
00543                         }
00544                         return $s."</table>";
00545         }
00546         
00547         /*
00548                 Raw function to return parameter value from $settings.
00549         */
00550         function DBParameter($param)
00551         {
00552                 if (empty($this->settings[$param])) return false;
00553                 $sql = $this->settings[$param][1];
00554                 return $this->_DBParameter($sql);
00555         }
00556         
00557         /*
00558                 Raw function returning array of poll paramters
00559         */
00560         function &PollParameters()
00561         {
00562                 $arr[0] = (float)$this->DBParameter('data cache hit ratio');
00563                 $arr[1] = (float)$this->DBParameter('data reads');
00564                 $arr[2] = (float)$this->DBParameter('data writes');
00565                 $arr[3] = (integer) $this->DBParameter('current connections');
00566                 return $arr;
00567         }
00568         
00569         /*
00570                 Low-level Get Database Parameter
00571         */
00572         function _DBParameter($sql)
00573         {
00574                 $savelog = $this->conn->LogSQL(false);
00575                 if (is_array($sql)) {
00576                 global $ADODB_FETCH_MODE;
00577                 
00578                         $sql1 = $sql[0];
00579                         $key = $sql[1];
00580                         if (sizeof($sql)>2) $pos = $sql[2];
00581                         else $pos = 1;
00582                         if (sizeof($sql)>3) $coef = $sql[3];
00583                         else $coef = false;
00584                         $ret = false;
00585                         $save = $ADODB_FETCH_MODE;
00586                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00587                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00588                         
00589                         $rs = $this->conn->Execute($sql1);
00590                         
00591                         if (isset($savem)) $this->conn->SetFetchMode($savem);
00592                         $ADODB_FETCH_MODE = $save;
00593                         if ($rs) {
00594                                 while (!$rs->EOF) {
00595                                         $keyf = reset($rs->fields);
00596                                         if (trim($keyf) == $key) {
00597                                                 $ret = $rs->fields[$pos];
00598                                                 if ($coef) $ret *= $coef;
00599                                                 break;
00600                                         }
00601                                         $rs->MoveNext();
00602                                 }
00603                                 $rs->Close();
00604                         }
00605                         $this->conn->LogSQL($savelog);
00606                         return $ret;
00607                 } else {
00608                         if (strncmp($sql,'=',1) == 0) {
00609                                 $fn = substr($sql,1);
00610                                 return $this->$fn();
00611                         }
00612                         $sql = str_replace('$DATABASE',$this->conn->database,$sql);
00613                         $ret = $this->conn->GetOne($sql);
00614                         $this->conn->LogSQL($savelog);
00615                         
00616                         return $ret;
00617                 }
00618         }
00619         
00620         /*
00621                 Warn if cache ratio falls below threshold. Displayed in "Description" column.
00622         */
00623         function WarnCacheRatio($val)
00624         {
00625                 if ($val < $this->warnRatio) 
00626                          return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>';
00627                 else return '';
00628         }
00629         
00630         /***********************************************************************************************/
00631         //                                    HIGH LEVEL UI FUNCTIONS
00632         /***********************************************************************************************/
00633 
00634         
00635         function UI($pollsecs=5)
00636         {
00637         
00638     $perf_table = adodb_perf::table();
00639         $conn = $this->conn;
00640         
00641         $app = $conn->host;
00642         if ($conn->host && $conn->database) $app .= ', db=';
00643         $app .= $conn->database;
00644         
00645         if ($app) $app .= ', ';
00646         $savelog = $this->conn->LogSQL(false);  
00647         $info = $conn->ServerInfo();
00648         if (isset($_GET['clearsql'])) {
00649                 $this->conn->Execute("delete from $perf_table");
00650         }
00651         $this->conn->LogSQL($savelog);
00652         
00653         // magic quotes
00654         
00655         if (isset($_GET['sql']) && get_magic_quotes_gpc()) {
00656                 $_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']);
00657         }
00658         
00659         if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10;
00660         else  $nsql = $_SESSION['ADODB_PERF_SQL'];
00661         
00662         $app .= $info['description'];
00663         
00664         
00665         if (isset($_GET['do'])) $do = $_GET['do'];
00666         else if (isset($_POST['do'])) $do = $_POST['do'];
00667          else if (isset($_GET['sql'])) $do = 'viewsql';
00668          else $do = 'stats';
00669          
00670         if (isset($_GET['nsql'])) {
00671                 if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql'];
00672         }
00673         echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>";
00674         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>";
00675         else $form = "<td>&nbsp;</td>";
00676         
00677         $allowsql = !defined('ADODB_PERF_NO_RUN_SQL');
00678         
00679         if  (empty($_GET['hidem']))
00680         echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2>
00681         <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td>
00682         <a href=?do=stats><b>Performance Stats</b></a> &nbsp; <a href=?do=viewsql><b>View SQL</b></a>
00683          &nbsp; <a href=?do=tables><b>View Tables</b></a> &nbsp; <a href=?do=poll><b>Poll Stats</b></a>",
00684          $allowsql ? ' &nbsp; <a href=?do=dosql><b>Run SQL</b></a>' : '',
00685          "$form",
00686          "</tr></table>";
00687 
00688          
00689                 switch ($do) {
00690                 default:
00691                 case 'stats':
00692                         echo $this->HealthCheck();
00693                         //$this->conn->debug=1;
00694                         echo $this->CheckMemory();
00695                         break;
00696                 case 'poll':
00697                         echo "<iframe width=720 height=80% 
00698                                 src=\"{$_SERVER['PHP_SELF']}?do=poll2&hidem=1\"></iframe>";
00699                         break;
00700                 case 'poll2':
00701                         echo "<pre>";
00702                         $this->Poll($pollsecs);
00703                         break;
00704                 
00705                 case 'dosql':
00706                         if (!$allowsql) break;
00707                         
00708                         $this->DoSQLForm();
00709                         break;
00710                 case 'viewsql':
00711                         if (empty($_GET['hidem']))
00712                                 echo "&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
00713                         echo($this->SuspiciousSQL($nsql));
00714                         echo($this->ExpensiveSQL($nsql));
00715                         echo($this->InvalidSQL($nsql));
00716                         break;
00717                 case 'tables': 
00718                         echo $this->Tables(); break;
00719                 }
00720                 global $ADODB_vers;
00721                 echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>";
00722         }
00723         
00724         /*
00725                 Runs in infinite loop, returning real-time statistics
00726         */
00727         function Poll($secs=5)
00728         {
00729                 $this->conn->fnExecute = false;
00730                 //$this->conn->debug=1;
00731                 if ($secs <= 1) $secs = 1;
00732                 echo "Accumulating statistics, every $secs seconds...\n";flush();
00733                 $arro =& $this->PollParameters();
00734                 $cnt = 0;
00735                 set_time_limit(0);
00736                 sleep($secs);
00737                 while (1) {
00738 
00739                         $arr =& $this->PollParameters();
00740                         
00741                         $hits   = sprintf('%2.2f',$arr[0]);
00742                         $reads  = sprintf('%12.4f',($arr[1]-$arro[1])/$secs);
00743                         $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs);
00744                         $sess = sprintf('%5d',$arr[3]);
00745                         
00746                         $load = $this->CPULoad();
00747                         if ($load !== false) {
00748                                 $oslabel = 'WS-CPU%';
00749                                 $osval = sprintf(" %2.1f  ",(float) $load);
00750                         }else {
00751                                 $oslabel = '';
00752                                 $osval = '';
00753                         }
00754                         if ($cnt % 10 == 0) echo " Time   ".$oslabel."   Hit%   Sess           Reads/s          Writes/s\n"; 
00755                         $cnt += 1;
00756                         echo date('H:i:s').'  '.$osval."$hits  $sess $reads $writes\n";
00757                         flush();
00758                         
00759                         if (connection_aborted()) return;
00760                         
00761                         sleep($secs);
00762                         $arro = $arr;
00763                 }
00764         }
00765         
00766         /*
00767                 Returns basic health check in a command line interface
00768         */
00769         function HealthCheckCLI()
00770         {
00771                 return $this->HealthCheck(true);
00772         }
00773         
00774                 
00775         /*
00776                 Returns basic health check as HTML
00777         */
00778         function HealthCheck($cli=false)
00779         {
00780                 $saveE = $this->conn->fnExecute;
00781                 $this->conn->fnExecute = false; 
00782                 if ($cli) $html = '';
00783                 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles;
00784                 
00785                 $oldc = false;
00786                 $bgc = '';
00787                 foreach($this->settings as $name => $arr) {
00788                         if ($arr === false) break;
00789                         
00790                         if (!is_string($name)) {
00791                                 if ($cli) $html .= " -- $arr -- \n";
00792                                 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> &nbsp;</td></tr>";
00793                                 continue;
00794                         }
00795                         
00796                         if (!is_array($arr)) break;
00797                         $category = $arr[0];
00798                         $how = $arr[1];
00799                         if (sizeof($arr)>2) $desc = $arr[2];
00800                         else $desc = ' &nbsp; ';
00801                         
00802                         
00803                         if ($category == 'HIDE') continue;
00804                         
00805                         $val = $this->_DBParameter($how);
00806                         
00807                         if ($desc && strncmp($desc,"=",1) === 0) {
00808                                 $fn = substr($desc,1);
00809                                 $desc = $this->$fn($val);
00810                         }
00811                         
00812                         if ($val === false) {
00813                                 $m = $this->conn->ErrorMsg();
00814                                 $val = "Error: $m"; 
00815                         } else {
00816                                 if (is_numeric($val) && $val >= 256*1024) {
00817                                         if ($val % (1024*1024) == 0) {
00818                                                 $val /= (1024*1024);
00819                                                 $val .= 'M';
00820                                         } else if ($val % 1024 == 0) {
00821                                                 $val /= 1024;
00822                                                 $val .= 'K';
00823                                         }
00824                                         //$val = htmlspecialchars($val);
00825                                 }
00826                         }
00827                         if ($category != $oldc) {
00828                                 $oldc = $category;
00829                                 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color;
00830                         }
00831                         if (strlen($desc)==0) $desc = '&nbsp;';
00832                         if (strlen($val)==0) $val = '&nbsp;';
00833                         if ($cli) {
00834                                 $html  .= str_replace('&nbsp;','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc)));
00835                                 
00836                         }else {
00837                                 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n";
00838                         }
00839                 }
00840                 
00841                 if (!$cli) $html .= "</table>\n";
00842                 $this->conn->fnExecute = $saveE;
00843                         
00844                 return $html;   
00845         }
00846         
00847         function Tables($orderby='1')
00848         {
00849                 if (!$this->tablesSQL) return false;
00850                 
00851                 $savelog = $this->conn->LogSQL(false);
00852                 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby);
00853                 $this->conn->LogSQL($savelog);
00854                 $html = rs2html($rs,false,false,false,false);
00855                 return $html;
00856         }
00857         
00858 
00859         function CreateLogTable()
00860         {
00861                 if (!$this->createTableSQL) return false;
00862                 
00863                 $savelog = $this->conn->LogSQL(false);
00864                 $ok = $this->conn->Execute($this->createTableSQL);
00865                 $this->conn->LogSQL($savelog);
00866                 return ($ok) ? true : false;
00867         }
00868         
00869         function DoSQLForm()
00870         {
00871         
00872                 
00873                 $PHP_SELF = $_SERVER['PHP_SELF'];
00874                 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : '';
00875 
00876                 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows'];
00877                 else $rows = 3;
00878                 
00879                 if (isset($_REQUEST['SMALLER'])) {
00880                         $rows /= 2;
00881                         if ($rows < 3) $rows = 3;
00882                         $_SESSION['phplens_sqlrows'] = $rows;
00883                 }
00884                 if (isset($_REQUEST['BIGGER'])) {
00885                         $rows *= 2;
00886                         $_SESSION['phplens_sqlrows'] = $rows;
00887                 }
00888                 
00889 ?>
00890 
00891 <form method="POST" action="<?php echo $PHP_SELF ?>">
00892 <table><tr>
00893 <td> Form size: <input type="submit" value=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " name="BIGGER">
00894 </td>
00895 <td align=right>
00896 <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>
00897 </td></tr>
00898   <tr>
00899   <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>
00900   </td>
00901   </tr>
00902  </table>
00903 </form>
00904 
00905 <?php
00906                 if (!isset($_REQUEST['sql'])) return;
00907                 
00908                 $sql = $this->undomq(trim($sql));
00909                 if (substr($sql,strlen($sql)-1) === ';') {
00910                         $print = true;
00911                         $sqla = $this->SplitSQL($sql);
00912                 } else  {
00913                         $print = false;
00914                         $sqla = array($sql);
00915                 }
00916                 foreach($sqla as $sqls) {
00917 
00918                         if (!$sqls) continue;
00919                         
00920                         if ($print) {
00921                                 print "<p>".htmlspecialchars($sqls)."</p>";
00922                                 flush();
00923                         }
00924                         $savelog = $this->conn->LogSQL(false);
00925                         $rs = $this->conn->Execute($sqls);
00926                         $this->conn->LogSQL($savelog);
00927                         if ($rs && is_object($rs) && !$rs->EOF) {
00928                                 rs2html($rs);
00929                                 while ($rs->NextRecordSet()) {
00930                                         print "<table width=98% bgcolor=#C0C0FF><tr><td>&nbsp;</td></tr></table>";
00931                                         rs2html($rs);
00932                                 }
00933                         } else {
00934                                 $e1 = (integer) $this->conn->ErrorNo();
00935                                 $e2 = $this->conn->ErrorMsg();
00936                                 if (($e1) || ($e2)) {
00937                                         if (empty($e1)) $e1 = '-1'; // postgresql fix
00938                                         print ' &nbsp; '.$e1.': '.$e2;
00939                                 } else {
00940                                         print "<p>No Recordset returned<br></p>";
00941                                 }
00942                         }
00943                 } // foreach
00944         }
00945         
00946         function SplitSQL($sql)
00947         {
00948                 $arr = explode(';',$sql);
00949                 return $arr;
00950         }
00951         
00952         function undomq($m) 
00953         {
00954         if (get_magic_quotes_gpc()) {
00955                 // undo the damage
00956                 $m = str_replace('\\\\','\\',$m);
00957                 $m = str_replace('\"','"',$m);
00958                 $m = str_replace('\\\'','\'',$m);
00959         }
00960         return $m;
00961 }
00962 
00963     
00964    /************************************************************************/
00965    
00989     function OptimizeTables()
00990     {
00991         $args = func_get_args();
00992         $numArgs = func_num_args();
00993         
00994         if ( $numArgs == 0) return false;
00995         
00996         $mode = ADODB_OPT_LOW; 
00997         $lastArg = $args[ $numArgs - 1];
00998         if ( !is_string($lastArg)) {
00999             $mode = $lastArg;
01000             unset( $args[ $numArgs - 1]);
01001         }
01002         
01003         foreach( $args as $table) {
01004             $this->optimizeTable( $table, $mode);
01005         }
01006         }
01007 
01020     function OptimizeTable( $table, $mode = ADODB_OPT_LOW) 
01021     {
01022         ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType));
01023         return false;
01024     }
01025     
01034     function optimizeDatabase() 
01035     {
01036         $conn = $this->conn;
01037         if ( !$conn) return false;
01038         
01039         $tables = $conn->MetaTables( 'TABLES');
01040         if ( !$tables ) return false;
01041 
01042         foreach( $tables as $table) {
01043             if ( !$this->optimizeTable( $table)) {
01044                 return false;
01045             }
01046         }
01047       
01048         return true;
01049     }
01050     // end hack 
01051 }
01052 
01053 ?>


Généré par Les spécialistes TYPO3 avec  doxygen 1.4.6