IcingaDB does not delete history data from all tables containin historical data

Icinga 2 Icinga DB
1

The IcingaDB configuration mentions that history data can be deleted: Configuration - Icinga DB

This works as expected, but I am wondering, why the “history” table is not cleaned by the configuration. At the moment, it is 500MB in size - after 4 months of usage. And we do not need this history data.

# Retention is an optional feature to limit the number of days that historical data is available,
# as no historical data is deleted by default.
retention:
  # Number of days to retain full historical data. By default, historical data is retained forever.
  history-days: 30

  # Number of days to retain historical data for SLA reporting. By default, it is retained forever.
  sla-days: 30

  # Map of history category to number of days to retain its data in order to
  # enable retention only for specific categories or to
  # override the number that has been configured in history-days.
  options:
#    acknowledgement:
#    comment:
#    downtime:
#    flapping:
#    notification:
#    state:

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: Starting history retention

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category acknowledgement

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category comment

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category downtime

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category flapping

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category notification

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category state

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category sla_downtime

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Starting history retention for category sla_state

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category sla_state from table sla_history_state older than 2024-09-24 14:01:44.491232117 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: config-sync: Finished initial state sync in 466.494887ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: Starting state runtime updates sync

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: runtime-updates: Syncing runtime updates of HostState

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: runtime-updates: Syncing runtime updates of ServiceState

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category acknowledgement from table acknowledgement_history older than 2024-09-24 14:01:44.492486466 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category comment from table comment_history older than 2024-09-24 14:01:44.492510422 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category downtime from table downtime_history older than 2024-09-24 14:01:44.492601616 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category flapping from table flapping_history older than 2024-09-24 14:01:44.492614836 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: runtime-updates: Syncing runtime updates of Customvar

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: runtime-updates: Syncing runtime updates of CustomvarFlat

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category notification from table notification_history older than 2024-09-24 14:01:44.492627335 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category state from table state_history older than 2024-09-24 14:01:44.492642931 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: retention: Cleaning up historical data for category sla_downtime from table sla_history_downtime older than 2024-09-24 14:01:44.49265397 +0000 UTC

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM sla_history_state WHERE environment_id = :environment_id AND event_time < :time\nORDER BY event_time LIMIT 5000" with 0 rows in 3.39099ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM acknowledgement_history WHERE environment_id = :environment_id AND clear_time < :time\nORDER BY clear_time LIMIT 5000" with 0 rows in 2.881939ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM comment_history WHERE environment_id = :environment_id AND remove_time < :time\nORDER BY remove_time LIMIT 5000" with 0 rows in 2.95242ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM downtime_history WHERE environment_id = :environment_id AND end_time < :time\nORDER BY end_time LIMIT 5000" with 0 rows in 2.915408ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM flapping_history WHERE environment_id = :environment_id AND end_time < :time\nORDER BY end_time LIMIT 5000" with 0 rows in 3.606936ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM sla_history_downtime WHERE environment_id = :environment_id AND downtime_end < :time\nORDER BY downtime_end LIMIT 5000" with 0 rows in 11.063588ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM notification_history WHERE environment_id = :environment_id AND send_time < :time\nORDER BY send_time LIMIT 5000" with 0 rows in 11.618811ms

Oct 24 14:01:44 icinga0.cloud.nospamproxy.com icingadb[1382592]: database: Finished executing "DELETE FROM state_history WHERE environment_id = :environment_id AND event_time < :time\nORDER BY event_time LIMIT 5000" with 0 rows in 14.22041ms
  • Icinga DB Web version (System - About):
  • Icinga Web 2 version (System - About): 2.12.1
  • Web browser: Edge
  • Icinga 2 version (icinga2 --version): r2.14.2-1
  • Icinga DB version (icingadb --version): 1.1.3
  • PHP version used (php --version): PHP 8.1.2-1ubuntu2.19
  • Server operating system and version: Ubuntu 24.04
2

The SQL schema should take of this, as the history table references the other tables where retention takes place with a cascade rule.

I have just verified this on a testing instance.

MariaDB [icingadb]> select id from state_history limit 1;
+--------------------------------------------+
| id                                         |
+--------------------------------------------+
| 0x2959401C8032940C1E5021C71C36937073BE5409 |
+--------------------------------------------+
1 row in set (0.001 sec)

MariaDB [icingadb]> select id from history where state_history_id = 0x2959401C8032940C1E5021C71C36937073BE5409;
+--------------------------------------------+
| id                                         |
+--------------------------------------------+
| 0x785BA5AEBC679B30DFB039C32A62272B49346ABE |
+--------------------------------------------+
1 row in set (0.001 sec)

MariaDB [icingadb]> delete from state_history where id = 0x2959401C8032940C1E5021C71C36937073BE5409;
Query OK, 1 row affected (0.001 sec)

MariaDB [icingadb]> select * from history where id = 0x785BA5AEBC679B30DFB039C32A62272B49346ABE;
Empty set (0.001 sec)

As the history table references every other history table, it may just be bigger.
Could you please show the timestamps of the oldest entries?

select event_time from history order by event_time asc limit 10;