TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
perf-mysql.inc.php
Go to the documentation of this file.
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 
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 }
perf_mysql\$createTableSQL
$createTableSQL
Definition: perf-mysql.inc.php:22
perf_mysql\GetWrites
GetWrites()
Definition: perf-mysql.inc.php:159
adodb_perf\Tracer
Tracer($sql)
Definition: adodb-perf.inc.php:382
$sql
$sql
Definition: server.php:82
ADODB_OPT_LOW
const ADODB_OPT_LOW
Definition: adodb-perf.inc.php:23
perf_mysql\GetReads
GetReads()
Definition: perf-mysql.inc.php:130
adodb_perf\$conn
$conn
Definition: adodb-perf.inc.php:218
perf_mysql\$optimizeTableLow
$optimizeTableLow
Definition: perf-mysql.inc.php:285
$rs
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:92
perf_mysql\$optimizeTableHigh
$optimizeTableHigh
Definition: perf-mysql.inc.php:286
perf_mysql\Tables
Tables()
Definition: perf-mysql.inc.php:119
ADODB_OPT_HIGH
const ADODB_OPT_HIGH
Definition: adodb-perf.inc.php:22
perf_mysql\perf_mysql
perf_mysql(&$conn)
Definition: perf-mysql.inc.php:84
perf_mysql\GetInnoDBHitRatio
GetInnoDBHitRatio()
Definition: perf-mysql.inc.php:246
perf_mysql
Definition: perf-mysql.inc.php:18
die
die
Definition: index.php:6
adodb_perf\DBParameter
DBParameter($param)
Definition: adodb-perf.inc.php:583
perf_mysql\$settings
$settings
Definition: perf-mysql.inc.php:31
perf_mysql\Explain
Explain($sql, $partial=false)
Definition: perf-mysql.inc.php:89
perf_mysql\GetKeyHitRatio
GetKeyHitRatio()
Definition: perf-mysql.inc.php:275
perf_mysql\GetQHitRatio
GetQHitRatio()
Definition: perf-mysql.inc.php:222
adodb_perf\$table
$table
Definition: adodb-perf.inc.php:220
perf_mysql\FindDBHitRatio
FindDBHitRatio()
Definition: perf-mysql.inc.php:192
perf_mysql\optimizeTable
optimizeTable( $table, $mode=ADODB_OPT_LOW)
Definition: perf-mysql.inc.php:291
rs2html
rs2html(&$rs, $ztabhtml=false, $zheaderarray=false, $htmlspecialchars=true, $echo=true)
Definition: tohtml.inc.php:41
adodb_perf\_DBParameter
_DBParameter($sql)
Definition: adodb-perf.inc.php:605
perf_mysql\$tablesSQL
$tablesSQL
Definition: perf-mysql.inc.php:20
adodb_perf
Definition: adodb-perf.inc.php:217