lib/adodb/adodb-perf.inc.php

   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]).' &nbsp; '.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>&nbsp;</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> &nbsp; <a href=?do=viewsql><b>View SQL</b></a>
 724          &nbsp; <a href=?do=tables><b>View Tables</b></a> &nbsp; <a href=?do=poll><b>Poll Stats</b></a>",
 725          $allowsql ? ' &nbsp; <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>&nbsp; <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 "&nbsp; <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> &nbsp;</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 = ' &nbsp; ';
 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 = '&nbsp;';
 875                         if (strlen($val)==0) $val = '&nbsp;';
 876                         if ($cli) {
 877                                 $html  .= str_replace('&nbsp;','',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=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " 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>&nbsp;</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 ' &nbsp; '.$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 bool true on success, false 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 bool true on success, false 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 bool true on success, false 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 }