[Summary view]

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

   1  <?php
   2  /*
   3  @version   v5.20.16  12-Jan-2020
   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 http://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  	function tables($orderby='1')
 122  	 {
 123  	 	 if (!$this->tablesSQL) return false;
 124  
 125  	 	 $rs = $this->conn->Execute($this->tablesSQL);
 126  	 	 if (!$rs) return false;
 127  
 128  	 	 $html = rs2html($rs,false,false,false,false);
 129  	 	 return $html;
 130  	 }
 131  
 132  	function GetReads()
 133  	 {
 134  	 global $ADODB_FETCH_MODE;
 135  	 	 $save = $ADODB_FETCH_MODE;
 136  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 137  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 138  
 139  	 	 $rs = $this->conn->Execute('show status');
 140  
 141  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 142  	 	 $ADODB_FETCH_MODE = $save;
 143  
 144  	 	 if (!$rs) return 0;
 145  	 	 $val = 0;
 146  	 	 while (!$rs->EOF) {
 147  	 	 	 switch($rs->fields[0]) {
 148  	 	 	 case 'Com_select':
 149  	 	 	 	 $val = $rs->fields[1];
 150  	 	 	 	 $rs->Close();
 151  	 	 	 	 return $val;
 152  	 	 	 }
 153  	 	 	 $rs->MoveNext();
 154  	 	 }
 155  
 156  	 	 $rs->Close();
 157  
 158  	 	 return $val;
 159  	 }
 160  
 161  	function GetWrites()
 162  	 {
 163  	 global $ADODB_FETCH_MODE;
 164  	 	 $save = $ADODB_FETCH_MODE;
 165  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 166  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 167  
 168  	 	 $rs = $this->conn->Execute('show status');
 169  
 170  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 171  	 	 $ADODB_FETCH_MODE = $save;
 172  
 173  	 	 if (!$rs) return 0;
 174  	 	 $val = 0.0;
 175  	 	 while (!$rs->EOF) {
 176  	 	 	 switch($rs->fields[0]) {
 177  	 	 	 case 'Com_insert':
 178  	 	 	 	 $val += $rs->fields[1]; break;
 179  	 	 	 case 'Com_delete':
 180  	 	 	 	 $val += $rs->fields[1]; break;
 181  	 	 	 case 'Com_update':
 182  	 	 	 	 $val += $rs->fields[1]/2;
 183  	 	 	 	 $rs->Close();
 184  	 	 	 	 return $val;
 185  	 	 	 }
 186  	 	 	 $rs->MoveNext();
 187  	 	 }
 188  
 189  	 	 $rs->Close();
 190  
 191  	 	 return $val;
 192  	 }
 193  
 194  	function FindDBHitRatio()
 195  	 {
 196  	 	 // first find out type of table
 197  	 	 //$this->conn->debug=1;
 198  
 199  	 	 global $ADODB_FETCH_MODE;
 200  	 	 $save = $ADODB_FETCH_MODE;
 201  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 202  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 203  
 204  	 	 $rs = $this->conn->Execute('show table status');
 205  
 206  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 207  	 	 $ADODB_FETCH_MODE = $save;
 208  
 209  	 	 if (!$rs) return '';
 210  	 	 $type = strtoupper($rs->fields[1]);
 211  	 	 $rs->Close();
 212  	 	 switch($type){
 213  	 	 case 'MYISAM':
 214  	 	 case 'ISAM':
 215  	 	 	 return $this->DBParameter('MyISAM cache hit ratio').' (MyISAM)';
 216  	 	 case 'INNODB':
 217  	 	 	 return $this->DBParameter('InnoDB cache hit ratio').' (InnoDB)';
 218  	 	 default:
 219  	 	 	 return $type.' not supported';
 220  	 	 }
 221  
 222  	 }
 223  
 224  	function GetQHitRatio()
 225  	 {
 226  	 	 //Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached
 227  	 	 $hits = $this->_DBParameter(array("show status","Qcache_hits"));
 228  	 	 $total = $this->_DBParameter(array("show status","Qcache_inserts"));
 229  	 	 $total += $this->_DBParameter(array("show status","Qcache_not_cached"));
 230  
 231  	 	 $total += $hits;
 232  	 	 if ($total) return round(($hits*100)/$total,2);
 233  	 	 return 0;
 234  	 }
 235  
 236  	 /*
 237  	 	 Use session variable to store Hit percentage, because MySQL
 238  	 	 does not remember last value of SHOW INNODB STATUS hit ratio
 239  
 240  	 	 # 1st query to SHOW INNODB STATUS
 241  	 	 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 242  	 	 Buffer pool hit rate 1000 / 1000
 243  
 244  	 	 # 2nd query to SHOW INNODB STATUS
 245  	 	 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 246  	 	 No buffer pool activity since the last printout
 247  	 */
 248  	function GetInnoDBHitRatio()
 249  	 {
 250  	 global $ADODB_FETCH_MODE;
 251  
 252  	 	 $save = $ADODB_FETCH_MODE;
 253  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 254  	 	 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 255  
 256  	 	 $rs = $this->conn->Execute('show engine innodb status');
 257  
 258  	 	 if (isset($savem)) $this->conn->SetFetchMode($savem);
 259  	 	 $ADODB_FETCH_MODE = $save;
 260  
 261  	 	 if (!$rs || $rs->EOF) return 0;
 262  	 	 $stat = $rs->fields[0];
 263  	 	 $rs->Close();
 264  	 	 $at = strpos($stat,'Buffer pool hit rate');
 265  	 	 $stat = substr($stat,$at,200);
 266  	 	 if (preg_match('!Buffer pool hit rate\s*([0-9]*) / ([0-9]*)!',$stat,$arr)) {
 267  	 	 	 $val = 100*$arr[1]/$arr[2];
 268  	 	 	 $_SESSION['INNODB_HIT_PCT'] = $val;
 269  	 	 	 return round($val,2);
 270  	 	 } else {
 271  	 	 	 if (isset($_SESSION['INNODB_HIT_PCT'])) return $_SESSION['INNODB_HIT_PCT'];
 272  	 	 	 return 0;
 273  	 	 }
 274  	 	 return 0;
 275  	 }
 276  
 277  	function GetKeyHitRatio()
 278  	 {
 279  	 	 $hits = $this->_DBParameter(array("show status","Key_read_requests"));
 280  	 	 $reqs = $this->_DBParameter(array("show status","Key_reads"));
 281  	 	 if ($reqs == 0) return 0;
 282  
 283  	 	 return round(($hits/($reqs+$hits))*100,2);
 284  	 }
 285  
 286      // start hack
 287      var $optimizeTableLow = 'CHECK TABLE %s FAST QUICK';
 288      var $optimizeTableHigh = 'OPTIMIZE TABLE %s';
 289  
 290      /**
 291       * @see adodb_perf#optimizeTable
 292       */
 293       function optimizeTable( $table, $mode = ADODB_OPT_LOW)
 294       {
 295          if ( !is_string( $table)) return false;
 296  
 297          $conn = $this->conn;
 298          if ( !$conn) return false;
 299  
 300          $sql = '';
 301          switch( $mode) {
 302              case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break;
 303              case ADODB_OPT_HIGH : $sql = $this->optimizeTableHigh; break;
 304              default :
 305              {
 306                  // May dont use __FUNCTION__ constant for BC (__FUNCTION__ Added in PHP 4.3.0)
 307                  ADOConnection::outp( sprintf( "<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, __FUNCTION__, $mode));
 308                  return false;
 309              }
 310          }
 311          $sql = sprintf( $sql, $table);
 312  
 313          return $conn->Execute( $sql) !== false;
 314       }
 315      // end hack
 316  }