PageRenderTime 43ms CodeModel.GetById 20ms app.highlight 3ms RepoModel.GetById 17ms app.codeStats 1ms

/mysql_watcher/mysql_logger_sb

https://bitbucket.org/lindenlab/apiary/
Python | 221 lines | 120 code | 41 blank | 60 comment | 9 complexity | 8edd375091504d3958eb67a0bb63efdc 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"""
 28Log all queries hitting a particular mysql database
 29Dump all queries into streambase
 30"""
 31
 32try:
 33    import psyco
 34    psyco.full()
 35except:
 36    pass
 37
 38import getopt
 39import os.path
 40import re
 41import socket
 42import sys
 43import time
 44import md5
 45import struct
 46import time
 47import errno
 48
 49from dblibs.dbutil import LLQueryStream, remote_mysql_stream
 50from socket import *
 51
 52# Socket Params
 53SB_HOST = 'query-profiler.lindenlab.com'
 54SB_PORT = 15999
 55BUFSIZ = 1024
 56SB_ADDR = (SB_HOST, SB_PORT)
 57
 58def open_SB_socket():
 59
 60    tcpSBSocket=socket(AF_INET, SOCK_STREAM)
 61    try:
 62        tcpSBSocket.connect(SB_ADDR)
 63    except error, e:
 64        print "Could not create socket connection to %s ", SB_ADDR
 65        print str(e)
 66        tcpSBSocket.close()
 67
 68    return tcpSBSocket
 69
 70
 71def close_SB_socket(tcpSBSocket):
 72
 73    tcpSBSocket.close()
 74
 75
 76def watch_host(query_stream, host):
 77    "Watches query traffic for a particular host.  Dumps queries into streambase."
 78
 79    tcpSBSocket = open_SB_socket()
 80
 81    # make output path
 82    log_path = "./%s" % host
 83    os.system("mkdir -p %s" % log_path)
 84
 85    done = 0
 86    query_count = 0
 87
 88    while not done:
 89        (event_type, query) = query_stream.getNextEvent()
 90        event_num = None
 91        event_time = None
 92
 93        if event_type == "QueryStart":
 94            event_num = 1
 95            event_time = query.mStartTime
 96        elif event_type == "QueryResponse":
 97            event_num = 2
 98            event_time = query.mResponseTime
 99        elif event_type == "Quit":
100            event_num = 3
101            event_time = "null"
102
103        if event_num is not None:
104
105            if event_num == 3:
106                query_clean = "null"
107                md5query = "null"
108            else:
109                query_clean = query.mData['query_clean']
110                md5query = md5.new(query_clean).hexdigest()
111
112            event_hash = "%s:%s" % (query.mData['host'], query.mData['port'])
113            event_hash = md5.new(event_hash).digest()[:4]
114            event_hash = "%s" % struct.unpack("I", event_hash)
115            event_hash = event_hash[:4]
116
117            query_hash = "%s:%s:%s" % (query.mData['host'], query.mData['port'], query.mData['query_clean'])
118            query_hash = md5.new(query_hash).hexdigest()
119
120#            query_no_whitespace = query.mData['query'].replace("\n", "")
121
122#            query_test = 'dfsdf"sdf'
123#            query_test_escaped = query_test.replace('"', '\\"')
124#            print query_test_escaped
125#            query_escaped = query_no_whitespace.replace('"', '\\"')
126#            query_escaped = query_escaped.replace("'", "\\'")
127#            query_escaped_quoted = '"' + query_escaped + '"'
128
129#            query_no_whitespace = query_no_whitespace[:10]
130
131#            print "unclean query %s" % (query.mData['query'])
132#            print "query %s" % (query_no_whitespace)
133
134            dummy_query = "foo"
135
136            tuple = "%s^%s^%s^%s^%s^%s^%s^%s^%s^%s^%s" % (host, event_num, event_hash, event_time, query.mData['host'], query.mData['port'], query.mData['host_clean'], query_clean, md5query, query_hash, dummy_query)
137
138#            if query_count % 10 == 0:
139#                print "skipping query %s" % (tuple)
140#                query_count = query_count+1
141#                continue
142
143            try:
144                # uncomment for debuggery
145                datestring = time.strftime ("%D %T").strip()
146                #print datestring, " : " , tuple
147                #query_error_file.write(tuple + "\n")
148                tcpSBSocket.send(tuple + "\n")
149                query_count = query_count+1
150#                print "%s : query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus))
151
152#                try:
153#                    query_log_file = open("%s/query_log.log" % log_path, "a")
154#                    datestring = time.strftime ("%D %T").strip()
155#                    query_log_file.write("%s: %s \n" % (datestring, tuple))
156#                except IOError:
157#                    print ("can't write to file %s", query_log_file)
158                if query_count % 1000000 == 0:
159                    print "%s : query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus))
160                    try:
161                        query_error_file = open("%s/query_error.log" % log_path, "a")
162                        query_error_file.write("%s query count = %s, connection table = %s \n" % (datestring, query_count, len (query_stream.mConnStatus)))
163                        query_error_file.close()
164                    except IOError:
165                        print ("can't write to file %s", query_error_file)
166
167            except Exception, error:
168                count = 0
169                # retry the same query 5 times
170                while count < 1:
171                    datestring = time.strftime ("%D %T").strip()
172                    # find out what the error was, log to file
173                    print "%s error #%s printing to socket: %s " % (datestring, count, tuple)
174                    print str(error)
175                    print "%s query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus))
176
177                    try:
178                        query_error_file = open("%s/query_error.log" % log_path, "a")
179                        query_error_file.write("%s error #%s printing to socket: %s \n" % (datestring, count, tuple))
180                        query_error_file.write("%s \n" % str(error))
181                        query_error_file.write("%s query count = %s, connection table = %s \n" % (datestring, query_count, len (query_stream.mConnStatus)))
182                        query_error_file.close()
183                    except IOError:
184                        print ("can't write to file %s", query_error_file)
185                    # If we don't close and reopen the socket, it will stop accepting
186                    # writes after the first error.
187                    close_SB_socket(tcpSBSocket)
188                    time.sleep(2)
189                    tcpSBSocket = open_SB_socket()
190                    count = count+1
191                print "Skipping query ... \n"
192#                return 0
193            continue
194
195        continue
196
197
198
199
200
201
202            
203if __name__ == "__main__":
204    opts, args = getopt.getopt(sys.argv[1:], "", ["host="])
205
206    host = None
207    for o, a in opts:
208        if o in ("--host"):
209            host = a
210    if not host:
211        print "Specify a host using --host="
212        sys.exit(1)
213
214    # Start up the stream from the target host and create a file
215    # that we can hand to LLQueryStream
216    query_stream_file = remote_mysql_stream(host)
217    query_stream = LLQueryStream(query_stream_file)
218
219    watch_host(query_stream, host)
220
221