Custom MySQL statistics with collectd

Written by Pythian Marketing | Aug 29, 2016 4:00:00 AM

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 move collectd.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 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 MinVersion clause 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 Query clause
The metric name will be a combination of the Type and 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 dbi by 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.

Conclusion

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.

MySQL Database Consulting Services

Ready to optimize your MySQL Database for the future?

View full post