[Summary view]

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  /*
   3  @version   v5.21.0  2021-02-27
   4  @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   5  @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   6    Released under both BSD license and Lesser GPL library license.
   7    Whenever there is any discrepancy between the two licenses,
   8    the BSD license will take precedence. See License.txt.
   9    Set tabs to 4 for best viewing.
  10  
  11    Latest version is available at https://adodb.org/
  12  
  13    Library for basic performance monitoring and tuning
  14  
  15  */
  16  
  17  // security - hide paths
  18  if (!defined('ADODB_DIR')) die();
  19  
  20  class perf_mysql extends adodb_perf{
  21  
  22  	 var $tablesSQL = 'show table status';
  23  
  24  	 var $createTableSQL = "CREATE TABLE adodb_logsql (
  25  	 	   created datetime NOT NULL,
  26  	 	   sql0 varchar(250) NOT NULL,
  27  	 	   sql1 text NOT NULL,
  28  	 	   params text NOT NULL,
  29  	 	   tracer text NOT NULL,
  30  	 	   timer decimal(16,6) NOT NULL
  31  	 	 )";
  32  
  33  	 var $settings = array(
  34  	 'Ratios',
  35  	 	 'MyISAM cache hit ratio' => array('RATIO',
  36  	 	 	 '=GetKeyHitRatio',
  37  	 	 	 '=WarnCacheRatio'),
  38  	 	 'InnoDB cache hit ratio' => array('RATIO',
  39  	 	 	 '=GetInnoDBHitRatio',
  40  	 	 	 '=WarnCacheRatio'),
  41  	 	 'data cache hit ratio' => array('HIDE', # only if called
  42  	 	 	 '=FindDBHitRatio',
  43  	 	 	 '=WarnCacheRatio'),
  44  	 	 'sql cache hit ratio' => array('RATIO',
  45  	 	 	 '=GetQHitRatio',
  46  	 	 	 ''),
  47  	 'IO',
  48  	 	 'data reads' => array('IO',
  49  	 	 	 '=GetReads',
  50  	 	 	 'Number of selects (Key_reads is not accurate)'),
  51  	 	 'data writes' => array('IO',
  52  	 	 	 '=GetWrites',
  53  	 	 	 'Number of inserts/updates/deletes * coef (Key_writes is not accurate)'),
  54  
  55  	 'Data Cache',
  56  	 	 'MyISAM data cache size' => array('DATAC',
  57  	 	 	 array("show variables", 'key_buffer_size'),
  58  	 	 	 '' ),
  59  	 	 'BDB data cache size' => array('DATAC',
  60  	 	 	 array("show variables", 'bdb_cache_size'),
  61  	 	 	 '' ),
  62  	 	 'InnoDB data cache size' => array('DATAC',
  63  	 	 	 array("show variables", 'innodb_buffer_pool_size'),
  64  	 	 	 '' ),
  65  	 'Memory Usage',
  66  	 	 'read buffer size' => array('CACHE',
  67  	 	 	 array("show variables", 'read_buffer_size'),
  68  	 	 	 '(per session)'),
  69  	 	 'sort buffer size' => array('CACHE',
  70  	 	 	 array("show variables", 'sort_buffer_size'),
  71  	 	 	 'Size of sort buffer (per session)' ),
  72  	 	 'table cache' => array('CACHE',
  73  	 	 	 array("show variables", 'table_cache'),
  74  	 	 	 'Number of tables to keep open'),
  75  	 'Connections',
  76  	 	 'current connections' => array('SESS',
  77  	 	 	 array('show status','Threads_connected'),
  78  	 	 	 ''),
  79  	 	 'max connections' => array( 'SESS',
  80  	 	 	 array("show variables",'max_connections'),
  81  	 	 	 ''),
  82  
  83  	 	 false
  84  	 );
  85  
  86  	function __construct(&$conn)
  87  	 {
  88  	 	 $this->conn = $conn;
  89  	 }
  90  
  91  	function Explain($sql,$partial=false)
  92  	 {
  93  
  94  	 	 if (strtoupper(substr(trim($sql),0,6)) !== 'SELECT') return '<p>Unable to EXPLAIN non-select statement</p>';
  95  	 	 $save = $this->conn->LogSQL(false);
  96  	 	 if ($partial) {
  97  	 	 	 $sqlq = $this->conn->qstr($sql.'%');
  98  	 	 	 $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
  99  	 	 	 if ($arr) {
 100  	 	 	 	 foreach($arr as $row) {
 101  	 	 	 	 	 $sql = reset($row);
 102  	 	 	 	 	 if (crc32($sql) == $partial) break;
 103  	 	 	 	 }
 104  	 	 	 }
 105  	 	 }
 106  	 	 $sql = str_replace('?',"''",$sql);
 107  
 108  	 	 if ($partial) {
 109  	 	 	 $sqlq = $this->conn->qstr($sql.'%');
 110  	 	 	 $sql = $this->conn->GetOne("select sql1 from adodb_logsql where sql1 like $sqlq");
 111  	 	 }
 112  
 113  	 	 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
 114  	 	 $rs = $this->conn->Execute('EXPLAIN '.$sql);
 115  	 	 $s .= rs2html($rs,false,false,false,false);
 116  	 	 $this->conn->LogSQL($save);
 117  	 	 $s .= $this->Tracer($sql);
 118  	 	 return $s;
 119  	 }
 120  
 121  	 /**
 122  	  * Returns a list of table statuses.
 123  	  * 
 124  	  * @param  string  $orderby  Unused (compatibility with parent method)
 125  	  * @return string A formatted set of recordsets
 126  	  */
 127  	function tables($orderby='1')
 128  	 {
 129  	 	 if (!$this->tablesSQL) return false;
 130  
 131  	 	 $rs = $this->conn->Execute($this->tablesSQL);
 132  	 	 if (!$rs) return false;
 133  
 134  	 	 $html = rs2html($rs,false,false,false,false);
 135  	 	 return $html;
 136  	 }
 137  
 138  	function GetReads()
 139  	 {
 140  	 global $ADODB_FETCH_MODE;
 141  	 	 $save = $ADODB_FETCH_MODE;
 142  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 143  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 144  
 145  	 	 $rs = $this->conn->Execute('show status');
 146  
 147  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 148  	 	 $ADODB_FETCH_MODE = $save;
 149  
 150  	 	 if (!$rs) return 0;
 151  	 	 $val = 0;
 152  	 	 while (!$rs->EOF) {
 153  	 	 	 switch($rs->fields[0]) {
 154  	 	 	 case 'Com_select':
 155  	 	 	 	 $val = $rs->fields[1];
 156  	 	 	 	 $rs->Close();
 157  	 	 	 	 return $val;
 158  	 	 	 }
 159  	 	 	 $rs->MoveNext();
 160  	 	 }
 161  
 162  	 	 $rs->Close();
 163  
 164  	 	 return $val;
 165  	 }
 166  
 167  	function GetWrites()
 168  	 {
 169  	 global $ADODB_FETCH_MODE;
 170  	 	 $save = $ADODB_FETCH_MODE;
 171  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 172  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 173  
 174  	 	 $rs = $this->conn->Execute('show status');
 175  
 176  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 177  	 	 $ADODB_FETCH_MODE = $save;
 178  
 179  	 	 if (!$rs) return 0;
 180  	 	 $val = 0.0;
 181  	 	 while (!$rs->EOF) {
 182  	 	 	 switch($rs->fields[0]) {
 183  	 	 	 case 'Com_insert':
 184  	 	 	 	 $val += $rs->fields[1]; break;
 185  	 	 	 case 'Com_delete':
 186  	 	 	 	 $val += $rs->fields[1]; break;
 187  	 	 	 case 'Com_update':
 188  	 	 	 	 $val += $rs->fields[1]/2;
 189  	 	 	 	 $rs->Close();
 190  	 	 	 	 return $val;
 191  	 	 	 }
 192  	 	 	 $rs->MoveNext();
 193  	 	 }
 194  
 195  	 	 $rs->Close();
 196  
 197  	 	 return $val;
 198  	 }
 199  
 200  	function FindDBHitRatio()
 201  	 {
 202  	 	 // first find out type of table
 203  	 	 //$this->conn->debug=1;
 204  
 205  	 	 global $ADODB_FETCH_MODE;
 206  	 	 $save = $ADODB_FETCH_MODE;
 207  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 208  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 209  
 210  	 	 $rs = $this->conn->Execute('show table status');
 211  
 212  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 213  	 	 $ADODB_FETCH_MODE = $save;
 214  
 215  	 	 if (!$rs) return '';
 216  	 	 $type = strtoupper($rs->fields[1]);
 217  	 	 $rs->Close();
 218  	 	 switch($type){
 219  	 	 case 'MYISAM':
 220  	 	 case 'ISAM':
 221  	 	 	 return $this->DBParameter('MyISAM cache hit ratio').' (MyISAM)';
 222  	 	 case 'INNODB':
 223  	 	 	 return $this->DBParameter('InnoDB cache hit ratio').' (InnoDB)';
 224  	 	 default:
 225  	 	 	 return $type.' not supported';
 226  	 	 }
 227  
 228  	 }
 229  
 230  	function GetQHitRatio()
 231  	 {
 232  	 	 //Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached
 233  	 	 $hits = $this->_DBParameter(array("show status","Qcache_hits"));
 234  	 	 $total = $this->_DBParameter(array("show status","Qcache_inserts"));
 235  	 	 $total += $this->_DBParameter(array("show status","Qcache_not_cached"));
 236  
 237  	 	 $total += $hits;
 238  	 	 if ($total) return round(($hits*100)/$total,2);
 239  	 	 return 0;
 240  	 }
 241  
 242  	 /*
 243  	 	 Use session variable to store Hit percentage, because MySQL
 244  	 	 does not remember last value of SHOW INNODB STATUS hit ratio
 245  
 246  	 	 # 1st query to SHOW INNODB STATUS
 247  	 	 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 248  	 	 Buffer pool hit rate 1000 / 1000
 249  
 250  	 	 # 2nd query to SHOW INNODB STATUS
 251  	 	 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 252  	 	 No buffer pool activity since the last printout
 253  	 */
 254  	function GetInnoDBHitRatio()
 255  	 {
 256  	 global $ADODB_FETCH_MODE;
 257  
 258  	 	 $save = $ADODB_FETCH_MODE;
 259  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 260  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 261  
 262  	 	 $rs = $this->conn->Execute('show engine innodb status');
 263  
 264  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 265  	 	 $ADODB_FETCH_MODE = $save;
 266  
 267  	 	 if (!$rs || $rs->EOF) return 0;
 268  	 	 $stat = $rs->fields[0];
 269  	 	 $rs->Close();
 270  	 	 $at = strpos($stat,'Buffer pool hit rate');
 271  	 	 $stat = substr($stat,$at,200);
 272  	 	 if (preg_match('!Buffer pool hit rate\s*([0-9]*) / ([0-9]*)!',$stat,$arr)) {
 273  	 	 	 $val = 100*$arr[1]/$arr[2];
 274  	 	 	 $_SESSION['INNODB_HIT_PCT'] = $val;
 275  	 	 	 return round($val,2);
 276  	 	 } else {
 277  	 	 	 if (isset($_SESSION['INNODB_HIT_PCT'])) return $_SESSION['INNODB_HIT_PCT'];
 278  	 	 	 return 0;
 279  	 	 }
 280  	 	 return 0;
 281  	 }
 282  
 283  	function GetKeyHitRatio()
 284  	 {
 285  	 	 $hits = $this->_DBParameter(array("show status","Key_read_requests"));
 286  	 	 $reqs = $this->_DBParameter(array("show status","Key_reads"));
 287  	 	 if ($reqs == 0) return 0;
 288  
 289  	 	 return round(($hits/($reqs+$hits))*100,2);
 290  	 }
 291  
 292      // start hack
 293      var $optimizeTableLow = 'CHECK TABLE %s FAST QUICK';
 294      var $optimizeTableHigh = 'OPTIMIZE TABLE %s';
 295  
 296      /**
 297       * @see adodb_perf#optimizeTable
 298       */
 299       function optimizeTable( $table, $mode = ADODB_OPT_LOW)
 300       {
 301          if ( !is_string( $table)) return false;
 302  
 303          $conn = $this->conn;
 304          if ( !$conn) return false;
 305  
 306          $sql = '';
 307          switch( $mode) {
 308              case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break;
 309              case ADODB_OPT_HIGH : $sql = $this->optimizeTableHigh; break;
 310              default :
 311                  // May don't use __FUNCTION__ constant for BC (__FUNCTION__ Added in PHP 4.3.0)
 312                  ADOConnection::outp( sprintf( "<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, __FUNCTION__, $mode));
 313                  return false;
 314          }
 315          $sql = sprintf( $sql, $table);
 316  
 317          return $conn->Execute( $sql) !== false;
 318       }
 319      // end hack
 320  }