## DB IDO <a id="db-ido"></a>
-The IDO (Icinga Data Output) modules for Icinga 2 take care of exporting all
+The IDO (Icinga Data Output) feature for Icinga 2 takes care of exporting all
configuration and status information into a database. The IDO database is used
-by Icinga Web 2.
+by Icinga Web 2 as data backend.
Details on the installation can be found in the [Configuring DB IDO](02-getting-started.md#configuring-db-ido-mysql)
chapter. Details on the configuration can be found in the
The DB IDO feature supports [High Availability](06-distributed-monitoring.md#distributed-monitoring-high-availability-db-ido) in
the Icinga 2 cluster.
-The following example query checks the health of the current Icinga 2 instance
-writing its current status to the DB IDO backend table `icinga_programstatus`
-every 10 seconds. By default it checks 60 seconds into the past which is a reasonable
+### DB IDO Health <a id="db-ido-health"></a>
+
+If the monitoring health indicator is critical in Icinga Web 2,
+you can use the following queries to manually check whether Icinga 2
+is actually updating the IDO database.
+
+Icinga 2 writes its current status to the `icinga_programstatus` table
+every 10 seconds. The query below checks 60 seconds into the past which is a reasonable
amount of time -- adjust it for your requirements. If the condition is not met,
the query returns an empty result.
Example for MySQL:
- # mysql -u root -p icinga -e "SELECT status_update_time FROM icinga_programstatus ps
- JOIN icinga_instances i ON ps.instance_id=i.instance_id
- WHERE (UNIX_TIMESTAMP(ps.status_update_time) > UNIX_TIMESTAMP(NOW())-60)
- AND i.instance_name='default';"
+```
+# mysql -u root -p icinga -e "SELECT status_update_time FROM icinga_programstatus ps
+ JOIN icinga_instances i ON ps.instance_id=i.instance_id
+ WHERE (UNIX_TIMESTAMP(ps.status_update_time) > UNIX_TIMESTAMP(NOW())-60)
+ AND i.instance_name='default';"
+
++---------------------+
+| status_update_time |
++---------------------+
+| 2014-05-29 14:29:56 |
++---------------------+
+```
- +---------------------+
- | status_update_time |
- +---------------------+
- | 2014-05-29 14:29:56 |
- +---------------------+
+Example for PostgreSQL:
+```
+# export PGPASSWORD=icinga; psql -U icinga -d icinga -c "SELECT ps.status_update_time FROM icinga_programstatus AS ps
+ JOIN icinga_instances AS i ON ps.instance_id=i.instance_id
+ WHERE ((SELECT extract(epoch from status_update_time) FROM icinga_programstatus) > (SELECT extract(epoch from now())-60))
+ AND i.instance_name='default'";
+
+status_update_time
+------------------------
+ 2014-05-29 15:11:38+02
+(1 Zeile)
+```
-Example for PostgreSQL:
+A detailed list on the available table attributes can be found in the [DB IDO Schema documentation](24-appendix.md#schema-db-ido).
- # export PGPASSWORD=icinga; psql -U icinga -d icinga -c "SELECT ps.status_update_time FROM icinga_programstatus AS ps
- JOIN icinga_instances AS i ON ps.instance_id=i.instance_id
- WHERE ((SELECT extract(epoch from status_update_time) FROM icinga_programstatus) > (SELECT extract(epoch from now())-60))
- AND i.instance_name='default'";
+### DB IDO Cleanup <a id="db-ido-cleanup"></a>
- status_update_time
- ------------------------
- 2014-05-29 15:11:38+02
- (1 Zeile)
+Objects get deactivated when they are deleted from the configuration.
+This is visible with the `is_active` column in the `icinga_objects` table.
+Therefore all queries need to join this table and add `WHERE is_active=1` as
+condition. Deleted objects preserve their history table entries for later SLA
+reporting.
+Historical data isn't purged by default. You can enable the least
+kept data age inside the `cleanup` configuration attribute for the
+IDO features [IdoMysqlConnection](09-object-types.md#objecttype-idomysqlconnection)
+and [IdoPgsqlConnection](09-object-types.md#objecttype-idopgsqlconnection).
-A detailed list on the available table attributes can be found in the [DB IDO Schema documentation](24-appendix.md#schema-db-ido).
+Example if you prefer to keep notification history for 30 days:
+
+```
+ cleanup = {
+ notifications_age = 30d
+ contactnotifications_age = 30d
+ }
+```
+
+The historical tables are populated depending on the data `categories` specified.
+Some tables are empty by default.
+
+### DB IDO Tuning <a id="db-ido-tuning"></a>
+
+As with any application database, there are ways to optimize and tune the database performance.
+
+General tips for performance tuning:
+
+* [MariaDB KB](https://mariadb.com/kb/en/library/optimization-and-tuning/)
+* [PostgreSQL Wiki](https://wiki.postgresql.org/wiki/Performance_Optimization)
+
+Re-creation of indexes, changed column values, etc. will increase the database size. Ensure to
+add health checks for this, and monitor the trend in your Grafana dashboards.
+
+In order to optimize the tables, there are different approaches. Always keep in mind to have a
+current backup and schedule maintenance downtime for these kind of tasks!
+
+MySQL:
+
+```
+mariadb> OPTIMIZE TABLE icinga_statehistory;
+```
+
+> **Important**
+>
+> Tables might not support optimization at runtime. This can take a **long** time.
+>
+> `Table does not support optimize, doing recreate + analyze instead`.
+
+If you want to optimize all tables in a specified database, there is a script called `mysqlcheck`.
+This also allows to repair broken tables in the case of emergency.
+
+```
+mysqlcheck --optimize icinga
+```
+
+PostgreSQL:
+
+```
+icinga=# vacuum;
+VACUUM
+```
+
+> **Note**
+>
+> Don't use `VACUUM FULL` as this has a severe impact on performance.
## External Commands <a id="external-commands"></a>
The database is assumed to exist so this object will make no attempt to create it currently.
+If [SELinux](22-selinux.md#selinux) is enabled, it will not allow access for Icinga 2 to InfluxDB until the [boolean](22-selinux.md#selinux-policy-booleans)
+`icinga2_can_connect_all` is set to true as InfluxDB is not providing its own policy.
+
More configuration details can be found [here](09-object-types.md#objecttype-influxdbwriter).
#### Instance Tagging <a id="influxdb-writer-instance-tags"></a>
> **Note**
>
-> Elasticsearch 5.x is required. This feature has been successfully tested with Elasticsearch 5.6.4.
+> Elasticsearch 5.x or 6.x are required. This feature has been successfully tested with
+> Elasticsearch 5.6.7 and 6.3.1.
+
+
Enable the feature and restart Icinga 2.
The following characters are escaped in perfdata labels:
- Character | Escaped character
- --------------|--------------------------
- whitespace | _
- \ | _
- / | _
- :: | .
+ Character | Escaped character
+ ------------|--------------------------
+ whitespace | _
+ \ | _
+ / | _
+ :: | .
Note that perfdata labels may contain dots (`.`) allowing to
add more subsequent levels inside the tree.
and, or, negate
Operator | Negate | Description
- ----------|------------------------
+ ----------|----------|-------------
= | != | Equality
~ | !~ | Regex match
=~ | !=~ | Equality ignoring case
## Status Data Files <a id="status-data"></a>
+> **Note**
+>
+> This feature is DEPRECATED and will be removed in Icinga 2 v2.11.
+
Icinga 1.x writes object configuration data and status data in a cyclic
interval to its `objects.cache` and `status.dat` files. Icinga 2 provides
the `StatusDataWriter` object which dumps all configuration objects and
If you are not using any web interface or addon which uses these files,
you can safely disable this feature.
-
## Compat Log Files <a id="compat-logging"></a>
+> **Note**
+>
+> This feature is DEPRECATED and will be removed in Icinga 2 v2.11.
+
The Icinga 1.x log format is considered being the `Compat Log`
in Icinga 2 provided with the `CompatLogger` object.
## Check Result Files <a id="check-result-files"></a>
+> **Note**
+>
+> This feature is DEPRECATED and will be removed in Icinga 2 v2.11.
+
Icinga 1.x writes its check result files to a temporary spool directory
where they are processed in a regular interval.
While this is extremely inefficient in performance regards it has been
projects / icinga2 / blobdiff