download this file: mysql.log.status.php
view text/plain: mysql.log.status.php file encoding: UTF-8
[goback]
#!/usr/sbin/php -q
<?php
##
## mysqld log files analyzing
## databases, tables query statistics
##
## [author]
## - Chilbong Kim <san2(at)linuxchannel.net>
## - http://linuxchannel.net/
##
## [changes]
## - 2003.07.27 : fixed, check_args()
## - 2003.07.18 : fixed, memory_limit by fopen(), it's maximum file size
## - 2003.07.11 : bug fixed and add options
## - 2003.07.09 : new build
##
## [download & online source view]
## - http://ftp.linuxchannel.net/devel/php_mysql_log/
##
## [requirements]
## - PHP CGI
## - some mysqld log files
##
## [usage]
## shell> php -q this.file.name --help
## or
## shell> chmod u+x this.file.name
## shell> ./this.file.name --help
##
## print usage to stderr
##
function usage($errstr='')
{
error_log(
'usage : '.T_MD.'php -q '.basename(__FILE__).
' [OPTIONS] [mysqld.log [mysqld.log2 ...]]'.T_ME."\n".
'options:'."\n".
' -h, --help print this message and exit'."\n".
' -r, --print_r print by print_r() style, this default'."\n".
' -p, --php print by associative array style'."\n".
' -s, --serialize print by serialize() style'."\n"
);
if($errstr) error_log(T_MR.$errstr.T_ME);
exit;
}
function check_args($argc, $argv, &$pr, &$logfiles)
{
global $_SERVER; // for PHP/4.0.x
## check user-agent, only php execute on command line shell
##
if($_SERVER[HTTP_USER_AGENT])
{ usage('sorry !!!'); }
## reset
##
$pr = $logfiles = array();
for($i=1; $i<$argc; $i++) // skip $i=0
{
$cw = preg_replace('/^[-]+(?)/','\\1',&$argv[$i]);
$cw = ($cw[0].$cw[1] == 'pr') ? 'r' : $cw[0];
if($cw == 'h')
{ usage(); break; }
else if($cw == 'r' || $cw == 'p' || $cw == 's')
{ $pr[$cw] = TRUE; }
else
{
if(file_exists(&$argv[$i]) && is_file(&$argv[$i]))
{ $logfiles[] = &$argv[$i]; }
else
{ error_log(T_MR.$argv[$i].' file not exists'.T_ME); }
}
}
if(sizeof($logfiles)<1) usage();
if(!$pr[r] && !$pr[p] && !$pr[s]) $pr[r] = TRUE;
}
## Direct IO access get file
## 2002.08.23
##
function get_diofile($file)
{
if($fp = @dio_open($file,O_RDONLY))
{
$contents = dio_read($fp,filesize($file));
dio_close($fp);
}
return $contents;
}
## common get file
## 2002.08.23
##
function get_file($file)
{
if(!file_exists($file)) return '';
if(function_exists(dio_open)) return get_diofile($file);
if($fp = @fopen($file,'r'))
{
$contents = fread($fp,filesize($file));
fclose($fp);
}
return $contents;
}
## get percents
##
function add_per($sums, $types, $s2=array(), $q2=array())
{
$tmp = array_keys($sums);
foreach($tmp AS $dt)
{
foreach($types[$dt] AS $q=>$v)
{
$types[$dt][$q] .= ' ('.
sprintf('%.2f',@($v*100/$sums[$dt])).'%)';
}
if($s2[$dt] && $q2[$dt]) add_per(&$s2[$dt], &$q2[$dt]);
}
}
## get table name in each line
##
function get_table_name($line, $splitw)
{
if($splitw)
{
list(,$cwords) = preg_split("/$splitw/i",$line);
list($table) = preg_split('/[\s]+/',$cwords);
$table = preg_replace('/[(,\'"`].*$/','',$table);
if(preg_match('/\./',$table))
{ list($refdb,$table) = preg_split('/\./',$table); }
}
return array(trim($table),trim($refdb));
}
## for printing
##
function arr2print($array, $name, $title='', $int=0)
{
if(!$array || !is_array($array)) return;
if($title) echo "\n".'## '.$title."\n".'##'."\n";
if(!$int) $pt = '\'';
$keys = array_keys($array);
$size = sizeof($array);
for($i=0; $i<$size; $i++)
{
$key = &$keys[$i];
$var = $name.'[\''.$key.'\']';
if(is_array($array[$key]))
{ arr2print($array[$key],$var,0,$int); }
else
{ echo "\$$var\t= ${pt}$array[$key]${pt};\n"; }
}
}
######################################################
######################################################
set_time_limit(0);
ini_set('memory_limit','50M'); // it's maximum file size by fopen()
global $_SERVER; // force to set
unset($argc,$argv,$dbq,$dbs,$tableq,$tables,$pr,$logfiles); // reset
## for xterm, color-terminal
##
define('T_MR', sprintf('%s',"\033[1;31m")); // bold red
define('T_MD', sprintf('%s',"\033[1;39m")); // bold white
define('T_ME', sprintf('%s',"\033[0;39m")); // normal white
$argc = $_SERVER[argc];
$argv = $_SERVER[argv]; // array
## check arguments
##
check_args($argc,$argv,&$pr,&$logfiles); // $pr and $logfile is reference
## split words of each query, for get_table_name()
##
$splits = array
(
'select' => 'FROM\s+',
'insert' => 'INSERT\s+(LOW_PRIORITY|DELAYED)*\s*(IGNORE)*\s*(INTO)*\s*',
'update' => 'UPDATE\s+(LOW_PRIORITY)*\s*(IGNORE)*\s*',
'delete' => 'FROM\s+',
'show' => 'CREATE\s+TABLE\s+',
'desc' => 'DESC\s+',
'describe' => 'DESCRIBE\s+',
'explain' => 'EXPLAIN\s+(SELECT.+FROM\s+)*',
'lock' => 'TABLES\s+',
'create' => 'TABLE\s+(IF\s+NOT\s+EXISTS)*\s*',
'replace' => 'REPLACE\s+(LOW_PRIORITY|DELAYED)*\s*(INTO)*\s*',
'alter' => 'TABLE\s+',
'analyze' => 'TABLE\s+',
'optimize' => 'TABLE\s+',
'rename' => 'TABLE\s+',
'drop' => 'TABLE\s+(IF\s+EXISTS)*\s*',
'backup' => 'TABLE\s+',
'restore' => 'TABLE\s+',
'check' => 'TABLE\s+',
'repair' => 'TABLE\s+',
'load' => 'INTO\s+TABLE\s+',
'handler' => 'HANDLER\s+',
'turncate' => 'TABLE\s+',
);
## get contents
##
//$contents = `cat $logfile 2>/dev/null`;
$size = sizeof($logfiles);
for($i=0; $i<$size; $i++)
{ $contents .= get_file(&$logfiles[$i]); }
## parsing
##
$contents = preg_replace
(
array('/([0-9]+)\sInit DB/','/[0-9]+\s(Init_DB|Query|Connect)/'),
array('\\1 Init_DB','__DIV__\\0'),
$contents
);
## get array(splited by '__DIV__')
##
$arr = preg_split('/__DIV__/',$contents);
$size = sizeof($arr);
$term = (int)($size/10);
## get query statistics each databases, tables
##
for($i=1; $i<$size; $i++) // skip 0
{
if(!($i%$term)) error_log(sprintf('%03.2f',$i*100/$size).'% done');
flush();
$line = preg_replace('/[\r\n]+/',' ',&$arr[$i]);
list($nid,$cmd,$args,$on,$db) = preg_split('/[\s]+/',$line);
if($cmd == 'Connect')
{
if($db && $on == 'on' && !preg_match('/^(for|[\d]+)$/',$db))
{ $dbname = $db; } // change dbname
else
{ $dbname = ''; } // this false
continue;
}
else if($cmd == 'Init_DB')
{
if(!$db && !$on)
{ $dbname = $args; } // change dbname
else
{ $dbname = ''; } // this false
continue;
}
else if($cmd == 'Query') // this Query
{
$query = strtolower($args); // query command
list($table,$refdb) = get_table_name($line,&$splits[$query]);
$tmpdb = $refdb ? $refdb : $dbname;
if($tmpdb)
{
$dbq[$tmpdb][$query]++; // db query statistics
$dbs[$tmpdb]++; // db sum statistics
if($table)
{
$tableq[$tmpdb][$table][$query]++; // table statistics
$tables[$tmpdb][$table]++; // table sum statistics
}
}
}
}
error_log('100.00% done'."\n".'preparing to print. wait...');
sleep(1);
unset($arr);
@add_per(&$dbs, &$dbq, &$tables, &$tableq); // use array-references
@ksort($dbq);
@ksort($dbs);
@ksort($tableq);
@ksort($tables);
## print print_r() style
##
if($pr[r])
{
echo "\n".'STATISTICS OF DATABASES(QUERY) :'."\n";
print_r($dbq);
print_r($dbs);
echo "\n".'STATISTICS OF TABLES(QUERY) :'."\n";
print_r($tableq);
print_r($tables);
}
## print associative-array style
##
if($pr[p])
{
echo '<?php'."\n\n";
arr2print($dbq,'databas_query','database query statistics :');
arr2print($dbs,'databas_sum','database sum statistics :',1);
arr2print($tableq,'table_query','table query statistics :');
arr2print($tables,'table_sum','table sum statistics :',1);
echo "\n".'?>';
}
## print serialize style
##
if($pr[s])
{
echo serialize($dbq)."\n";
echo serialize($dbs)."\n";
echo serialize($tableq)."\n";
echo serialize($tables)."\n";
}
## last
##
unset($dbq,$dbs,$tableq,$tables);
flush();
exit;
?>