062e2a2fc17ea91a6685bfa65e0c65fb1b93be3b
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / perf / perf-mysql.inc.php
1 <?php
2 /*
3 V4.93 10 Oct 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 ?>