MariaDB Table statistics with collectd-python

Published on

MariaDB is an open source fork of MySQL initiated by its original author Michael "Monty" Widenius. It is a drop-in replacement of the famous open source RDBMS – enhancing it with some very nice features and performance improvements.

MariaDB is virtually compatible with almost all third-party tools and utilities of the MySQL ecosystem, including monitoring tools. My dear go-to metrics collecting tool collectd has a native MySQL plugin that is also compatible, although MariaDB can expose more metrics than MySQL – and than the collectd plugin is aware of. Case in point: the user statistics, introduced in MariaDB 5.2.0.

These statistics provide various fine-grained metrics on user activity and tables/indexes usage. Being especially interested in table statistics to know which tables are the most queried/modified, I decided to poll these metrics using collectd's Python module. This module executes Python code within a collectd process, avoiding the overhead of spawning a full-fledged Python interpreter every N seconds for instance using collectd's exec plugin.

Here is my attempt at such a Python module:

Requirements:

Limitations:

Copy the script in a location known to your system's Python interpreter:

$ python -c 'import sys; print sys.path'
['', '/usr/lib/python2.7', '/usr/lib/python2.7/plat-linux2', '/usr/lib/python2.7/lib-tk', '/usr/lib/python2.7/lib-old', '/usr/lib/python2.7/lib-dynload', '/usr/local/lib/python2.7/dist-packages', '/usr/lib/python2.7/dist-packages', '/usr/lib/pymodules/python2.7']

Note: it is also possible to configure collectd using the ModulePath directive to call the script from another location outside of Python's standard paths.

Here is the corresponding collectd configuration to use it:

<Plugin python>
  LogTraces true
  Interactive false
  Import "collectd_mariadb_tablestats"

  <Module collectd_mariadb_tablestats>
    mariadb_login "<DB login>"
    mariadb_password "<DB password>"
    mariadb_ignore_schemas "mysql"
    mariadb_ignore_tables "not_this_table" "not_this_ones_either_*"
  </Module>
</Plugin>

Note: the Import directive must have the same name as the Python script – without the .py extension –, e.g. Import "collectd_mariadb_tablestats" if the script is at /usr/local/lib/python2.7/dist-packages/collectd_mariadb_tablestats.py.

The module is configurable via a Module directive within the Python module block, that must have the same name as the Python script – without the .py extension:

Here is an example of the collected metrics displayed in Facette: