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:
- MariaDB user statistics enabled
- collectd compiled with Python module
- MySQL Python connector
Limitations:
- the module expects the MariaDB server to be listening on the local host on the port 3306
- polls the
ROWS_READ
/ROWS_CHANGED
metrics from theTABLE_STATISTICS
only - 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>
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:
mariadb_login
: login of the user to log into the database servermariadb_password
: password of the user to log into the database servermariadb_ignore_schemas
: list of Unix shell-style wildcards for ignoring certain schemas (databases) from the resultsmariadb_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: