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:
- MariaDB user statistics enabled
- collectd compiled with Python module
- MySQL Python connector
- the module expects the MariaDB server to be listening on the local host on the port 3306
- polls the
ROWS_CHANGEDmetrics from the
- not tested with Python 3
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>
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
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:
mariadb_login: login of the user to log into the database server
mariadb_password: password of the user to log into the database server
mariadb_ignore_schemas: list of Unix shell-style wildcards for ignoring certain schemas (databases) from the results
mariadb_ignore_tables: list of Unix shell-style wildcards for ignoring certain tables from the results
Here is an example of the collected metrics displayed in Facette: