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

   1 <?php
   2 /*
   3 V4.20 22 Feb 2004  (c) 2000-2004 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://php.weblogs.com/
  10
  11   Library for basic performance monitoring and tuning
  12
  13 */
  14
  15 class perf_mysql extends adodb_perf{
  16
  17         var $tablesSQL = 'show table status';
  18
  19         var $createTableSQL = "CREATE TABLE adodb_logsql (
  20                   created datetime NOT NULL,
  21                   sql0 varchar(250) NOT NULL,
  22                   sql1 text NOT NULL,
  23                   params text NOT NULL,
  24                   tracer text NOT NULL,
  25                   timer decimal(16,6) NOT NULL
  26                 )";
  27
  28         var $settings = array(
  29         'Ratios',
  30                 'MyISAM cache hit ratio' => array('RATIO',
  31                         '=GetKeyHitRatio',
  32                         '=WarnCacheRatio'),
  33                 'InnoDB cache hit ratio' => array('RATIO',
  34                         '=GetInnoDBHitRatio',
  35                         '=WarnCacheRatio'),
  36                 'data cache hit ratio' => array('HIDE', # only if called
  37                         '=FindDBHitRatio',
  38                         '=WarnCacheRatio'),
  39                 'sql cache hit ratio' => array('RATIO',
  40                         '=GetQHitRatio',
  41                         ''),
  42         'IO',
  43                 'data reads' => array('IO',
  44                         '=GetReads',
  45                         'Number of selects (Key_reads is not accurate)'),
  46                 'data writes' => array('IO',
  47                         '=GetWrites',
  48                         'Number of inserts/updates/deletes * coef (Key_writes is not accurate)'),
  49
  50         'Data Cache',
  51                 'MyISAM data cache size' => array('DATAC',
  52                         array("show variables", 'key_buffer_size'),
  53                         '' ),
  54                 'BDB data cache size' => array('DATAC',
  55                         array("show variables", 'bdb_cache_size'),
  56                         '' ),
  57                 'InnoDB data cache size' => array('DATAC',
  58                         array("show variables", 'innodb_buffer_pool_size'),
  59                         '' ),
  60         'Memory Usage',
  61                 'read buffer size' => array('CACHE',
  62                         array("show variables", 'read_buffer_size'),
  63                         '(per session)'),
  64                 'sort buffer size' => array('CACHE',
  65                         array("show variables", 'sort_buffer_size'),
  66                         'Size of sort buffer (per session)' ),
  67                 'table cache' => array('CACHE',
  68                         array("show variables", 'table_cache'),
  69                         'Number of tables to keep open'),
  70         'Connections',
  71                 'current connections' => array('SESS',
  72                         array('show status','Threads_connected'),
  73                         ''),
  74                 'max connections' => array( 'SESS',
  75                         array("show variables",'max_connections'),
  76                         ''),
  77
  78                 false
  79         );
  80
  81         function perf_mysql(&$conn)
  82         {
  83                 $this->conn =& $conn;
  84         }
  85
  86         function Explain($sql,$partial=false)
  87         {
  88
  89                 if (strtoupper(substr(trim($sql),0,6)) !== 'SELECT') return '<p>Unable to EXPLAIN non-select statement</p>';
  90                 $save = $this->conn->LogSQL(false);
  91                 if ($partial) {
  92                         $sqlq = $this->conn->qstr($sql.'%');
  93                         $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
  94                         if ($arr) {
  95                                 foreach($arr as $row) {
  96                                         $sql = reset($row);
  97                                         if (crc32($sql) == $partial) break;
  98                                 }
  99                         }
 100                 }
 101                 $sql = str_replace('?',"''",$sql);
 102
 103                 if ($partial) {
 104                         $sqlq = $this->conn->qstr($sql.'%');
 105                         $sql = $this->conn->GetOne("select sql1 from adodb_logsql where sql1 like $sqlq");
 106                 }
 107
 108                 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
 109                 $rs = $this->conn->Execute('EXPLAIN '.$sql);
 110                 $s .= rs2html($rs,false,false,false,false);
 111                 $this->conn->LogSQL($save);
 112                 $s .= $this->Tracer($sql);
 113                 return $s;
 114         }
 115
 116         function Tables()
 117         {
 118                 if (!$this->tablesSQL) return false;
 119
 120                 $rs = $this->conn->Execute($this->tablesSQL);
 121                 if (!$rs) return false;
 122
 123                 $html = rs2html($rs,false,false,false,false);
 124                 return $html;
 125         }
 126
 127         function GetReads()
 128         {
 129         global $ADODB_FETCH_MODE;
 130                 $save = $ADODB_FETCH_MODE;
 131                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 132                 $rs = $this->conn->Execute('show status');
 133                 $ADODB_FETCH_MODE = $save;
 134
 135                 if (!$rs) return 0;
 136                 $val = 0;
 137                 while (!$rs->EOF) {
 138                         switch($rs->fields[0]) {
 139                         case 'Com_select':
 140                                 $val = $rs->fields[1];
 141                                 $rs->Close();
 142                                 return $val;
 143                         }
 144                         $rs->MoveNext();
 145                 }
 146
 147                 $rs->Close();
 148
 149                 return $val;
 150         }
 151
 152         function GetWrites()
 153         {
 154         global $ADODB_FETCH_MODE;
 155                 $save = $ADODB_FETCH_MODE;
 156                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 157                 $rs = $this->conn->Execute('show status');
 158                 $ADODB_FETCH_MODE = $save;
 159
 160                 if (!$rs) return 0;
 161                 $val = 0.0;
 162                 while (!$rs->EOF) {
 163                         switch($rs->fields[0]) {
 164                         case 'Com_insert':
 165                                 $val += $rs->fields[1]; break;
 166                         case 'Com_delete':
 167                                 $val += $rs->fields[1]; break;
 168                         case 'Com_update':
 169                                 $val += $rs->fields[1]/2;
 170                                 $rs->Close();
 171                                 return $val;
 172                         }
 173                         $rs->MoveNext();
 174                 }
 175
 176                 $rs->Close();
 177
 178                 return $val;
 179         }
 180
 181         function FindDBHitRatio()
 182         {
 183                 // first find out type of table
 184                 //$this->conn->debug=1;
 185                 $rs = $this->conn->Execute('show table status');
 186                 if (!$rs) return '';
 187                 $type = strtoupper($rs->fields[1]);
 188                 $rs->Close();
 189                 switch($type){
 190                 case 'MYISAM':
 191                 case 'ISAM':
 192                         return $this->DBParameter('MyISAM cache hit ratio').' (MyISAM)';
 193                 case 'INNODB':
 194                         return $this->DBParameter('InnoDB cache hit ratio').' (InnoDB)';
 195                 default:
 196                         return $type.' not supported';
 197                 }
 198
 199         }
 200
 201         function GetQHitRatio()
 202         {
 203                 //Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached
 204                 $hits = $this->_DBParameter(array("show status","Qcache_hits"));
 205                 $total = $this->_DBParameter(array("show status","Qcache_inserts"));
 206                 $total += $this->_DBParameter(array("show status","Qcache_not_cached"));
 207
 208                 $total += $hits;
 209                 if ($total) return ($hits*100)/$total;
 210                 return 0;
 211         }
 212
 213         /*
 214                 Use session variable to store Hit percentage, because MySQL
 215                 does not remember last value of SHOW INNODB STATUS hit ratio
 216
 217                 # 1st query to SHOW INNODB STATUS
 218                 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 219                 Buffer pool hit rate 1000 / 1000
 220
 221                 # 2nd query to SHOW INNODB STATUS
 222                 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 223                 No buffer pool activity since the last printout
 224         */
 225         function GetInnoDBHitRatio()
 226         {
 227         global $HTTP_SESSION_VARS;
 228
 229                 $rs = $this->conn->Execute('show innodb status');
 230                 if (!$rs || $rs->EOF) return 0;
 231                 $stat = $rs->fields[0];
 232                 $rs->Close();
 233                 $at = strpos($stat,'Buffer pool hit rate');
 234                 $stat = substr($stat,$at,200);
 235                 if (preg_match('!Buffer pool hit rate\s*([0-9]*) / ([0-9]*)!',$stat,$arr)) {
 236                         $val = 100*$arr[1]/$arr[2];
 237                         $HTTP_SESSION_VARS['INNODB_HIT_PCT'] = $val;
 238                         return $val;
 239                 } else {
 240                         if (isset($HTTP_SESSION_VARS['INNODB_HIT_PCT'])) return $HTTP_SESSION_VARS['INNODB_HIT_PCT'];
 241                         return 0;
 242                 }
 243                 return 0;
 244         }
 245
 246         function GetKeyHitRatio()
 247         {
 248                 $hits = $this->_DBParameter(array("show status","Key_read_requests"));
 249                 $reqs = $this->_DBParameter(array("show status","Key_reads"));
 250                 if ($reqs == 0) return 0;
 251
 252                 return ($hits/($reqs+$hits))*100;
 253         }
 254
 255 }
 256 ?>