Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
icinga-migration opened this issue Jun 20, 2016 · 2 comments

Comments

Projects
None yet
1 participant
@icinga-migration
Copy link
Member

commented Jun 20, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 20, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Nov 11, 2016

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

  • Parent Id deleted 10073
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.