"TYPO3 4.0.1: typo3_src-4.0.1/typo3/sysext/adodb/adodb/perf/perf-mysql.inc.php Source File", "datetime" => "Sat Dec 2 19:22:26 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

perf-mysql.inc.php

00001 <?php
00002 /* 
00003 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. See License.txt. 
00007   Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   Library for basic performance monitoring and tuning 
00012   
00013 */
00014 
00015 // security - hide paths
00016 if (!defined('ADODB_DIR')) die();
00017 
00018 class perf_mysql extends adodb_perf{
00019         
00020         var $tablesSQL = 'show table status';
00021         
00022         var $createTableSQL = "CREATE TABLE adodb_logsql (
00023                   created datetime NOT NULL,
00024                   sql0 varchar(250) NOT NULL,
00025                   sql1 text NOT NULL,
00026                   params text NOT NULL,
00027                   tracer text NOT NULL,
00028                   timer decimal(16,6) NOT NULL
00029                 )";
00030                 
00031         var $settings = array(
00032         'Ratios',
00033                 'MyISAM cache hit ratio' => array('RATIO',
00034                         '=GetKeyHitRatio',
00035                         '=WarnCacheRatio'),
00036                 'InnoDB cache hit ratio' => array('RATIO',
00037                         '=GetInnoDBHitRatio',
00038                         '=WarnCacheRatio'),
00039                 'data cache hit ratio' => array('HIDE', # only if called
00040                         '=FindDBHitRatio',
00041                         '=WarnCacheRatio'),
00042                 'sql cache hit ratio' => array('RATIO',
00043                         '=GetQHitRatio',
00044                         ''),
00045         'IO',
00046                 'data reads' => array('IO',
00047                         '=GetReads',
00048                         'Number of selects (Key_reads is not accurate)'),
00049                 'data writes' => array('IO',
00050                         '=GetWrites',
00051                         'Number of inserts/updates/deletes * coef (Key_writes is not accurate)'),
00052                 
00053         'Data Cache',
00054                 'MyISAM data cache size' => array('DATAC',
00055                         array("show variables", 'key_buffer_size'),
00056                         '' ),
00057                 'BDB data cache size' => array('DATAC',
00058                         array("show variables", 'bdb_cache_size'),
00059                         '' ),
00060                 'InnoDB data cache size' => array('DATAC',
00061                         array("show variables", 'innodb_buffer_pool_size'),
00062                         '' ),
00063         'Memory Usage',
00064                 'read buffer size' => array('CACHE',
00065                         array("show variables", 'read_buffer_size'),
00066                         '(per session)'),
00067                 'sort buffer size' => array('CACHE',
00068                         array("show variables", 'sort_buffer_size'),
00069                         'Size of sort buffer (per session)' ),
00070                 'table cache' => array('CACHE',
00071                         array("show variables", 'table_cache'),
00072                         'Number of tables to keep open'),
00073         'Connections',  
00074                 'current connections' => array('SESS',
00075                         array('show status','Threads_connected'),
00076                         ''),
00077                 'max connections' => array( 'SESS',
00078                         array("show variables",'max_connections'),
00079                         ''),
00080         
00081                 false
00082         );
00083         
00084         function perf_mysql(&$conn)
00085         {
00086                 $this->conn =& $conn;
00087         }
00088         
00089         function Explain($sql,$partial=false)
00090         {
00091                 
00092                 if (strtoupper(substr(trim($sql),0,6)) !== 'SELECT') return '<p>Unable to EXPLAIN non-select statement</p>';
00093                 $save = $this->conn->LogSQL(false);
00094                 if ($partial) {
00095                         $sqlq = $this->conn->qstr($sql.'%');
00096                         $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
00097                         if ($arr) {
00098                                 foreach($arr as $row) {
00099                                         $sql = reset($row);
00100                                         if (crc32($sql) == $partial) break;
00101                                 }
00102                         }
00103                 }
00104                 $sql = str_replace('?',"''",$sql);
00105                 
00106                 if ($partial) {
00107                         $sqlq = $this->conn->qstr($sql.'%');
00108                         $sql = $this->conn->GetOne("select sql1 from adodb_logsql where sql1 like $sqlq");
00109                 }
00110                 
00111                 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
00112                 $rs = $this->conn->Execute('EXPLAIN '.$sql);
00113                 $s .= rs2html($rs,false,false,false,false);
00114                 $this->conn->LogSQL($save);
00115                 $s .= $this->Tracer($sql);
00116                 return $s;
00117         }
00118         
00119         function Tables()
00120         {
00121                 if (!$this->tablesSQL) return false;
00122                 
00123                 $rs = $this->conn->Execute($this->tablesSQL);
00124                 if (!$rs) return false;
00125                 
00126                 $html = rs2html($rs,false,false,false,false);
00127                 return $html;
00128         }
00129         
00130         function GetReads()
00131         {
00132         global $ADODB_FETCH_MODE;
00133                 $save = $ADODB_FETCH_MODE;
00134                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00135                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00136                 
00137                 $rs = $this->conn->Execute('show status');
00138                 
00139                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00140                 $ADODB_FETCH_MODE = $save;
00141                 
00142                 if (!$rs) return 0;
00143                 $val = 0;
00144                 while (!$rs->EOF) {
00145                         switch($rs->fields[0]) {
00146                         case 'Com_select': 
00147                                 $val = $rs->fields[1];
00148                                 $rs->Close();
00149                                 return $val;
00150                         }
00151                         $rs->MoveNext();
00152                 } 
00153                 
00154                 $rs->Close();
00155                 
00156                 return $val;
00157         }
00158         
00159         function GetWrites()
00160         {
00161         global $ADODB_FETCH_MODE;
00162                 $save = $ADODB_FETCH_MODE;
00163                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00164                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00165                 
00166                 $rs = $this->conn->Execute('show status');
00167                 
00168                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00169                 $ADODB_FETCH_MODE = $save;
00170                 
00171                 if (!$rs) return 0;
00172                 $val = 0.0;
00173                 while (!$rs->EOF) {
00174                         switch($rs->fields[0]) {
00175                         case 'Com_insert': 
00176                                 $val += $rs->fields[1]; break;
00177                         case 'Com_delete': 
00178                                 $val += $rs->fields[1]; break;
00179                         case 'Com_update': 
00180                                 $val += $rs->fields[1]/2;
00181                                 $rs->Close();
00182                                 return $val;
00183                         }
00184                         $rs->MoveNext();
00185                 } 
00186                 
00187                 $rs->Close();
00188                 
00189                 return $val;
00190         }
00191         
00192         function FindDBHitRatio()
00193         {
00194                 // first find out type of table
00195                 //$this->conn->debug=1;
00196                 
00197                 global $ADODB_FETCH_MODE;
00198                 $save = $ADODB_FETCH_MODE;
00199                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00200                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00201                 
00202                 $rs = $this->conn->Execute('show table status');
00203                 
00204                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00205                 $ADODB_FETCH_MODE = $save;
00206                 
00207                 if (!$rs) return '';
00208                 $type = strtoupper($rs->fields[1]);
00209                 $rs->Close();
00210                 switch($type){
00211                 case 'MYISAM':
00212                 case 'ISAM':
00213                         return $this->DBParameter('MyISAM cache hit ratio').' (MyISAM)';
00214                 case 'INNODB':
00215                         return $this->DBParameter('InnoDB cache hit ratio').' (InnoDB)';
00216                 default:
00217                         return $type.' not supported';
00218                 }
00219                 
00220         }
00221         
00222         function GetQHitRatio()
00223         {
00224                 //Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached
00225                 $hits = $this->_DBParameter(array("show status","Qcache_hits"));
00226                 $total = $this->_DBParameter(array("show status","Qcache_inserts"));
00227                 $total += $this->_DBParameter(array("show status","Qcache_not_cached"));
00228                 
00229                 $total += $hits;
00230                 if ($total) return round(($hits*100)/$total,2);
00231                 return 0;
00232         }
00233         
00234         /*
00235                 Use session variable to store Hit percentage, because MySQL
00236                 does not remember last value of SHOW INNODB STATUS hit ratio
00237                 
00238                 # 1st query to SHOW INNODB STATUS
00239                 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
00240                 Buffer pool hit rate 1000 / 1000
00241                 
00242                 # 2nd query to SHOW INNODB STATUS
00243                 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
00244                 No buffer pool activity since the last printout
00245         */
00246         function GetInnoDBHitRatio()
00247         {
00248         global $ADODB_FETCH_MODE;
00249         
00250                 $save = $ADODB_FETCH_MODE;
00251                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00252                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00253                 
00254                 $rs = $this->conn->Execute('show innodb status');
00255                 
00256                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00257                 $ADODB_FETCH_MODE = $save;
00258                 
00259                 if (!$rs || $rs->EOF) return 0;
00260                 $stat = $rs->fields[0];
00261                 $rs->Close();
00262                 $at = strpos($stat,'Buffer pool hit rate');
00263                 $stat = substr($stat,$at,200);
00264                 if (preg_match('!Buffer pool hit rate\s*([0-9]*) / ([0-9]*)!',$stat,$arr)) {
00265                         $val = 100*$arr[1]/$arr[2];
00266                         $_SESSION['INNODB_HIT_PCT'] = $val;
00267                         return round($val,2);
00268                 } else {
00269                         if (isset($_SESSION['INNODB_HIT_PCT'])) return $_SESSION['INNODB_HIT_PCT'];
00270                         return 0;
00271                 }
00272                 return 0;
00273         }
00274         
00275         function GetKeyHitRatio()
00276         {
00277                 $hits = $this->_DBParameter(array("show status","Key_read_requests"));
00278                 $reqs = $this->_DBParameter(array("show status","Key_reads"));
00279                 if ($reqs == 0) return 0;
00280                 
00281                 return round(($hits/($reqs+$hits))*100,2);
00282         }
00283         
00284     // start hack 
00285     var $optimizeTableLow = 'CHECK TABLE %s FAST QUICK';
00286     var $optimizeTableHigh = 'OPTIMIZE TABLE %s';
00287     
00291      function optimizeTable( $table, $mode = ADODB_OPT_LOW) 
00292      {
00293         if ( !is_string( $table)) return false;
00294         
00295         $conn = $this->conn;
00296         if ( !$conn) return false;
00297         
00298         $sql = '';
00299         switch( $mode) {
00300             case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break;
00301             case ADODB_OPT_HIGH : $sql = $this->optimizeTableHigh; break;
00302             default : 
00303             {
00304                 // May dont use __FUNCTION__ constant for BC (__FUNCTION__ Added in PHP 4.3.0)
00305                 ADOConnection::outp( sprintf( "<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, __FUNCTION__, $mode));
00306                 return false;
00307             }
00308         }
00309         $sql = sprintf( $sql, $table);
00310         
00311         return $conn->Execute( $sql) !== false;
00312      }
00313     // end hack 
00314 }
00315 ?>