Even though collectd has a MySQL plugin, you may want to collect other statistics, not included with the plugin or even custom ones from your applications. The DBI plugin allows you to build statistics from queries, opening a world of possibilities.
What about Plugin:MySQL?
Several of our customers use collectd to capture and push system statistics to graphs engines such as graphite. When it comes to MySQL, it has a specific plugin with several built-in statistics that are useful but not enough in most cases.
One option to overcome this limitation is to use the DBI plugin to build custom statistics, based on MySQL queries. This approach can be used to easily capture any status variable or even to collect application metrics. It’s worth mentioning that even though this post is oriented to MySQL databases, the DBI plugin can be used with other RDBMSs as well.
Let’s see what minimum steps are required to create a DBI-based metric against a MySQL database.
Step 1: Install / make sure the following packages are available
rhelhost~# yum install collectd-mysql libdbi-dbd-mysql
If you had collectd installed but an upgraded was required, you would need to move
collectd.conf.rpmsave back to
Step 2: Create the dbi.conf file
# vim /etc/collectd.d/dbi.conf
LoadPlugin dbi <plugin dbi=''> <query "temp_tables_ondisk"=''> Statement "select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'CREATED_TMP_DISK_TABLES'" MinVersion 50100 <result> Type "derive" InstancePrefix "ondisk_tmp_tables" ValuesFrom "VARIABLE_VALUE" </result> </query> <query "another_dbi_metric"=''> ...... </query> <database "local_mysql_server"=''> Driver "mysql" DriverOption "host" "127.0.0.1" DriverOption "username" "xxxxxxxxx" DriverOption "password" "xxxxxxxxx" DriverOption "dbname" "information_schema" SelectDB "information_schema" Query "temp_tables_ondisk" Query "another_dbi_metric" </database> </plugin>
Aside from the
LoadPlugin clause, this file will store the metrics configuration and datasource. The configuration is fairly self explanatory and each section is documented here, but allow me to point out a few details to save you time:
- Use the
MinVersionclause to specify what is the minimum MySQL version this metric can executed against (in this case version should at least 5.1)
- Specify the
<database>block once for each MySQL instance running locally. Then bind the database with the corresponding metric by using the
- The metric name will be a combination of the
Typeand the name provided inside the tag
- The Type clause inside the block will determine if you are going to be capturing the increments per time unit (
DERIVE) or the actual values returned by the query (
- Keep in mind that all queries configured will be executed simultaneously against the monitored instance. You might want to adjust the polling interval for the dbi plugin replacing
LoadPlugin dbiby the following block:
<LoadPlugin dbi> Interval <secs> </LoadPlugin>
Step 3: Restart collectd
# service collectd restart
Step 4: Check collectd log for errors
Collectd log will report any dbi errors including issues to load the module, connect to the database or execute the query. You can change your collectd logging configuration to make it more verbose or to redirect the output to a specific file:
LoadPlugin logfile <plugin logfile=''> LogLevel info File "/mnt/ephemeral/tmp/collectd.log" Timestamp true PrintSeverity false </plugin>
Step 5: Check that the values are being captured
Once your metrics are properly configured, you will see that the proper rrd file is being generated (
<type>_<query_name>.rrd) or that the new metric is available on graphite so that it could be plotted after enough values are captured.
The DBI plugin allows you to enhance MySQL (and also other popular RDBMS) monitoring when you have a collectd-based infrastructure in place. Starting from MySQL v5.1, any configuration or status variable can be accessed through the information schema, using a standard SELECT statement against GLOBAL_VARIABLES or GLOBAL_STATUS tables respectively. Finally, remember that by default, the DBI plugin will use the global collectd polling interval and even when this variable is configured at the plugin level, all queries will be executed simultaneously.
Interested in working with Gabriel? Schedule a tech call.