merge mysql-slow.log and apache

diff --git a/mysql-slow-apache.pl b/mysql-slow-apache.pl
new file mode 100755 (executable)
index 0000000..dbce3ca
--- /dev/null
+++ b/mysql-slow-apache.pl
@@ -0,0 +1,61 @@
+#!/usr/bin/perl
+use warnings;
+use strict;
+use autodie;
+use HTTP::Date;
+use POSIX qw(strftime);
+use Data::Dump qw(dump);
+
+$|=1;
+
+my $t;
+
+sub t_iso {
+       return strftime("%Y-%m-%d %H:%M:%S", localtime($_[0]));
+}
+
+open(my $slow_fh, '<', '/var/log/mysql/mysql-slow.log');
+while(<$slow_fh>) {
+       print $_;
+       if ( m/Time:\s+(\S+)\s+(\S+)/ ) {
+               $t = str2time("20$1 $2");
+       } elsif ( m/Query_time: (\S+)/ ) {
+               my $d = $1;
+               my $t_start = $t - $d;
+               print "XXX ",t_iso($t), " -> ", t_iso($t_start), $/;
+               my $test;
+               foreach ( $t , $t_start, $t_start - 0.5 ) {
+                       $test->{ strftime("%d/%b/%Y:%H:%M:%S", localtime($_)) }++; # apache
+               }
+               print "XXX test=",dump($test),$/;
+               my $patt = join('|', sort keys %$test);
+       
+               my $cmd = "grep -E '($patt)' /var/log/apache2/other_vhosts_access.log";
+               print "XXX $cmd\n";
+               system $cmd;
+=for xxx
+               my $date_log = "/var/log/apache2/other_vhosts_access.log-20$1";
+               if ( -e $date_log ) {
+                       system "grep $2 $date_log";
+               } else {
+                       system "grep $2 /var/log/apache2/other_vhosts_access.log";
+               }
+=cut
+       }
+}
+
+__END__
+#!/bin/sh -xe
+
+stdbuf -o L cat /var/log/mysql/mysql-slow.log \
+| stdbuf -e L tee /dev/stderr \
+| stdbuf -o L grep Time: \
+| stdbuf -o L awk '{ print "grep --line-buffered " $4 " /var/log/apache2/other_vhosts_access.log-20"$3 ; fflush(stdout); }' | sh
+
+exit 0
+
+zgrep Query_time /var/log/mysql/mysql-slow.log*
+
+exit 0
+
+grep -E '(Query_time|Time)' /var/log/mysql/mysql-slow.log