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

Jump to bottom

[dev.icinga.com #10066] Missing indexes for icinga_endpoints* and icinga_zones* tables in DB IDO schema #3361

Closed
icinga-migration opened this issue Sep 1, 2015 · 8 comments
Closed

[dev.icinga.com #10066] Missing indexes for icinga_endpoints* and icinga_zones* tables in DB IDO schema #3361

icinga-migration opened this issue Sep 1, 2015 · 8 comments
Labels
area/db-ido Database output bug Something isn't working
Milestone
2.5.0

Comments

@icinga-migration
Copy link

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

Created by tgelf on 2015-09-01 12:33:42 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2016-06-22 10:57:17 +00:00)
Target Version: 2.5.0
Last Update: 2016-11-11 08:46:51 +00:00 (in Redmine)

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

It's a new feature and rarely used, still tables should get meaningful indexes.

Changesets

2016-06-22 10:56:24 +00:00 by mfriedrich 7f56a04

DB IDO: Add missing indexes for icinga_endpoints* and icinga_zones* tables

fixes #10066

Relations:
@icinga-migration
Copy link
Author

Updated by tgelf on 2015-09-01 14:11:02 +00:00

Same goes for icinga_zones and icinga_zonestatus

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-01 14:21:04 +00:00

  • Parent Id set to 10073

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-09-04 09:21:54 +00:00

  • Status changed from New to Assigned

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-01-23 11:47:39 +00:00

  • Status changed from Assigned to New
  • Assigned to deleted tgelf

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-06-22 10:16:22 +00:00

  • Duplicated set to 12012

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-06-22 10:49:36 +00:00

  • Subject changed from IDO: There is no index related to icinga_endpoints to Missing indexes for icinga_endpoints* and icinga_zones* tables in DB IDO schema
  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
  • Target Version set to 2.5.0

MySQL

icinga_endpoints{,tatus}

  • endpoint_object_id
  • zone_object_id
MariaDB [icinga]> explain select oe.name1 as endpoint_name, oz.name1 as zone_name from icinga_endpoints e join icinga_objects oe on e.endpoint_object_id=oe.object_id join icinga_zones z on e.zone_object_id=z.zone_object_id join icinga_objects oz on z.zone_object_id=oz.object_id;
+------+-------------+-------+--------+---------------------+---------------------+---------+-----------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys       | key                 | key_len | ref                         | rows | Extra       |
+------+-------------+-------+--------+---------------------+---------------------+---------+-----------------------------+------+-------------+
|    1 | SIMPLE      | e     | ALL    | NULL                | NULL                | NULL    | NULL                        |    8 | Using where |
|    1 | SIMPLE      | z     | ref    | idx_zones_object_id | idx_zones_object_id | 9       | icinga.e.zone_object_id     |    1 | Using index |
|    1 | SIMPLE      | oe    | eq_ref | PRIMARY             | PRIMARY             | 8       | icinga.e.endpoint_object_id |    1 |             |
|    1 | SIMPLE      | oz    | eq_ref | PRIMARY             | PRIMARY             | 8       | icinga.e.zone_object_id     |    1 |             |
+------+-------------+-------+--------+---------------------+---------------------+---------+-----------------------------+------+-------------+
4 rows in set (0.00 sec)

CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id);
CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id);

CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id);
CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id);

MariaDB [icinga]> explain select oe.name1 as endpoint_name, oz.name1 as zone_name from icinga_endpoints e join icinga_objects oe on e.endpoint_object_id=oe.object_id join icinga_zones z on e.zone_object_id=z.zone_object_id join icinga_objects oz on z.zone_object_id=oz.object_id;
+------+-------------+-------+--------+------------------------------------------------------+---------------------+---------+-----------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys                                        | key                 | key_len | ref                         | rows | Extra       |
+------+-------------+-------+--------+------------------------------------------------------+---------------------+---------+-----------------------------+------+-------------+
|    1 | SIMPLE      | e     | ALL    | idx_endpoints_object_id,idx_endpoints_zone_object_id | NULL                | NULL    | NULL                        |    9 | Using where |
|    1 | SIMPLE      | z     | ref    | idx_zones_object_id                                  | idx_zones_object_id | 9       | icinga.e.zone_object_id     |    1 | Using index |
|    1 | SIMPLE      | oe    | eq_ref | PRIMARY                                              | PRIMARY             | 8       | icinga.e.endpoint_object_id |    1 |             |
|    1 | SIMPLE      | oz    | eq_ref | PRIMARY                                              | PRIMARY             | 8       | icinga.e.zone_object_id     |    1 |             |
+------+-------------+-------+--------+------------------------------------------------------+---------------------+---------+-----------------------------+------+-------------+
4 rows in set (0.00 sec)

icinga_zones{,tatus}

  • zone_object_id
  • parent_zone_object_id
MariaDB [icinga]> explain select oz.name1 as zone_name, opz.name1 as parent_zone_name from icinga_zones z join icinga_objects oz on z.zone_object_id=oz.object_id join icinga_zones pz on z.parent_zone_object_id=pz.zone_object_id join icinga_objects opz on pz.zone_object_id=opz.object_id;
+------+-------------+-------+--------+---------------+---------+---------+--------------------------------+------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                            | rows | Extra                                           |
+------+-------------+-------+--------+---------------+---------+---------+--------------------------------+------+-------------------------------------------------+
|    1 | SIMPLE      | z     | ALL    | NULL          | NULL    | NULL    | NULL                           |   10 | Using where                                     |
|    1 | SIMPLE      | oz    | eq_ref | PRIMARY       | PRIMARY | 8       | icinga.z.zone_object_id        |    1 |                                                 |
|    1 | SIMPLE      | opz   | eq_ref | PRIMARY       | PRIMARY | 8       | icinga.z.parent_zone_object_id |    1 |                                                 |
|    1 | SIMPLE      | pz    | ALL    | NULL          | NULL    | NULL    | NULL                           |   10 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+--------+---------------+---------+---------+--------------------------------+------+-------------------------------------------------+
4 rows in set (0.01 sec)

CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id);
CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id);

CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id);
CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id);

MariaDB [icinga]> explain select oz.name1 as zone_name, opz.name1 as parent_zone_name from icinga_zones z join icinga_objects oz on z.zone_object_id=oz.object_id join icinga_zones pz on z.parent_zone_object_id=pz.zone_object_id join icinga_objects opz on pz.zone_object_id=opz.object_id;
+------+-------------+-------+--------+------------------------------------------------+---------------------+---------+--------------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys                                  | key                 | key_len | ref                            | rows | Extra       |
+------+-------------+-------+--------+------------------------------------------------+---------------------+---------+--------------------------------+------+-------------+
|    1 | SIMPLE      | z     | ALL    | idx_zones_object_id,idx_zones_parent_object_id | NULL                | NULL    | NULL                           |   10 | Using where |
|    1 | SIMPLE      | pz    | ref    | idx_zones_object_id                            | idx_zones_object_id | 9       | icinga.z.parent_zone_object_id |    1 | Using index |
|    1 | SIMPLE      | oz    | eq_ref | PRIMARY                                        | PRIMARY             | 8       | icinga.z.zone_object_id        |    1 |             |
|    1 | SIMPLE      | opz   | eq_ref | PRIMARY                                        | PRIMARY             | 8       | icinga.z.parent_zone_object_id |    1 |             |
+------+-------------+-------+--------+------------------------------------------------+---------------------+---------+--------------------------------+------+-------------+
4 rows in set (0.00 sec)

The first entry would use the index, but it seems that MySQL assumes a full table join and doesn't use the index here. Same goes for the endpoint tables.

PostgreSQL

icinga=> explain select oz.name1 as zone_name, opz.name1 as parent_zone_name from icinga_zones z join icinga_objects oz on z.zone_object_id=oz.object_id join icinga_zones pz on z.parent_zone_object_id=pz.zone_object_id join icinga_objects opz on pz.zone_object_id=opz.object_id;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Merge Join  (cost=1109.55..1223.55 rows=7200 width=28)
   Merge Cond: (z.parent_zone_object_id = pz.zone_object_id)
   ->  Sort  (cost=1026.18..1029.18 rows=1200 width=44)
         Sort Key: z.parent_zone_object_id
         ->  Hash Join  (cost=909.81..964.81 rows=1200 width=44)
               Hash Cond: (z.parent_zone_object_id = opz.object_id)
               ->  Hash Join  (cost=454.90..493.40 rows=1200 width=22)
                     Hash Cond: (z.zone_object_id = oz.object_id)
                     ->  Seq Scan on icinga_zones z  (cost=0.00..22.00 rows=1200 width=16)
                     ->  Hash  (cost=313.29..313.29 rows=11329 width=22)
                           ->  Seq Scan on icinga_objects oz  (cost=0.00..313.29 rows=11329 width=22)
               ->  Hash  (cost=313.29..313.29 rows=11329 width=22)
                     ->  Seq Scan on icinga_objects opz  (cost=0.00..313.29 rows=11329 width=22)
   ->  Sort  (cost=83.37..86.37 rows=1200 width=8)
         Sort Key: pz.zone_object_id
         ->  Seq Scan on icinga_zones pz  (cost=0.00..22.00 rows=1200 width=8)
(16 rows)

CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id);
CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id);

CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id);
CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id);

icinga=> explain select oz.name1 as zone_name, opz.name1 as parent_zone_name from icinga_zones z join icinga_objects oz on z.zone_object_id=oz.object_id join icinga_zones pz on z.parent_zone_object_id=pz.zone_object_id join icinga_objects opz on pz.zone_object_id=opz.object_id;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.70..51.39 rows=3 width=28)
   Join Filter: (z.parent_zone_object_id = pz.zone_object_id)
   ->  Nested Loop  (cost=0.57..50.91 rows=3 width=44)
         ->  Nested Loop  (cost=0.29..25.97 rows=3 width=22)
               ->  Seq Scan on icinga_zones z  (cost=0.00..1.03 rows=3 width=16)
               ->  Index Scan using pk_object_id on icinga_objects oz  (cost=0.29..8.30 rows=1 width=22)
                     Index Cond: (object_id = z.zone_object_id)
         ->  Index Scan using pk_object_id on icinga_objects opz  (cost=0.29..8.30 rows=1 width=22)
               Index Cond: (object_id = z.parent_zone_object_id)
   ->  Index Only Scan using idx_zones_object_id on icinga_zones pz  (cost=0.13..0.15 rows=1 width=8)
         Index Cond: (zone_object_id = opz.object_id)
(11 rows)

--

icinga=> explain select oe.name1 as endpoint_name, oz.name1 as zone_name from icinga_endpoints e join icinga_objects oe on e.endpoint_object_id=oe.object_id join icinga_zones z on e.zone_object_id=z.zone_object_id join icinga_objects oz on z.zone_object_id=oz.object_id;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1.64..57.90 rows=9 width=28)
   ->  Nested Loop  (cost=1.35..38.86 rows=9 width=30)
         ->  Hash Join  (cost=1.07..19.82 rows=9 width=24)
               Hash Cond: (e.zone_object_id = z.zone_object_id)
               ->  Seq Scan on icinga_endpoints e  (cost=0.00..16.30 rows=630 width=16)
               ->  Hash  (cost=1.03..1.03 rows=3 width=8)
                     ->  Seq Scan on icinga_zones z  (cost=0.00..1.03 rows=3 width=8)
         ->  Index Scan using pk_object_id on icinga_objects oe  (cost=0.29..2.11 rows=1 width=22)
               Index Cond: (object_id = e.endpoint_object_id)
   ->  Index Scan using pk_object_id on icinga_objects oz  (cost=0.29..2.11 rows=1 width=22)
         Index Cond: (object_id = e.zone_object_id)
(11 rows)

CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id);
CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id);

CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id);
CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id);

icinga=> explain select oe.name1 as endpoint_name, oz.name1 as zone_name from icinga_endpoints e join icinga_objects oe on e.endpoint_object_id=oe.object_id join icinga_zones z on e.zone_object_id=z.zone_object_id join icinga_objects oz on z.zone_object_id=oz.object_id;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.70..17.80 rows=1 width=28)
   Join Filter: (e.zone_object_id = z.zone_object_id)
   ->  Nested Loop  (cost=0.57..17.64 rows=1 width=44)
         ->  Nested Loop  (cost=0.29..9.32 rows=1 width=22)
               ->  Seq Scan on icinga_endpoints e  (cost=0.00..1.01 rows=1 width=16)
               ->  Index Scan using pk_object_id on icinga_objects oe  (cost=0.29..8.30 rows=1 width=22)
                     Index Cond: (object_id = e.endpoint_object_id)
         ->  Index Scan using pk_object_id on icinga_objects oz  (cost=0.29..8.30 rows=1 width=22)
               Index Cond: (object_id = e.zone_object_id)
   ->  Index Only Scan using idx_zones_object_id on icinga_zones z  (cost=0.13..0.15 rows=1 width=8)
         Index Cond: (zone_object_id = oz.object_id)
(11 rows)

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-06-22 10:57:17 +00:00

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

Applied in changeset 7f56a04.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-11-11 08:46:51 +00:00

  • Parent Id deleted 10073

@icinga-migration icinga-migration added bug Something isn't working area/db-ido Database output labels Jan 17, 2017
@icinga-migration icinga-migration added this to the 2.5.0 milestone Jan 17, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/db-ido Database output bug Something isn't working
Projects
None yet
Milestone
2.5.0
Development

No branches or pull requests
1 participant
@icinga-migration