#!/usr/local/bin/php -q
<?php
##
## mysql log file analyzing(databases query statistics)
##
## <san2(at)linuxchannel.net>, http://linuxchannel.net/
## 2003.07.09 : new build
##
set_time_limit(0);
if($_SERVER[HTTP_USER_AGENT])
{ exit('sorry !!!'); }
if(!file_exists($logfile=$_SERVER[argv][1]))
{
$T_MR = sprintf('%s',"\033[1;31m"); // bold red
$T_MD = sprintf('%s',"\033[1;39m"); // bold white
$T_ME = sprintf('%s',"\033[0;39m"); // normal white
error_log($T_MR.$logfile.' file is not exits'.$T_ME);
exit;
}
$contents = `cat $logfile 2>/dev/null`;
$contents = preg_replace
(
array('/([0-9]+)\sInit DB/','/[0-9]+\s(Init_DB|Query)/'),
array('\\1 Init_DB','__DIV__\\0'),
$contents
);
$arr = preg_split('/__DIV__/',$contents);
$size = sizeof($arr);
$init = get_init_args($arr,$size);
$cid = $init[cid];
$sidx = $init[sidx] + 1;
$dbname = $init[dbname];
for($i=$sidx; $i<$size; $i++)
{
list($nid,$cmd,$args) = preg_split('/[\s]+/',$arr[$i]);
if($cmd == 'Query') // this Query
{
$d[$dbname][(strtolower($args))]++;
$s[$dbname]++;
}
else $dbname = $args; // change
//error_log("[$i] => [$dbname] [$cmd] [$args]\n");
}
unset($arr);
$tmp = array_keys($s);
$size = sizeof($tmp);
foreach($tmp AS $dbname)
{
foreach($d[$dbname] AS $q=>$v)
{ $d[$dbname][$q] .= ' ('.sprintf('%.2f',@($v*100/$s[$dbname])).' %)'; }
}
ksort($d);
ksort($s);
print_r($d);
print_r($s);
function get_init_args($array, $size)
{
for($i=1; $i<$size; $i++)
{
list($cid,$cmd,$args) = preg_split('/[\s]+/',$array[$i]);
if($cmd == 'Init_DB') break;
}
return array('sidx'=>$i,'cid'=>$cid,'dbname'=>$args);
}
exit;
?>
==========================
실행 예)
# php -q mysql.log.statistics.php /usr/local/mysql/var/foo.host.log
실행 결과)
Array
(
[xxx] => Array
(
[show] => 46 (50.55 %)
[desc] => 1 (1.10 %)
[select] => 36 (39.56 %)
[resource] => 8 (8.79 %)
)
[xxx] => Array
(
[show] => 3 (0.01 %)
[select] => 10343 (50.29 %)
[insert] => 10216 (49.67 %)
[lock] => 2 (0.01 %)
[update] => 2 (0.01 %)
[unlock] => 2 (0.01 %)
)
[xxx] => Array
(
[select] => 9 (69.23 %)
[show] => 4 (30.77 %)
)
[xxx] => Array
(
[show] => 3 (100.00 %)
)
[xxx] => Array
(
[select] => 4534 (88.68 %)
[update] => 248 (4.85 %)
[insert] => 134 (2.62 %)
[delete] => 59 (1.15 %)
[drop] => 28 (0.55 %)
[show] => 71 (1.39 %)
[create] => 16 (0.31 %)
[alter] => 12 (0.23 %)
[desc] => 6 (0.12 %)
[lock] => 1 (0.02 %)
[unlock] => 1 (0.02 %)
[set] => 3 (0.06 %)
)
)
Array
(
[xxx] => 91
[xxx] => 20568
[xxx] => 13
[xxx] => 3
[xxx] => 5113
) |
sun's longitude:42 29 21
/board/delete.php:소스보기
