[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3  @version   v5.20.3  01-Jan-2016
   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.sourceforge.net
  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()
 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  }