#!/usr/bin/python

# Written by Tim Ellis - 2007
#
# released under the terms of the GNU GPL
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation version 2 of the License.
#
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
# details.
#
# You should have received a copy of the GNU General Public License along with
# this program; if not, write to the
#
# Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor,
# Boston, MA  02110-1301, USA.
#
# ----------------------------------------------------------------------------
#
# This script analyzes dbmon.pl output to find a list of queries that are
# taking the most resources.

import sys
import commands
import logging

readdir = "/var/log/dbmon"
logFile = readdir + "/dbmon.log"
sqlFile = readdir + "/dbmon.sql"
logLvl=logging.INFO

# process input arguments
from optparse import OptionParser
parser = OptionParser()
parser.add_option("-k", "--killed",
                  dest="queriesKilled", action="store_true", default=False,
                  help="List queries that were killed")
parser.add_option("-m", "--min",
                  dest="minQueries", default="",
                  help="Minimum number of queries to worry about")
parser.add_option("-s", "--sec",
                  dest="querySeconds", default="",
                  help="Number of seconds query must run to be worried about")
parser.add_option("-a", "--allloglines",
                  dest="allLogLines", action="store_true", default=False,
                  help="Process the entire log, not just the first 500,000 lines")
(options, args) = parser.parse_args()

logging.basicConfig(
    datefmt="%Y-%m-%d %H:%M:%S",
    level=logLvl,
    format="%(levelname)s %(asctime)s %(message)s")

filePtr = file(logFile, 'r')
# read off the initial ""
queryLine = filePtr.readline().rstrip()

linesProcessed = 0
eofReached = 0
queryCounts = {}
queryTimings = {}
while (eofReached == 0):
    queryLine = filePtr.readline().rstrip()
    # if line starts with " - " it's just info
    if (len(queryLine) > 4 and queryLine[0:3] == " - "):
        logging.debug(queryLine)
    # this is (probably) a query, deal with it
    elif (len(queryLine) > 34):
        # first 32 chars only letters/numbers, likely an MD5 hash
        queryHash = queryLine[0:32]
        if (queryHash.isalnum()):
            # if it's a Finish line, it'll have a timing on the query
            splitLine = queryLine.split('\t')
            if (splitLine[1] == "Finish"):
                # the timing looks like "0.987 sec" so break out the "sec" part
                timingSplit = splitLine[6].split(' ')

                countThisQuery = False

                # if they passed in "-k" only show queries that were killed
                if (options.queriesKilled == True):
                    if (splitLine[4] == "Killed"):
                        countThisQuery = True
                    else:
                        countThisQuery = False
                else:
                     countThisQuery = True

                # by some code path above, countThisQuery became true, so we
                # count this query
                if (countThisQuery == True):
                    # how many times the query was seen
                    if (queryCounts.has_key(queryHash)):
                        queryCounts[queryHash] += 1
                    else:
                        queryCounts[queryHash] = 1

                    # also interested in timings for the queries
                    if (queryTimings.has_key(queryHash)):
                        queryTimings[queryHash] += float(timingSplit[0])
                    else:
                        queryTimings[queryHash] = float(timingSplit[0])
        # we don't know what happened here
        else:
            logging.debug("Hash %s is not alphanumeric." % (queryHash))
    elif (queryLine == ""):
        logging.debug("Hit end of file. Quitting")
        eofReached = 1

    # give a bit of status
    linesProcessed += 1
    if (linesProcessed % 100000 == 0):
        logging.info("Processed %d lines..." % (linesProcessed))
        if (options.allLogLines == False and linesProcessed >= 500000):
            logging.info("Quitting at 500000 lines. Pass -a if you want to process the entire log file.")
            break

# output the keys found for testing
for key in queryCounts:
    printThisKey = False
    if (options.minQueries <> ""):
        if (queryCounts[key] > float(options.minQueries)):
            printThisKey = True
    else:
         printThisKey = True

    if (printThisKey):
        avg = queryTimings[key]/queryCounts[key]
        print ("%8.1f  %s  occurrences: %7d  totalTime: %8.1f  avgTime: %0.5f" % 
            (queryTimings[key], key, queryCounts[key], queryTimings[key], avg))