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