Keeping icinga2's database usage in check

This may have been obvious to those who have managed to navigate the icinga2 documentation better than I, but apparently the IdoMySqlConnection object keeps the following information (and more) in the database forever by default:

• every notification
• every log message
• every state change

See the full list in the IdoMySqlConnection section of the docs. Included amongst this data is the full host/service status at the time.

On paper, this is quite sensible, but when you have many thousands of checks being scheduled every minute this can rapidly balloon. Additionally, (although indexes have been added over time) this almost certainly hurts the performance of frontend tools such as icingaweb2.

Luckily, icinga2 does have built-in cleaning code which will tidy up data after it reaches a certain age; simply turn it on in your IdoMySqlConnection object definition. For example:

object IdoMysqlConnection "mysql-ido" {
  user = "icinga2"
  password = "icinga2password"
  database = "icinga2"

  cleanup = {
    downtimehistory_age = 48h
    contactnotifications_age = 31d
  }
}

Again, refer to the IdoMySqlConnection section in the docs for a full list of parameters that may be passed to clean.

Not so fast

There is a downside. If, like me, you’ve got to this point after months or years of running icinga2, you may have fairly large¹ tables for some of these types, and icinga2 isn’t too intelligent about how it does its cleanup². For example:

DELETE FROM icinga_logentries
  WHERE instance_id = 1
    AND logentry_time <= FROM_UNIXTIME(1456790400);

If you are in this position, and don’t value your historic data too highly, I would recommend truncating the relevant tables before enabling cleanup; it should be relatively obvious which table corresponds to which type of cleanup.