#!/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
)