[dev.icinga.com #11993] Comment/Downtime delete queries are slow

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/11993

Created by mfriedrich on 2016-06-20 15:14:32 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2016-06-20 15:40:03 +00:00)
Target Version: 2.5.0
Last Update: 2016-11-11 08:47:02 +00:00 (in Redmine)

Icinga Version: 2.4.10
Backport?: Not yet backported
Include in Changelog: 1

---

The update and delete queries for comment* and downtime are not using the currently deployed indexes. Either there's *_time missing, or the instance_id.

RemoveDowntime does not hit the index, entry_time is missing.

        query1.WhereCriteria->Set("object_id", checkable);
        query1.WhereCriteria->Set("internal_downtime_id", downtime->GetLegacyId());
        query1.WhereCriteria->Set("instance_id", 0); /* DbConnection class fills in real ID */

RemoveComment does not fit the index either, comment_time and instance_id are missing.

        query1.WhereCriteria->Set("object_id", checkable);
        query1.WhereCriteria->Set("internal_comment_id", comment->GetLegacyId());

MariaDB [icinga]> show index from icinga_comments;
+-----------------+------------+------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name               | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| icinga_comments |          0 | PRIMARY                |            1 | comment_id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_comments |          0 | instance_id            |            1 | instance_id         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_comments |          0 | instance_id            |            2 | object_id           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_comments |          0 | instance_id            |            3 | comment_time        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_comments |          0 | instance_id            |            4 | internal_comment_id | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_comments |          1 | comments_i_id_idx      |            1 | instance_id         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_comments |          1 | comments_object_id_idx |            1 | object_id           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

MariaDB [icinga]> show index from icinga_commenthistory;
+-----------------------+------------+---------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name                  | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+---------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| icinga_commenthistory |          0 | PRIMARY                   |            1 | commenthistory_id   | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_commenthistory |          0 | instance_id               |            1 | instance_id         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_commenthistory |          0 | instance_id               |            2 | object_id           | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_commenthistory |          0 | instance_id               |            3 | comment_time        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_commenthistory |          0 | instance_id               |            4 | internal_comment_id | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_commenthistory |          1 | commenthistory_delete_idx |            1 | instance_id         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_commenthistory |          1 | commenthistory_delete_idx |            2 | comment_time        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_commenthistory |          1 | commenthistory_delete_idx |            3 | internal_comment_id | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------------+------------+---------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.01 sec)

MariaDB [icinga]> show index from icinga_scheduleddowntime;
+--------------------------+------------+---------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                    | Non_unique | Key_name                        | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------+------------+---------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| icinga_scheduleddowntime |          0 | PRIMARY                         |            1 | scheduleddowntime_id | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_scheduleddowntime |          0 | instance_id                     |            1 | instance_id          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_scheduleddowntime |          0 | instance_id                     |            2 | object_id            | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_scheduleddowntime |          0 | instance_id                     |            3 | entry_time           | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_scheduleddowntime |          0 | instance_id                     |            4 | internal_downtime_id | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_scheduleddowntime |          1 | scheduleddowntime_i_id_idx      |            1 | instance_id          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_scheduleddowntime |          1 | scheduleddowntime_object_id_idx |            1 | object_id            | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------------+------------+---------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

MariaDB [icinga]> show index from icinga_downtimehistory;
+------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name       | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| icinga_downtimehistory |          0 | PRIMARY        |            1 | downtimehistory_id   | A         |         177 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_downtimehistory |          0 | instance_id    |            1 | instance_id          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_downtimehistory |          0 | instance_id    |            2 | object_id            | A         |          59 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_downtimehistory |          0 | instance_id    |            3 | entry_time           | A         |         177 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_downtimehistory |          0 | instance_id    |            4 | internal_downtime_id | A         |         177 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_downtimehistory |          1 | sla_idx_dohist |            1 | object_id            | A         |          59 |     NULL | NULL   | YES  | BTREE      |         |               |
| icinga_downtimehistory |          1 | sla_idx_dohist |            2 | actual_start_time    | A         |          88 |     NULL | NULL   |      | BTREE      |         |               |
| icinga_downtimehistory |          1 | sla_idx_dohist |            3 | actual_end_time      | A         |         177 |     NULL | NULL   |      | BTREE      |         |               |
+------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

Changesets

2016-06-20 15:38:01 +00:00 by mfriedrich 409a385

DB IDO: Ensure that delete queries for comments/downtimes are matching the index

fixes #11993

[icinga-migration]

Updated by mfriedrich on 2016-06-20 15:40:03 +00:00

• Status changed from Assigned to Resolved
• Done % changed from 0 to 100

Applied in changeset 409a385.

[icinga-migration]

Updated by mfriedrich on 2016-11-11 08:47:03 +00:00

• Parent Id deleted 10073