Our community support channels provide interesting insights into how things are being monitored in various environments. Sometimes it is not only about finding the right configuration syntax or fiddling with the perfect cluster setup. This time I’d like to share a solution for a common problem that I discovered while I was helping another Icinga user. :-)

Monitor the size of a database

Sounds easy if you are familiar with MySQL and the common check plugins – putting it all together might get complicated, especially for beginners.

Luckily, the question already provided a sample SQL query for fetching the database size:

MariaDB [(none)]> select sum(data_length + index_length) / 1024 / 1024 as "db size" from information_schema.tables where table_schema = 'icinga';
+-------------+
| db size     |
+-------------+
| 31.09375000 |
+-------------+
1 row in set (0.01 sec)

Two questions arise:

  • Is there a plugin which automatically checks the database size from a given parameter?
  • Alternatively, can I just run this query and compare the returned integer value in MB?

Find a plugin and integrate it

There’s a basic check_mysql plugin that is part of the monitoring plugins project. Additionally,  check_mysql_health has proven itself in many environments: it offers fast and easy monitoring. This plugin is also part of the Icinga training sessions demonstrating its power.

Once you’ve successfully installed it into the default Icinga 2 PluginDir (I’m skipping detailed installation instructions here), let’s go for a CheckCommand definition. The Icinga 2 Template Library (ITL) already provides such a definition inside the contributed plugins section. Include the plugins in the file /etc/icinga2/icinga2.conf:

include <plugins-contrib>

Also, make sure to define the constant PluginContribDir in the file /etc/icinga2/constants.conf:

const PluginContribDir = "/usr/lib64/nagios/plugins"

Now it is time to read about the required parameters in the documentation. We’ll need that information for setting the appropriate custom attributes later.

Create a Host and Service Apply Rule

mysql_health_db-size_icingaweb2One thing I’m always keen on: use a custom attribute dictionary on the host and allow to pass as many custom parameters to the service objects as possible. Combine this with an apply for rule and use the possibilities of the Icinga 2 DSL.

In order to use the mysql_health CheckCommand we’ll need to delegate at least the following custom attributes:

  • mysql_health_hostname: Defaults to the host’s address attribute (optional).
  • mysql_health_username: MySQL database user with the appropriate permissions for the information_schema database here.
  • mysql_health_password: MySQL database user password.
  • mysql_health_mode: “sql”, since we want to run a generic SQL query here.
  • mysql_health_name: SQL query string we want to execute; ensure that it returns a single number/count.
  • mysql_health_name2: In combination with the “sql” mode this sets the performance data label/output prefix.
  • mysql_health_units: The default calculation uses MB, so we’ll tell the plugin to use it as performance data unit.
  • mysql_health_warning: Warning threshold in MB.
  • mysql_health_critical: Critical threshold in MB.

This is a long list but once you’ve carefully read the plugin documentation and tested the various parameters it will become more clear.

Let’s construct an apply for rule generating services based on the host custom attribute databases (this is a dictionary/hash with the database name as key and multiple parameters, e.g. for thresholds).

apply Service "db-size-" for (db_name => config in host.vars.databases) {

Define the intervals and include the mysql_health check command:

  check_interval = 1m
  retry_interval = 30s

  check_command = "mysql_health"

Check whether the host dictionary provides additional configuration (such as different database username or password) and set a default. In this example the root password has access to information_schema.

  if (config.mysql_health_username) {
    vars.mysql_health_username = config.mysql_health_username
  } else {
    vars.mysql_health_username = "root"
  }
  if (config.mysql_health_password) {
    vars.mysql_health_password = config.mysql_health_password
  } else {
    vars.mysql_health_password = "icingar0xx"
  }

Now specify the sql mode and build the query. Cool thing – the query is based on the current database name that we are generating a service object for. That way we don’t need two apply rules for the databases icinga and icingaweb2 later on.

  vars.mysql_health_mode = "sql"
  vars.mysql_health_name = "select sum(data_length + index_length) / 1024 / 1024 from information_schema.tables where table_schema = '" + db_name + "';"
  vars.mysql_health_name2 = "db_size"
  vars.mysql_health_units = "MB"

Optionally, inherit warning and critical thresholds defined in the host dictionary databases. Its value is mapped into the config dictionary in the local service apply for scope. Inherit additional parameters into the service custom attributes in vars.

  if (config.mysql_health_warning) {
    vars.mysql_health_warning = config.mysql_health_warning
  }
  if (config.mysql_health_critical) {
    vars.mysql_health_critical = config.mysql_health_critical
  }

  vars += config

Question for the reader: How should the host object look like in order to generate services? :-)

The answer is simple – based on existing examples and the documentation, it is pretty straight forward:

object Host "icingamaster" {
  address = "127.0.0.1"
  check_command = "hostalive"

  /* database checks */
  vars.databases["icinga"] = {
    mysql_health_warning = 4096 //MB
    mysql_health_critical = 8192 //MB
  }
  vars.databases["icingaweb2"] = {
    mysql_health_warning = 4096 //MB
    mysql_health_critical = 8192 //MB
  }
}

Voilà – validate your configuration and reload the Icinga 2 service.

Since this is a real world example, I’ve also integrated it into the icinga2x Vagrant box. :-)

Conclusion

mysql_health_db-size_grafanaWhile it is not always clear which plugin is the best, it’s always worth looking into the existing ITL CheckCommand definitions. Maybe there already is one which also provides the perfect answer to your questions. If not, hop onto Icinga Exchange and submit the newly created CheckCommand definition to the upstream. :-)

check_mysql_health provides many possibilities to monitor your databases (local or remote) and is fairly easy to setup. Once you gather the required monitoring metrics, e.g. by manually executing the plugin or querying your database, it is all of the same (CheckCommand, host, and service configuration).

Once the Icinga 2 configuration validation returns OK, reload the daemon and enjoy fancy monitoring in Icinga Web 2 and Graphs in your preferred metrics dashboard.