Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

adodb-perf.inc.php @ 8222

Revision 8222, 31.3 KB checked in by angelo, 11 years ago (diff)
Ticket #3491 - Compatibilizar Expresso com novas versoes do PHP
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/*
3	V5.18 3 Sep 2012 (c) 2000-2012 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	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	define( 'ADODB_OPT_HIGH', 2);
23	define( 'ADODB_OPT_LOW', 1);
24
25	global $ADODB_PERF_MIN;
26	$ADODB_PERF_MIN = 0.05; // log only if >= minimum number of secs to run
27
28
29	// returns in K the memory of current process, or 0 if not known
30	function adodb_getmem()
31	{
32	if (function_exists('memory_get_usage'))
33	return (integer) ((memory_get_usage()+512)/1024);
34
35	$pid = getmypid();
36
37	if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) {
38	$output = array();
39
40	exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output);
41	return substr($output[5], strpos($output[5], ':') + 1);
42	}
43
44	/* Hopefully UNIX */
45	exec("ps --pid $pid --no-headers -o%mem,size", $output);
46	if (sizeof($output) == 0) return 0;
47
48	$memarr = explode(' ',$output[0]);
49	if (sizeof($memarr)>=2) return (integer) $memarr[1];
50
51	return 0;
52	}
53
54	// avoids localization problems where , is used instead of .
55	function adodb_round($n,$prec)
56	{
57	return number_format($n, $prec, '.', '');
58	}
59
60	/* obsolete: return microtime value as a float. Retained for backward compat */
61	function adodb_microtime()
62	{
63	return microtime(true);
64	}
65
66	/* sql code timing */
67	function adodb_log_sql(&$connx,$sql,$inputarr)
68	{
69	$perf_table = adodb_perf::table();
70	$connx->fnExecute = false;
71	$a0 = microtime(true);
72	$rs = $connx->Execute($sql,$inputarr);
73	$a1 = microtime(true);
74
75	if (!empty($connx->_logsql) && (empty($connx->_logsqlErrors) \|\| !$rs)) {
76	global $ADODB_LOG_CONN;
77
78	if (!empty($ADODB_LOG_CONN)) {
79	$conn = $ADODB_LOG_CONN;
80	if ($conn->databaseType != $connx->databaseType)
81	$prefix = '/dbx='.$connx->databaseType .'/ ';
82	else
83	$prefix = '';
84	} else {
85	$conn = $connx;
86	$prefix = '';
87	}
88
89	$conn->_logsql = false; // disable logsql error simulation
90	$dbT = $conn->databaseType;
91
92	$time = $a1 - $a0;
93
94	if (!$rs) {
95	$errM = $connx->ErrorMsg();
96	$errN = $connx->ErrorNo();
97	$conn->lastInsID = 0;
98	$tracer = substr('ERROR: '.htmlspecialchars($errM),0,250);
99	} else {
100	$tracer = '';
101	$errM = '';
102	$errN = 0;
103	$dbg = $conn->debug;
104	$conn->debug = false;
105	if (!is_object($rs) \|\| $rs->dataProvider == 'empty')
106	$conn->_affected = $conn->affected_rows(true);
107	$conn->lastInsID = @$conn->Insert_ID();
108	$conn->debug = $dbg;
109	}
110	if (isset($_SERVER['HTTP_HOST'])) {
111	$tracer .= '<br>'.$_SERVER['HTTP_HOST'];
112	if (isset($_SERVER['PHP_SELF'])) $tracer .= htmlspecialchars($_SERVER['PHP_SELF']);
113	} else
114	if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.htmlspecialchars($_SERVER['PHP_SELF']);
115	//$tracer .= (string) adodb_backtrace(false);
116
117	$tracer = (string) substr($tracer,0,500);
118
119	if (is_array($inputarr)) {
120	if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr);
121	else {
122	// Quote string parameters so we can see them in the
123	// performance stats. This helps spot disabled indexes.
124	$xar_params = $inputarr;
125	foreach ($xar_params as $xar_param_key => $xar_param) {
126	if (gettype($xar_param) == 'string')
127	$xar_params[$xar_param_key] = '"' . $xar_param . '"';
128	}
129	$params = implode(', ', $xar_params);
130	if (strlen($params) >= 3000) $params = substr($params, 0, 3000);
131	}
132	} else {
133	$params = '';
134	}
135
136	if (is_array($sql)) $sql = $sql[0];
137	if ($prefix) $sql = $prefix.$sql;
138	$arr = array('b'=>strlen($sql).'.'.crc32($sql),
139	'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6));
140	//var_dump($arr);
141	$saved = $conn->debug;
142	$conn->debug = 0;
143
144	$d = $conn->sysTimeStamp;
145	if (empty($d)) $d = date("'Y-m-d H:i:s'");
146	if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') {
147	$isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";
148	} else if ($dbT == 'odbc_mssql' \|\| $dbT == 'informix' \|\| strncmp($dbT,'odbtp',4)==0) {
149	$timer = $arr['f'];
150	if ($dbT == 'informix') $sql2 = substr($sql2,0,230);
151
152	$sql1 = $conn->qstr($arr['b']);
153	$sql2 = $conn->qstr($arr['c']);
154	$params = $conn->qstr($arr['d']);
155	$tracer = $conn->qstr($arr['e']);
156
157	$isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";
158	if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);
159	$arr = false;
160	} else {
161	if ($dbT == 'db2') $arr['f'] = (float) $arr['f'];
162	$isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)";
163	}
164
165	global $ADODB_PERF_MIN;
166	if ($errN != 0 \|\| $time >= $ADODB_PERF_MIN) {
167	$ok = $conn->Execute($isql,$arr);
168	} else
169	$ok = true;
170
171	$conn->debug = $saved;
172
173	if ($ok) {
174	$conn->_logsql = true;
175	} else {
176	$err2 = $conn->ErrorMsg();
177	$conn->_logsql = true; // enable logsql error simulation
178	$perf = NewPerfMonitor($conn);
179	if ($perf) {
180	if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr);
181	} else {
182	$ok = $conn->Execute("create table $perf_table (
183	created varchar(50),
184	sql0 varchar(250),
185	sql1 varchar(4000),
186	params varchar(3000),
187	tracer varchar(500),
188	timer decimal(16,6))");
189	}
190	if (!$ok) {
191	ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");
192	$conn->_logsql = false;
193	}
194	}
195	$connx->_errorMsg = $errM;
196	$connx->_errorCode = $errN;
197	}
198	$connx->fnExecute = 'adodb_log_sql';
199	return $rs;
200	}
201
202
203	/*
204	The settings data structure is an associative array that database parameter per element.
205
206	Each database parameter element in the array is itself an array consisting of:
207
208	0: category code, used to group related db parameters
209	1: either
210	a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'",
211	b. array holding sql string and field to look for, e.g. array('show variables','table_cache'),
212	c. a string prefixed by =, then a PHP method of the class is invoked,
213	e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue',
214	2: description of the database parameter
215	*/
216
217	class adodb_perf {
218	var $conn;
219	var $color = '#F0F0F0';
220	var $table = '<table border=1 bgcolor=white>';
221	var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>';
222	var $warnRatio = 90;
223	var $tablesSQL = false;
224	var $cliFormat = "%32s => %s \r\n";
225	var $sql1 = 'sql1'; // used for casting sql1 to text for mssql
226	var $explain = true;
227	var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>";
228	var $createTableSQL = false;
229	var $maxLength = 2000;
230
231	// Sets the tablename to be used
232	static function table($newtable = false)
233	{
234	static $_table;
235
236	if (!empty($newtable)) $_table = $newtable;
237	if (empty($_table)) $_table = 'adodb_logsql';
238	return $_table;
239	}
240
241	// returns array with info to calculate CPU Load
242	function _CPULoad()
243	{
244	/*
245
246	cpu 524152 2662 2515228 336057010
247	cpu0 264339 1408 1257951 168025827
248	cpu1 259813 1254 1257277 168031181
249	page 622307 25475680
250	swap 24 1891
251	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
252	disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320)
253	ctxt 66155838
254	btime 1062315585
255	processes 69293
256
257	*/
258	// Algorithm is taken from
259	// http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/414b0e1b-499c-411e-8a02-6a12e339c0f1/
260	if (strncmp(PHP_OS,'WIN',3)==0) {
261	if (PHP_VERSION == '5.0.0') return false;
262	if (PHP_VERSION == '5.0.1') return false;
263	if (PHP_VERSION == '5.0.2') return false;
264	if (PHP_VERSION == '5.0.3') return false;
265	if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737
266
267	static $FAIL = false;
268	if ($FAIL) return false;
269
270	$objName = "winmgmts:{impersonationLevel=impersonate}!\\\\.\\root\\CIMV2";
271	$myQuery = "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor WHERE Name = '_Total'";
272
273	try {
274	@$objWMIService = new COM($objName);
275	if (!$objWMIService) {
276	$FAIL = true;
277	return false;
278	}
279
280	$info[0] = -1;
281	$info[1] = 0;
282	$info[2] = 0;
283	$info[3] = 0;
284	foreach($objWMIService->ExecQuery($myQuery) as $objItem) {
285	$info[0] = $objItem->PercentProcessorTime();
286	}
287
288	} catch(Exception $e) {
289	$FAIL = true;
290	echo $e->getMessage();
291	return false;
292	}
293
294	return $info;
295	}
296
297	// Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com)
298	$statfile = '/proc/stat';
299	if (!file_exists($statfile)) return false;
300
301	$fd = fopen($statfile,"r");
302	if (!$fd) return false;
303
304	$statinfo = explode("\n",fgets($fd, 1024));
305	fclose($fd);
306	foreach($statinfo as $line) {
307	$info = explode(" ",$line);
308	if($info[0]=="cpu") {
309	array_shift($info); // pop off "cpu"
310	if(!$info[0]) array_shift($info); // pop off blank space (if any)
311	return $info;
312	}
313	}
314
315	return false;
316
317	}
318
319	/* NOT IMPLEMENTED */
320	function MemInfo()
321	{
322	/*
323
324	total: used: free: shared: buffers: cached:
325	Mem: 1055289344 917299200 137990144 0 165437440 599773184
326	Swap: 2146775040 11055104 2135719936
327	MemTotal: 1030556 kB
328	MemFree: 134756 kB
329	MemShared: 0 kB
330	Buffers: 161560 kB
331	Cached: 581384 kB
332	SwapCached: 4332 kB
333	Active: 494468 kB
334	Inact_dirty: 322856 kB
335	Inact_clean: 24256 kB
336	Inact_target: 168316 kB
337	HighTotal: 131064 kB
338	HighFree: 1024 kB
339	LowTotal: 899492 kB
340	LowFree: 133732 kB
341	SwapTotal: 2096460 kB
342	SwapFree: 2085664 kB
343	Committed_AS: 348732 kB
344	*/
345	}
346
347
348	/*
349	Remember that this is client load, not db server load!
350	*/
351	var $_lastLoad;
352	function CPULoad()
353	{
354	$info = $this->_CPULoad();
355	if (!$info) return false;
356
357	if (strncmp(PHP_OS,'WIN',3)==0) {
358	return (integer) $info[0];
359	}else {
360	if (empty($this->_lastLoad)) {
361	sleep(1);
362	$this->_lastLoad = $info;
363	$info = $this->_CPULoad();
364	}
365
366	$last = $this->_lastLoad;
367	$this->_lastLoad = $info;
368
369	$d_user = $info[0] - $last[0];
370	$d_nice = $info[1] - $last[1];
371	$d_system = $info[2] - $last[2];
372	$d_idle = $info[3] - $last[3];
373
374	//printf("Delta - User: %f Nice: %f System: %f Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle);
375
376	$total=$d_user+$d_nice+$d_system+$d_idle;
377	if ($total<1) $total=1;
378	return 100*($d_user+$d_nice+$d_system)/$total;
379	}
380	}
381
382	function Tracer($sql)
383	{
384	$perf_table = adodb_perf::table();
385	$saveE = $this->conn->fnExecute;
386	$this->conn->fnExecute = false;
387
388	global $ADODB_FETCH_MODE;
389	$save = $ADODB_FETCH_MODE;
390	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
391	if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
392
393	$sqlq = $this->conn->qstr($sql);
394	$arr = $this->conn->GetArray(
395	"select count(*),tracer
396	from $perf_table where sql1=$sqlq
397	group by tracer
398	order by 1 desc");
399	$s = '';
400	if ($arr) {
401	$s .= '<h3>Scripts Affected</h3>';
402	foreach($arr as $k) {
403	$s .= sprintf("%4d",$k[0]).'   '.strip_tags($k[1]).'<br>';
404	}
405	}
406
407	if (isset($savem)) $this->conn->SetFetchMode($savem);
408	$ADODB_CACHE_MODE = $save;
409	$this->conn->fnExecute = $saveE;
410	return $s;
411	}
412
413	/*
414	Explain Plan for $sql.
415	If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the
416	actual sql.
417	*/
418	function Explain($sql,$partial=false)
419	{
420	return false;
421	}
422
423	function InvalidSQL($numsql = 10)
424	{
425
426	if (isset($_GET['sql'])) return;
427	$s = '<h3>Invalid SQL</h3>';
428	$saveE = $this->conn->fnExecute;
429	$this->conn->fnExecute = false;
430	$perf_table = adodb_perf::table();
431	$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);
432	$this->conn->fnExecute = $saveE;
433	if ($rs) {
434	$s .= rs2html($rs,false,false,false,false);
435	} else
436	return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
437
438	return $s;
439	}
440
441
442	/*
443	This script identifies the longest running SQL
444	*/
445	function _SuspiciousSQL($numsql = 10)
446	{
447	global $ADODB_FETCH_MODE;
448
449	$perf_table = adodb_perf::table();
450	$saveE = $this->conn->fnExecute;
451	$this->conn->fnExecute = false;
452
453	if (isset($_GET['exps']) && isset($_GET['sql'])) {
454	$partial = !empty($_GET['part']);
455	echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
456	}
457
458	if (isset($_GET['sql'])) return;
459	$sql1 = $this->sql1;
460
461	$save = $ADODB_FETCH_MODE;
462	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
463	if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
464	//$this->conn->debug=1;
465	$rs = $this->conn->SelectLimit(
466	"select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
467	from $perf_table
468	where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')
469	and (tracer is null or tracer not like 'ERROR:%')
470	group by sql1
471	order by 1 desc",$numsql);
472	if (isset($savem)) $this->conn->SetFetchMode($savem);
473	$ADODB_FETCH_MODE = $save;
474	$this->conn->fnExecute = $saveE;
475
476	if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
477	$s = "<h3>Suspicious SQL</h3>
478	<font size=1>The following SQL have high average execution times</font><br>
479	<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";
480	$max = $this->maxLength;
481	while (!$rs->EOF) {
482	$sql = $rs->fields[1];
483	$raw = urlencode($sql);
484	if (strlen($raw)>$max-100) {
485	$sql2 = substr($sql,0,$max-500);
486	$raw = urlencode($sql2).'&part='.crc32($sql);
487	}
488	$prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">";
489	$suffix = "</a>";
490	if ($this->explain == false \|\| strlen($prefix)>$max) {
491	$suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>';
492	$prefix = '';
493	}
494	$s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
495	"<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
496	$rs->MoveNext();
497	}
498	return $s."</table>";
499
500	}
501
502	function CheckMemory()
503	{
504	return '';
505	}
506
507
508	function SuspiciousSQL($numsql=10)
509	{
510	return adodb_perf::_SuspiciousSQL($numsql);
511	}
512
513	function ExpensiveSQL($numsql=10)
514	{
515	return adodb_perf::_ExpensiveSQL($numsql);
516	}
517
518
519	/*
520	This reports the percentage of load on the instance due to the most
521	expensive few SQL statements. Tuning these statements can often
522	make huge improvements in overall system performance.
523	*/
524	function _ExpensiveSQL($numsql = 10)
525	{
526	global $ADODB_FETCH_MODE;
527
528	$perf_table = adodb_perf::table();
529	$saveE = $this->conn->fnExecute;
530	$this->conn->fnExecute = false;
531
532	if (isset($_GET['expe']) && isset($_GET['sql'])) {
533	$partial = !empty($_GET['part']);
534	echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
535	}
536
537	if (isset($_GET['sql'])) return;
538
539	$sql1 = $this->sql1;
540	$save = $ADODB_FETCH_MODE;
541	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
542	if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
543
544	$rs = $this->conn->SelectLimit(
545	"select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
546	from $perf_table
547	where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')
548	and (tracer is null or tracer not like 'ERROR:%')
549	group by sql1
550	having count(*)>1
551	order by 1 desc",$numsql);
552	if (isset($savem)) $this->conn->SetFetchMode($savem);
553	$this->conn->fnExecute = $saveE;
554	$ADODB_FETCH_MODE = $save;
555	if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
556	$s = "<h3>Expensive SQL</h3>
557	<font size=1>Tuning the following SQL could reduce the server load substantially</font><br>
558	<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";
559	$max = $this->maxLength;
560	while (!$rs->EOF) {
561	$sql = $rs->fields[1];
562	$raw = urlencode($sql);
563	if (strlen($raw)>$max-100) {
564	$sql2 = substr($sql,0,$max-500);
565	$raw = urlencode($sql2).'&part='.crc32($sql);
566	}
567	$prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">";
568	$suffix = "</a>";
569	if($this->explain == false \|\| strlen($prefix>$max)) {
570	$prefix = '';
571	$suffix = '';
572	}
573	$s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
574	"<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
575	$rs->MoveNext();
576	}
577	return $s."</table>";
578	}
579
580	/*
581	Raw function to return parameter value from $settings.
582	*/
583	function DBParameter($param)
584	{
585	if (empty($this->settings[$param])) return false;
586	$sql = $this->settings[$param][1];
587	return $this->_DBParameter($sql);
588	}
589
590	/*
591	Raw function returning array of poll paramters
592	*/
593	function PollParameters()
594	{
595	$arr[0] = (float)$this->DBParameter('data cache hit ratio');
596	$arr[1] = (float)$this->DBParameter('data reads');
597	$arr[2] = (float)$this->DBParameter('data writes');
598	$arr[3] = (integer) $this->DBParameter('current connections');
599	return $arr;
600	}
601
602	/*
603	Low-level Get Database Parameter
604	*/
605	function _DBParameter($sql)
606	{
607	$savelog = $this->conn->LogSQL(false);
608	if (is_array($sql)) {
609	global $ADODB_FETCH_MODE;
610
611	$sql1 = $sql[0];
612	$key = $sql[1];
613	if (sizeof($sql)>2) $pos = $sql[2];
614	else $pos = 1;
615	if (sizeof($sql)>3) $coef = $sql[3];
616	else $coef = false;
617	$ret = false;
618	$save = $ADODB_FETCH_MODE;
619	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
620	if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
621
622	$rs = $this->conn->Execute($sql1);
623
624	if (isset($savem)) $this->conn->SetFetchMode($savem);
625	$ADODB_FETCH_MODE = $save;
626	if ($rs) {
627	while (!$rs->EOF) {
628	$keyf = reset($rs->fields);
629	if (trim($keyf) == $key) {
630	$ret = $rs->fields[$pos];
631	if ($coef) $ret *= $coef;
632	break;
633	}
634	$rs->MoveNext();
635	}
636	$rs->Close();
637	}
638	$this->conn->LogSQL($savelog);
639	return $ret;
640	} else {
641	if (strncmp($sql,'=',1) == 0) {
642	$fn = substr($sql,1);
643	return $this->$fn();
644	}
645	$sql = str_replace('$DATABASE',$this->conn->database,$sql);
646	$ret = $this->conn->GetOne($sql);
647	$this->conn->LogSQL($savelog);
648
649	return $ret;
650	}
651	}
652
653	/*
654	Warn if cache ratio falls below threshold. Displayed in "Description" column.
655	*/
656	function WarnCacheRatio($val)
657	{
658	if ($val < $this->warnRatio)
659	return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>';
660	else return '';
661	}
662
663	function clearsql()
664	{
665	$perf_table = adodb_perf::table();
666	$this->conn->Execute("delete from $perf_table where created<".$this->conn->sysTimeStamp);
667	}
668	/***********************************************************************************************/
669	// HIGH LEVEL UI FUNCTIONS
670	/***********************************************************************************************/
671
672
673	function UI($pollsecs=5)
674	{
675	global $ADODB_LOG_CONN;
676
677	$perf_table = adodb_perf::table();
678	$conn = $this->conn;
679
680	$app = $conn->host;
681	if ($conn->host && $conn->database) $app .= ', db=';
682	$app .= $conn->database;
683
684	if ($app) $app .= ', ';
685	$savelog = $this->conn->LogSQL(false);
686	$info = $conn->ServerInfo();
687	if (isset($_GET['clearsql'])) {
688	$this->clearsql();
689	}
690	$this->conn->LogSQL($savelog);
691
692	// magic quotes
693
694	if (isset($_GET['sql']) && get_magic_quotes_gpc()) {
695	$_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']);
696	}
697
698	if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10;
699	else $nsql = $_SESSION['ADODB_PERF_SQL'];
700
701	$app .= $info['description'];
702
703
704	if (isset($_GET['do'])) $do = $_GET['do'];
705	else if (isset($_POST['do'])) $do = $_POST['do'];
706	else if (isset($_GET['sql'])) $do = 'viewsql';
707	else $do = 'stats';
708
709	if (isset($_GET['nsql'])) {
710	if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql'];
711	}
712	echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>";
713	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>";
714	else $form = "<td> </td>";
715
716	$allowsql = !defined('ADODB_PERF_NO_RUN_SQL');
717	global $ADODB_PERF_MIN;
718	$app .= " (Min sql timing \$ADODB_PERF_MIN=$ADODB_PERF_MIN secs)";
719
720	if (empty($_GET['hidem']))
721	echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2>
722	<b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td>
723	<a href=?do=stats><b>Performance Stats</b></a>   <a href=?do=viewsql><b>View SQL</b></a>
724	<a href=?do=tables><b>View Tables</b></a>   <a href=?do=poll><b>Poll Stats</b></a>",
725	$allowsql ? '   <a href=?do=dosql><b>Run SQL</b></a>' : '',
726	"$form",
727	"</tr></table>";
728
729
730	switch ($do) {
731	default:
732	case 'stats':
733	if (empty($ADODB_LOG_CONN))
734	echo "<p>  <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
735	echo $this->HealthCheck();
736	//$this->conn->debug=1;
737	echo $this->CheckMemory();
738	break;
739	case 'poll':
740	$self = htmlspecialchars($_SERVER['PHP_SELF']);
741	echo "<iframe width=720 height=80%
742	src=\"{$self}?do=poll2&hidem=1\"></iframe>";
743	break;
744	case 'poll2':
745	echo "<pre>";
746	$this->Poll($pollsecs);
747	break;
748
749	case 'dosql':
750	if (!$allowsql) break;
751
752	$this->DoSQLForm();
753	break;
754	case 'viewsql':
755	if (empty($_GET['hidem']))
756	echo "  <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
757	echo($this->SuspiciousSQL($nsql));
758	echo($this->ExpensiveSQL($nsql));
759	echo($this->InvalidSQL($nsql));
760	break;
761	case 'tables':
762	echo $this->Tables(); break;
763	}
764	global $ADODB_vers;
765	echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>";
766	}
767
768	/*
769	Runs in infinite loop, returning real-time statistics
770	*/
771	function Poll($secs=5)
772	{
773	$this->conn->fnExecute = false;
774	//$this->conn->debug=1;
775	if ($secs <= 1) $secs = 1;
776	echo "Accumulating statistics, every $secs seconds...\n";flush();
777	$arro = $this->PollParameters();
778	$cnt = 0;
779	set_time_limit(0);
780	sleep($secs);
781	while (1) {
782
783	$arr = $this->PollParameters();
784
785	$hits = sprintf('%2.2f',$arr[0]);
786	$reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs);
787	$writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs);
788	$sess = sprintf('%5d',$arr[3]);
789
790	$load = $this->CPULoad();
791	if ($load !== false) {
792	$oslabel = 'WS-CPU%';
793	$osval = sprintf(" %2.1f ",(float) $load);
794	}else {
795	$oslabel = '';
796	$osval = '';
797	}
798	if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n";
799	$cnt += 1;
800	echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n";
801	flush();
802
803	if (connection_aborted()) return;
804
805	sleep($secs);
806	$arro = $arr;
807	}
808	}
809
810	/*
811	Returns basic health check in a command line interface
812	*/
813	function HealthCheckCLI()
814	{
815	return $this->HealthCheck(true);
816	}
817
818
819	/*
820	Returns basic health check as HTML
821	*/
822	function HealthCheck($cli=false)
823	{
824	$saveE = $this->conn->fnExecute;
825	$this->conn->fnExecute = false;
826	if ($cli) $html = '';
827	else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles;
828
829	$oldc = false;
830	$bgc = '';
831	foreach($this->settings as $name => $arr) {
832	if ($arr === false) break;
833
834	if (!is_string($name)) {
835	if ($cli) $html .= " -- $arr -- \n";
836	else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i>  </td></tr>";
837	continue;
838	}
839
840	if (!is_array($arr)) break;
841	$category = $arr[0];
842	$how = $arr[1];
843	if (sizeof($arr)>2) $desc = $arr[2];
844	else $desc = '   ';
845
846
847	if ($category == 'HIDE') continue;
848
849	$val = $this->_DBParameter($how);
850
851	if ($desc && strncmp($desc,"=",1) === 0) {
852	$fn = substr($desc,1);
853	$desc = $this->$fn($val);
854	}
855
856	if ($val === false) {
857	$m = $this->conn->ErrorMsg();
858	$val = "Error: $m";
859	} else {
860	if (is_numeric($val) && $val >= 256*1024) {
861	if ($val % (1024*1024) == 0) {
862	$val /= (1024*1024);
863	$val .= 'M';
864	} else if ($val % 1024 == 0) {
865	$val /= 1024;
866	$val .= 'K';
867	}
868	//$val = htmlspecialchars($val);
869	}
870	}
871	if ($category != $oldc) {
872	$oldc = $category;
873	//$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color;
874	}
875	if (strlen($desc)==0) $desc = ' ';
876	if (strlen($val)==0) $val = ' ';
877	if ($cli) {
878	$html .= str_replace(' ','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc)));
879
880	}else {
881	$html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n";
882	}
883	}
884
885	if (!$cli) $html .= "</table>\n";
886	$this->conn->fnExecute = $saveE;
887
888	return $html;
889	}
890
891	function Tables($orderby='1')
892	{
893	if (!$this->tablesSQL) return false;
894
895	$savelog = $this->conn->LogSQL(false);
896	$rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby);
897	$this->conn->LogSQL($savelog);
898	$html = rs2html($rs,false,false,false,false);
899	return $html;
900	}
901
902
903	function CreateLogTable()
904	{
905	if (!$this->createTableSQL) return false;
906
907	$table = $this->table();
908	$sql = str_replace('adodb_logsql',$table,$this->createTableSQL);
909	$savelog = $this->conn->LogSQL(false);
910	$ok = $this->conn->Execute($sql);
911	$this->conn->LogSQL($savelog);
912	return ($ok) ? true : false;
913	}
914
915	function DoSQLForm()
916	{
917
918
919	$PHP_SELF = htmlspecialchars($_SERVER['PHP_SELF']);
920	$sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : '';
921
922	if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows'];
923	else $rows = 3;
924
925	if (isset($_REQUEST['SMALLER'])) {
926	$rows /= 2;
927	if ($rows < 3) $rows = 3;
928	$_SESSION['phplens_sqlrows'] = $rows;
929	}
930	if (isset($_REQUEST['BIGGER'])) {
931	$rows *= 2;
932	$_SESSION['phplens_sqlrows'] = $rows;
933	}
934
935	?>
936
937	<form method="POST" action="<?php echo $PHP_SELF ?>">
938	<table><tr>
939	<td> Form size: <input type="submit" value=" < " name="SMALLER"><input type="submit" value=" > > " name="BIGGER">
940	</td>
941	<td align=right>
942	<input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>
943	</td></tr>
944	<tr>
945	<td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>
946	</td>
947	</tr>
948	</table>
949	</form>
950
951	<?php
952	if (!isset($_REQUEST['sql'])) return;
953
954	$sql = $this->undomq(trim($sql));
955	if (substr($sql,strlen($sql)-1) === ';') {
956	$print = true;
957	$sqla = $this->SplitSQL($sql);
958	} else {
959	$print = false;
960	$sqla = array($sql);
961	}
962	foreach($sqla as $sqls) {
963
964	if (!$sqls) continue;
965
966	if ($print) {
967	print "<p>".htmlspecialchars($sqls)."</p>";
968	flush();
969	}
970	$savelog = $this->conn->LogSQL(false);
971	$rs = $this->conn->Execute($sqls);
972	$this->conn->LogSQL($savelog);
973	if ($rs && is_object($rs) && !$rs->EOF) {
974	rs2html($rs);
975	while ($rs->NextRecordSet()) {
976	print "<table width=98% bgcolor=#C0C0FF><tr><td> </td></tr></table>";
977	rs2html($rs);
978	}
979	} else {
980	$e1 = (integer) $this->conn->ErrorNo();
981	$e2 = $this->conn->ErrorMsg();
982	if (($e1) \|\| ($e2)) {
983	if (empty($e1)) $e1 = '-1'; // postgresql fix
984	print '   '.$e1.': '.$e2;
985	} else {
986	print "<p>No Recordset returned<br></p>";
987	}
988	}
989	} // foreach
990	}
991
992	function SplitSQL($sql)
993	{
994	$arr = explode(';',$sql);
995	return $arr;
996	}
997
998	function undomq($m)
999	{
1000	if (get_magic_quotes_gpc()) {
1001	// undo the damage
1002	$m = str_replace('\\\\','\\',$m);
1003	$m = str_replace('\"','"',$m);
1004	$m = str_replace('\\\'','\'',$m);
1005	}
1006	return $m;
1007	}
1008
1009
1010	/************************************************************************/
1011
1012	/**
1013	* Reorganise multiple table-indices/statistics/..
1014	* OptimizeMode could be given by last Parameter
1015	*
1016	* @example
1017	* <pre>
1018	* optimizeTables( 'tableA');
1019	* </pre>
1020	* <pre>
1021	* optimizeTables( 'tableA', 'tableB', 'tableC');
1022	* </pre>
1023	* <pre>
1024	* optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW);
1025	* </pre>
1026	*
1027	* @param string table name of the table to optimize
1028	* @param int mode optimization-mode
1029	* <code>ADODB_OPT_HIGH</code> for full optimization
1030	* <code>ADODB_OPT_LOW</code> for CPU-less optimization
1031	* Default is LOW <code>ADODB_OPT_LOW</code>
1032	* @author Markus Staab
1033	* @return Returns <code>true</code> on success and <code>false</code> on error
1034	*/
1035	function OptimizeTables()
1036	{
1037	$args = func_get_args();
1038	$numArgs = func_num_args();
1039
1040	if ( $numArgs == 0) return false;
1041
1042	$mode = ADODB_OPT_LOW;
1043	$lastArg = $args[ $numArgs - 1];
1044	if ( !is_string($lastArg)) {
1045	$mode = $lastArg;
1046	unset( $args[ $numArgs - 1]);
1047	}
1048
1049	foreach( $args as $table) {
1050	$this->optimizeTable( $table, $mode);
1051	}
1052	}
1053
1054	/**
1055	* Reorganise the table-indices/statistics/.. depending on the given mode.
1056	* Default Implementation throws an error.
1057	*
1058	* @param string table name of the table to optimize
1059	* @param int mode optimization-mode
1060	* <code>ADODB_OPT_HIGH</code> for full optimization
1061	* <code>ADODB_OPT_LOW</code> for CPU-less optimization
1062	* Default is LOW <code>ADODB_OPT_LOW</code>
1063	* @author Markus Staab
1064	* @return Returns <code>true</code> on success and <code>false</code> on error
1065	*/
1066	function OptimizeTable( $table, $mode = ADODB_OPT_LOW)
1067	{
1068	ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType));
1069	return false;
1070	}
1071
1072	/**
1073	* Reorganise current database.
1074	* Default implementation loops over all <code>MetaTables()</code> and
1075	* optimize each using <code>optmizeTable()</code>
1076	*
1077	* @author Markus Staab
1078	* @return Returns <code>true</code> on success and <code>false</code> on error
1079	*/
1080	function optimizeDatabase()
1081	{
1082	$conn = $this->conn;
1083	if ( !$conn) return false;
1084
1085	$tables = $conn->MetaTables( 'TABLES');
1086	if ( !$tables ) return false;
1087
1088	foreach( $tables as $table) {
1089	if ( !$this->optimizeTable( $table)) {
1090	return false;
1091	}
1092	}
1093
1094	return true;
1095	}
1096	// end hack
1097	}
1098
1099	?>

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: sandbox/2.5.1-evolucao/phpgwapi/inc/adodb/adodb-perf.inc.php @ 8222

Download in other formats: