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