[Summary view]

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

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