/mysql_watcher/mysql_watcher

  1#!/usr/bin/env python
  2#
  3# $LicenseInfo:firstyear=2010&license=mit$
  4# 
  5# Copyright (c) 2010, Linden Research, Inc.
  6# 
  7# Permission is hereby granted, free of charge, to any person obtaining a copy
  8# of this software and associated documentation files (the "Software"), to deal
  9# in the Software without restriction, including without limitation the rights
 10# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 11# copies of the Software, and to permit persons to whom the Software is
 12# furnished to do so, subject to the following conditions:
 13# 
 14# The above copyright notice and this permission notice shall be included in
 15# all copies or substantial portions of the Software.
 16# 
 17# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 18# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 19# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 20# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 21# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 22# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 23# THE SOFTWARE.
 24# $/LicenseInfo$
 25#
 26
 27"""
 28Provides real-time query profiling of mysql by using tcpdump and netcat.
 29
 30You need to have the root ssh key to use it.
 31"""
 32
 33try:
 34    # To improve performance on older Pythons that support psyco
 35    import psyco
 36    psyco.full()
 37except:
 38    pass
 39
 40import curses
 41import curses.wrapper
 42import getopt
 43import os
 44import re
 45import socket
 46import sys
 47import time
 48
 49from dblibs.dbutil import LLQueryStream, LLQueryStatMap, LLBinnedQueryStats, remote_mysql_stream
 50from dblibs.dbbrowser import *
 51
 52DUMP_INTERVAL = 5.0
 53
 54def process_query_stream(stdscr, query_stream, query_metadata, host):
 55    "Processes mysql query events coming from the stream"
 56
 57    #
 58    # Options/parameters
 59    #
 60    output_path = "./%s" % host
 61    # Make output path
 62    os.system("mkdir -p %s" % output_path)
 63
 64    # Curses configuration stuff
 65    size = stdscr.getmaxyx()
 66    stdscr.nodelay(True) # Non-blocking keyboard input
 67
 68    sort_by = "total_time"
 69
 70    count = 0
 71    cur_time = 0
 72    last_time = time.time()
 73    last_count = 0
 74    total_stats = LLQueryStatMap("Total", time.time())
 75    cur_stats = LLQueryStatMap("5 seconds", time.time())
 76    prev_stats = LLQueryStatMap("5 seconds", time.time())
 77    display_stats = prev_stats
 78
 79    # Initialize the curses query data browser
 80    query_browser = LLQueryBrowser(stdscr, cur_stats, query_metadata)
 81
 82    last_hour = 0
 83    done = False
 84    try:
 85        while not done:
 86            # The real work happens inside here - this is where we get the next event coming off of the mysql stream
 87            (event_type, query) = query_stream.getNextEvent()
 88
 89            # Use the start time to determine which hour bin to put the query into
 90            start_time = query.mStartTime
 91            start_hour = time.localtime(start_time)[3]
 92            
 93            if event_type == "QueryStart":
 94                total_stats.queryStart(query)
 95                cur_stats.queryStart(query)
 96            elif (event_type == "QueryResponse"):
 97                total_stats.queryResponse(query)
 98                cur_stats.queryResponse(query)
 99                count += 1
100            elif event_type == "Quit":
101                # Quit is an "instantaneous" query, both start and response
102                total_stats.queryStart(query)
103                total_stats.queryResponse(query)
104                cur_stats.queryStart(query)
105                cur_stats.queryResponse(query)
106                continue
107
108            if 0 == (count % 20):
109                # Handle keyboard input every 20th event (slow in the case of a not-very-loaded database
110                c = 0
111                done = False
112                while -1 != c:
113                    # Fetch the key from curses
114                    c = stdscr.getch()
115                    if -1 == c:
116                        # Skip if no keyboard input
117                        pass
118                    # See if the generic query browser class handles it
119                    elif query_browser.handleKey(c):
120                        # Yep, just redraw
121                        query_browser.redraw()
122                        pass
123                    #
124                    # Check other keyboard events
125                    #
126                    elif c == ord('q'):
127                        # Quit
128                        done = True
129                        break
130                    elif c == ord(' '):
131                        # Switch which stats we're displaying between the previous interval (last 5 secs) and total interval
132                        if display_stats == prev_stats:
133                            display_stats = total_stats
134                        elif display_stats:
135                            display_stats = prev_stats
136                        query_browser.setQueryMap(display_stats)
137                        query_browser.redraw()
138                    elif c == ord('d'):
139                        # Dump output to files
140                        total_stats.dumpTiming("%s/query_timing.txt" % output_path)
141                        total_stats.dumpLLSD("%s/query_dump.llsd" % output_path)
142                    else:
143                        print "Pressed key %s" % c
144
145                # Switch the current stat accumulator bin every DUMP_INTERVAL
146                cur_time = time.time()
147                if (cur_time - last_time > DUMP_INTERVAL):
148                    last_time = cur_time
149                    last_count = count
150                    if display_stats == prev_stats:
151                        display_stats = cur_stats
152                    prev_stats = cur_stats
153                    prev_stats.mElapsedTime = 5.0
154                    cur_stats = LLQueryStatMap("5 seconds", time.time())
155                    query_browser.setQueryMap(display_stats)
156                    query_browser.redraw()
157    except KeyboardInterrupt:
158        pass
159
160    # Dump total stats on exit
161    total_stats.dumpTiming("%s/query_timing.txt" % output_path)
162    total_stats.dumpLLSD("%s/query_dump.llsd" % output_path)
163
164
165#
166# Start the main application, parse parameters
167#
168if __name__ == "__main__":
169    opts, args = getopt.getopt(sys.argv[1:], "", ["host="])
170
171    host = None
172    for o, a in opts:
173        if o in ("--host"):
174            host = a
175    if not host:
176        print "Specify a host using --host="
177        sys.exit(1)
178
179    # Load existing metadata
180    query_metadata = LLQueryMetadata("./query_info.llsd")
181
182    # Start up the stream from the target host and create a file
183    # that we can hand to LLQueryStream.
184    query_stream_file = remote_mysql_stream(host)
185    query_stream = LLQueryStream(query_stream_file)
186
187    # start up the curses interface and start processing the stream data
188    curses.wrapper(process_query_stream, query_stream, query_metadata, host)