#!/usr/bin/env php
<?php
/**
*
* Copyright (c) 2008-2012, Perforce Software, Inc. All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL PERFORCE SOFTWARE, INC. BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
* THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*
*
* log_analyzer.php
* ---------------------------
*
* log_analyzer takes a VTRACK log file as input, and produces a
* formatted text report listing the commands with longest compute
* phases, longest database lock times, and so on.
*
* log_analyzer uses sqlite3 as its database, and leaves the
* sqlite3 database file for future reference.
*
* log_analyzer is based on track2sql, with a modified table
* definitions to speed up SQL queries.
*
* Requires the SQLite3 PHP package, which is standard as of PHP 5.3.
*
* See also: http://public.perforce.com/guest/stewart_lord/track2sql/README
*
*
* U S A G E
* -----------
*
* php log_analyzer.php [ logFile ] [ sqlFile ] [ dbFile ] [ outFile ] [stateFile]
*
* Options:
* logFile name of input log file
* sqlFile name of output SQL file
* dbFile name of database file to create
* outFile name of output report file
* stateFile name of state file, useful for repeat runs
* -v|V|h view version and usage information
*
* Examples:
* $ log_analyzer.php log.txt track.sql track.db track.txt track.state
*
*
* O U T P U T
* -------------
*
* The main output is a formatted text file with several
* canned queries.
*
* Also produces an SQL file and database with the following
* tables:
*
* +------------+---------------+------+-----+
* | P R O C E S S |
* +------------+---------------+------+-----+
* | Field | Type | Null | Key |
* +------------+---------------+------+-----+
* | processKey | int | NO | YES |
* | time | int(11) | NO | |
* | pid | int(11) | NO | |
* | user | varchar(255) | NO | |
* | client | varchar(255) | NO | |
* | ip | varchar(255) | NO | |
* | app | varchar(255) | NO | |
* | cmd | varchar(255) | NO | |
* | args | text | YES | |
* | lapse | decimal(10,3) | YES | |
* | uCpu | int(11) | YES | |
* | sCpu | int(11) | YES | |
* | diskIn | int(11) | YES | |
* | diskOut | int(11) | YES | |
* | ipcIn | int(11) | YES | |
* | ipcOut | int(11) | YES | |
* | maxRss | int(11) | YES | |
* | pageFaults | int(11) | YES | |
* | rpcMsgsIn | int(11) | YES | |
* | rpcMsgsOut | int(11) | YES | |
* | rpcSizeIn | int(11) | YES | |
* | rpcSizeOut | int(11) | YES | |
* +------------+---------------+------+-----+
*
* +-------------+--------------+------+-----+
* | T A B L E U S E |
* +-------------+--------------+------+-----+
* | Field | Type | Null | Key |
* +-------------+--------------+------+-----+
* | processKey | int | NO | YES |
* | tableName | varchar(255) | NO | YES |
* | pagesIn | int(11) | YES | |
* | pagesOut | int(11) | YES | |
* | pagesCached | int(11) | YES | |
* | readLocks | int(11) | YES | |
* | writeLocks | int(11) | YES | |
* | getRows | int(11) | YES | |
* | posRows | int(11) | YES | |
* | scanRows | int(11) | YES | |
* | putRows | int(11) | YES | |
* | delRows | int(11) | YES | |
* | readWait | int(11) | YES | |
* | readHeld | int(11) | YES | |
* | writeWait | int(11) | YES | |
* | writeHeld | int(11) | YES | |
* +-------------+--------------+------+-----+
*
*/
ini_set("memory_limit","2G"); // the more the better - just in case
// the sqlite module needs it (it
// shouldn't). ;-)
main( $argv, $argc ); // run it!
/**
* The main function.
* loops over input file - writes to output file.
*
* @param array argv the options given to dictate behavior.
* @param int argc the number of arguments.
*/
function main( $argv, $argc )
{
// relax error reporting to suppress notices.
error_reporting( E_ALL & ~E_NOTICE );
// set default timezone to silence warnings when using date/time funcs.
date_default_timezone_set(@date_default_timezone_get());
// output usage and version information if so instructed.
// consider -v, -V, and -h to be a request for this info.
if ( $argv[1] == "-v" || $argv[1] == "-V" || $argv[1] == "-h" )
{
prt_help();
exit;
}
// parse args:
if ( $argc != 6 ) {
// die("Invalid arguments: run with -h for help\n\n");
prt_help();
exit;
}
$inputFile = $argv[ 1 ];
print "Processing log file $inputFile\n";
$sqlFile = $argv[ 2 ];
print "Writing SQL to file $sqlFile\n";
$dbName = $argv[ 3 ];
print "Creating sqlite3 db in file $dbName\n";
$outFile = $argv[ 4 ];
print "Writing report to file $outFile\n";
$processKeyStateFile = $argv[ 5 ];
if (is_file($processKeyStateFile)) {
try {
$processKey = trim(fgets(fopen($processKeyStateFile, "r")));
} catch (Exception $e) {
echo "Problem reading $processKeyFile: $e";
$processKey = 1; /* default */
}
}
print "Using $processKey as the next \$processKey.\n";
if ($inputFile == '-') {
$inputFile = 'php://stdin';
}
// open the files and test handles.
$in = fopen( $inputFile, 'r' );
$sqlFh = fopen( $sqlFile, 'w' );
$outFh = fopen( $outFile, 'w' );
$htmlFh = fopen( $outFile . ".html", 'w' );
if ( !is_resource( $in ) )
die( "Error: Unable to open input stream.\n" );
if ( !is_resource( $sqlFh ) )
die( "Error: Unable to open SQL output stream.\n" );
if ( !is_resource( $outFh ) )
die( "Error: Unable to open report output stream.\n" );
if ( !is_resource( $htmlFh ) )
die( "Error: Unable to open HTML report output stream.\n" );
// write out the create table statements.
print "Parsing log writing entries to $dbName and $sqlFile...\n\n";
// open database
if (is_file($dbName)) { unlink($dbName); }
$db = new SQLite3($dbName);
exec_log_sql($db, $sqlFh, getCreateStatements());
// SQLite tuning...
exec_log_sql($db, $sqlFh, 'PRAGMA page_size=65536;'); // 64KiB
exec_log_sql($db, $sqlFh, 'PRAGMA cache_size=32000;'); // 2GiB
// no fsync thank you very much
exec_log_sql($db, $sqlFh, 'PRAGMA synchronous=0;');
// not writing (slow) journal
exec_log_sql($db, $sqlFh, 'PRAGMA journal_mode=OFF;');
// do all inserts in a (possibly huge) transaction!
exec_log_sql($db, $sqlFh, 'BEGIN TRANSACTION;');
// start reading lines - recognize 'info' blocks, buffer and parse them.
while ( $line = fgets( $in ) )
{
// Some P4V sends version strings with NULL chars in places
// where slashes should be used, we are working around it by
// replacing all NULL chars to slashes. ;-)
// See job037665 for details.
$line = str_replace("\0", "/", $line);
// if not inside info block, look for start of block.
if ( !$inInfo )
{
if ( isInfoStart( $line ) )
$inInfo = TRUE;
}
// if inside info block, buffer and look for end of block.
else if ( $inInfo )
{
if ( isInfoEnd( $line ) )
{
$sql = parseInfo( $buffer, $processKey++ );
if ( $sql ) {
exec_log_sql($db, $sqlFh, $sql);
}
if ( !isInfoStart( $line ) )
$inInfo = FALSE;
$buffer = NULL;
} else {
$buffer .= $line;
}
}
}
print "Comitting... ";
exec_log_sql( $db, $sqlFh, 'COMMIT;');
print "Done!\n";
// write out state file
fwrite(fopen($processKeyStateFile, "w"), "$processKey\n");
// create index
print "Creating index on table process...\n";
exec_log_sql($db, $sqlFh,
'CREATE INDEX IF NOT EXISTS process_key_idx ON process (processKey);');
print "Creating index on table tableUse... ";
exec_log_sql($db, $sqlFh,
'CREATE INDEX IF NOT EXISTS tableuse_key_idx ON
tableUse (processKey);');
exec_log_sql($db, $sqlFh,
'CREATE INDEX IF NOT EXISTS tableuse_key_idx ON
tableUse (tableName);');
print "Done!\n";
// close input and sql files
fclose( $in );
fclose( $sqlFh );
// run queries
print "Running queries...\n\n";
fwrite($htmlFh,"<html>\n<head>\n</head>\n");
fwrite($htmlFh, "<style TYPE='text/css'>\n" .
"table {\n\t" .
"border:1px solid;\n" .
"border-collapse:collapse;\n" .
"}\n" .
"table td {\n\t" .
"border:1px solid;\n" .
"background-color:white;\n" .
"}\n" .
"table th {\n\t" .
"border:1px solid;\n" .
"background-color:blue;\n" .
"color:white;\n" .
"}\n" .
"</style>\n");
fwrite($htmlFh, "<body>\n");
fwrite($htmlFh, "<a href='#avgwait'>Average Wait</a><br>\n");
fwrite($htmlFh, "<a href='#readpct'>Read/Write Percentage</a><br>\n");
fwrite($htmlFh, "<a href='#startend'>Start/End Time</a><br>\n");
fwrite($htmlFh, "<a href='#compute'>Longest Compute Phases</a><br>\n");
fwrite($htmlFh, "<a href='#mostio'>Most I/O</a><br>\n");
fwrite($htmlFh, "<a href='#held'>Longest Lock Hold Times</a><br>\n");
rpt_avg_wait($db,$outFh,$htmlFh);
rpt_read_pct($db,$outFh,$htmlFh);
rpt_start_end($db,$outFh,$htmlFh);
rpt_long_computes($db,$outFh,$htmlFh);
rpt_most_io($db,$outFh,$htmlFh);
rpt_held_time($db,$outFh,$htmlFh);
// close output file and db
print "Done!\n\n";
fwrite($htmlFh,"</body>\n</html>");
fclose( $outFh );
fclose( $htmlFh );
$db->close();
}
/**
* Execute SQL and write it to a file handler
*/
function exec_log_sql($db, $sqlFh, $sql) {
/* echo $sql; */
fwrite( $sqlFh, $sql."\n");
$db->exec($sql);
}
/**
* Print command help
*/
function prt_help()
{
$help = "\nlog_analyzer revision " . '$Change: 7621 $';
$help .= "\n" . str_repeat( "-", strlen( $help ) ) . "\n\n";
$help .= "log_analyzer takes a VTRACK log file (server version 2005.2 \n" .
"or greater) as input, and produces an SQL file, sqlite3 database, \n" .
"and a formatted text report as output.\n" .
"For more information, such as the schema and example queries,\n" .
"visit: \n\n " .
"http://public.perforce.com/guest/stewart_lord/track2sql/README\n\n" .
"Usage: \n " .
"php log_analyzer.php [ logFile ] [ sqlFile ] [ dbFile ] [ outFile ] [ stateFile ]\n\n" .
" Options: \n" .
" logFile name of input log file \n" .
" sqlFile name of output SQL file \n" .
" dbFile name of database file to create \n" .
" outFile name of output report file \n" .
" stateFile name of output state file, useful for repeat runs \n" .
" -v|V|h view version and usage information \n\n" .
" Examples: \n" .
" $ track2sql.php log.txt track.sql track.db track.txt track.state\n\n";
echo $help;
}
/**
* Runs query for 25 longest compute phases
*/
function rpt_long_computes($db,$outFh,$htmlFh)
{
$sql = "
SELECT
process.processKey,
process.client,
process.args,
user,
cmd,
datetime(time, 'unixepoch') AS time,
CASE WHEN MAX( readHeld + writeHeld ) > MAX( readWait + writeWait ) THEN
MAX( readHeld + writeHeld ) - MAX( readWait + writeWait )
ELSE
MAX( readHeld + writeHeld )
END
AS compute
FROM tableUse JOIN process USING (processKey)
GROUP BY tableUse.processKey
ORDER BY compute DESC LIMIT 25";
// execute the sql statement
$results = $db->query( $sql );
fwrite( $outFh, "------------------------------------------------------------\n");
fwrite( $outFh, "LONGEST COMPUTE PHASES\n");
fwrite( $outFh, sprintf("%20s %30s %30s %25s %15s\n", "user", "client", "command", "date", "compute (ms)"));
fwrite( $htmlFh, "<h1>LONGEST COMPUTE PHASES</h1><a name='compute'></a>\n");
fwrite( $htmlFh, "<table>\n<tr>\n<th>user</th>\n<th>client</th>\n<th>command</th>\n<th>date</th>\n");
fwrite( $htmlFh, "<th>compute (ms)</th>\n<th>args</th>\n<th>process</th>\n</tr>\n");
while ($row = $results->fetchArray(SQLITE3_NUM)) {
fwrite( $outFh, sprintf("%20s %30s %30s %25s %15.1f\n", $row[3], $row[1], $row[4], $row[5], $row[6]));
fwrite( $htmlFh, "<tr>\n");
fwrite( $htmlFh, "<td>$row[3]</td>\n");
fwrite( $htmlFh, "<td>$row[1]</td>\n");
fwrite( $htmlFh, "<td>$row[4]</td>\n");
fwrite( $htmlFh, "<td>$row[5]</td>\n");
fwrite( $htmlFh, "<td>$row[6]</td>\n");
fwrite( $htmlFh, "<td>$row[2]</td>\n");
fwrite( $htmlFh, "<td>$row[0]</td>\n");
fwrite( $htmlFh, "</tr>\n");
}
fwrite( $htmlFh, "</table>\n");
fwrite( $outFh, "------------------------------------------------------------\n\n\n");
}
/**
* Runs query for most i/o
*/
function rpt_most_io($db,$outFh,$htmlFh)
{
$sql = "
SELECT
user,cmd,SUM(pagesIn+pagesOut) as io, process.processKey, process.client, process.args
FROM tableUse JOIN process USING (processKey)
GROUP BY tableUse.processKey ORDER BY io
DESC LIMIT 25";
// execute the sql statement
$results = $db->query( $sql );
fwrite( $outFh, "------------------------------------------------------------\n");
fwrite( $outFh, "MOST I/O\n");
fwrite( $outFh, sprintf("%20s %30s %30s %12s\n", "user", "client", "command", "pages"));
fwrite( $htmlFh, "<h1>MOST I/O</h1><a name='mostio'></a>\n");
fwrite( $htmlFh, "<table>\n<tr>\n<th>user</th>\n<th>client</th>\n<th>command</th>\n");
fwrite( $htmlFh, "<th>pages</th>\n<th>args</th>\n<th>process</th>\n</tr>\n");
while ($row = $results->fetchArray(SQLITE3_NUM)) {
fwrite( $outFh, sprintf("%20s %30s %30s %12d\n", $row[0], $row[4], $row[1], $row[2]));
fwrite( $htmlFh, "<tr>\n");
fwrite( $htmlFh, "<td>$row[0]</td>\n");
fwrite( $htmlFh, "<td>$row[4]</td>\n");
fwrite( $htmlFh, "<td>$row[1]</td>\n");
fwrite( $htmlFh, "<td>$row[2]</td>\n");
fwrite( $htmlFh, "<td>$row[5]</td>\n");
fwrite( $htmlFh, "<td>$row[3]</td>\n");
fwrite( $htmlFh, "</tr>\n");
}
fwrite( $htmlFh, "</table>\n");
fwrite( $outFh, "------------------------------------------------------------\n\n\n");
}
/**
* Runs query for average wait time
*/
function rpt_avg_wait($db,$outFh,$htmlFh)
{
$sql = "
SELECT
AVG(readWait+writeWait) as wait FROM tableUse";
// execute the sql statement
$results = $db->query( $sql );
fwrite( $outFh, "------------------------------------------------------------\n");
fwrite( $outFh, "Average wait (ms)\n");
fwrite( $htmlFh, "<h1>Average wait (ms)</h1><a name='avgwait'></a>\n");
while ($row = $results->fetchArray(SQLITE3_NUM)) {
fwrite( $outFh, sprintf("%f\n", $row[0]));
fwrite( $htmlFh, "<br><b>$row[0]</b>\n");
}
fwrite( $outFh, "------------------------------------------------------------\n\n\n");
}
/**
* Runs query for read/write percentage
*/
function rpt_read_pct($db,$outFh,$htmlFh)
{
$sql = "
SELECT
CAST(SUM(pagesIn) as float)/CAST(SUM(pagesIn+pagesOut) as float) * CAST(100 as float) as readPct,
CAST(SUM(pagesOut) as float)/CAST(SUM(pagesIn+pagesOut) as float) * CAST(100 as float) as writePct
FROM tableUse";
// execute the sql statement
$results = $db->query( $sql );
fwrite( $outFh, "------------------------------------------------------------\n");
fwrite( $outFh, "Read/write percentage\n");
fwrite( $htmlFh, "<h1>Read/write percentage</h1><a name='readpct'></a>\n");
while ($row = $results->fetchArray(SQLITE3_NUM)) {
fwrite( $outFh, sprintf("%f\t%f\n", $row[0],$row[1]));
fwrite( $htmlFh, "<br><b>$row[0]</b><br><b>$row[1]</b>\n");
}
fwrite( $outFh, "------------------------------------------------------------\n\n\n");
}
/**
* Runs query for 25 longest lock holds
*/
function rpt_held_time($db,$outFh,$htmlFh)
{
$sql = "
SELECT
user,SUM(readHeld+writeHeld) as held
FROM tableUse JOIN process USING (processKey)
GROUP BY user ORDER BY held DESC LIMIT 25";
// execute the sql statement
$results = $db->query( $sql );
fwrite( $outFh, "------------------------------------------------------------\n");
fwrite( $outFh, "LONGEST LOCK HOLD TIMES\n");
fwrite( $outFh, sprintf("%30s %15s\n", "user", "hold time (ms)"));
fwrite( $htmlFh, "<h1>LONGEST LOCK HOLD TIMES</h1><a name='held'></a>\n");
fwrite( $htmlFh, "<table>\n<tr>\n<th>user</th>\n<th>hold time (ms)</th>\n</tr>\n");
while ($row = $results->fetchArray(SQLITE3_NUM)) {
fwrite( $outFh, sprintf("%30s %15.1f\n", $row[0], $row[1]));
fwrite( $htmlFh, "<tr>\n");
fwrite( $htmlFh, "<td>$row[0]</td>\n");
fwrite( $htmlFh, "<td>$row[1]</td>\n");
fwrite( $htmlFh, "</tr>\n");
}
fwrite( $htmlFh, "</table>\n");
fwrite( $outFh, "------------------------------------------------------------\n\n\n");
}
/**
* Runs query for log start/end
*/
function rpt_start_end($db,$outFh,$htmlFh)
{
$sql = "
SELECT
datetime(MIN(time), 'unixepoch') AS start,
datetime(MAX(time), 'unixepoch') AS end FROM process";
// execute the sql statement
$results = $db->query( $sql );
fwrite( $outFh, "------------------------------------------------------------\n");
fwrite( $outFh, "Start/end time\n");
fwrite( $htmlFh, "<h1>Start/end time</h1><a name='startend'></a>\n");
while ($row = $results->fetchArray(SQLITE3_NUM)) {
fwrite( $outFh, sprintf("%s\t%s\n", $row[0],$row[1]));
fwrite( $htmlFh, "<br><b>$row[0]</b><br><b>$row[1]</b>\n");
}
fwrite( $outFh, "------------------------------------------------------------\n\n\n");
}
/**
* Quick test for the beginning of an info block.
*
* @param string line the line to test.
* @return bool the result of the test.
*/
function isInfoStart( $line )
{
if ( ( $line === "Perforce server info:\n" ) or
( $line === "Perforce server info:\r\n" ) )
return TRUE;
}
/**
* Quick test for the end of an info block.
*
* @param string line the line to test.
* @return bool the result of the test.
*/
function isInfoEnd( $line )
{
if ( !trim( $line ) || isInfoStart( $line ) )
return TRUE;
}
/**
* Extract track information from info block.
*
* @param string info the info block to parse.
*/
function parseInfo( $info, $processId )
{
// early exit if not track output.
if ( !isTrackOutput( $info ) ) return;
// lcheung - uuid is good however it's slow(er) to use a string as
// primary key
/* // manufacture a key for joining process */
/* // and table records. */
/* $key = quote( makeProcessKey() ); */
$key = $processId; /* so we use an integer instead. ;-) */
// build the process record.
$processInfo = extractProcessInfo( $info );
$sql =
"INSERT INTO process " .
"VALUES ( " . $key . "," . implode( ",", $processInfo ) . " );\n";
// build the table records.
$tablesInfo = extractTablesInfo( $info );
if ( !is_array( $tablesInfo ) ) return $sql;
foreach ( $tablesInfo as $tableInfo )
{
$sql .=
"INSERT INTO tableUse " .
"VALUES ( " . $key . "," . implode( ",", $tableInfo ) . ");\n";
}
// return the sql.
return $sql;
}
/**
* Test if the given string looks like vtrack output
* with a complete process information line.
*
* @param string info the info block to test.
* @return bool true if the info block appears to be usable track output.
*/
function isTrackOutput( $info )
{
return ( strpos( $info, "\n--- " ) !== FALSE &&
strpos( substr( $info, 0, strpos( $info, "\n" ) ), "@" ) !== FALSE );
}
/**
* Make a unique key (UUID) for joining processes and table use records.
*
* @return string a UUID to use as a process key.
*/
function makeProcessKey()
{
$chars = md5( uniqid( mt_rand(), TRUE ) );
$uuid = substr( $chars, 0, 8 ) . '-';
$uuid .= substr( $chars, 8, 4 ) . '-';
$uuid .= substr( $chars, 12, 4 ) . '-';
$uuid .= substr( $chars, 16, 4 ) . '-';
$uuid .= substr( $chars, 20, 12 );
return $uuid;
}
/**
* Take a vtrack block, and extract process information.
* Resulting array contains 21 elements (quoted where appropriate).
*
* @param string track the vtrack output to parse.
* @return array the exploded process information.
*/
function extractProcessInfo( $track )
{
// break into lines.
$lines = explode( "\n", $track );
// pull apart the first line.
$parts = explode( " ", trim( $lines[0] ), 7 );
list( $user, $client ) = explode( "@", $parts[4], 2 );
list( $app, $command ) = explode( "] '", $parts[6], 2 );
list( $cmd, $args ) = explode( " ", $command, 2 );
$process[] = strtotime( $parts[0] . " " . $parts[1] );
$process[] = intval( $parts[3] );
$process[] = quote( trim( $user ) );
$process[] = quote( trim( $client ) );
$process[] = quote( trim( $parts[5] ) );
$process[] = quote( substr( $app, 1 ) );
if ( $args )
{
$process[] = quote( $cmd );
$process[] = quote( substr( $args, 0, -1 ) );
} else {
$process[] = quote( substr( $cmd, 0, -1 ) );
$process[] = "NULL";
}
// search the next three lines for lapse, usage and rpc.
// -> only first could be lapse.
// -> first or second could be usage.
// -> anyone could be rpc.
for ( $i = 1; ( $i < 4 ) && ( $i < count( $lines ) ); $i++ )
{
if ( $i == 1 && substr( $lines[ $i ], 0, 10 ) == "--- lapse " )
$lapseTime = trim( $lines[ $i ] );
else if ( $i < 3 && substr( $lines[ $i ], 0, 10 ) == "--- usage " )
$usageStats = trim( $lines[ $i ] );
else if ( substr( $lines[ $i ], 0, 8 ) == "--- rpc " )
$rpcStats = trim( $lines[ $i ] );
}
// insert lapse time into process info array - or pad with 'NULL'.
if ( $lapseTime )
{
$process[] = floatval( substr( $lapseTime, 10, -1 ) );
} else {
$process[] = "NULL";
}
// insert usage stats into process info array - or pad with 'NULL'.
if ( $usageStats )
{
$parts = preg_split( "/ |\+/", $usageStats );
for ( $i = 2; $i < 10; $i++ )
$process[] = intval( $parts[ $i ] );
} else {
for ( $i = 0; $i < 8; $i++ )
$process[] = "NULL";
}
// insert rcp stats into process info array - or pad with 'NULL'.
if ( $rpcStats )
{
$parts = preg_split( "/ |\+|\//", $rpcStats );
for ( $i = 6; $i < 10; $i++ )
$process[] = intval( $parts[ $i ] );
} else {
for ( $i = 0; $i < 4; $i++ )
$process[] = "NULL";
}
// return process info array.
return $process;
}
/**
* Take a vtrack block, and extract tables usage information.
*
* @param string track the vtrack output to parse.
* @return array the exploded tables information.
*/
function extractTablesInfo( $track )
{
// break apart track output on tables.
$blocks = explode( "--- db.", $track );
for ( $i = 1; $i < count( $blocks ); $i++ )
{
$tables[] = extractTableInfo( $blocks[ $i ] );
}
return $tables;
}
/**
* Take a snippet of vtrack, and extract single table usage information.
* Returns an array containing 15 elements.
*
* @param string track the vtrack output to parse.
* @return array the exploded table information.
*/
function extractTableInfo( $track )
{
// break into lines.
$lines = explode( "\n", $track );
// first line is table name.
$table[] = "'" . trim( $lines[0] ) . "'";
// search the next four lines for pages, rows and locks.
// -> only first could be pages.
// -> first, second or third could be rows.
// -> anyone could be locks.
for ( $i = 1; ( $i < 5 ) && ( $i < count( $lines ) ); $i++ )
{
if ( $i == 1 && substr( $lines[ $i ], 0, 14 ) == "--- pages in" )
$pageStats = trim( $lines[ $i ] );
else if ( $i < 4 && substr( $lines[ $i ], 0, 12 ) == "--- locks " &&
substr( $lines[ $i ], 0, 16 ) != "--- locks wait" )
$rowStats = trim( $lines[ $i ] );
else if ( substr( $lines[ $i ], 0, 16 ) == "--- locks wait" )
$lockStats = substr( $lines[ $i ], 33 );
else if ( substr( $lines[ $i ], 0, 16 ) == "--- total lock" )
$lockStats = substr( $lines[ $i ], 38 );
}
// insert page stats into table info array - or pad with 'NULL'.
if ( $pageStats )
{
$parts = preg_split( "/[ |\+]+/", $pageStats );
for ( $i = 5; $i < 8; $i++ )
$table[] = intval( $parts[ $i ] );
} else {
for ( $i = 0; $i < 3; $i++ )
$table[] = "NULL";
}
// insert row stats into table info array - or pad with 'NULL'.
if ( $rowStats )
{
// switch for server version.
// -> 14 parts in 2005.2 track output.
// *read/write locks aren't differentiated.
// -> 17 parts in 2006.1+ track output.
// *read/write locks are broken out.
$parts = preg_split( "/[ |\/|\+]+/", $rowStats );
if ( count( $parts ) == 14 )
{
$table[] = intval( $parts[2] );
$table[] = intval( $parts[2] );
for ( $i = 9; $i < 14; $i++ )
$table[] = intval( $parts[ $i ] );
} else {
$table[] = intval( $parts[4] );
$table[] = intval( $parts[5] );
for ( $i = 12; $i < 17; $i++ )
$table[] = intval( $parts[ $i ] );
}
} else {
for ( $i = 0; $i < 7; $i++ )
$table[] = "NULL";
}
// insert lock stats into table info array - or pad with 'NULL'.
if ( $lockStats )
{
$parts = preg_split( "/[ |\/|\+]+/", $lockStats );
for ( $i = 0; $i < 4; $i++ )
$table[] = intval( $parts[ $i ] );
} else {
for ( $i = 0; $i < 4; $i++ )
$table[] = "NULL";
}
// return table info array.
return $table;
}
/**
* Create process and tableUse tables.
*
* @return string the sql to create the tables.
*/
function getCreateStatements()
{
// create syntax for process table.
$sql =
"CREATE TABLE IF NOT EXISTS process ( \n" .
" processKey INTEGER PRIMARY KEY, \n" .
" time INTEGER NOT NULL, \n" .
" pid INTEGER NOT NULL, \n" .
" user varchar(255) NOT NULL, \n" .
" client varchar(255) NOT NULL, \n" .
" ip varchar(255) NOT NULL, \n" .
" app varchar(255) NOT NULL, \n" .
" cmd varchar(255) NOT NULL, \n" .
" args text NULL, \n" .
" lapse decimal(10,3) NULL, \n" .
" uCpu INTEGER NULL, \n" .
" sCpu INTEGER NULL, \n" .
" diskIn INTEGER NULL, \n" .
" diskOut INTEGER NULL, \n" .
" ipcIn INTEGER NULL, \n" .
" ipcOut INTEGER NULL, \n" .
" maxRss INTEGER NULL, \n" .
" pageFaults INTEGER NULL, \n" .
" rpcMsgsIn INTEGER NULL, \n" .
" rpcMsgsOut INTEGER NULL, \n" .
" rpcSizeIn INTEGER NULL, \n" .
" rpcSizeOut INTEGER NULL \n" .
/* " PRIMARY KEY ( processKey ) \n" . */
"); \n";
// create syntax for tableUse table.
$sql .=
"CREATE TABLE IF NOT EXISTS tableUse ( \n" .
" processKey INTEGER, \n" .
" tableName varchar(255) NOT NULL, \n" .
" pagesIn INTEGER NULL, \n" .
" pagesOut INTEGER NULL, \n" .
" pagesCached INTEGER NULL, \n" .
" readLocks INTEGER NULL, \n" .
" writeLocks INTEGER NULL, \n" .
" getRows INTEGER NULL, \n" .
" posRows INTEGER NULL, \n" .
" scanRows INTEGER NULL, \n" .
" putRows INTEGER NULL, \n" .
" delRows INTEGER NULL, \n" .
" readWait INTEGER NULL, \n" .
" readHeld INTEGER NULL, \n" .
" writeWait INTEGER NULL, \n" .
" writeHeld INTEGER NULL \n" .
/* " PRIMARY KEY ( processKey, tableName ) \n" . */
// not having primary key no key constraint checks!
"); \n";
return $sql;
}
/**
* Quote a string for use as a SQL value. Wraps string in
* single quotes and escapes embedded single quotes with a quote.
*
* @param string value the string to quote.
* @return string the quoted and escaped string.
*/
function quote( $value )
{
return "'" . str_replace( "'", "''", $value ) . "'";
}
?>