• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1#!/usr/bin/python
2
3# Copyright 2016 The Chromium OS Authors. All rights reserved.
4# Use of this source code is governed by a BSD-style license that can be
5# found in the LICENSE file.
6
7"""Queries a MySQL database and emits status metrics to Monarch.
8
9Note: confusingly, 'Innodb_buffer_pool_reads' is actually the cache-misses, not
10the number of reads to the buffer pool.  'Innodb_buffer_pool_read_requests'
11corresponds to the number of reads the the buffer pool.
12"""
13import logging
14import sys
15
16import MySQLdb
17import time
18
19import common
20
21from autotest_lib.client.common_lib import global_config
22from autotest_lib.client.common_lib import utils
23from autotest_lib.client.common_lib.cros import retry
24
25try:
26    from chromite.lib import metrics
27    from chromite.lib import ts_mon_config
28except ImportError:
29    metrics = utils.metrics_mock
30    ts_mon_config = utils.metrics_mock
31
32
33AT_DIR='/usr/local/autotest'
34DEFAULT_USER = global_config.global_config.get_config_value(
35        'CROS', 'db_backup_user', type=str, default='')
36DEFAULT_PASSWD = global_config.global_config.get_config_value(
37        'CROS', 'db_backup_password', type=str, default='')
38
39LOOP_INTERVAL = 60
40GET_STATUS_SLEEP_SECONDS = 20
41GET_STATUS_MAX_TIMEOUT_SECONDS = 60 * 60
42
43EMITTED_STATUSES_COUNTERS = [
44    'bytes_received',
45    'bytes_sent',
46    'connections',
47    'Innodb_buffer_pool_read_requests',
48    'Innodb_buffer_pool_reads',
49    'Innodb_row_lock_time_avg',
50    'Innodb_row_lock_current_waits',
51    'questions',
52    'slow_queries',
53    'threads_created',
54]
55
56EMITTED_STATUS_GAUGES = ['threads_running', 'threads_connected']
57
58
59class MySQLConnection(object):
60    """Maintains a db connection and a cursor."""
61
62    def __init__(self, *args, **kwargs):
63        self.args = args
64        self.kwargs = kwargs
65        self.db = None
66        self.cursor = None
67
68    def Connect(self):
69        """Establishes a MySQL connection and creates a cursor."""
70        self.db = MySQLdb.connect(*self.args, **self.kwargs)
71        self.cursor = self.db.cursor()
72
73    def Reconnect(self):
74       """Attempts to close the connection, then reconnects."""
75       try:
76           self.cursor.close()
77           self.db.close()
78       except MySQLdb.Error:
79           pass
80       self.Connect()
81
82
83def GetStatus(connection, status):
84  """Get the status variable from the database, retrying on failure.
85
86  @param connection: MySQLdb cursor to query with.
87  @param status: Name of the status variable.
88  @returns The mysql query result.
89  """
90  def _GetStatusWithoutRetry(connection, s):
91      """Gets the status variable from the database."""
92      connection.cursor.execute('SHOW GLOBAL STATUS LIKE "%s";' % s)
93      output = connection.cursor.fetchone()[1]
94
95      if not output:
96          logging.error('Cannot find any global status like %s', s)
97      return int(output)
98
99  get_status = retry.retry(
100      MySQLdb.OperationalError,
101      delay_sec=GET_STATUS_SLEEP_SECONDS,
102      timeout_min=GET_STATUS_MAX_TIMEOUT_SECONDS,
103      callback=connection.Reconnect
104  )(_GetStatusWithoutRetry)
105
106  return get_status(connection, status)
107
108
109def QueryAndEmit(baselines, conn):
110    """Queries MySQL for important stats and emits Monarch metrics
111
112    @param baselines: A dict containing the initial values for the cumulative
113                      metrics.
114    @param conn: The mysql connection object.
115    """
116    for status in EMITTED_STATUSES_COUNTERS:
117        metric_name = 'chromeos/autotest/afe_db/%s' % status.lower()
118        delta = GetStatus(conn, status) - baselines[status]
119        metrics.Counter(metric_name).set(delta)
120
121    for status in EMITTED_STATUS_GAUGES:
122        metric_name = 'chromeos/autotest/afe_db/%s' % status.lower()
123        metrics.Gauge(metric_name).set(GetStatus(conn, status))
124
125    pages_free = GetStatus(conn, 'Innodb_buffer_pool_pages_free')
126    pages_total = GetStatus(conn, 'Innodb_buffer_pool_pages_total')
127
128    metrics.Gauge('chromeos/autotest/afe_db/buffer_pool_pages').set(
129        pages_free, fields={'used': False})
130
131    metrics.Gauge('chromeos/autotest/afe_db/buffer_pool_pages').set(
132        pages_total - pages_free, fields={'used': True})
133
134
135def main():
136    """Sets up ts_mon and repeatedly queries MySQL stats"""
137    logging.basicConfig(stream=sys.stdout, level=logging.INFO)
138    conn = MySQLConnection('localhost', DEFAULT_USER, DEFAULT_PASSWD)
139    conn.Connect()
140
141    with ts_mon_config.SetupTsMonGlobalState('mysql_stats', indirect=True):
142      QueryLoop(conn)
143
144
145def QueryLoop(conn):
146    """Queries and emits metrics every LOOP_INTERVAL seconds.
147
148    @param conn: The mysql connection object.
149    """
150    # Get the baselines for cumulative metrics. Otherwise the windowed rate at
151    # the very beginning will be extremely high as it shoots up from 0 to its
152    # current value.
153    baselines = dict((s, GetStatus(conn, s))
154                     for s in EMITTED_STATUSES_COUNTERS)
155
156    while True:
157        now = time.time()
158        QueryAndEmit(baselines, conn)
159        time_spent = time.time() - now
160        sleep_duration = LOOP_INTERVAL - time_spent
161        time.sleep(max(0, sleep_duration))
162
163
164if __name__ == '__main__':
165  main()
166