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.Quick start
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
[code lang="bash"] rhelhost~# yum install collectd-mysql libdbi-dbd-mysql [/code] If you had collectd installed but an upgraded was required, you would need to movecollectd.conf.rpmsave back to
collectd.conf
Step 2: Create the dbi.conf file
[code lang="bash"] # vim /etc/collectd.d/dbi.conf [/code] [code lang="xml"] 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> [/code] Aside from theLoadPlugin 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 theQueryclause - 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 (GAUGE) - 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
[code lang="bash"] # service collectd restart [/code]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: [code lang="xml"] LoadPlugin logfile <plugin logfile=''> LogLevel info File "/mnt/ephemeral/tmp/collectd.log" Timestamp true PrintSeverity false </plugin> [/code]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.
