lib/adodb/perf/perf-mysql.inc.php

   1 <?php
   2 /*
   3 V5.19  23-Apr-2014  (c) 2000-2014 John Lim (jlim#natsoft.com). 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 engine 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 }