collectd-python module for reading MariaDB user statistics

collectd_mariadb_tablestats.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

# collectd-python module for reading MariaDB user statistics
# Author: Marc Falzon
#
# Requirements:
#   * MariaDB >= 5.2.0 with user statistics enabled
#       (see https://mariadb.com/kb/en/mariadb/user-statistics/)
#   * collectd compiled with Python module
#   * MySQL Python connector
#     (see http://dev.mysql.com/doc/connector-python/en/index.html)
#
# Configuration:
#
#   <Plugin python>
#     LogTraces true
#     Interactive false
#     Import "collectd_mariadb_tablestats"
#     <Module collectd_mariadb_tablestats>
#       mariadb_login "<user>"
#       mariadb_password "<password>"
#       mariadb_ignore_schemas "..." ["..." "..."]
#       mariadb_ignore_tables "..." ["..." "..."]
#     </Module>
#   </Plugin>
#
# References:
# https://mariadb.com/blog/how-connect-python-programs-mariadb
# https://collectd.org/documentation/manpages/collectd-python.5.shtml

import collectd
import mysql.connector as mariadb
from fnmatch import fnmatch


def _is_ignored(string, patterns):
    for pattern in patterns:
        if fnmatch(string, pattern):
            return True
    return False


def config(config):
    login = ''
    password = ''
    data = {
        'ignored_schemas': (),
        'ignored_tables': (),
    }

    if len(config.values) == 1 and config.values[0] == 'collectd_mariadb_tablestats':
        for child in config.children:
            if child.key == 'mariadb_login':
                login = child.values[0]
            if child.key == 'mariadb_password':
                password = child.values[0]
            if child.key == 'mariadb_ignore_schemas':
                data['ignored_schemas'] = child.values
            if child.key == 'mariadb_ignore_tables':
                data['ignored_tables'] = child.values

        try:
            db = mariadb.connect(user=login, password=password)
            data['db'] = db
        except mariadb.Error as error:
            print("collectd_mariadb_tablestats: error: {}".format(error))
            return

        collectd.register_read(read, data=data)
        collectd.register_shutdown(shutdown, data=data)


def read(data):
    cursor = data['db'].cursor()

    try:
        cursor.execute("SHOW TABLE_STATISTICS")
    except mariadb.Error as error:
        print("collectd_mariadb_tablestats: error: {}".format(error))
        return

    for table_schema, table_name, rows_read, rows_changed, _ in cursor:
        if _is_ignored(table_schema, data['ignored_schemas']) or _is_ignored(table_name, data['ignored_tables']):
            continue

        vl = collectd.Values(plugin='mariadb_tablestats', type='derive')
        vl.plugin_instance = '%s-%s' % (table_schema, table_name)

        vl.dispatch(interval=-1, type_instance='rows_read', values=[rows_read])
        vl.dispatch(interval=-1, type_instance='rows_changed', values=[rows_changed])


def shutdown(data):
    data['db'].close()


collectd.register_config(config)