/mysql_watcher/mysql_watcher
Python | 188 lines | 113 code | 19 blank | 56 comment | 10 complexity | 85fa8b44e4521e8ff388952d56c4a56d MD5 | raw file
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)