1 | <?php |
---|
2 | /* |
---|
3 | V4.51 29 July 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://adodb.sourceforge.net |
---|
10 | |
---|
11 | Library for basic performance monitoring and tuning. |
---|
12 | |
---|
13 | My apologies if you see code mixed with presentation. The presentation suits |
---|
14 | my needs. If you want to separate code from presentation, be my guest. Patches |
---|
15 | are welcome. |
---|
16 | |
---|
17 | */ |
---|
18 | |
---|
19 | if (!defined(ADODB_DIR)) include_once(dirname(__FILE__).'/adodb.inc.php'); |
---|
20 | include_once(ADODB_DIR.'/tohtml.inc.php'); |
---|
21 | |
---|
22 | |
---|
23 | // avoids localization problems where , is used instead of . |
---|
24 | function adodb_round($n,$prec) |
---|
25 | { |
---|
26 | return number_format($n, $prec, '.', ''); |
---|
27 | } |
---|
28 | |
---|
29 | /* return microtime value as a float */ |
---|
30 | function adodb_microtime() |
---|
31 | { |
---|
32 | $t = microtime(); |
---|
33 | $t = explode(' ',$t); |
---|
34 | return (float)$t[1]+ (float)$t[0]; |
---|
35 | } |
---|
36 | |
---|
37 | /* sql code timing */ |
---|
38 | function& adodb_log_sql(&$conn,$sql,$inputarr) |
---|
39 | { |
---|
40 | global $HTTP_SERVER_VARS; |
---|
41 | |
---|
42 | $perf_table = adodb_perf::table(); |
---|
43 | $conn->fnExecute = false; |
---|
44 | $t0 = microtime(); |
---|
45 | $rs =& $conn->Execute($sql,$inputarr); |
---|
46 | $t1 = microtime(); |
---|
47 | |
---|
48 | if (!empty($conn->_logsql)) { |
---|
49 | $conn->_logsql = false; // disable logsql error simulation |
---|
50 | $dbT = $conn->databaseType; |
---|
51 | |
---|
52 | $a0 = split(' ',$t0); |
---|
53 | $a0 = (float)$a0[1]+(float)$a0[0]; |
---|
54 | |
---|
55 | $a1 = split(' ',$t1); |
---|
56 | $a1 = (float)$a1[1]+(float)$a1[0]; |
---|
57 | |
---|
58 | $time = $a1 - $a0; |
---|
59 | |
---|
60 | if (!$rs) { |
---|
61 | $errM = $conn->ErrorMsg(); |
---|
62 | $errN = $conn->ErrorNo(); |
---|
63 | $conn->lastInsID = 0; |
---|
64 | $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250); |
---|
65 | } else { |
---|
66 | $tracer = ''; |
---|
67 | $errM = ''; |
---|
68 | $errN = 0; |
---|
69 | $dbg = $conn->debug; |
---|
70 | $conn->debug = false; |
---|
71 | if (!is_object($rs) || $rs->dataProvider == 'empty') |
---|
72 | $conn->_affected = $conn->affected_rows(true); |
---|
73 | $conn->lastInsID = @$conn->Insert_ID(); |
---|
74 | $conn->debug = $dbg; |
---|
75 | } |
---|
76 | if (isset($HTTP_SERVER_VARS['HTTP_HOST'])) { |
---|
77 | $tracer .= '<br>'.$HTTP_SERVER_VARS['HTTP_HOST']; |
---|
78 | if (isset($HTTP_SERVER_VARS['PHP_SELF'])) $tracer .= $HTTP_SERVER_VARS['PHP_SELF']; |
---|
79 | } else |
---|
80 | if (isset($HTTP_SERVER_VARS['PHP_SELF'])) $tracer .= '<br>'.$HTTP_SERVER_VARS['PHP_SELF']; |
---|
81 | //$tracer .= (string) adodb_backtrace(false); |
---|
82 | |
---|
83 | $tracer = substr($tracer,0,500); |
---|
84 | |
---|
85 | if (is_array($inputarr)) { |
---|
86 | if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr); |
---|
87 | else { |
---|
88 | $params = ''; |
---|
89 | $params = implode(', ',$inputarr); |
---|
90 | if (strlen($params) >= 3000) $params = substr($params, 0, 3000); |
---|
91 | } |
---|
92 | } else { |
---|
93 | $params = ''; |
---|
94 | } |
---|
95 | |
---|
96 | if (is_array($sql)) $sql = $sql[0]; |
---|
97 | $arr = array('b'=>trim(substr($sql,0,230)), |
---|
98 | 'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6)); |
---|
99 | //var_dump($arr); |
---|
100 | $saved = $conn->debug; |
---|
101 | $conn->debug = 0; |
---|
102 | |
---|
103 | if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') { |
---|
104 | $isql = "insert into $perf_table values($conn->sysTimeStamp,:b,:c,:d,:e,:f)"; |
---|
105 | } else if ($dbT == 'odbc_mssql' || $dbT == 'informix') { |
---|
106 | $timer = $arr['f']; |
---|
107 | if ($dbT == 'informix') $sql2 = substr($sql2,0,230); |
---|
108 | |
---|
109 | $sql1 = $conn->qstr($arr['b']); |
---|
110 | $sql2 = $conn->qstr($arr['c']); |
---|
111 | $params = $conn->qstr($arr['d']); |
---|
112 | $tracer = $conn->qstr($arr['e']); |
---|
113 | |
---|
114 | $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($conn->sysTimeStamp,$sql1,$sql2,$params,$tracer,$timer)"; |
---|
115 | if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql); |
---|
116 | $arr = false; |
---|
117 | } else { |
---|
118 | $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $conn->sysTimeStamp,?,?,?,?,?)"; |
---|
119 | } |
---|
120 | $ok = $conn->Execute($isql,$arr); |
---|
121 | $conn->debug = $saved; |
---|
122 | |
---|
123 | if ($ok) { |
---|
124 | $conn->_logsql = true; |
---|
125 | } else { |
---|
126 | $err2 = $conn->ErrorMsg(); |
---|
127 | $conn->_logsql = true; // enable logsql error simulation |
---|
128 | $perf =& NewPerfMonitor($conn); |
---|
129 | if ($perf) { |
---|
130 | if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr); |
---|
131 | } else { |
---|
132 | $ok = $conn->Execute("create table $perf_table ( |
---|
133 | created varchar(50), |
---|
134 | sql0 varchar(250), |
---|
135 | sql1 varchar(4000), |
---|
136 | params varchar(3000), |
---|
137 | tracer varchar(500), |
---|
138 | timer decimal(16,6))"); |
---|
139 | } |
---|
140 | if (!$ok) { |
---|
141 | ADOConnection::outp( "<b>LOGSQL Insert Failed</b>: $isql<br>$err2</br>"); |
---|
142 | $conn->_logsql = false; |
---|
143 | } |
---|
144 | } |
---|
145 | $conn->_errorMsg = $errM; |
---|
146 | $conn->_errorCode = $errN; |
---|
147 | } |
---|
148 | $conn->fnExecute = 'adodb_log_sql'; |
---|
149 | return $rs; |
---|
150 | } |
---|
151 | |
---|
152 | |
---|
153 | /* |
---|
154 | The settings data structure is an associative array that database parameter per element. |
---|
155 | |
---|
156 | Each database parameter element in the array is itself an array consisting of: |
---|
157 | |
---|
158 | 0: category code, used to group related db parameters |
---|
159 | 1: either |
---|
160 | a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'", |
---|
161 | b. array holding sql string and field to look for, e.g. array('show variables','table_cache'), |
---|
162 | c. a string prefixed by =, then a PHP method of the class is invoked, |
---|
163 | e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue', |
---|
164 | 2: description of the database parameter |
---|
165 | */ |
---|
166 | |
---|
167 | class adodb_perf { |
---|
168 | var $conn; |
---|
169 | var $color = '#F0F0F0'; |
---|
170 | var $table = '<table border=1 bgcolor=white>'; |
---|
171 | var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>'; |
---|
172 | var $warnRatio = 90; |
---|
173 | var $tablesSQL = false; |
---|
174 | var $cliFormat = "%32s => %s \r\n"; |
---|
175 | var $sql1 = 'sql1'; // used for casting sql1 to text for mssql |
---|
176 | var $explain = true; |
---|
177 | var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>"; |
---|
178 | var $createTableSQL = false; |
---|
179 | var $maxLength = 2000; |
---|
180 | |
---|
181 | // Sets the tablename to be used |
---|
182 | function table($newtable = false) |
---|
183 | { |
---|
184 | static $_table; |
---|
185 | |
---|
186 | if (!empty($newtable)) $_table = $newtable; |
---|
187 | if (empty($_table)) $_table = 'adodb_logsql'; |
---|
188 | return $_table; |
---|
189 | } |
---|
190 | |
---|
191 | // returns array with info to calculate CPU Load |
---|
192 | function _CPULoad() |
---|
193 | { |
---|
194 | /* |
---|
195 | |
---|
196 | cpu 524152 2662 2515228 336057010 |
---|
197 | cpu0 264339 1408 1257951 168025827 |
---|
198 | cpu1 259813 1254 1257277 168031181 |
---|
199 | page 622307 25475680 |
---|
200 | swap 24 1891 |
---|
201 | intr 890153570 868093576 6 0 4 4 0 6 1 2 0 0 0 124 0 8098760 2 13961053 0 0 0 0 0 0 0 0 0 0 0 0 0 16 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 |
---|
202 | disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320) |
---|
203 | ctxt 66155838 |
---|
204 | btime 1062315585 |
---|
205 | processes 69293 |
---|
206 | |
---|
207 | */ |
---|
208 | // Algorithm is taken from |
---|
209 | // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/example__obtaining_raw_performance_data.asp |
---|
210 | if (strncmp(PHP_OS,'WIN',3)==0) { |
---|
211 | @$c = new COM("WinMgmts:{impersonationLevel=impersonate}!Win32_PerfRawData_PerfOS_Processor.Name='_Total'"); |
---|
212 | if (!$c) return false; |
---|
213 | |
---|
214 | $info[0] = $c->PercentProcessorTime; |
---|
215 | $info[1] = 0; |
---|
216 | $info[2] = 0; |
---|
217 | $info[3] = $c->TimeStamp_Sys100NS; |
---|
218 | //print_r($info); |
---|
219 | return $info; |
---|
220 | } |
---|
221 | |
---|
222 | // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com) |
---|
223 | $statfile = '/proc/stat'; |
---|
224 | if (!file_exists($statfile)) return false; |
---|
225 | |
---|
226 | $fd = fopen($statfile,"r"); |
---|
227 | if (!$fd) return false; |
---|
228 | |
---|
229 | $statinfo = explode("\n",fgets($fd, 1024)); |
---|
230 | fclose($fd); |
---|
231 | foreach($statinfo as $line) { |
---|
232 | $info = explode(" ",$line); |
---|
233 | if($info[0]=="cpu") { |
---|
234 | array_shift($info); // pop off "cpu" |
---|
235 | if(!$info[0]) array_shift($info); // pop off blank space (if any) |
---|
236 | return $info; |
---|
237 | } |
---|
238 | } |
---|
239 | |
---|
240 | return false; |
---|
241 | |
---|
242 | } |
---|
243 | |
---|
244 | /* NOT IMPLEMENTED */ |
---|
245 | function MemInfo() |
---|
246 | { |
---|
247 | /* |
---|
248 | |
---|
249 | total: used: free: shared: buffers: cached: |
---|
250 | Mem: 1055289344 917299200 137990144 0 165437440 599773184 |
---|
251 | Swap: 2146775040 11055104 2135719936 |
---|
252 | MemTotal: 1030556 kB |
---|
253 | MemFree: 134756 kB |
---|
254 | MemShared: 0 kB |
---|
255 | Buffers: 161560 kB |
---|
256 | Cached: 581384 kB |
---|
257 | SwapCached: 4332 kB |
---|
258 | Active: 494468 kB |
---|
259 | Inact_dirty: 322856 kB |
---|
260 | Inact_clean: 24256 kB |
---|
261 | Inact_target: 168316 kB |
---|
262 | HighTotal: 131064 kB |
---|
263 | HighFree: 1024 kB |
---|
264 | LowTotal: 899492 kB |
---|
265 | LowFree: 133732 kB |
---|
266 | SwapTotal: 2096460 kB |
---|
267 | SwapFree: 2085664 kB |
---|
268 | Committed_AS: 348732 kB |
---|
269 | */ |
---|
270 | } |
---|
271 | |
---|
272 | |
---|
273 | /* |
---|
274 | Remember that this is client load, not db server load! |
---|
275 | */ |
---|
276 | var $_lastLoad; |
---|
277 | function CPULoad() |
---|
278 | { |
---|
279 | $info = $this->_CPULoad(); |
---|
280 | if (!$info) return false; |
---|
281 | |
---|
282 | if (empty($this->_lastLoad)) { |
---|
283 | sleep(1); |
---|
284 | $this->_lastLoad = $info; |
---|
285 | $info = $this->_CPULoad(); |
---|
286 | } |
---|
287 | |
---|
288 | $last = $this->_lastLoad; |
---|
289 | $this->_lastLoad = $info; |
---|
290 | |
---|
291 | $d_user = $info[0] - $last[0]; |
---|
292 | $d_nice = $info[1] - $last[1]; |
---|
293 | $d_system = $info[2] - $last[2]; |
---|
294 | $d_idle = $info[3] - $last[3]; |
---|
295 | |
---|
296 | //printf("Delta - User: %f Nice: %f System: %f Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle); |
---|
297 | |
---|
298 | if (strncmp(PHP_OS,'WIN',3)==0) { |
---|
299 | if ($d_idle < 1) $d_idle = 1; |
---|
300 | return 100*(1-$d_user/$d_idle); |
---|
301 | }else { |
---|
302 | $total=$d_user+$d_nice+$d_system+$d_idle; |
---|
303 | if ($total<1) $total=1; |
---|
304 | return 100*($d_user+$d_nice+$d_system)/$total; |
---|
305 | } |
---|
306 | } |
---|
307 | |
---|
308 | function Tracer($sql) |
---|
309 | { |
---|
310 | $perf_table = adodb_perf::table(); |
---|
311 | $saveE = $this->conn->fnExecute; |
---|
312 | $this->conn->fnExecute = false; |
---|
313 | |
---|
314 | $sqlq = $this->conn->qstr($sql); |
---|
315 | $arr = $this->conn->GetArray( |
---|
316 | "select count(*),tracer |
---|
317 | from $perf_table where sql1=$sqlq |
---|
318 | group by tracer |
---|
319 | order by 1 desc"); |
---|
320 | $s = ''; |
---|
321 | if ($arr) { |
---|
322 | $s .= '<h3>Scripts Affected</h3>'; |
---|
323 | foreach($arr as $k) { |
---|
324 | $s .= sprintf("%4d",$k[0]).' '.strip_tags($k[1]).'<br>'; |
---|
325 | } |
---|
326 | } |
---|
327 | $this->conn->fnExecute = $saveE; |
---|
328 | return $s; |
---|
329 | } |
---|
330 | |
---|
331 | /* |
---|
332 | Explain Plan for $sql. |
---|
333 | If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the |
---|
334 | actual sql. |
---|
335 | */ |
---|
336 | function Explain($sql,$partial=false) |
---|
337 | { |
---|
338 | return false; |
---|
339 | } |
---|
340 | |
---|
341 | function InvalidSQL($numsql = 10) |
---|
342 | { |
---|
343 | global $HTTP_GET_VARS; |
---|
344 | |
---|
345 | if (isset($HTTP_GET_VARS['sql'])) return; |
---|
346 | $s = '<h3>Invalid SQL</h3>'; |
---|
347 | $saveE = $this->conn->fnExecute; |
---|
348 | $this->conn->fnExecute = false; |
---|
349 | $perf_table = adodb_perf::table(); |
---|
350 | $rs =& $this->conn->SelectLimit("select distinct count(*),sql1,tracer as error_msg from $perf_table where tracer like 'ERROR:%' group by sql1,tracer order by 1 desc",$numsql);//,$numsql); |
---|
351 | $this->conn->fnExecute = $saveE; |
---|
352 | if ($rs) { |
---|
353 | $s .= rs2html($rs,false,false,false,false); |
---|
354 | } else |
---|
355 | return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; |
---|
356 | |
---|
357 | return $s; |
---|
358 | } |
---|
359 | |
---|
360 | |
---|
361 | /* |
---|
362 | This script identifies the longest running SQL |
---|
363 | */ |
---|
364 | function _SuspiciousSQL($numsql = 10) |
---|
365 | { |
---|
366 | global $ADODB_FETCH_MODE,$HTTP_GET_VARS; |
---|
367 | |
---|
368 | $perf_table = adodb_perf::table(); |
---|
369 | $saveE = $this->conn->fnExecute; |
---|
370 | $this->conn->fnExecute = false; |
---|
371 | |
---|
372 | if (isset($HTTP_GET_VARS['exps']) && isset($HTTP_GET_VARS['sql'])) { |
---|
373 | $partial = !empty($HTTP_GET_VARS['part']); |
---|
374 | echo "<a name=explain></a>".$this->Explain($HTTP_GET_VARS['sql'],$partial)."\n"; |
---|
375 | } |
---|
376 | |
---|
377 | if (isset($HTTP_GET_VARS['sql'])) return; |
---|
378 | $sql1 = $this->sql1; |
---|
379 | |
---|
380 | $save = $ADODB_FETCH_MODE; |
---|
381 | $ADODB_FETCH_MODE = ADODB_FETCH_NUM; |
---|
382 | //$this->conn->debug=1; |
---|
383 | $rs =& $this->conn->SelectLimit( |
---|
384 | "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer |
---|
385 | from $perf_table |
---|
386 | where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') |
---|
387 | and (tracer is null or tracer not like 'ERROR:%') |
---|
388 | group by sql1 |
---|
389 | order by 1 desc",$numsql); |
---|
390 | $ADODB_FETCH_MODE = $save; |
---|
391 | $this->conn->fnExecute = $saveE; |
---|
392 | |
---|
393 | if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; |
---|
394 | $s = "<h3>Suspicious SQL</h3> |
---|
395 | <font size=1>The following SQL have high average execution times</font><br> |
---|
396 | <table border=1 bgcolor=white><tr><td><b>Avg Time</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n"; |
---|
397 | $max = $this->maxLength; |
---|
398 | while (!$rs->EOF) { |
---|
399 | $sql = $rs->fields[1]; |
---|
400 | $raw = urlencode($sql); |
---|
401 | if (strlen($raw)>$max-100) { |
---|
402 | $sql2 = substr($sql,0,$max-500); |
---|
403 | $raw = urlencode($sql2).'&part='.crc32($sql); |
---|
404 | } |
---|
405 | $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">"; |
---|
406 | $suffix = "</a>"; |
---|
407 | if ($this->explain == false || strlen($prefix)>$max) { |
---|
408 | $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>'; |
---|
409 | $prefix = ''; |
---|
410 | } |
---|
411 | $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". |
---|
412 | "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; |
---|
413 | $rs->MoveNext(); |
---|
414 | } |
---|
415 | return $s."</table>"; |
---|
416 | |
---|
417 | } |
---|
418 | |
---|
419 | function CheckMemory() |
---|
420 | { |
---|
421 | return ''; |
---|
422 | } |
---|
423 | |
---|
424 | |
---|
425 | function SuspiciousSQL($numsql=10) |
---|
426 | { |
---|
427 | return adodb_perf::_SuspiciousSQL($numsql); |
---|
428 | } |
---|
429 | |
---|
430 | function ExpensiveSQL($numsql=10) |
---|
431 | { |
---|
432 | return adodb_perf::_ExpensiveSQL($numsql); |
---|
433 | } |
---|
434 | |
---|
435 | |
---|
436 | /* |
---|
437 | This reports the percentage of load on the instance due to the most |
---|
438 | expensive few SQL statements. Tuning these statements can often |
---|
439 | make huge improvements in overall system performance. |
---|
440 | */ |
---|
441 | function _ExpensiveSQL($numsql = 10) |
---|
442 | { |
---|
443 | global $HTTP_GET_VARS,$ADODB_FETCH_MODE; |
---|
444 | |
---|
445 | $perf_table = adodb_perf::table(); |
---|
446 | $saveE = $this->conn->fnExecute; |
---|
447 | $this->conn->fnExecute = false; |
---|
448 | |
---|
449 | if (isset($HTTP_GET_VARS['expe']) && isset($HTTP_GET_VARS['sql'])) { |
---|
450 | $partial = !empty($HTTP_GET_VARS['part']); |
---|
451 | echo "<a name=explain></a>".$this->Explain($HTTP_GET_VARS['sql'],$partial)."\n"; |
---|
452 | } |
---|
453 | |
---|
454 | if (isset($HTTP_GET_VARS['sql'])) return; |
---|
455 | |
---|
456 | $sql1 = $this->sql1; |
---|
457 | $save = $ADODB_FETCH_MODE; |
---|
458 | $ADODB_FETCH_MODE = ADODB_FETCH_NUM; |
---|
459 | $rs =& $this->conn->SelectLimit( |
---|
460 | "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer |
---|
461 | from $perf_table |
---|
462 | where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') |
---|
463 | and (tracer is null or tracer not like 'ERROR:%') |
---|
464 | group by sql1 |
---|
465 | order by 1 desc",$numsql); |
---|
466 | |
---|
467 | $this->conn->fnExecute = $saveE; |
---|
468 | $ADODB_FETCH_MODE = $save; |
---|
469 | if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; |
---|
470 | $s = "<h3>Expensive SQL</h3> |
---|
471 | <font size=1>Tuning the following SQL will reduce the server load substantially</font><br> |
---|
472 | <table border=1 bgcolor=white><tr><td><b>Load</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n"; |
---|
473 | $max = $this->maxLength; |
---|
474 | while (!$rs->EOF) { |
---|
475 | $sql = $rs->fields[1]; |
---|
476 | $raw = urlencode($sql); |
---|
477 | if (strlen($raw)>$max-100) { |
---|
478 | $sql2 = substr($sql,0,$max-500); |
---|
479 | $raw = urlencode($sql2).'&part='.crc32($sql); |
---|
480 | } |
---|
481 | $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">"; |
---|
482 | $suffix = "</a>"; |
---|
483 | if($this->explain == false || strlen($prefix>$max)) { |
---|
484 | $prefix = ''; |
---|
485 | $suffix = ''; |
---|
486 | } |
---|
487 | $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". |
---|
488 | "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; |
---|
489 | $rs->MoveNext(); |
---|
490 | } |
---|
491 | return $s."</table>"; |
---|
492 | } |
---|
493 | |
---|
494 | /* |
---|
495 | Raw function to return parameter value from $settings. |
---|
496 | */ |
---|
497 | function DBParameter($param) |
---|
498 | { |
---|
499 | if (empty($this->settings[$param])) return false; |
---|
500 | $sql = $this->settings[$param][1]; |
---|
501 | return $this->_DBParameter($sql); |
---|
502 | } |
---|
503 | |
---|
504 | /* |
---|
505 | Raw function returning array of poll paramters |
---|
506 | */ |
---|
507 | function &PollParameters() |
---|
508 | { |
---|
509 | $arr[0] = (float)$this->DBParameter('data cache hit ratio'); |
---|
510 | $arr[1] = (float)$this->DBParameter('data reads'); |
---|
511 | $arr[2] = (float)$this->DBParameter('data writes'); |
---|
512 | $arr[3] = (integer) $this->DBParameter('current connections'); |
---|
513 | return $arr; |
---|
514 | } |
---|
515 | |
---|
516 | /* |
---|
517 | Low-level Get Database Parameter |
---|
518 | */ |
---|
519 | function _DBParameter($sql) |
---|
520 | { |
---|
521 | $savelog = $this->conn->LogSQL(false); |
---|
522 | if (is_array($sql)) { |
---|
523 | global $ADODB_FETCH_MODE; |
---|
524 | |
---|
525 | $sql1 = $sql[0]; |
---|
526 | $key = $sql[1]; |
---|
527 | if (sizeof($sql)>2) $pos = $sql[2]; |
---|
528 | else $pos = 1; |
---|
529 | if (sizeof($sql)>3) $coef = $sql[3]; |
---|
530 | else $coef = false; |
---|
531 | $ret = false; |
---|
532 | $save = $ADODB_FETCH_MODE; |
---|
533 | $ADODB_FETCH_MODE = ADODB_FETCH_NUM; |
---|
534 | $rs = $this->conn->Execute($sql1); |
---|
535 | $ADODB_FETCH_MODE = $save; |
---|
536 | if ($rs) { |
---|
537 | while (!$rs->EOF) { |
---|
538 | $keyf = reset($rs->fields); |
---|
539 | if (trim($keyf) == $key) { |
---|
540 | $ret = $rs->fields[$pos]; |
---|
541 | if ($coef) $ret *= $coef; |
---|
542 | break; |
---|
543 | } |
---|
544 | $rs->MoveNext(); |
---|
545 | } |
---|
546 | $rs->Close(); |
---|
547 | } |
---|
548 | $this->conn->LogSQL($savelog); |
---|
549 | return $ret; |
---|
550 | } else { |
---|
551 | if (strncmp($sql,'=',1) == 0) { |
---|
552 | $fn = substr($sql,1); |
---|
553 | return $this->$fn(); |
---|
554 | } |
---|
555 | $sql = str_replace('$DATABASE',$this->conn->database,$sql); |
---|
556 | $ret = $this->conn->GetOne($sql); |
---|
557 | $this->conn->LogSQL($savelog); |
---|
558 | |
---|
559 | return $ret; |
---|
560 | } |
---|
561 | } |
---|
562 | |
---|
563 | /* |
---|
564 | Warn if cache ratio falls below threshold. Displayed in "Description" column. |
---|
565 | */ |
---|
566 | function WarnCacheRatio($val) |
---|
567 | { |
---|
568 | if ($val < $this->warnRatio) |
---|
569 | return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>'; |
---|
570 | else return ''; |
---|
571 | } |
---|
572 | |
---|
573 | /***********************************************************************************************/ |
---|
574 | // HIGH LEVEL UI FUNCTIONS |
---|
575 | /***********************************************************************************************/ |
---|
576 | |
---|
577 | |
---|
578 | function UI($pollsecs=5) |
---|
579 | { |
---|
580 | global $HTTP_GET_VARS,$HTTP_SERVER_VARS,$HTTP_POST_VARS; |
---|
581 | |
---|
582 | $perf_table = adodb_perf::table(); |
---|
583 | $conn = $this->conn; |
---|
584 | |
---|
585 | $app = $conn->host; |
---|
586 | if ($conn->host && $conn->database) $app .= ', db='; |
---|
587 | $app .= $conn->database; |
---|
588 | |
---|
589 | if ($app) $app .= ', '; |
---|
590 | $savelog = $this->conn->LogSQL(false); |
---|
591 | $info = $conn->ServerInfo(); |
---|
592 | if (isset($HTTP_GET_VARS['clearsql'])) { |
---|
593 | $this->conn->Execute("delete from $perf_table"); |
---|
594 | } |
---|
595 | $this->conn->LogSQL($savelog); |
---|
596 | |
---|
597 | // magic quotes |
---|
598 | |
---|
599 | if (isset($HTTP_GET_VARS['sql']) && get_magic_quotes_gpc()) { |
---|
600 | $_GET['sql'] = $HTTP_GET_VARS['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$HTTP_GET_VARS['sql']); |
---|
601 | } |
---|
602 | |
---|
603 | if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10; |
---|
604 | else $nsql = $_SESSION['ADODB_PERF_SQL']; |
---|
605 | |
---|
606 | $app .= $info['description']; |
---|
607 | |
---|
608 | |
---|
609 | if (isset($HTTP_GET_VARS['do'])) $do = $HTTP_GET_VARS['do']; |
---|
610 | else if (isset($HTTP_POST_VARS['do'])) $do = $HTTP_POST_VARS['do']; |
---|
611 | else if (isset($HTTP_GET_VARS['sql'])) $do = 'viewsql'; |
---|
612 | else $do = 'stats'; |
---|
613 | |
---|
614 | if (isset($HTTP_GET_VARS['nsql'])) { |
---|
615 | if ($HTTP_GET_VARS['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $HTTP_GET_VARS['nsql']; |
---|
616 | } |
---|
617 | echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>"; |
---|
618 | if ($do == 'viewsql') $form = "<td><form># SQL:<input type=hidden value=viewsql name=do> <input type=text size=4 name=nsql value=$nsql><input type=submit value=Go></td></form>"; |
---|
619 | else $form = "<td> </td>"; |
---|
620 | |
---|
621 | $allowsql = !defined('ADODB_PERF_NO_RUN_SQL'); |
---|
622 | |
---|
623 | if (empty($HTTP_GET_VARS['hidem'])) |
---|
624 | echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2> |
---|
625 | <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td> |
---|
626 | <a href=?do=stats><b>Performance Stats</b></a> <a href=?do=viewsql><b>View SQL</b></a> |
---|
627 | <a href=?do=tables><b>View Tables</b></a> <a href=?do=poll><b>Poll Stats</b></a>", |
---|
628 | $allowsql ? ' <a href=?do=dosql><b>Run SQL</b></a>' : '', |
---|
629 | "$form", |
---|
630 | "</tr></table>"; |
---|
631 | |
---|
632 | |
---|
633 | switch ($do) { |
---|
634 | default: |
---|
635 | case 'stats': |
---|
636 | echo $this->HealthCheck(); |
---|
637 | //$this->conn->debug=1; |
---|
638 | echo $this->CheckMemory(); |
---|
639 | break; |
---|
640 | case 'poll': |
---|
641 | echo "<iframe width=720 height=80% |
---|
642 | src=\"{$HTTP_SERVER_VARS['PHP_SELF']}?do=poll2&hidem=1\"></iframe>"; |
---|
643 | break; |
---|
644 | case 'poll2': |
---|
645 | echo "<pre>"; |
---|
646 | $this->Poll($pollsecs); |
---|
647 | break; |
---|
648 | |
---|
649 | case 'dosql': |
---|
650 | if (!$allowsql) break; |
---|
651 | |
---|
652 | $this->DoSQLForm(); |
---|
653 | break; |
---|
654 | case 'viewsql': |
---|
655 | if (empty($HTTP_GET_VARS['hidem'])) |
---|
656 | echo " <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; |
---|
657 | echo($this->SuspiciousSQL($nsql)); |
---|
658 | echo($this->ExpensiveSQL($nsql)); |
---|
659 | echo($this->InvalidSQL($nsql)); |
---|
660 | break; |
---|
661 | case 'tables': |
---|
662 | echo $this->Tables(); break; |
---|
663 | } |
---|
664 | global $ADODB_vers; |
---|
665 | echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>"; |
---|
666 | } |
---|
667 | |
---|
668 | /* |
---|
669 | Runs in infinite loop, returning real-time statistics |
---|
670 | */ |
---|
671 | function Poll($secs=5) |
---|
672 | { |
---|
673 | $this->conn->fnExecute = false; |
---|
674 | //$this->conn->debug=1; |
---|
675 | if ($secs <= 1) $secs = 1; |
---|
676 | echo "Accumulating statistics, every $secs seconds...\n";flush(); |
---|
677 | $arro =& $this->PollParameters(); |
---|
678 | $cnt = 0; |
---|
679 | set_time_limit(0); |
---|
680 | sleep($secs); |
---|
681 | while (1) { |
---|
682 | $arr =& $this->PollParameters(); |
---|
683 | |
---|
684 | $hits = sprintf('%2.2f',$arr[0]); |
---|
685 | $reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs); |
---|
686 | $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs); |
---|
687 | $sess = sprintf('%5d',$arr[3]); |
---|
688 | |
---|
689 | $load = $this->CPULoad(); |
---|
690 | if ($load !== false) { |
---|
691 | $oslabel = 'WS-CPU%'; |
---|
692 | $osval = sprintf(" %2.1f ",(float) $load); |
---|
693 | }else { |
---|
694 | $oslabel = ''; |
---|
695 | $osval = ''; |
---|
696 | } |
---|
697 | if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n"; |
---|
698 | $cnt += 1; |
---|
699 | echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n"; |
---|
700 | flush(); |
---|
701 | |
---|
702 | sleep($secs); |
---|
703 | $arro = $arr; |
---|
704 | } |
---|
705 | } |
---|
706 | |
---|
707 | /* |
---|
708 | Returns basic health check in a command line interface |
---|
709 | */ |
---|
710 | function HealthCheckCLI() |
---|
711 | { |
---|
712 | return $this->HealthCheck(true); |
---|
713 | } |
---|
714 | |
---|
715 | |
---|
716 | /* |
---|
717 | Returns basic health check as HTML |
---|
718 | */ |
---|
719 | function HealthCheck($cli=false) |
---|
720 | { |
---|
721 | $saveE = $this->conn->fnExecute; |
---|
722 | $this->conn->fnExecute = false; |
---|
723 | if ($cli) $html = ''; |
---|
724 | else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles; |
---|
725 | |
---|
726 | $oldc = false; |
---|
727 | $bgc = ''; |
---|
728 | foreach($this->settings as $name => $arr) { |
---|
729 | if ($arr === false) break; |
---|
730 | |
---|
731 | if (!is_string($name)) { |
---|
732 | if ($cli) $html .= " -- $arr -- \n"; |
---|
733 | else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> </td></tr>"; |
---|
734 | continue; |
---|
735 | } |
---|
736 | |
---|
737 | if (!is_array($arr)) break; |
---|
738 | $category = $arr[0]; |
---|
739 | $how = $arr[1]; |
---|
740 | if (sizeof($arr)>2) $desc = $arr[2]; |
---|
741 | else $desc = ' '; |
---|
742 | |
---|
743 | |
---|
744 | if ($category == 'HIDE') continue; |
---|
745 | |
---|
746 | $val = $this->_DBParameter($how); |
---|
747 | |
---|
748 | if ($desc && strncmp($desc,"=",1) === 0) { |
---|
749 | $fn = substr($desc,1); |
---|
750 | $desc = $this->$fn($val); |
---|
751 | } |
---|
752 | |
---|
753 | if ($val === false) { |
---|
754 | $m = $this->conn->ErrorMsg(); |
---|
755 | $val = "Error: $m"; |
---|
756 | } else { |
---|
757 | if (is_numeric($val) && $val >= 256*1024) { |
---|
758 | if ($val % (1024*1024) == 0) { |
---|
759 | $val /= (1024*1024); |
---|
760 | $val .= 'M'; |
---|
761 | } else if ($val % 1024 == 0) { |
---|
762 | $val /= 1024; |
---|
763 | $val .= 'K'; |
---|
764 | } |
---|
765 | //$val = htmlspecialchars($val); |
---|
766 | } |
---|
767 | } |
---|
768 | if ($category != $oldc) { |
---|
769 | $oldc = $category; |
---|
770 | //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color; |
---|
771 | } |
---|
772 | if (strlen($desc)==0) $desc = ' '; |
---|
773 | if (strlen($val)==0) $val = ' '; |
---|
774 | if ($cli) { |
---|
775 | $html .= str_replace(' ','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc))); |
---|
776 | |
---|
777 | }else { |
---|
778 | $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n"; |
---|
779 | } |
---|
780 | } |
---|
781 | |
---|
782 | if (!$cli) $html .= "</table>\n"; |
---|
783 | $this->conn->fnExecute = $saveE; |
---|
784 | |
---|
785 | return $html; |
---|
786 | } |
---|
787 | |
---|
788 | function Tables($orderby='1') |
---|
789 | { |
---|
790 | if (!$this->tablesSQL) return false; |
---|
791 | |
---|
792 | $savelog = $this->conn->LogSQL(false); |
---|
793 | $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby); |
---|
794 | $this->conn->LogSQL($savelog); |
---|
795 | $html = rs2html($rs,false,false,false,false); |
---|
796 | return $html; |
---|
797 | } |
---|
798 | |
---|
799 | |
---|
800 | function CreateLogTable() |
---|
801 | { |
---|
802 | if (!$this->createTableSQL) return false; |
---|
803 | |
---|
804 | $savelog = $this->conn->LogSQL(false); |
---|
805 | $ok = $this->conn->Execute($this->createTableSQL); |
---|
806 | $this->conn->LogSQL($savelog); |
---|
807 | return ($ok) ? true : false; |
---|
808 | } |
---|
809 | |
---|
810 | function DoSQLForm() |
---|
811 | { |
---|
812 | global $HTTP_SERVER_VARS,$HTTP_GET_VARS,$HTTP_POST_VARS,$HTTP_SESSION_VARS; |
---|
813 | |
---|
814 | $HTTP_VARS = array_merge($HTTP_GET_VARS,$HTTP_POST_VARS); |
---|
815 | |
---|
816 | $PHP_SELF = $HTTP_SERVER_VARS['PHP_SELF']; |
---|
817 | $sql = isset($HTTP_VARS['sql']) ? $HTTP_VARS['sql'] : ''; |
---|
818 | |
---|
819 | if (isset($HTTP_SESSION_VARS['phplens_sqlrows'])) $rows = $HTTP_SESSION_VARS['phplens_sqlrows']; |
---|
820 | else $rows = 3; |
---|
821 | |
---|
822 | if (isset($HTTP_VARS['SMALLER'])) { |
---|
823 | $rows /= 2; |
---|
824 | if ($rows < 3) $rows = 3; |
---|
825 | $HTTP_SESSION_VARS['phplens_sqlrows'] = $rows; |
---|
826 | } |
---|
827 | if (isset($HTTP_VARS['BIGGER'])) { |
---|
828 | $rows *= 2; |
---|
829 | $HTTP_SESSION_VARS['phplens_sqlrows'] = $rows; |
---|
830 | } |
---|
831 | |
---|
832 | ?> |
---|
833 | |
---|
834 | <form method="POST" action="<?php echo $PHP_SELF ?>"> |
---|
835 | <table><tr> |
---|
836 | <td> Form size: <input type="submit" value=" < " name="SMALLER"><input type="submit" value=" > > " name="BIGGER"> |
---|
837 | </td> |
---|
838 | <td align=right> |
---|
839 | <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql> |
---|
840 | </td></tr> |
---|
841 | <tr> |
---|
842 | <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea> |
---|
843 | </td> |
---|
844 | </tr> |
---|
845 | </table> |
---|
846 | </form> |
---|
847 | |
---|
848 | <?php |
---|
849 | if (!isset($HTTP_VARS['sql'])) return; |
---|
850 | |
---|
851 | $sql = $this->undomq(trim($sql)); |
---|
852 | if (substr($sql,strlen($sql)-1) === ';') { |
---|
853 | $print = true; |
---|
854 | $sqla = $this->SplitSQL($sql); |
---|
855 | } else { |
---|
856 | $print = false; |
---|
857 | $sqla = array($sql); |
---|
858 | } |
---|
859 | foreach($sqla as $sqls) { |
---|
860 | |
---|
861 | if (!$sqls) continue; |
---|
862 | |
---|
863 | if ($print) { |
---|
864 | print "<p>".htmlspecialchars($sqls)."</p>"; |
---|
865 | flush(); |
---|
866 | } |
---|
867 | $savelog = $this->conn->LogSQL(false); |
---|
868 | $rs = $this->conn->Execute($sqls); |
---|
869 | $this->conn->LogSQL($savelog); |
---|
870 | if ($rs && is_object($rs) && !$rs->EOF) { |
---|
871 | rs2html($rs); |
---|
872 | while ($rs->NextRecordSet()) { |
---|
873 | print "<table width=98% bgcolor=#C0C0FF><tr><td> </td></tr></table>"; |
---|
874 | rs2html($rs); |
---|
875 | } |
---|
876 | } else { |
---|
877 | $e1 = (integer) $this->conn->ErrorNo(); |
---|
878 | $e2 = $this->conn->ErrorMsg(); |
---|
879 | if (($e1) || ($e2)) { |
---|
880 | if (empty($e1)) $e1 = '-1'; // postgresql fix |
---|
881 | print ' '.$e1.': '.$e2; |
---|
882 | } else { |
---|
883 | print "<p>No Recordset returned<br></p>"; |
---|
884 | } |
---|
885 | } |
---|
886 | } // foreach |
---|
887 | } |
---|
888 | |
---|
889 | function SplitSQL($sql) |
---|
890 | { |
---|
891 | $arr = explode(';',$sql); |
---|
892 | return $arr; |
---|
893 | } |
---|
894 | |
---|
895 | function undomq(&$m) |
---|
896 | { |
---|
897 | if (get_magic_quotes_gpc()) { |
---|
898 | // undo the damage |
---|
899 | $m = str_replace('\\\\','\\',$m); |
---|
900 | $m = str_replace('\"','"',$m); |
---|
901 | $m = str_replace('\\\'','\'',$m); |
---|
902 | } |
---|
903 | return $m; |
---|
904 | } |
---|
905 | } |
---|
906 | |
---|
907 | |
---|
908 | |
---|
909 | ?> |
---|