1<?php 2/* 3V4.66 28 Sept 2005 (c) 2000-2005 John Lim (jlim@natsoft.com.my). All rights reserved. 4 Released under both BSD license and Lesser GPL library license. 5 Whenever there is any discrepancy between the two licenses, 6 the BSD license will take precedence. See License.txt. 7 Set tabs to 4 for best viewing. 8 9 Latest version is available at http://adodb.sourceforge.net 10 11 Library for basic performance monitoring and tuning. 12 13 My apologies if you see code mixed with presentation. The presentation suits 14 my needs. If you want to separate code from presentation, be my guest. Patches 15 are welcome. 16 17*/ 18 19if (!defined(ADODB_DIR)) include_once(dirname(__FILE__).'/adodb.inc.php'); 20include_once(ADODB_DIR.'/tohtml.inc.php'); 21 22define( 'ADODB_OPT_HIGH', 2); 23define( 'ADODB_OPT_LOW', 1); 24 25// returns in K the memory of current process, or 0 if not known 26function adodb_getmem() 27{ 28 if (function_exists('memory_get_usage')) 29 return (integer) ((memory_get_usage()+512)/1024); 30 31 $pid = getmypid(); 32 33 if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) { 34 $output = array(); 35 36 exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output); 37 return substr($output[5], strpos($output[5], ':') + 1); 38 } 39 40 /* Hopefully UNIX */ 41 exec("ps --pid $pid --no-headers -o%mem,size", $output); 42 if (sizeof($output) == 0) return 0; 43 44 $memarr = explode(' ',$output[0]); 45 if (sizeof($memarr)>=2) return (integer) $memarr[1]; 46 47 return 0; 48} 49 50// avoids localization problems where , is used instead of . 51function adodb_round($n,$prec) 52{ 53 return number_format($n, $prec, '.', ''); 54} 55 56/* return microtime value as a float */ 57function adodb_microtime() 58{ 59 $t = microtime(); 60 $t = explode(' ',$t); 61 return (float)$t[1]+ (float)$t[0]; 62} 63 64/* sql code timing */ 65function& adodb_log_sql(&$conn,$sql,$inputarr) 66{ 67 68 $perf_table = adodb_perf::table(); 69 $conn->fnExecute = false; 70 $t0 = microtime(); 71 $rs =& $conn->Execute($sql,$inputarr); 72 $t1 = microtime(); 73 74 if (!empty($conn->_logsql)) { 75 $conn->_logsql = false; // disable logsql error simulation 76 $dbT = $conn->databaseType; 77 78 $a0 = split(' ',$t0); 79 $a0 = (float)$a0[1]+(float)$a0[0]; 80 81 $a1 = split(' ',$t1); 82 $a1 = (float)$a1[1]+(float)$a1[0]; 83 84 $time = $a1 - $a0; 85 86 if (!$rs) { 87 $errM = $conn->ErrorMsg(); 88 $errN = $conn->ErrorNo(); 89 $conn->lastInsID = 0; 90 $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250); 91 } else { 92 $tracer = ''; 93 $errM = ''; 94 $errN = 0; 95 $dbg = $conn->debug; 96 $conn->debug = false; 97 if (!is_object($rs) || $rs->dataProvider == 'empty') 98 $conn->_affected = $conn->affected_rows(true); 99 $conn->lastInsID = @$conn->Insert_ID(); 100 $conn->debug = $dbg; 101 } 102 if (isset($_SERVER['HTTP_HOST'])) { 103 $tracer .= '<br>'.$_SERVER['HTTP_HOST']; 104 if (isset($_SERVER['PHP_SELF'])) $tracer .= $_SERVER['PHP_SELF']; 105 } else 106 if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.$_SERVER['PHP_SELF']; 107 //$tracer .= (string) adodb_backtrace(false); 108 109 $tracer = (string) substr($tracer,0,500); 110 111 if (is_array($inputarr)) { 112 if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr); 113 else { 114 // Quote string parameters so we can see them in the 115 // performance stats. This helps spot disabled indexes. 116 $xar_params = $inputarr; 117 foreach ($xar_params as $xar_param_key => $xar_param) { 118 if (gettype($xar_param) == 'string') 119 $xar_params[$xar_param_key] = '"' . $xar_param . '"'; 120 } 121 $params = implode(', ', $xar_params); 122 if (strlen($params) >= 3000) $params = substr($params, 0, 3000); 123 } 124 } else { 125 $params = ''; 126 } 127 128 if (is_array($sql)) $sql = $sql[0]; 129 $arr = array('b'=>strlen($sql).'.'.crc32($sql), 130 'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6)); 131 //var_dump($arr); 132 $saved = $conn->debug; 133 $conn->debug = 0; 134 135 $d = $conn->sysTimeStamp; 136 if (empty($d)) $d = date("'Y-m-d H:i:s'"); 137 if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') { 138 $isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)"; 139 } else if ($dbT == 'odbc_mssql' || $dbT == 'informix') { 140 $timer = $arr['f']; 141 if ($dbT == 'informix') $sql2 = substr($sql2,0,230); 142 143 $sql1 = $conn->qstr($arr['b']); 144 $sql2 = $conn->qstr($arr['c']); 145 $params = $conn->qstr($arr['d']); 146 $tracer = $conn->qstr($arr['e']); 147 148 $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)"; 149 if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql); 150 $arr = false; 151 } else { 152 $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)"; 153 } 154 155 $ok = $conn->Execute($isql,$arr); 156 $conn->debug = $saved; 157 158 if ($ok) { 159 $conn->_logsql = true; 160 } else { 161 $err2 = $conn->ErrorMsg(); 162 $conn->_logsql = true; // enable logsql error simulation 163 $perf =& NewPerfMonitor($conn); 164 if ($perf) { 165 if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr); 166 } else { 167 $ok = $conn->Execute("create table $perf_table ( 168 created varchar(50), 169 sql0 varchar(250), 170 sql1 varchar(4000), 171 params varchar(3000), 172 tracer varchar(500), 173 timer decimal(16,6))"); 174 } 175 if (!$ok) { 176 ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>"); 177 $conn->_logsql = false; 178 } 179 } 180 $conn->_errorMsg = $errM; 181 $conn->_errorCode = $errN; 182 } 183 $conn->fnExecute = 'adodb_log_sql'; 184 return $rs; 185} 186 187 188/* 189The settings data structure is an associative array that database parameter per element. 190 191Each database parameter element in the array is itself an array consisting of: 192 1930: category code, used to group related db parameters 1941: either 195 a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'", 196 b. array holding sql string and field to look for, e.g. array('show variables','table_cache'), 197 c. a string prefixed by =, then a PHP method of the class is invoked, 198 e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue', 1992: description of the database parameter 200*/ 201 202class adodb_perf { 203 var $conn; 204 var $color = '#F0F0F0'; 205 var $table = '<table border=1 bgcolor=white>'; 206 var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>'; 207 var $warnRatio = 90; 208 var $tablesSQL = false; 209 var $cliFormat = "%32s => %s \r\n"; 210 var $sql1 = 'sql1'; // used for casting sql1 to text for mssql 211 var $explain = true; 212 var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>"; 213 var $createTableSQL = false; 214 var $maxLength = 2000; 215 216 // Sets the tablename to be used 217 function table($newtable = false) 218 { 219 static $_table; 220 221 if (!empty($newtable)) $_table = $newtable; 222 if (empty($_table)) $_table = 'adodb_logsql'; 223 return $_table; 224 } 225 226 // returns array with info to calculate CPU Load 227 function _CPULoad() 228 { 229/* 230 231cpu 524152 2662 2515228 336057010 232cpu0 264339 1408 1257951 168025827 233cpu1 259813 1254 1257277 168031181 234page 622307 25475680 235swap 24 1891 236intr 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 237disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320) 238ctxt 66155838 239btime 1062315585 240processes 69293 241 242*/ 243 // Algorithm is taken from 244 // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/example__obtaining_raw_performance_data.asp 245 if (strncmp(PHP_OS,'WIN',3)==0) { 246 if (PHP_VERSION == '5.0.0') return false; 247 if (PHP_VERSION == '5.0.1') return false; 248 if (PHP_VERSION == '5.0.2') return false; 249 if (PHP_VERSION == '5.0.3') return false; 250 if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737 251 252 @$c = new COM("WinMgmts:{impersonationLevel=impersonate}!Win32_PerfRawData_PerfOS_Processor.Name='_Total'"); 253 if (!$c) return false; 254 255 $info[0] = $c->PercentProcessorTime; 256 $info[1] = 0; 257 $info[2] = 0; 258 $info[3] = $c->TimeStamp_Sys100NS; 259 //print_r($info); 260 return $info; 261 } 262 263 // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com) 264 $statfile = '/proc/stat'; 265 if (!file_exists($statfile)) return false; 266 267 $fd = fopen($statfile,"r"); 268 if (!$fd) return false; 269 270 $statinfo = explode("\n",fgets($fd, 1024)); 271 fclose($fd); 272 foreach($statinfo as $line) { 273 $info = explode(" ",$line); 274 if($info[0]=="cpu") { 275 array_shift($info); // pop off "cpu" 276 if(!$info[0]) array_shift($info); // pop off blank space (if any) 277 return $info; 278 } 279 } 280 281 return false; 282 283 } 284 285 /* NOT IMPLEMENTED */ 286 function MemInfo() 287 { 288 /* 289 290 total: used: free: shared: buffers: cached: 291Mem: 1055289344 917299200 137990144 0 165437440 599773184 292Swap: 2146775040 11055104 2135719936 293MemTotal: 1030556 kB 294MemFree: 134756 kB 295MemShared: 0 kB 296Buffers: 161560 kB 297Cached: 581384 kB 298SwapCached: 4332 kB 299Active: 494468 kB 300Inact_dirty: 322856 kB 301Inact_clean: 24256 kB 302Inact_target: 168316 kB 303HighTotal: 131064 kB 304HighFree: 1024 kB 305LowTotal: 899492 kB 306LowFree: 133732 kB 307SwapTotal: 2096460 kB 308SwapFree: 2085664 kB 309Committed_AS: 348732 kB 310 */ 311 } 312 313 314 /* 315 Remember that this is client load, not db server load! 316 */ 317 var $_lastLoad; 318 function CPULoad() 319 { 320 $info = $this->_CPULoad(); 321 if (!$info) return false; 322 323 if (empty($this->_lastLoad)) { 324 sleep(1); 325 $this->_lastLoad = $info; 326 $info = $this->_CPULoad(); 327 } 328 329 $last = $this->_lastLoad; 330 $this->_lastLoad = $info; 331 332 $d_user = $info[0] - $last[0]; 333 $d_nice = $info[1] - $last[1]; 334 $d_system = $info[2] - $last[2]; 335 $d_idle = $info[3] - $last[3]; 336 337 //printf("Delta - User: %f Nice: %f System: %f Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle); 338 339 if (strncmp(PHP_OS,'WIN',3)==0) { 340 if ($d_idle < 1) $d_idle = 1; 341 return 100*(1-$d_user/$d_idle); 342 }else { 343 $total=$d_user+$d_nice+$d_system+$d_idle; 344 if ($total<1) $total=1; 345 return 100*($d_user+$d_nice+$d_system)/$total; 346 } 347 } 348 349 function Tracer($sql) 350 { 351 $perf_table = adodb_perf::table(); 352 $saveE = $this->conn->fnExecute; 353 $this->conn->fnExecute = false; 354 355 global $ADODB_FETCH_MODE; 356 $save = $ADODB_FETCH_MODE; 357 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 358 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 359 360 $sqlq = $this->conn->qstr($sql); 361 $arr = $this->conn->GetArray( 362"select count(*),tracer 363 from $perf_table where sql1=$sqlq 364 group by tracer 365 order by 1 desc"); 366 $s = ''; 367 if ($arr) { 368 $s .= '<h3>Scripts Affected</h3>'; 369 foreach($arr as $k) { 370 $s .= sprintf("%4d",$k[0]).' '.strip_tags($k[1]).'<br>'; 371 } 372 } 373 374 if (isset($savem)) $this->conn->SetFetchMode($savem); 375 $ADODB_CACHE_MODE = $save; 376 $this->conn->fnExecute = $saveE; 377 return $s; 378 } 379 380 /* 381 Explain Plan for $sql. 382 If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the 383 actual sql. 384 */ 385 function Explain($sql,$partial=false) 386 { 387 return false; 388 } 389 390 function InvalidSQL($numsql = 10) 391 { 392 393 if (isset($_GET['sql'])) return; 394 $s = '<h3>Invalid SQL</h3>'; 395 $saveE = $this->conn->fnExecute; 396 $this->conn->fnExecute = false; 397 $perf_table = adodb_perf::table(); 398 $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); 399 $this->conn->fnExecute = $saveE; 400 if ($rs) { 401 $s .= rs2html($rs,false,false,false,false); 402 } else 403 return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 404 405 return $s; 406 } 407 408 409 /* 410 This script identifies the longest running SQL 411 */ 412 function _SuspiciousSQL($numsql = 10) 413 { 414 global $ADODB_FETCH_MODE; 415 416 $perf_table = adodb_perf::table(); 417 $saveE = $this->conn->fnExecute; 418 $this->conn->fnExecute = false; 419 420 if (isset($_GET['exps']) && isset($_GET['sql'])) { 421 $partial = !empty($_GET['part']); 422 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 423 } 424 425 if (isset($_GET['sql'])) return; 426 $sql1 = $this->sql1; 427 428 $save = $ADODB_FETCH_MODE; 429 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 430 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 431 //$this->conn->debug=1; 432 $rs =& $this->conn->SelectLimit( 433 "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer 434 from $perf_table 435 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') 436 and (tracer is null or tracer not like 'ERROR:%') 437 group by sql1 438 order by 1 desc",$numsql); 439 if (isset($savem)) $this->conn->SetFetchMode($savem); 440 $ADODB_FETCH_MODE = $save; 441 $this->conn->fnExecute = $saveE; 442 443 if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 444 $s = "<h3>Suspicious SQL</h3> 445<font size=1>The following SQL have high average execution times</font><br> 446<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"; 447 $max = $this->maxLength; 448 while (!$rs->EOF) { 449 $sql = $rs->fields[1]; 450 $raw = urlencode($sql); 451 if (strlen($raw)>$max-100) { 452 $sql2 = substr($sql,0,$max-500); 453 $raw = urlencode($sql2).'&part='.crc32($sql); 454 } 455 $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">"; 456 $suffix = "</a>"; 457 if ($this->explain == false || strlen($prefix)>$max) { 458 $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>'; 459 $prefix = ''; 460 } 461 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". 462 "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; 463 $rs->MoveNext(); 464 } 465 return $s."</table>"; 466 467 } 468 469 function CheckMemory() 470 { 471 return ''; 472 } 473 474 475 function SuspiciousSQL($numsql=10) 476 { 477 return adodb_perf::_SuspiciousSQL($numsql); 478 } 479 480 function ExpensiveSQL($numsql=10) 481 { 482 return adodb_perf::_ExpensiveSQL($numsql); 483 } 484 485 486 /* 487 This reports the percentage of load on the instance due to the most 488 expensive few SQL statements. Tuning these statements can often 489 make huge improvements in overall system performance. 490 */ 491 function _ExpensiveSQL($numsql = 10) 492 { 493 global $ADODB_FETCH_MODE; 494 495 $perf_table = adodb_perf::table(); 496 $saveE = $this->conn->fnExecute; 497 $this->conn->fnExecute = false; 498 499 if (isset($_GET['expe']) && isset($_GET['sql'])) { 500 $partial = !empty($_GET['part']); 501 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 502 } 503 504 if (isset($_GET['sql'])) return; 505 506 $sql1 = $this->sql1; 507 $save = $ADODB_FETCH_MODE; 508 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 509 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 510 511 $rs =& $this->conn->SelectLimit( 512 "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer 513 from $perf_table 514 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') 515 and (tracer is null or tracer not like 'ERROR:%') 516 group by sql1 517 having count(*)>1 518 order by 1 desc",$numsql); 519 if (isset($savem)) $this->conn->SetFetchMode($savem); 520 $this->conn->fnExecute = $saveE; 521 $ADODB_FETCH_MODE = $save; 522 if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 523 $s = "<h3>Expensive SQL</h3> 524<font size=1>Tuning the following SQL could reduce the server load substantially</font><br> 525<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"; 526 $max = $this->maxLength; 527 while (!$rs->EOF) { 528 $sql = $rs->fields[1]; 529 $raw = urlencode($sql); 530 if (strlen($raw)>$max-100) { 531 $sql2 = substr($sql,0,$max-500); 532 $raw = urlencode($sql2).'&part='.crc32($sql); 533 } 534 $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">"; 535 $suffix = "</a>"; 536 if($this->explain == false || strlen($prefix>$max)) { 537 $prefix = ''; 538 $suffix = ''; 539 } 540 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". 541 "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; 542 $rs->MoveNext(); 543 } 544 return $s."</table>"; 545 } 546 547 /* 548 Raw function to return parameter value from $settings. 549 */ 550 function DBParameter($param) 551 { 552 if (empty($this->settings[$param])) return false; 553 $sql = $this->settings[$param][1]; 554 return $this->_DBParameter($sql); 555 } 556 557 /* 558 Raw function returning array of poll paramters 559 */ 560 function &PollParameters() 561 { 562 $arr[0] = (float)$this->DBParameter('data cache hit ratio'); 563 $arr[1] = (float)$this->DBParameter('data reads'); 564 $arr[2] = (float)$this->DBParameter('data writes'); 565 $arr[3] = (integer) $this->DBParameter('current connections'); 566 return $arr; 567 } 568 569 /* 570 Low-level Get Database Parameter 571 */ 572 function _DBParameter($sql) 573 { 574 $savelog = $this->conn->LogSQL(false); 575 if (is_array($sql)) { 576 global $ADODB_FETCH_MODE; 577 578 $sql1 = $sql[0]; 579 $key = $sql[1]; 580 if (sizeof($sql)>2) $pos = $sql[2]; 581 else $pos = 1; 582 if (sizeof($sql)>3) $coef = $sql[3]; 583 else $coef = false; 584 $ret = false; 585 $save = $ADODB_FETCH_MODE; 586 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 587 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 588 589 $rs = $this->conn->Execute($sql1); 590 591 if (isset($savem)) $this->SetFetchMode($savem); 592 $ADODB_FETCH_MODE = $save; 593 if ($rs) { 594 while (!$rs->EOF) { 595 $keyf = reset($rs->fields); 596 if (trim($keyf) == $key) { 597 $ret = $rs->fields[$pos]; 598 if ($coef) $ret *= $coef; 599 break; 600 } 601 $rs->MoveNext(); 602 } 603 $rs->Close(); 604 } 605 $this->conn->LogSQL($savelog); 606 return $ret; 607 } else { 608 if (strncmp($sql,'=',1) == 0) { 609 $fn = substr($sql,1); 610 return $this->$fn(); 611 } 612 $sql = str_replace('$DATABASE',$this->conn->database,$sql); 613 $ret = $this->conn->GetOne($sql); 614 $this->conn->LogSQL($savelog); 615 616 return $ret; 617 } 618 } 619 620 /* 621 Warn if cache ratio falls below threshold. Displayed in "Description" column. 622 */ 623 function WarnCacheRatio($val) 624 { 625 if ($val < $this->warnRatio) 626 return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>'; 627 else return ''; 628 } 629 630 /***********************************************************************************************/ 631 // HIGH LEVEL UI FUNCTIONS 632 /***********************************************************************************************/ 633 634 635 function UI($pollsecs=5) 636 { 637 638 $perf_table = adodb_perf::table(); 639 $conn = $this->conn; 640 641 $app = $conn->host; 642 if ($conn->host && $conn->database) $app .= ', db='; 643 $app .= $conn->database; 644 645 if ($app) $app .= ', '; 646 $savelog = $this->conn->LogSQL(false); 647 $info = $conn->ServerInfo(); 648 if (isset($_GET['clearsql'])) { 649 $this->conn->Execute("delete from $perf_table"); 650 } 651 $this->conn->LogSQL($savelog); 652 653 // magic quotes 654 655 if (isset($_GET['sql']) && get_magic_quotes_gpc()) { 656 $_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']); 657 } 658 659 if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10; 660 else $nsql = $_SESSION['ADODB_PERF_SQL']; 661 662 $app .= $info['description']; 663 664 665 if (isset($_GET['do'])) $do = $_GET['do']; 666 else if (isset($_POST['do'])) $do = $_POST['do']; 667 else if (isset($_GET['sql'])) $do = 'viewsql'; 668 else $do = 'stats'; 669 670 if (isset($_GET['nsql'])) { 671 if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql']; 672 } 673 echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>"; 674 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>"; 675 else $form = "<td> </td>"; 676 677 $allowsql = !defined('ADODB_PERF_NO_RUN_SQL'); 678 679 if (empty($_GET['hidem'])) 680 echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2> 681 <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td> 682 <a href=?do=stats><b>Performance Stats</b></a> <a href=?do=viewsql><b>View SQL</b></a> 683 <a href=?do=tables><b>View Tables</b></a> <a href=?do=poll><b>Poll Stats</b></a>", 684 $allowsql ? ' <a href=?do=dosql><b>Run SQL</b></a>' : '', 685 "$form", 686 "</tr></table>"; 687 688 689 switch ($do) { 690 default: 691 case 'stats': 692 echo $this->HealthCheck(); 693 //$this->conn->debug=1; 694 echo $this->CheckMemory(); 695 break; 696 case 'poll': 697 echo "<iframe width=720 height=80% 698 src=\"{$_SERVER['PHP_SELF']}?do=poll2&hidem=1\"></iframe>"; 699 break; 700 case 'poll2': 701 echo "<pre>"; 702 $this->Poll($pollsecs); 703 break; 704 705 case 'dosql': 706 if (!$allowsql) break; 707 708 $this->DoSQLForm(); 709 break; 710 case 'viewsql': 711 if (empty($_GET['hidem'])) 712 echo " <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; 713 echo($this->SuspiciousSQL($nsql)); 714 echo($this->ExpensiveSQL($nsql)); 715 echo($this->InvalidSQL($nsql)); 716 break; 717 case 'tables': 718 echo $this->Tables(); break; 719 } 720 global $ADODB_vers; 721 echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>"; 722 } 723 724 /* 725 Runs in infinite loop, returning real-time statistics 726 */ 727 function Poll($secs=5) 728 { 729 $this->conn->fnExecute = false; 730 //$this->conn->debug=1; 731 if ($secs <= 1) $secs = 1; 732 echo "Accumulating statistics, every $secs seconds...\n";flush(); 733 $arro =& $this->PollParameters(); 734 $cnt = 0; 735 set_time_limit(0); 736 sleep($secs); 737 while (1) { 738 739 $arr =& $this->PollParameters(); 740 741 $hits = sprintf('%2.2f',$arr[0]); 742 $reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs); 743 $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs); 744 $sess = sprintf('%5d',$arr[3]); 745 746 $load = $this->CPULoad(); 747 if ($load !== false) { 748 $oslabel = 'WS-CPU%'; 749 $osval = sprintf(" %2.1f ",(float) $load); 750 }else { 751 $oslabel = ''; 752 $osval = ''; 753 } 754 if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n"; 755 $cnt += 1; 756 echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n"; 757 flush(); 758 759 if (connection_aborted()) return; 760 761 sleep($secs); 762 $arro = $arr; 763 } 764 } 765 766 /* 767 Returns basic health check in a command line interface 768 */ 769 function HealthCheckCLI() 770 { 771 return $this->HealthCheck(true); 772 } 773 774 775 /* 776 Returns basic health check as HTML 777 */ 778 function HealthCheck($cli=false) 779 { 780 $saveE = $this->conn->fnExecute; 781 $this->conn->fnExecute = false; 782 if ($cli) $html = ''; 783 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles; 784 785 $oldc = false; 786 $bgc = ''; 787 foreach($this->settings as $name => $arr) { 788 if ($arr === false) break; 789 790 if (!is_string($name)) { 791 if ($cli) $html .= " -- $arr -- \n"; 792 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> </td></tr>"; 793 continue; 794 } 795 796 if (!is_array($arr)) break; 797 $category = $arr[0]; 798 $how = $arr[1]; 799 if (sizeof($arr)>2) $desc = $arr[2]; 800 else $desc = ' '; 801 802 803 if ($category == 'HIDE') continue; 804 805 $val = $this->_DBParameter($how); 806 807 if ($desc && strncmp($desc,"=",1) === 0) { 808 $fn = substr($desc,1); 809 $desc = $this->$fn($val); 810 } 811 812 if ($val === false) { 813 $m = $this->conn->ErrorMsg(); 814 $val = "Error: $m"; 815 } else { 816 if (is_numeric($val) && $val >= 256*1024) { 817 if ($val % (1024*1024) == 0) { 818 $val /= (1024*1024); 819 $val .= 'M'; 820 } else if ($val % 1024 == 0) { 821 $val /= 1024; 822 $val .= 'K'; 823 } 824 //$val = htmlspecialchars($val); 825 } 826 } 827 if ($category != $oldc) { 828 $oldc = $category; 829 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color; 830 } 831 if (strlen($desc)==0) $desc = ' '; 832 if (strlen($val)==0) $val = ' '; 833 if ($cli) { 834 $html .= str_replace(' ','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc))); 835 836 }else { 837 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n"; 838 } 839 } 840 841 if (!$cli) $html .= "</table>\n"; 842 $this->conn->fnExecute = $saveE; 843 844 return $html; 845 } 846 847 function Tables($orderby='1') 848 { 849 if (!$this->tablesSQL) return false; 850 851 $savelog = $this->conn->LogSQL(false); 852 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby); 853 $this->conn->LogSQL($savelog); 854 $html = rs2html($rs,false,false,false,false); 855 return $html; 856 } 857 858 859 function CreateLogTable() 860 { 861 if (!$this->createTableSQL) return false; 862 863 $savelog = $this->conn->LogSQL(false); 864 $ok = $this->conn->Execute($this->createTableSQL); 865 $this->conn->LogSQL($savelog); 866 return ($ok) ? true : false; 867 } 868 869 function DoSQLForm() 870 { 871 872 873 $PHP_SELF = $_SERVER['PHP_SELF']; 874 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : ''; 875 876 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows']; 877 else $rows = 3; 878 879 if (isset($_REQUEST['SMALLER'])) { 880 $rows /= 2; 881 if ($rows < 3) $rows = 3; 882 $_SESSION['phplens_sqlrows'] = $rows; 883 } 884 if (isset($_REQUEST['BIGGER'])) { 885 $rows *= 2; 886 $_SESSION['phplens_sqlrows'] = $rows; 887 } 888 889?> 890 891<form method="POST" action="<?php echo $PHP_SELF ?>"> 892<table><tr> 893<td> Form size: <input type="submit" value=" < " name="SMALLER"><input type="submit" value=" > > " name="BIGGER"> 894</td> 895<td align=right> 896<input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql> 897</td></tr> 898 <tr> 899 <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea> 900 </td> 901 </tr> 902 </table> 903</form> 904 905<?php 906 if (!isset($_REQUEST['sql'])) return; 907 908 $sql = $this->undomq(trim($sql)); 909 if (substr($sql,strlen($sql)-1) === ';') { 910 $print = true; 911 $sqla = $this->SplitSQL($sql); 912 } else { 913 $print = false; 914 $sqla = array($sql); 915 } 916 foreach($sqla as $sqls) { 917 918 if (!$sqls) continue; 919 920 if ($print) { 921 print "<p>".htmlspecialchars($sqls)."</p>"; 922 flush(); 923 } 924 $savelog = $this->conn->LogSQL(false); 925 $rs = $this->conn->Execute($sqls); 926 $this->conn->LogSQL($savelog); 927 if ($rs && is_object($rs) && !$rs->EOF) { 928 rs2html($rs); 929 while ($rs->NextRecordSet()) { 930 print "<table width=98% bgcolor=#C0C0FF><tr><td> </td></tr></table>"; 931 rs2html($rs); 932 } 933 } else { 934 $e1 = (integer) $this->conn->ErrorNo(); 935 $e2 = $this->conn->ErrorMsg(); 936 if (($e1) || ($e2)) { 937 if (empty($e1)) $e1 = '-1'; // postgresql fix 938 print ' '.$e1.': '.$e2; 939 } else { 940 print "<p>No Recordset returned<br></p>"; 941 } 942 } 943 } // foreach 944 } 945 946 function SplitSQL($sql) 947 { 948 $arr = explode(';',$sql); 949 return $arr; 950 } 951 952 function undomq(&$m) 953 { 954 if (get_magic_quotes_gpc()) { 955 // undo the damage 956 $m = str_replace('\\\\','\\',$m); 957 $m = str_replace('\"','"',$m); 958 $m = str_replace('\\\'','\'',$m); 959 } 960 return $m; 961} 962 963 964 /************************************************************************/ 965 966 /** 967 * Reorganise multiple table-indices/statistics/.. 968 * OptimizeMode could be given by last Parameter 969 * 970 * @example 971 * <pre> 972 * optimizeTables( 'tableA'); 973 * </pre> 974 * <pre> 975 * optimizeTables( 'tableA', 'tableB', 'tableC'); 976 * </pre> 977 * <pre> 978 * optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW); 979 * </pre> 980 * 981 * @param string table name of the table to optimize 982 * @param int mode optimization-mode 983 * <code>ADODB_OPT_HIGH</code> for full optimization 984 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 985 * Default is LOW <code>ADODB_OPT_LOW</code> 986 * @author Markus Staab 987 * @return Returns <code>true</code> on success and <code>false</code> on error 988 */ 989 function OptimizeTables() 990 { 991 $args = func_get_args(); 992 $numArgs = func_num_args(); 993 994 if ( $numArgs == 0) return false; 995 996 $mode = ADODB_OPT_LOW; 997 $lastArg = $args[ $numArgs - 1]; 998 if ( !is_string($lastArg)) { 999 $mode = $lastArg; 1000 unset( $args[ $numArgs - 1]); 1001 } 1002 1003 foreach( $args as $table) { 1004 $this->optimizeTable( $table, $mode); 1005 } 1006 } 1007 1008 /** 1009 * Reorganise the table-indices/statistics/.. depending on the given mode. 1010 * Default Implementation throws an error. 1011 * 1012 * @param string table name of the table to optimize 1013 * @param int mode optimization-mode 1014 * <code>ADODB_OPT_HIGH</code> for full optimization 1015 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1016 * Default is LOW <code>ADODB_OPT_LOW</code> 1017 * @author Markus Staab 1018 * @return Returns <code>true</code> on success and <code>false</code> on error 1019 */ 1020 function OptimizeTable( $table, $mode = ADODB_OPT_LOW) 1021 { 1022 ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType)); 1023 return false; 1024 } 1025 1026 /** 1027 * Reorganise current database. 1028 * Default implementation loops over all <code>MetaTables()</code> and 1029 * optimize each using <code>optmizeTable()</code> 1030 * 1031 * @author Markus Staab 1032 * @return Returns <code>true</code> on success and <code>false</code> on error 1033 */ 1034 function optimizeDatabase() 1035 { 1036 $conn = $this->conn; 1037 if ( !$conn) return false; 1038 1039 $tables = $conn->MetaTables( 'TABLES'); 1040 if ( !$tables ) return false; 1041 1042 foreach( $tables as $table) { 1043 if ( !$this->optimizeTable( $table)) { 1044 return false; 1045 } 1046 } 1047 1048 return true; 1049 } 1050 // end hack 1051} 1052 1053?>