1#!/usr/bin/python 2# Copyright 2016 The Chromium OS Authors. All rights reserved. 3# Use of this source code is governed by a BSD-style license that can be 4# found in the LICENSE file. 5 6"""Kill slow queries in local autotest database.""" 7 8import logging 9import MySQLdb 10import optparse 11import sys 12import time 13 14import common 15from autotest_lib.client.common_lib import global_config 16from autotest_lib.site_utils import gmail_lib 17from autotest_lib.client.common_lib import utils 18 19try: 20 from chromite.lib import metrics 21 from chromite.lib import ts_mon_config 22except ImportError: 23 metrics = utils.metrics_mock 24 ts_mon_config = utils.metrics_mock 25 26AT_DIR='/usr/local/autotest' 27DEFAULT_USER = global_config.global_config.get_config_value( 28 'CROS', 'db_backup_user', type=str, default='') 29DEFAULT_PASSWD = global_config.global_config.get_config_value( 30 'CROS', 'db_backup_password', type=str, default='') 31DEFAULT_MAIL = global_config.global_config.get_config_value( 32 'SCHEDULER', 'notify_email', type=str, default='') 33 34 35def parse_options(): 36 """Parse the command line arguments.""" 37 usage = 'usage: %prog [options]' 38 parser = optparse.OptionParser(usage=usage) 39 parser.add_option('-u', '--user', default=DEFAULT_USER, 40 help='User to login to the Autotest DB. Default is the ' 41 'one defined in config file.') 42 parser.add_option('-p', '--password', default=DEFAULT_PASSWD, 43 help='Password to login to the Autotest DB. Default is ' 44 'the one defined in config file.') 45 parser.add_option('-t', '--timeout', type=int, default=300, 46 help='Timeout boundry of the slow database query. ' 47 'Default is 300s') 48 parser.add_option('-m', '--mail', default=DEFAULT_MAIL, 49 help='Mail address to send the summary to. Default is ' 50 'ChromeOS infra Deputy') 51 options, args = parser.parse_args() 52 return parser, options, args 53 54 55def verify_options_and_args(options, args): 56 """Verify the validity of options and args. 57 58 @param options: The parsed options to verify. 59 @param args: The parsed args to verify. 60 61 @returns: True if verification passes, False otherwise. 62 """ 63 if args: 64 logging.error('Unknown arguments: ' + str(args)) 65 return False 66 67 if not (options.user and options.password): 68 logging.error('Failed to get the default user of password for Autotest' 69 ' DB. Please specify them through the command line.') 70 return False 71 return True 72 73 74def format_the_output(slow_queries): 75 """Convert a list of slow queries into a readable string format. 76 77 e.g. [(a, b, c...)] --> 78 "Id: a 79 Host: b 80 User: c 81 ... 82 " 83 @param slow_queries: A list of tuples, one tuple contains all the info about 84 one single slow query. 85 86 @returns: one clean string representation of all the slow queries. 87 """ 88 query_str_list = [('Id: %s\nUser: %s\nHost: %s\ndb: %s\nCommand: %s\n' 89 'Time: %s\nState: %s\nInfo: %s\n') % 90 q for q in slow_queries] 91 return '\n'.join(query_str_list) 92 93 94def kill_slow_queries(user, password, timeout): 95 """Kill the slow database queries running beyond the timeout limit. 96 97 @param user: User to login to the Autotest DB. 98 @param password: Password to login to the Autotest DB. 99 @param timeout: Timeout limit to kill the slow queries. 100 101 @returns: a tuple, first element is the string representation of all the 102 killed slow queries, second element is the total number of them. 103 """ 104 db = MySQLdb.connect('localhost', user, password) 105 cursor = db.cursor() 106 # Get the processlist. 107 cursor.execute('SHOW FULL PROCESSLIST') 108 processlist = cursor.fetchall() 109 # Filter out the slow queries and kill them. 110 slow_queries = [p for p in processlist if p[4]=='Query' and p[5]>=timeout] 111 queries_str = '' 112 num_killed_queries = 0 113 if slow_queries: 114 queries_str = format_the_output(slow_queries) 115 queries_ids = [q[0] for q in slow_queries] 116 logging.info('Start killing following slow queries\n%s', queries_str) 117 for query_id in queries_ids: 118 logging.info('Killing %s...', query_id) 119 cursor.execute('KILL %d' % query_id) 120 logging.info('Done!') 121 num_killed_queries += 1 122 else: 123 logging.info('No slow queries over %ds!', timeout) 124 return (queries_str, num_killed_queries) 125 126 127def main(): 128 """Main entry.""" 129 logging.basicConfig(format='%(asctime)s %(message)s', 130 datefmt='%m/%d/%Y %H:%M:%S', level=logging.DEBUG) 131 132 with ts_mon_config.SetupTsMonGlobalState(service_name='kill_slow_queries', 133 indirect=True): 134 count = 0 135 parser, options, args = parse_options() 136 if not verify_options_and_args(options, args): 137 parser.print_help() 138 return 1 139 try: 140 while True: 141 result_log_strs, count = kill_slow_queries( 142 options.user, options.password, options.timeout) 143 if result_log_strs: 144 gmail_lib.send_email( 145 options.mail, 146 'Successfully killed slow autotest db queries', 147 'Below are killed queries:\n%s' % result_log_strs) 148 m = 'chromeos/autotest/afe_db/killed_slow_queries' 149 metrics.Counter(m).increment_by(count) 150 time.sleep(options.timeout) 151 except Exception as e: 152 logging.error('Failed to kill slow db queries.\n%s', e) 153 gmail_lib.send_email( 154 options.mail, 155 'Failed to kill slow autotest db queries.', 156 ('Error occurred during killing slow db queries:\n%s\n' 157 'Detailed logs can be found in /var/log/slow_queries.log on db' 158 ' backup server.\nTo avoid db crash, please check ASAP.') % e) 159 raise 160 161 162if __name__ == '__main__': 163 sys.exit(main()) 164 165