#!/usr/bin/env php * * See "php log_analyzer.php -h" for details. * * Examples: * $ zcat log.gz | log_analyzer.php -b logrpt - * * * 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 ) { // Defaults $cfg = array('sql' => false, 'force' => false, 'append' => false, 'basename' => 'logrpt' // prefix for auto-generated output files. ); // 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('UTC'); $shortopts = 'hqfab:s:d:o:H:S:'; $operand_pos = 1; foreach ( getopt($shortopts) as $opt => $val) { switch ($opt) { case 'q': $cfg['quiet'] = true; $operand_pos++; break; case 'f': $cfg['force'] = true; $operand_pos++; break; case 'a': $cfg['append'] = true; $operand_pos++; break; case 'b': $cfg['basename'] = $val; $operand_pos += 2; break; case 's': $cfg['sql'] = $val; $operand_pos += 2; break; case 'd': $cfg['db'] = $val; $operand_pos += 2; break; case 'o': $cfg['rpt'] = $val; $operand_pos += 2; break; case 'H': $cfg['rpt.html'] = $val; $operand_pos += 2; break; case 'S': $cfg['statefile'] = $val; $operand_pos += 2; break; case 'h': $printHelp = true; $operand_pos++; break; } } $cfg['logfiles'] = array_slice($argv, $operand_pos); if(!isset($cfg['db'])) { $cfg['db'] = $cfg['basename'].'.db'; } if(!isset($cfg['rpt'])) { $cfg['rpt'] = $cfg['basename'].'.txt'; } if(!isset($cfg['rpt.html'])) { $cfg['rpt.html'] = $cfg['basename'].'.html'; } if(!isset($cfg['state'])) { $cfg['state'] = $cfg['basename'].'.state'; } if($printHelp) { help($cfg); } // remove output files if exists if ($cfg['force']) { if (is_file($cfg['db'])) unlink($cfg['db']); if (is_file($cfg['rpt'])) unlink($cfg['rpt']); if (is_file($cfg['rpt.html'])) unlink($cfg['rpt.html']); if (is_file($cfg['state'])) unlink($cfg['state']); } if (is_file($cfg['state'])) { try { $processKey = trim(fgets(fopen($cfg['state'], "r"))); } catch (Exception $e) { echo "Problem reading $statefile, using 1 instead. ($e)"; $processKey = 1; /* default */ } } else { $processKey = 1; } if (!$cfg['quiet']) echo "Using $processKey as the next \$processKey.\n"; $sqlfile = $cfg['sql']; $opt_fb_text = " Use the -f option to overwrite it or use the -b option to specify a different prefix.\n"; if ( is_file($sqlfile) && !$cfg['force'] ) { die ("$sqlfile exists!".$opt_fb_text); } if ($sqlfile && !$cfg['quiet']) { echo "Writing SQL to file $sqlfile\n"; } $dbfile = $cfg['db']; if ( is_file($dbfile) && !$cfg['append'] ) { die ("$dbfile exists! Either remove the file or append to the file with the -a option.\n"); } if (!$cfg['quiet']) echo "Creating/writing to sqlite3 db ", $dbfile, ".\n"; $rptfile = $cfg['rpt']; $htmlrptfile = $cfg['rpt.html']; if ( (is_file($rptfile) || is_file($htmlrptfile)) && !$cfg['force']) { die ("Either $rptfile or $htmlrptfile exists!".$opt_fb_text); } if (!$cfg['quiet']) echo "Writing report to $rptfile and $htmlrptfile.\n"; $sqlfile = $cfg['sql']; if ( is_file($sqlfile) && !$cfg['force']) { die ("$sqlfile exists!".$opt_fb_text); } if ($sqlfile) { $sqlFh = fopen( $sqlfile, 'a' ); if ( !is_resource( $sqlFh ) ) die( "Error: Unable to open SQL output stream.\n" ); } else { $sqlFh = false; } // open database $db = new SQLite3($dbfile); // SQLite tuning... exec_log_sql($db, $sqlFh, 'PRAGMA page_size=65536;'); // 64KiB - max page size exec_log_sql($db, $sqlFh, 'PRAGMA cache_size=-2097152;'); // 2GiB cache exec_log_sql($db, $sqlFh, 'PRAGMA synchronous=0;'); // no fsync thank you very much exec_log_sql($db, $sqlFh, 'PRAGMA temp_store = MEMORY;'); // avoid on disk temp storage exec_log_sql($db, $sqlFh, 'PRAGMA journal_mode=OFF;'); // not writing (slow) journal // write out the create table statements. exec_log_sql($db, $sqlFh, getCreateStatements()); // Set LOCALE for numbers with ',' thousands separator setlocale(LC_NUMERIC, ''); // the big loop..... foreach($cfg['logfiles'] as $logfile) { if ($logfile == '-') { $logfile = 'php://stdin'; } $processKey = ingest($logfile, $db, $sqlFh, $processKey, $cfg); } $outFh = fopen( $cfg['rpt'], 'w' ); $htmlFh = fopen( $cfg['rpt.html'], 'w' ); 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 state file fwrite(fopen($cfg['state'], "w"), "$processKey\n"); // create index if (!$cfg['append']) { if (!$cfg['quiet']) print "Creating index on table process...\n"; exec_log_sql($db, $sqlFh, 'CREATE INDEX IF NOT EXISTS id_p_process_key ON process (processKey);'); if (!$cfg['quiet']) print "Creating index on table tableUse... "; exec_log_sql($db, $sqlFh, 'CREATE INDEX IF NOT EXISTS id_t_processkey ON tableUse (processKey);'); exec_log_sql($db, $sqlFh, 'CREATE INDEX IF NOT EXISTS idx_t_tablename ON tableUse (tableName);'); exec_log_sql($db, $sqlFh, 'CREATE INDEX IF NOT EXISTS idx_t_readheld ON tableUse (readHeld);'); exec_log_sql($db, $sqlFh, 'CREATE INDEX IF NOT EXISTS idx_t_writeheld ON tableUse (writeHeld);'); } if (!$cfg['quiet']) echo "Done!\n"; if (is_resource( $sqlFh ) ) fclose( $sqlFh ); // run queries if (!$cfg['quiet']) echo "Running queries...\n\n"; fwrite($htmlFh,"\n\n\n"); fwrite($htmlFh, "\n"); fwrite($htmlFh, "\n"); fwrite($htmlFh, "Average Wait
\n"); fwrite($htmlFh, "Read/Write Percentage
\n"); fwrite($htmlFh, "Start/End Time
\n"); fwrite($htmlFh, "Longest Compute Phases
\n"); fwrite($htmlFh, "Most I/O
\n"); fwrite($htmlFh, "Longest Lock Hold Times
\n"); fwrite($htmlFh, "Peak hours
\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); rpt_peak_hours($db,$outFh,$htmlFh); // close output file and db if (!$cfg['quiet']) print "Done!\n\n"; fwrite($htmlFh,"\n"); fclose( $outFh ); fclose( $htmlFh ); if ( is_resource($sqlFh) ) { fclose($sqlFh); } $db->close(); } function ingest($logfile, &$db, &$sqlFh, $processKey, &$cfg) { $started = time(); // open the files and test handles. $in = gzopen( $logfile, 'r' ); if ( !is_resource( $in ) ) die( "Error: Unable to open $logfile.\n" ); if (!$cfg['quiet']) echo "Processing $logfile... "; // 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 = gzgets( $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; } } } fclose( $in ); if (!$cfg['quiet']) print "committing... "; exec_log_sql( $db, $sqlFh, 'COMMIT;'); if (!$cfg['quiet']) echo "took ", time()-$started," seconds.\n"; return $processKey; } /** * Execute SQL and write it to a file handler */ function exec_log_sql($db, &$sqlFh, $sql) { if ($sqlFh) fwrite( $sqlFh, $sql."\n"); $db->exec($sql); } /** * Print command help */ function help($cfg) { $basename = $cfg['basename']; $db = $cfg['db']; $rpt = $cfg['rpt']; $htmlrpt = $cfg['rpt.html']; $sql = $cfg['sql']; $state = $cfg['state']; die(" Perforce Server Log Analyzer - a tool based on TRACK2SQL. Usage: $argv[0] [OPTION]... < logfile|- > [logs...] -h: this help. -q: be quiet. -f: overwrites any existing output files. -a: appends data to the SQLite database. -b: prefix used to auto-generate output names below. Defaults to '$basename'. -d: name of the generated SQLite3 database. Defaults to '$db'. -o: name of generated report, in plain-text. Defaults to '$rpt'. -H: name of generated report, in HTML. Defaults to '$htmlrpt'. -s: writes generated SQL to file. Not created by default. -S: name of a file that keeps the last processKey used. Defaults to '$state'. Note that combining options (like \"-qb foo\") is not supported. Patch welcome! Examples: log_analyzer.php -b logrpt log0.gz log1.gz xzcat log.xz log1.xz| log_analyzer.php -b logrpt - "); } /** * Runs query for 25 longest compute phases */ function rpt_long_computes($db,$outFh,$htmlFh) { $sec = ""; $sql = " SELECT process.processKey, process.client, process.args, user, cmd, datetime(time, 'unixepoch') AS time, process.pid, 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 %7s\n", "user", "client", "command", "date", "compute (secs)", "pid")); fwrite( $htmlFh, "

LONGEST COMPUTE PHASES

\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n\n\n\n\n\n\n\n\n"); fwrite( $htmlFh, "\n\n\n\n\n"); fwrite( $htmlFh, "\n\n\n\n"); while ($row = $results->fetchArray(SQLITE3_NUM)) { $sec = number_format($row[7]/1000, 3, ".", ","); fwrite( $outFh, sprintf("%20s %30s %30s %25s %15s %6d\n", $row[3], $row[1], $row[4], $row[5], $sec, $row[6])); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); } fwrite( $htmlFh, "
userclientcommanddatecompute (secs)argspid
$row[3]$row[1]$row[4]$row[5]$sec$row[2]$row[6]
\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, "

MOST I/O

\n"); fwrite( $htmlFh, "\n\n\n\n\n"); fwrite( $htmlFh, "\n\n\n\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, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); } fwrite( $htmlFh, "
userclientcommandpagesargsprocess
$row[0]$row[4]$row[1]$row[2]$row[5]$row[3]
\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, "

Average wait (ms)

\n"); while ($row = $results->fetchArray(SQLITE3_NUM)) { fwrite( $outFh, sprintf("%f\n", $row[0])); fwrite( $htmlFh, "
$row[0]\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, "

Read/write percentage

\n"); while ($row = $results->fetchArray(SQLITE3_NUM)) { fwrite( $outFh, sprintf("%f\t%f\n", $row[0],$row[1])); fwrite( $htmlFh, "
$row[0]
$row[1]\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, "

LONGEST LOCK HOLD TIMES

\n"); fwrite( $htmlFh, "\n\n\n\n\n"); while ($row = $results->fetchArray(SQLITE3_NUM)) { fwrite( $outFh, sprintf("%30s %15.1f\n", $row[0], $row[1])); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); fwrite( $htmlFh, "\n"); } fwrite( $htmlFh, "
userhold time (ms)
$row[0]$row[1]
\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, MAX(time)-MIN(time) FROM process"; // execute the sql statement $results = $db->query( $sql ); fwrite( $outFh, "------------------------------------------------------------\n"); fwrite( $outFh, "Start/end time\n"); fwrite( $htmlFh, "

Start/end time

\n"); fwrite( $htmlFh, "\n"); while ($row = $results->fetchArray(SQLITE3_NUM)) { fwrite( $outFh, sprintf("%s\t%s\n", $row[0],$row[1])); // FIXME :) $lapsed = $row[2]; $unit = 'second(s)'; if ($lapsed/60 >= 1) { $lapsed = $lapsed/60; $unit = 'minute(s)'; if ($lapsed/60 >= 1) { $lapsed = $lapsed/60; $unit = 'hour(s)'; if ($lapsed/24>=1) { $lapsed = $lapsed/24; $unit = 'day(s)'; if ($lapsed/7>=1) { $lapsed = $lapsed/7; $unit = 'week(s)'; } } } } fwrite( $htmlFh, "\n"); } fwrite( $htmlFh, "
startedendedlapsed
$row[0]$row[1]$lapsed $unit
\n" ); fwrite( $outFh, "------------------------------------------------------------\n\n\n"); } /** * Report the top 20 peak hours */ function rpt_peak_hours($db, $outFh, $htmlFh) { // FXIME!!! $sql = " SELECT datetime(time/3600*3600, 'unixepoch'), count(*) FROM process GROUP BY datetime(time/3600*3600, 'unixepoch') HAVING count(*) IN (SELECT count(*) FROM process GROUP BY datetime(time/3600*3600, 'unixepoch') ORDER BY count(*) DESC LIMIT 20) ORDER BY datetime(time/3600*3600, 'unixepoch') ; "; $results = $db->query( $sql ); fwrite( $outFh, "------------------------------------------------------------\n" ); fwrite( $outFh, "Top 20 peak hours\n" ); fwrite( $htmlFh, "

Top 20 peak hours

\n" ); fwrite( $htmlFh, "\n" ); while ($row = $results->fetchArray(SQLITE3_NUM)) { fwrite( $outFh, sprintf("%s\t%s\n", $row[0], $row[1]) ); fwrite( $htmlFh, "" ); } fwrite( $htmlFh, "
hourcommands
$row[0]$row[1]
\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; } /** * Debug print string */ function dprint ( $str ) { error_log(print_r($str, TRUE), 3, "php://stdout"); } /** * 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" ) { //dprint(substr($lines[ $i - 1 ], 0, 12)); if ( substr($lines[ $i - 1 ], 0, 12) !== "--- clients/" && substr($lines[ $i - 1 ], 0, 9) !== "--- meta/" ) $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 ) . "'"; } ?>