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