22

✏️ Editing: check_mysql_connections

#!/usr/bin/python
#
# Nagios plugin for MySQL tests, v0.2
# compatible with Python2.5+
# 10-06-2023 lukasz@worldhost.group
# 15-08-2023 - get thresholds from args
# 21-08-2023 - display failed tests in the alert
#
# Alerting conditions:
# 1. CRITICAL: when total amount of commands with 'Execute'/'Query' is > '--max-qe-processes'
# 2. WARNING: when 'Open_table_definitions' >= '@@table_definition_cache'
# 3. CRITICAL: when there are any queries in 'query cache lock' state
# 4. UNKNOWN: when MySQL command fails (ie db server is stopped)
###
import argparse
import subprocess
import sys

# reuse this function for all MySQL commands to make the code slightly shorter
def run_mysql_command(cmd):
    mysql_output = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
    cmd_return = mysql_output.wait()

if cmd_return != 0:
        print("MySQL command ended with non-zero status (" + str(cmd_return) + ")")
        sys.exit(3)

stdout, _ = mysql_output.communicate()
    try:
        mysql_result = stdout.decode('utf-8').split('\n')
    except UnicodeDecodeError:
        pass
    return mysql_result

# return amount of MySQL commands with 'Execute' and 'Query' types only
def num_procs():
    cmd_num_procs = "sudo /usr/bin/mysql -e \"show processlist;\""
    cmd_output = run_mysql_command(cmd_num_procs)
    num_queries=0

for cur_line in cmd_output:
        if '\tQuery\t' in cur_line or '\tExecute\t' in cur_line:
            num_queries+=1

return(num_queries)

# return value of MySQL variable 'Open_table_definitions'
def open_tables_vs_table_def():
    cmd_table_defs = "sudo /usr/bin/mysql -e \"show global status where Variable_name = 'Open_table_definitions';\""
    cmd_tabledef_cache = "sudo /usr/bin/mysql -e 'select @@table_definition_cache;'"
    cmd_output1 = run_mysql_command(cmd_table_defs)
    cmd_output2 = run_mysql_command(cmd_tabledef_cache)
    global table_definition_cache

for string in cmd_output1:
        if 'Open_table_definitions' in string:
            global open_table_definitions
            open_table_definitions = string.split('\t')[1]
            break

table_definition_cache = cmd_output2[1]
    if result_num_procs < args.trigger_above_numprocs:
        return 0
    else:
        if int(open_table_definitions) >= int(table_definition_cache):
            return 1
        else:
            return 0

# check for any state with 'Waiting for query cache lock'
def query_cache_lock():
    cmd_check_cache_lock = "sudo /usr/bin/mysql -e \"show processlist;\""
    cmd_output = run_mysql_command(cmd_check_cache_lock)
    global num_query_cache_lock

num_query_cache_lock=0
    for cur_line in cmd_output:
        if 'Waiting for query cache lock' in cur_line:
            num_query_cache_lock+=1

def status_info():
    sys.stdout.write("(Query/Execute processes: " + str(result_num_procs) + "; ")
    sys.stdout.write("Open_table_definitions / @@table_definiton_cache: " + str(open_table_definitions) + "/" + str(table_definition_cache) + "; ")
    sys.stdout.write("queries with cache lock: " + str(num_query_cache_lock) + ")\n")

## main
parser = argparse.ArgumentParser(description="Check MySQL connections Icinga plugin")
parser.add_argument("--max-qe-processes", type=int, required=True, dest="max_qe_procs", help="defines maximum allowed query/execute processes, set return level to CRITICAL if the result is above this threshold")
parser.add_argument("--trigger-open-table-check-above-numprocs", type=int, dest="trigger_above_numprocs", required=True, help="enable open_table_definitons check against table_definition_cache above this threshold")
parser.add_argument("--max-query-cache-lock", type=int, required=True, dest="max_query_cache_lock", help="set return level to CRITICAL if the amount of queries with cache lock is above this threshold")
args = parser.parse_args()

result_num_procs = num_procs()
result_open_tables_vs_table_def = open_tables_vs_table_def()
result_query_cache_lock = query_cache_lock()
ret=0

if result_num_procs > args.max_qe_procs:
    ret+=1

if result_open_tables_vs_table_def == 1:
    ret+=2

if num_query_cache_lock > args.max_query_cache_lock:
    ret+=4

# when alerting, print all failed tests
if ret == 0:
    sys.stdout.write("OK ")
    status_info()
    sys.exit(0)
elif ret == 1:
    sys.stdout.write(str(result_num_procs) + " command(s) in Execute/Query state, above " + str(args.max_qe_procs) + " ")
    status_info()
    sys.exit(1)
elif ret == 2:
    sys.stdout.write("open_table_definitions >= @@table_definition_cache ")
    status_info()
    sys.exit(2)
elif ret == 3:
    sys.stdout.write("CRITICAL1: " + str(result_num_procs) + " command(s) in Execute/Query state, above " + str(args.max_qe_procs) + " :: CRITICAL2: open_table_definitions >= @@table_definition_cache ")
    status_info()
    sys.exit(2)
elif ret == 4:
    sys.stdout.write(str(num_query_cache_lock) + " command(s) with query_cache_lock, max " + str(args.max_query_cache_lock) + " allowed ")
    status_info()
    sys.exit(2)
elif ret == 5:
    sys.stdout.write("WARNING: " + str(result_num_procs) + " command(s) in Execute/Query state, above " + str(args.max_qe_procs) + " :: CRITICAL: " + str(num_query_cache_lock) + " command(s) with query_cache_lock, max " + str(args.max_query_cache_lock) + " allowed ")
    status_info()
    sys.exit(2)
elif ret == 6:
    sys.stdout.write("CRITICAL1: open_table_definitions >= @@table_definition_cache " + ":: CRITICAL2: " + str(num_query_cache_lock) + " command(s) with query_cache_lock, max " + str(args.max_query_cache_lock) + " allowed ")
    status_info()
    sys.exit(2)
elif ret == 7:
    sys.stdout.write("WARNING: " + str(result_num_procs) + " command(s) in Execute/Query state, above " + str(args.max_qe_procs) + " :: CRITICAL1: open_table_definitions >= @@table_definition_cache " + ":: CRITICAL2: " + str(num_query_cache_lock) + " command(s) with query_cache_lock, max " + str(args.max_query_cache_lock) + " allowed ")
    status_info()
    sys.exit(2)

MR SKB