Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
- Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.
/lib/adodb/ -> adodb-perf.inc.php (source)
Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]
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 /** @var array Settings data. */ 241 var $settings = []; 242 243 // Sets the tablename to be used 244 static function table($newtable = false) 245 { 246 static $_table; 247 248 if (!empty($newtable)) $_table = $newtable; 249 if (empty($_table)) $_table = 'adodb_logsql'; 250 return $_table; 251 } 252 253 // returns array with info to calculate CPU Load 254 function _CPULoad() 255 { 256 /* 257 258 cpu 524152 2662 2515228 336057010 259 cpu0 264339 1408 1257951 168025827 260 cpu1 259813 1254 1257277 168031181 261 page 622307 25475680 262 swap 24 1891 263 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 264 disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320) 265 ctxt 66155838 266 btime 1062315585 267 processes 69293 268 269 */ 270 // Algorithm is taken from 271 // http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/414b0e1b-499c-411e-8a02-6a12e339c0f1/ 272 if (strncmp(PHP_OS,'WIN',3)==0) { 273 static $FAIL = false; 274 if ($FAIL) return false; 275 276 $objName = "winmgmts:{impersonationLevel=impersonate}!\\\\.\\root\\CIMV2"; 277 $myQuery = "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor WHERE Name = '_Total'"; 278 279 try { 280 @$objWMIService = new COM($objName); 281 if (!$objWMIService) { 282 $FAIL = true; 283 return false; 284 } 285 286 $info[0] = -1; 287 $info[1] = 0; 288 $info[2] = 0; 289 $info[3] = 0; 290 foreach($objWMIService->ExecQuery($myQuery) as $objItem) { 291 $info[0] = $objItem->PercentProcessorTime(); 292 } 293 294 } catch(Exception $e) { 295 $FAIL = true; 296 echo $e->getMessage(); 297 return false; 298 } 299 300 return $info; 301 } 302 303 // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com) 304 $statfile = '/proc/stat'; 305 if (!file_exists($statfile)) return false; 306 307 $fd = fopen($statfile,"r"); 308 if (!$fd) return false; 309 310 $statinfo = explode("\n",fgets($fd, 1024)); 311 fclose($fd); 312 foreach($statinfo as $line) { 313 $info = explode(" ",$line); 314 if($info[0]=="cpu") { 315 array_shift($info); // pop off "cpu" 316 if(!$info[0]) array_shift($info); // pop off blank space (if any) 317 return $info; 318 } 319 } 320 321 return false; 322 323 } 324 325 /* NOT IMPLEMENTED */ 326 function MemInfo() 327 { 328 /* 329 330 total: used: free: shared: buffers: cached: 331 Mem: 1055289344 917299200 137990144 0 165437440 599773184 332 Swap: 2146775040 11055104 2135719936 333 MemTotal: 1030556 kB 334 MemFree: 134756 kB 335 MemShared: 0 kB 336 Buffers: 161560 kB 337 Cached: 581384 kB 338 SwapCached: 4332 kB 339 Active: 494468 kB 340 Inact_dirty: 322856 kB 341 Inact_clean: 24256 kB 342 Inact_target: 168316 kB 343 HighTotal: 131064 kB 344 HighFree: 1024 kB 345 LowTotal: 899492 kB 346 LowFree: 133732 kB 347 SwapTotal: 2096460 kB 348 SwapFree: 2085664 kB 349 Committed_AS: 348732 kB 350 */ 351 } 352 353 354 /* 355 Remember that this is client load, not db server load! 356 */ 357 var $_lastLoad; 358 function CPULoad() 359 { 360 $info = $this->_CPULoad(); 361 if (!$info) return false; 362 363 if (strncmp(PHP_OS,'WIN',3)==0) { 364 return (integer) $info[0]; 365 }else { 366 if (empty($this->_lastLoad)) { 367 sleep(1); 368 $this->_lastLoad = $info; 369 $info = $this->_CPULoad(); 370 } 371 372 $last = $this->_lastLoad; 373 $this->_lastLoad = $info; 374 375 $d_user = $info[0] - $last[0]; 376 $d_nice = $info[1] - $last[1]; 377 $d_system = $info[2] - $last[2]; 378 $d_idle = $info[3] - $last[3]; 379 380 //printf("Delta - User: %f Nice: %f System: %f Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle); 381 382 $total=$d_user+$d_nice+$d_system+$d_idle; 383 if ($total<1) $total=1; 384 return 100*($d_user+$d_nice+$d_system)/$total; 385 } 386 } 387 388 function Tracer($sql) 389 { 390 $perf_table = adodb_perf::table(); 391 $saveE = $this->conn->fnExecute; 392 $this->conn->fnExecute = false; 393 394 global $ADODB_FETCH_MODE; 395 $save = $ADODB_FETCH_MODE; 396 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 397 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 398 399 $sqlq = $this->conn->qstr($sql); 400 $arr = $this->conn->GetArray( 401 "select count(*),tracer 402 from $perf_table where sql1=$sqlq 403 group by tracer 404 order by 1 desc"); 405 $s = ''; 406 if ($arr) { 407 $s .= '<h3>Scripts Affected</h3>'; 408 foreach($arr as $k) { 409 $s .= sprintf("%4d",$k[0]).' '.strip_tags($k[1]).'<br>'; 410 } 411 } 412 413 if (isset($savem)) $this->conn->SetFetchMode($savem); 414 $ADODB_CACHE_MODE = $save; 415 $this->conn->fnExecute = $saveE; 416 return $s; 417 } 418 419 /* 420 Explain Plan for $sql. 421 If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the 422 actual sql. 423 */ 424 function Explain($sql,$partial=false) 425 { 426 return false; 427 } 428 429 function InvalidSQL($numsql = 10) 430 { 431 432 if (isset($_GET['sql'])) return; 433 $s = '<h3>Invalid SQL</h3>'; 434 $saveE = $this->conn->fnExecute; 435 $this->conn->fnExecute = false; 436 $perf_table = adodb_perf::table(); 437 $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); 438 $this->conn->fnExecute = $saveE; 439 if ($rs) { 440 $s .= rs2html($rs,false,false,false,false); 441 } else 442 return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 443 444 return $s; 445 } 446 447 448 /* 449 This script identifies the longest running SQL 450 */ 451 function _SuspiciousSQL($numsql = 10) 452 { 453 global $ADODB_FETCH_MODE; 454 455 $perf_table = adodb_perf::table(); 456 $saveE = $this->conn->fnExecute; 457 $this->conn->fnExecute = false; 458 459 if (isset($_GET['exps']) && isset($_GET['sql'])) { 460 $partial = !empty($_GET['part']); 461 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 462 } 463 464 if (isset($_GET['sql'])) return; 465 $sql1 = $this->sql1; 466 467 $save = $ADODB_FETCH_MODE; 468 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 469 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 470 //$this->conn->debug=1; 471 $rs = $this->conn->SelectLimit( 472 "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer 473 from $perf_table 474 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') 475 and (tracer is null or tracer not like 'ERROR:%') 476 group by sql1 477 order by 1 desc",$numsql); 478 if (isset($savem)) $this->conn->SetFetchMode($savem); 479 $ADODB_FETCH_MODE = $save; 480 $this->conn->fnExecute = $saveE; 481 482 if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 483 $s = "<h3>Suspicious SQL</h3> 484 <font size=1>The following SQL have high average execution times</font><br> 485 <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"; 486 $max = $this->maxLength; 487 while (!$rs->EOF) { 488 $sql = $rs->fields[1]; 489 $raw = urlencode($sql); 490 if (strlen($raw)>$max-100) { 491 $sql2 = substr($sql,0,$max-500); 492 $raw = urlencode($sql2).'&part='.crc32($sql); 493 } 494 $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">"; 495 $suffix = "</a>"; 496 if ($this->explain == false || strlen($prefix)>$max) { 497 $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>'; 498 $prefix = ''; 499 } 500 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". 501 "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; 502 $rs->MoveNext(); 503 } 504 return $s."</table>"; 505 506 } 507 508 function CheckMemory() 509 { 510 return ''; 511 } 512 513 514 function SuspiciousSQL($numsql=10) 515 { 516 return adodb_perf::_SuspiciousSQL($numsql); 517 } 518 519 function ExpensiveSQL($numsql=10) 520 { 521 return adodb_perf::_ExpensiveSQL($numsql); 522 } 523 524 525 /* 526 This reports the percentage of load on the instance due to the most 527 expensive few SQL statements. Tuning these statements can often 528 make huge improvements in overall system performance. 529 */ 530 function _ExpensiveSQL($numsql = 10) 531 { 532 global $ADODB_FETCH_MODE; 533 534 $perf_table = adodb_perf::table(); 535 $saveE = $this->conn->fnExecute; 536 $this->conn->fnExecute = false; 537 538 if (isset($_GET['expe']) && isset($_GET['sql'])) { 539 $partial = !empty($_GET['part']); 540 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 541 } 542 543 if (isset($_GET['sql'])) return; 544 545 $sql1 = $this->sql1; 546 $save = $ADODB_FETCH_MODE; 547 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 548 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 549 550 $rs = $this->conn->SelectLimit( 551 "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer 552 from $perf_table 553 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') 554 and (tracer is null or tracer not like 'ERROR:%') 555 group by sql1 556 having count(*)>1 557 order by 1 desc",$numsql); 558 if (isset($savem)) $this->conn->SetFetchMode($savem); 559 $this->conn->fnExecute = $saveE; 560 $ADODB_FETCH_MODE = $save; 561 if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 562 $s = "<h3>Expensive SQL</h3> 563 <font size=1>Tuning the following SQL could reduce the server load substantially</font><br> 564 <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"; 565 $max = $this->maxLength; 566 while (!$rs->EOF) { 567 $sql = $rs->fields[1]; 568 $raw = urlencode($sql); 569 if (strlen($raw)>$max-100) { 570 $sql2 = substr($sql,0,$max-500); 571 $raw = urlencode($sql2).'&part='.crc32($sql); 572 } 573 $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">"; 574 $suffix = "</a>"; 575 if($this->explain == false || strlen($prefix>$max)) { 576 $prefix = ''; 577 $suffix = ''; 578 } 579 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". 580 "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; 581 $rs->MoveNext(); 582 } 583 return $s."</table>"; 584 } 585 586 /* 587 Raw function to return parameter value from $settings. 588 */ 589 function DBParameter($param) 590 { 591 if (empty($this->settings[$param])) return false; 592 $sql = $this->settings[$param][1]; 593 return $this->_DBParameter($sql); 594 } 595 596 /* 597 Raw function returning array of poll parameters 598 */ 599 function PollParameters() 600 { 601 $arr[0] = (float)$this->DBParameter('data cache hit ratio'); 602 $arr[1] = (float)$this->DBParameter('data reads'); 603 $arr[2] = (float)$this->DBParameter('data writes'); 604 $arr[3] = (integer) $this->DBParameter('current connections'); 605 return $arr; 606 } 607 608 /* 609 Low-level Get Database Parameter 610 */ 611 function _DBParameter($sql) 612 { 613 $savelog = $this->conn->LogSQL(false); 614 if (is_array($sql)) { 615 global $ADODB_FETCH_MODE; 616 617 $sql1 = $sql[0]; 618 $key = $sql[1]; 619 if (sizeof($sql)>2) $pos = $sql[2]; 620 else $pos = 1; 621 if (sizeof($sql)>3) $coef = $sql[3]; 622 else $coef = false; 623 $ret = false; 624 $save = $ADODB_FETCH_MODE; 625 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 626 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 627 628 $rs = $this->conn->Execute($sql1); 629 630 if (isset($savem)) $this->conn->SetFetchMode($savem); 631 $ADODB_FETCH_MODE = $save; 632 if ($rs) { 633 while (!$rs->EOF) { 634 $keyf = reset($rs->fields); 635 if (trim($keyf) == $key) { 636 $ret = $rs->fields[$pos]; 637 if ($coef) $ret *= $coef; 638 break; 639 } 640 $rs->MoveNext(); 641 } 642 $rs->Close(); 643 } 644 $this->conn->LogSQL($savelog); 645 return $ret; 646 } else { 647 if (strncmp($sql,'=',1) == 0) { 648 $fn = substr($sql,1); 649 return $this->$fn(); 650 } 651 $sql = str_replace('$DATABASE',$this->conn->database,$sql); 652 $ret = $this->conn->GetOne($sql); 653 $this->conn->LogSQL($savelog); 654 655 return $ret; 656 } 657 } 658 659 /* 660 Warn if cache ratio falls below threshold. Displayed in "Description" column. 661 */ 662 function WarnCacheRatio($val) 663 { 664 if ($val < $this->warnRatio) 665 return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>'; 666 else return ''; 667 } 668 669 function clearsql() 670 { 671 $perf_table = adodb_perf::table(); 672 $this->conn->Execute("delete from $perf_table where created<".$this->conn->sysTimeStamp); 673 } 674 /***********************************************************************************************/ 675 // HIGH LEVEL UI FUNCTIONS 676 /***********************************************************************************************/ 677 678 679 function UI($pollsecs=5) 680 { 681 global $ADODB_LOG_CONN; 682 683 $perf_table = adodb_perf::table(); 684 $conn = $this->conn; 685 686 $app = $conn->host; 687 if ($conn->host && $conn->database) $app .= ', db='; 688 $app .= $conn->database; 689 690 if ($app) $app .= ', '; 691 $savelog = $this->conn->LogSQL(false); 692 $info = $conn->ServerInfo(); 693 if (isset($_GET['clearsql'])) { 694 $this->clearsql(); 695 } 696 $this->conn->LogSQL($savelog); 697 698 if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10; 699 else $nsql = $_SESSION['ADODB_PERF_SQL']; 700 701 $app .= $info['description']; 702 703 704 if (isset($_GET['do'])) $do = $_GET['do']; 705 else if (isset($_POST['do'])) $do = $_POST['do']; 706 else if (isset($_GET['sql'])) $do = 'viewsql'; 707 else $do = 'stats'; 708 709 if (isset($_GET['nsql'])) { 710 if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql']; 711 } 712 echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>"; 713 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>"; 714 else $form = "<td> </td>"; 715 716 $allowsql = !defined('ADODB_PERF_NO_RUN_SQL'); 717 global $ADODB_PERF_MIN; 718 $app .= " (Min sql timing \$ADODB_PERF_MIN=$ADODB_PERF_MIN secs)"; 719 720 if (empty($_GET['hidem'])) 721 echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2> 722 <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> 723 <a href=?do=stats><b>Performance Stats</b></a> <a href=?do=viewsql><b>View SQL</b></a> 724 <a href=?do=tables><b>View Tables</b></a> <a href=?do=poll><b>Poll Stats</b></a>", 725 $allowsql ? ' <a href=?do=dosql><b>Run SQL</b></a>' : '', 726 "$form", 727 "</tr></table>"; 728 729 730 switch ($do) { 731 default: 732 case 'stats': 733 if (empty($ADODB_LOG_CONN)) 734 echo "<p> <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; 735 echo $this->HealthCheck(); 736 //$this->conn->debug=1; 737 echo $this->CheckMemory(); 738 break; 739 case 'poll': 740 $self = htmlspecialchars($_SERVER['PHP_SELF']); 741 echo "<iframe width=720 height=80% 742 src=\"{$self}?do=poll2&hidem=1\"></iframe>"; 743 break; 744 case 'poll2': 745 echo "<pre>"; 746 $this->Poll($pollsecs); 747 break; 748 749 case 'dosql': 750 if (!$allowsql) break; 751 752 $this->DoSQLForm(); 753 break; 754 case 'viewsql': 755 if (empty($_GET['hidem'])) 756 echo " <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; 757 echo($this->SuspiciousSQL($nsql)); 758 echo($this->ExpensiveSQL($nsql)); 759 echo($this->InvalidSQL($nsql)); 760 break; 761 case 'tables': 762 echo $this->Tables(); break; 763 } 764 global $ADODB_vers; 765 } 766 767 /* 768 Runs in infinite loop, returning real-time statistics 769 */ 770 function Poll($secs=5) 771 { 772 $this->conn->fnExecute = false; 773 //$this->conn->debug=1; 774 if ($secs <= 1) $secs = 1; 775 echo "Accumulating statistics, every $secs seconds...\n";flush(); 776 $arro = $this->PollParameters(); 777 $cnt = 0; 778 set_time_limit(0); 779 sleep($secs); 780 while (1) { 781 782 $arr = $this->PollParameters(); 783 784 $hits = sprintf('%2.2f',$arr[0]); 785 $reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs); 786 $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs); 787 $sess = sprintf('%5d',$arr[3]); 788 789 $load = $this->CPULoad(); 790 if ($load !== false) { 791 $oslabel = 'WS-CPU%'; 792 $osval = sprintf(" %2.1f ",(float) $load); 793 }else { 794 $oslabel = ''; 795 $osval = ''; 796 } 797 if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n"; 798 $cnt += 1; 799 echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n"; 800 flush(); 801 802 if (connection_aborted()) return; 803 804 sleep($secs); 805 $arro = $arr; 806 } 807 } 808 809 /* 810 Returns basic health check in a command line interface 811 */ 812 function HealthCheckCLI() 813 { 814 return $this->HealthCheck(true); 815 } 816 817 818 /* 819 Returns basic health check as HTML 820 */ 821 function HealthCheck($cli=false) 822 { 823 $saveE = $this->conn->fnExecute; 824 $this->conn->fnExecute = false; 825 if ($cli) $html = ''; 826 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles; 827 828 $oldc = false; 829 $bgc = ''; 830 foreach($this->settings as $name => $arr) { 831 if ($arr === false) break; 832 833 if (!is_string($name)) { 834 if ($cli) $html .= " -- $arr -- \n"; 835 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> </td></tr>"; 836 continue; 837 } 838 839 if (!is_array($arr)) break; 840 $category = $arr[0]; 841 $how = $arr[1]; 842 if (sizeof($arr)>2) $desc = $arr[2]; 843 else $desc = ' '; 844 845 846 if ($category == 'HIDE') continue; 847 848 $val = $this->_DBParameter($how); 849 850 if ($desc && strncmp($desc,"=",1) === 0) { 851 $fn = substr($desc,1); 852 $desc = $this->$fn($val); 853 } 854 855 if ($val === false) { 856 $m = $this->conn->ErrorMsg(); 857 $val = "Error: $m"; 858 } else { 859 if (is_numeric($val) && $val >= 256*1024) { 860 if ($val % (1024*1024) == 0) { 861 $val /= (1024*1024); 862 $val .= 'M'; 863 } else if ($val % 1024 == 0) { 864 $val /= 1024; 865 $val .= 'K'; 866 } 867 //$val = htmlspecialchars($val); 868 } 869 } 870 if ($category != $oldc) { 871 $oldc = $category; 872 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color; 873 } 874 if (strlen($desc)==0) $desc = ' '; 875 if (strlen($val)==0) $val = ' '; 876 if ($cli) { 877 $html .= str_replace(' ','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc))); 878 879 }else { 880 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n"; 881 } 882 } 883 884 if (!$cli) $html .= "</table>\n"; 885 $this->conn->fnExecute = $saveE; 886 887 return $html; 888 } 889 890 function Tables($orderby='1') 891 { 892 if (!$this->tablesSQL) return false; 893 894 $savelog = $this->conn->LogSQL(false); 895 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby); 896 $this->conn->LogSQL($savelog); 897 $html = rs2html($rs,false,false,false,false); 898 return $html; 899 } 900 901 902 function CreateLogTable() 903 { 904 if (!$this->createTableSQL) return false; 905 906 $table = $this->table(); 907 $sql = str_replace('adodb_logsql',$table,$this->createTableSQL); 908 $savelog = $this->conn->LogSQL(false); 909 $ok = $this->conn->Execute($sql); 910 $this->conn->LogSQL($savelog); 911 return ($ok) ? true : false; 912 } 913 914 function DoSQLForm() 915 { 916 917 918 $PHP_SELF = htmlspecialchars($_SERVER['PHP_SELF']); 919 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : ''; 920 921 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows']; 922 else $rows = 3; 923 924 if (isset($_REQUEST['SMALLER'])) { 925 $rows /= 2; 926 if ($rows < 3) $rows = 3; 927 $_SESSION['phplens_sqlrows'] = $rows; 928 } 929 if (isset($_REQUEST['BIGGER'])) { 930 $rows *= 2; 931 $_SESSION['phplens_sqlrows'] = $rows; 932 } 933 934 ?> 935 936 <form method="POST" action="<?php echo $PHP_SELF ?>"> 937 <table><tr> 938 <td> Form size: <input type="submit" value=" < " name="SMALLER"><input type="submit" value=" > > " name="BIGGER"> 939 </td> 940 <td align=right> 941 <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql> 942 </td></tr> 943 <tr> 944 <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea> 945 </td> 946 </tr> 947 </table> 948 </form> 949 950 <?php 951 if (!isset($_REQUEST['sql'])) return; 952 953 $sql = trim($sql); 954 if (substr($sql,strlen($sql)-1) === ';') { 955 $print = true; 956 $sqla = $this->SplitSQL($sql); 957 } else { 958 $print = false; 959 $sqla = array($sql); 960 } 961 foreach($sqla as $sqls) { 962 963 if (!$sqls) continue; 964 965 if ($print) { 966 print "<p>".htmlspecialchars($sqls)."</p>"; 967 flush(); 968 } 969 $savelog = $this->conn->LogSQL(false); 970 $rs = $this->conn->Execute($sqls); 971 $this->conn->LogSQL($savelog); 972 if ($rs && is_object($rs) && !$rs->EOF) { 973 rs2html($rs); 974 while ($rs->NextRecordSet()) { 975 print "<table width=98% bgcolor=#C0C0FF><tr><td> </td></tr></table>"; 976 rs2html($rs); 977 } 978 } else { 979 $e1 = (integer) $this->conn->ErrorNo(); 980 $e2 = $this->conn->ErrorMsg(); 981 if (($e1) || ($e2)) { 982 if (empty($e1)) $e1 = '-1'; // postgresql fix 983 print ' '.$e1.': '.$e2; 984 } else { 985 print "<p>No Recordset returned<br></p>"; 986 } 987 } 988 } // foreach 989 } 990 991 function SplitSQL($sql) 992 { 993 $arr = explode(';',$sql); 994 return $arr; 995 } 996 997 /************************************************************************/ 998 999 /** 1000 * Reorganise multiple table-indices/statistics/.. 1001 * OptimizeMode could be given by last Parameter 1002 * 1003 * @example 1004 * <pre> 1005 * optimizeTables( 'tableA'); 1006 * </pre> 1007 * <pre> 1008 * optimizeTables( 'tableA', 'tableB', 'tableC'); 1009 * </pre> 1010 * <pre> 1011 * optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW); 1012 * </pre> 1013 * 1014 * @param string table name of the table to optimize 1015 * @param int mode optimization-mode 1016 * <code>ADODB_OPT_HIGH</code> for full optimization 1017 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1018 * Default is LOW <code>ADODB_OPT_LOW</code> 1019 * @author Markus Staab 1020 * @return Returns <code>true</code> on success and <code>false</code> on error 1021 */ 1022 function OptimizeTables() 1023 { 1024 $args = func_get_args(); 1025 $numArgs = func_num_args(); 1026 1027 if ( $numArgs == 0) return false; 1028 1029 $mode = ADODB_OPT_LOW; 1030 $lastArg = $args[ $numArgs - 1]; 1031 if ( !is_string($lastArg)) { 1032 $mode = $lastArg; 1033 unset( $args[ $numArgs - 1]); 1034 } 1035 1036 foreach( $args as $table) { 1037 $this->optimizeTable( $table, $mode); 1038 } 1039 } 1040 1041 /** 1042 * Reorganise the table-indices/statistics/.. depending on the given mode. 1043 * Default Implementation throws an error. 1044 * 1045 * @param string table name of the table to optimize 1046 * @param int mode optimization-mode 1047 * <code>ADODB_OPT_HIGH</code> for full optimization 1048 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1049 * Default is LOW <code>ADODB_OPT_LOW</code> 1050 * @author Markus Staab 1051 * @return Returns <code>true</code> on success and <code>false</code> on error 1052 */ 1053 function OptimizeTable( $table, $mode = ADODB_OPT_LOW) 1054 { 1055 ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType)); 1056 return false; 1057 } 1058 1059 /** 1060 * Reorganise current database. 1061 * Default implementation loops over all <code>MetaTables()</code> and 1062 * optimize each using <code>optmizeTable()</code> 1063 * 1064 * @author Markus Staab 1065 * @return Returns <code>true</code> on success and <code>false</code> on error 1066 */ 1067 function optimizeDatabase() 1068 { 1069 $conn = $this->conn; 1070 if ( !$conn) return false; 1071 1072 $tables = $conn->MetaTables( 'TABLES'); 1073 if ( !$tables ) return false; 1074 1075 foreach( $tables as $table) { 1076 if ( !$this->optimizeTable( $table)) { 1077 return false; 1078 } 1079 } 1080 1081 return true; 1082 } 1083 // end hack 1084 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
