In larger installations of Icinga 2, IDO DB cleanup can become a challenge. Those cleanup queries are scheduled in between any other INSERT or UPDATE query.
This can cause two problems:
- Regular updates are deferred when cleanup is running
- Not all tables are indexed correctly, so cleanup can take longer
This tool is build to realize IDO cleanup outside of Icinga 2, so you should disable any cleanup inside
/etc/icinga2/features-available/ido*.conf and setup this tool as service.
Currently, only MySQL is supported.
You can download the built binary from releases.
To run the daemon with systemd see systemd directory, and install the files like follows, making sure to adjust the files to your needs.
cp netways-ido-cleanup.service /etc/systemd/system/
cp netways-ido-cleanup.env /etc/icinga2/netways-ido-cleanup
chown icinga.icinga /etc/icinga2/netways-ido-cleanup
chmod 640 /etc/icinga2/netways-ido-cleanup
systemctl daemon-reload
systemctl enable netways-ido-cleanup.service
systemctl start netways-ido-cleanup.service
systemctl status netways-ido-cleanup.service
$ export DB_DSN='icinga:icinga@tcp(database:3306)/icinga'
$ ./ido-cleanup
Usage of ido-cleanup:
--db string DB Connecting string (env:DB_DSN) (default "icinga:icinga@/icinga2")
--instance string IDO instance name (default "default")
--limit int Limit deleting rows in one query (default 10000)
--interval duration Cleanup every X seconds (default 1m0s)
--fast-interval duration Cleanup every X seconds - when more then 2x limit rows to delete (default 10s)
--once Just run once
--noop Just check - don't purge
--debug Enable debug logging
--acknowledgements uint How long to keep entries of acknowledgements in days
--commenthistory uint How long to keep entries of commenthistory in days (default 365)
--contactnotifications uint How long to keep entries of contactnotifications in days (default 365)
--contactnotificationmethods uint How long to keep entries of contactnotificationmethods in days
--downtimehistory uint How long to keep entries of downtimehistory in days (default 365)
--eventhandlers uint How long to keep entries of eventhandlers in days (default 365)
--externalcommands uint How long to keep entries of externalcommands in days
--flappinghistory uint How long to keep entries of flappinghistory in days
--hostchecks uint How long to keep entries of hostchecks in days
--logentries uint How long to keep entries of logentries in days (default 365)
--notifications uint How long to keep entries of notifications in days (default 365)
--processevents uint How long to keep entries of processevents in days
--statehistory uint How long to keep entries of statehistory in days (default 365)
--servicechecks uint How long to keep entries of servicechecks in days
--systemcommands uint How long to keep entries of systemcommands in days
$ ido-cleanup --once
INFO[0000] starting ido-cleanup
INFO[0000] deleted rows oldest="2019-03-01 11:26:44 +0000 UTC" rows=10000 table=commenthistory took=72.647379ms
INFO[0000] deleted rows oldest="2019-01-21 19:35:14 +0000 UTC" rows=6129 table=contactnotifications took=76.247963ms
INFO[0000] deleted rows oldest="2019-02-06 09:42:08 +0000 UTC" rows=209 table=downtimehistory took=3.992535ms
INFO[0000] deleted rows oldest="2019-04-23 07:29:36 +0000 UTC" rows=60 table=eventhandlers took=6.685573ms
INFO[0000] deleted rows oldest="2019-01-21 19:35:14 +0000 UTC" rows=6212 table=notifications took=84.653149ms
INFO[0000] deleted rows oldest="2019-01-20 10:16:30 +0000 UTC" rows=10000 table=statehistory took=135.619702ms
INFO[0000] stopping after one cleanup
Not all tables are indexed in a way to support fast deletions.
Please add those indexes to ensure speedy queries.
CREATE INDEX idx_notifications_cleanup ON icinga_notifications (`instance_id`,`start_time`);
CREATE INDEX idx_contactnotifications_cleanup on icinga_contactnotifications (instance_id, start_time);See Icinga/icinga2#7753.
Copyright (C) 2021 NETWAYS GmbH
This library is distributed under the GPL-3.0 license found in the LICENSE file.