[Sommaire] [Imprimer]

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