checks/mysql

   1 #!/usr/bin/python
   2 # -*- encoding: utf-8; py-indent-offset: 4 -*-
   3 # +------------------------------------------------------------------+
   4 # |             ____ _               _        __  __ _  __           |
   5 # |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
   6 # |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
   7 # |           | |___| | | |  __/ (__|   <    | |  | | . \            |
   8 # |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
   9 # |                                                                  |
  10 # | Copyright Mathias Kettner 2014             mk@mathias-kettner.de |
  11 # +------------------------------------------------------------------+
  12 #
  13 # This file is part of Check_MK.
  14 # The official homepage is at http://mathias-kettner.de/check_mk.
  15 #
  16 # check_mk is free software;  you can redistribute it and/or modify it
  17 # under the  terms of the  GNU General Public License  as published by
  18 # the Free Software Foundation in version 2.  check_mk is  distributed
  19 # in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
  20 # out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
  21 # PARTICULAR PURPOSE. See the  GNU General Public License for more de-
  22 # tails. You should have  received  a copy of the  GNU  General Public
  23 # License along with GNU Make; see the file  COPYING.  If  not,  write
  24 # to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
  25 # Boston, MA 02110-1301 USA.
  26
  27 # <<<mysql>>>
  28 # [[mysql]]
  29 # Aborted_clients 0
  30 # Aborted_connects        15
  31 # Binlog_cache_disk_use   0
  32 # Binlog_cache_use        0
  33 # Binlog_stmt_cache_disk_use      0
  34 # Binlog_stmt_cache_use   0
  35 # Bytes_received  7198841
  36 # Bytes_sent      19266624
  37 # Com_admin_commands      200
  38 # Com_assign_to_keycache  0
  39 # Com_alter_db    0
  40 # Com_alter_db_upgrade    0
  41
  42 #   .--Helpers-------------------------------------------------------------.
  43 #   |                  _   _      _                                        |
  44 #   |                 | | | | ___| |_ __   ___ _ __ ___                    |
  45 #   |                 | |_| |/ _ \ | '_ \ / _ \ '__/ __|                   |
  46 #   |                 |  _  |  __/ | |_) |  __/ |  \__ \                   |
  47 #   |                 |_| |_|\___|_| .__/ \___|_|  |___/                   |
  48 #   |                              |_|                                     |
  49 #   '----------------------------------------------------------------------'
  50
  51
  52 # FIXME: Crapy copy n paste! Consolidate with other mysql_* parse functions
  53 def parse_mysql(info):
  54     def parse_line(line):
  55         if len(line) == 2:
  56             varname, value = line
  57             try:
  58                 value = int(value)
  59             except:
  60                 pass
  61         else:
  62             varname = line[0]
  63             value = None
  64         return varname, value
  65
  66     parsed = {}
  67     instance = False
  68     for line in info:
  69         if line[0].startswith("[["):
  70             instance = line[0][2:-2]
  71             if instance == "":
  72                 instance = "mysql"
  73             parsed[instance] = {}
  74         elif instance:
  75             varname, value = parse_line(line)
  76             parsed[instance][varname] = value
  77
  78     # Old Agent Plugin, no Instances in output
  79     if not instance:
  80         parsed['mysql'] = {}
  81         for line in info:
  82             varname, value = parse_line(line)
  83             parsed['mysql'][varname] = value
  84
  85     return parsed
  86
  87
  88 def check_mysql_version(item, _no_params, parsed):
  89     if item in parsed and not len(parsed[item]) == 0:
  90         values = parsed[item]
  91         return 0, "Version: " + values['version']
  92
  93
  94 check_info['mysql'] = {
  95     "parse_function": parse_mysql,
  96     "inventory_function": discover(lambda k, values: 'version' in values),
  97     "check_function": check_mysql_version,
  98     "service_description": "MySQL Version %s",
  99 }
 100
 101 #.
 102 #   .--Sessions------------------------------------------------------------.
 103 #   |                ____                _                                 |
 104 #   |               / ___|  ___  ___ ___(_) ___  _ __  ___                 |
 105 #   |               \___ \ / _ \/ __/ __| |/ _ \| '_ \/ __|                |
 106 #   |                ___) |  __/\__ \__ \ | (_) | | | \__ \                |
 107 #   |               |____/ \___||___/___/_|\___/|_| |_|___/                |
 108 #   |                                                                      |
 109 #   '----------------------------------------------------------------------'
 110
 111 # params:
 112 # { "running" : (20, 40),
 113 #    "total" : (100, 400),
 114 #    "connections" : (3, 5 ),
 115 # }
 116
 117
 118 def check_mysql_sessions(item, params, parsed):
 119     if item in parsed and not len(parsed[item]) == 0:
 120         values = parsed[item]
 121         total_sessions = values["Threads_connected"]
 122         running_sessions = values["Threads_running"]
 123         connects = get_rate("mysql.sessions", time.time(), values["Connections"])
 124
 125         infos = []
 126         perfdata = []
 127         status = 0
 128
 129         for value, perfvar, what, format_str, unit in [
 130             (total_sessions, "total_sessions", "total", "%d", ""),
 131             (running_sessions, "running_sessions", "running", "%d", ""),
 132             (connects, "connect_rate", "connections", "%.2f", "/s"),
 133         ]:
 134             infos.append((format_str + " %s%s") % (value, what, unit))
 135             if what in params:
 136                 warn, crit = params[what]
 137                 if value >= crit:
 138                     status = 2
 139                     infos[-1] += "(!!)"
 140                 elif value >= warn:
 141                     status = max(status, 1)
 142                     infos[-1] += "(!)"
 143             else:
 144                 warn, crit = None, None
 145             perfdata.append((perfvar, value, warn, crit))
 146
 147         return status, ", ".join(infos), perfdata
 148
 149
 150 check_info['mysql.sessions'] = {
 151     "inventory_function": discover(lambda k, values: len(values.keys()) > 200),
 152     "check_function": check_mysql_sessions,
 153     "service_description": "MySQL Sessions %s",
 154     "has_perfdata": True,
 155     "group": "mysql_sessions",
 156 }
 157
 158 #.
 159 #   .--InnoDB-IO-----------------------------------------------------------.
 160 #   |           ___                   ____  ____       ___ ___             |
 161 #   |          |_ _|_ __  _ __   ___ |  _ \| __ )     |_ _/ _ \            |
 162 #   |           | || '_ \| '_ \ / _ \| | | |  _ \ _____| | | | |           |
 163 #   |           | || | | | | | | (_) | |_| | |_) |_____| | |_| |           |
 164 #   |          |___|_| |_|_| |_|\___/|____/|____/     |___\___/            |
 165 #   |                                                                      |
 166 #   '----------------------------------------------------------------------'
 167
 168
 169 def check_mysql_iostat(item, params, parsed):
 170     if item in parsed:
 171         values = parsed[item]
 172
 173         if not ("Innodb_data_read" in values and "Innodb_data_written" in values):
 174             return 3, "Incomplete agent output"
 175
 176         line = [None, None, values["Innodb_data_read"] / 512, values["Innodb_data_written"] / 512]
 177         return check_diskstat_line(time.time(), 'innodb_io' + item, params, line)
 178
 179
 180 check_info['mysql.innodb_io'] = {
 181     "inventory_function": discover(lambda k, values: "Innodb_data_read" in values),
 182     "check_function": check_mysql_iostat,
 183     "service_description": "MySQL InnoDB IO %s",
 184     "has_perfdata": True,
 185     "group": "mysql_innodb_io",
 186     "includes": ["diskstat.include"],
 187 }
 188
 189 #.
 190 #   .--Connections---------------------------------------------------------.
 191 #   |        ____                            _   _                         |
 192 #   |       / ___|___  _ __  _ __   ___  ___| |_(_) ___  _ __  ___         |
 193 #   |      | |   / _ \| '_ \| '_ \ / _ \/ __| __| |/ _ \| '_ \/ __|        |
 194 #   |      | |__| (_) | | | | | | |  __/ (__| |_| | (_) | | | \__ \        |
 195 #   |       \____\___/|_| |_|_| |_|\___|\___|\__|_|\___/|_| |_|___/        |
 196 #   |                                                                      |
 197 #   +----------------------------------------------------------------------+
 198
 199
 200 # TODO: This check should rather output the current number of connections.
 201 # The historic maximum can be viewed in the RRD data...
 202 def check_mysql_connections(item, params, parsed):
 203     if item in parsed:
 204         values = parsed[item]
 205         if 'Max_used_connections' not in values:
 206             return 3, 'Connection information are missing'
 207
 208         # The maximum number of connections that have been in use simultaneously
 209         # since the server started.
 210         conn = float(values['Max_used_connections'])
 211
 212         # Maximum number of possible parallel connections
 213         max_conn = float(values['max_connections'])
 214
 215         perc_used = conn / max_conn * 100
 216
 217         status = 0
 218         status_txt = ''
 219         perf_data = [
 220             ("connections_max_used", conn),
 221             ("connections_max", max_conn),
 222             ('connections_perc_used', perc_used),
 223         ]
 224
 225         if 'perc_used' in params:
 226             warn, crit = params['perc_used']
 227             if perc_used >= crit:
 228                 status = 2
 229                 status_txt = ' (Threshold (%0.2f%%) for number of maximum parallel connections ' \
 230                              'has been reached at least once since program start' % crit
 231             elif perc_used >= warn:
 232                 status = 1
 233                 status_txt = ' (Threshold (%0.2f%%) for number of maximum parallel connections ' \
 234                              'has been reached at least once since program start' % warn
 235
 236
 237         return status, 'Max. parallel Connections: %d (Max.: %d): %0.2f%%%s' % \
 238                 (conn, max_conn, perc_used, status_txt), perf_data
 239
 240
 241 @discover
 242 def mysql_connections(instance, values):
 243     return all(x in values for x in ['Max_used_connections', 'max_connections'])
 244
 245
 246 check_info['mysql.connections'] = {
 247     "inventory_function": mysql_connections,
 248     "check_function": check_mysql_connections,
 249     "service_description": "MySQL Connections %s",
 250     "group": "mysql_connections",
 251     "has_perfdata": True,
 252 }