File: D:/HostingSpaces/MDalebout3/prdct.nl/wwwroot/timesheet/includes/adodb5/docs/docs-perf.htm
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>ADOdb Performance Monitoring Library</title>
<style type="text/css">
body, td {
/*font-family: Arial, Helvetica, sans-serif;*/
font-size: 11pt;
}
pre {
font-size: 9pt;
background-color: #EEEEEE; padding: .5em; margin: 0px;
}
.toplink {
font-size: 8pt;
}
</style>
</head>
<body>
<h3>The ADOdb Performance Monitoring Library</h3>
<p>V5.06 16 Oct 2008 (c) 2000-2009 John Lim (jlim#natsoft.com)</p>
<p><font size="1">This software is dual licensed using BSD-Style and
LGPL. This means you can use it in compiled proprietary and commercial
products.</font></p>
<p>Useful ADOdb links: <a href="http://adodb.sourceforge.net/#download">Download</a>
<a href="http://adodb.sourceforge.net/#docs">Other Docs</a>
</p>
<h3>Introduction</h3>
<p>This module, part of the ADOdb package, provides both CLI and HTML
interfaces for viewing key performance indicators of your database.
This is very useful because web apps such as the popular phpMyAdmin
currently do not provide effective database health monitoring tools.
The module provides the following: </p>
<ul>
<li>A quick health check of your database server using <code>$perf->HealthCheck()</code>
or <code>$perf->HealthCheckCLI()</code>. </li>
<li>User interface for performance monitoring, <code>$perf->UI()</code>.
This UI displays:
<ul>
<li>the health check, </li>
<li>all SQL logged and their query plans, </li>
<li>a list of all tables in the current database</li>
<li>an interface to continiously poll the server for key
performance indicators such as CPU, Hit Ratio, Disk I/O</li>
<li>a form where you can enter and run SQL interactively.</li>
</ul>
</li>
<li>Gives you an API to build database monitoring tools for a server
farm, for example calling <code>$perf->DBParameter('data cache hit
ratio')</code> returns this very important statistic in a database
independant manner. </li>
</ul>
<p>ADOdb also has the ability to log all SQL executed, using <a
href="docs-adodb.htm#logsql">LogSQL</a>. All SQL logged can be
analyzed through the performance monitor <a href="#ui">UI</a>. In the <i>View
SQL</i> mode, we categorize the SQL into 3 types:
</p>
<ul>
<li><b>Suspicious SQL</b>: queries with high average execution times,
and are potential candidates for rewriting</li>
<li><b>Expensive SQL</b>: queries with high total execution times
(#executions * avg execution time). Optimizing these queries will
reduce your database server load.</li>
<li><b>Invalid SQL</b>: queries that generate errors.</li>
</ul>
<p>Each query is hyperlinked to a description of the query plan, and
every PHP script that executed that query is also shown.</p>
<p>Please note that the information presented is a very basic database
health check, and does not provide a complete overview of database
performance. Although some attempt has been made to make it work across
multiple databases in the same way, it is impossible to do so. For the
health check, we do try to display the following key database
parameters for all drivers:</p>
<ul>
<li><b>data cache size</b> - The amount of memory allocated to the
cache.</li>
<li><b>data cache hit ratio</b> - A measure of how effective the
cache is, as a percentage. The higher, the better.</li>
<li><b>current connections</b> - The number of sessions currently
connected to the database. </li>
</ul>
<p>You will need to connect to the database as an administrator to view
most of the parameters. </p>
<p>Code improvements as very welcome, particularly adding new database
parameters and automated tuning hints.</p>
<a name="usage"></a>
<h3>Usage</h3>
<p>Currently, the following drivers: <em>mysql</em>, <em>postgres</em>,
<em>oci8</em>, <em>mssql</em>, <i>informix</i> and <em>db2</em> are
supported. To create a new performance monitor, call NewPerfMonitor( )
as demonstrated below: </p>
<pre><?php<br>include_once('adodb.inc.php');<br>session_start(); <font
color="#006600"># session variables required for monitoring</font><br>$conn = ADONewConnection($driver);<br>$conn->Connect($server,$user,$pwd,$db);<br>$perf =& NewPerfMonitor($conn);<br>$perf->UI($pollsecs=5);<br>?><br></pre>
<p>It