[dev.icinga.com #2274] Add Index for BP-AddOn in Large Environments

[icinga-migration]

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

Created by jschanz on 2012-01-19 08:20:09 +00:00

Assignee: Tommi
Status: Resolved (closed on 2012-03-30 15:52:59 +00:00)
Target Version: 1.7
Last Update: 2014-12-08 14:46:34 +00:00 (in Redmine)

---

Please add a default Index to icinga_statehistory

CREATE INDEX state on icinga_statehistory (object_id, state);

Otherwise there will be no index used for this table. The table will be getting very large ... e.g. 1,500,000 rows half a year with 8000 services an 2000 hosts.

The Query from BP-Addon is something like:

SELECT COUNT(*) AS num_results FROM (SELECT i.statehistory_id FROM icinga_statehistory i INNER JOIN icinga_objects i2 ON i.object_id = i2.object_id INNER JOIN icinga_services i3 ON i.object_id = i3.service_object_id INNER JOIN icinga_hosts i4 ON i3.host_object_id = i4.host_object_id INNER JOIN icinga_objects i5 ON i4.host_object_id = i5.object_id INNER JOIN icinga_instances i6 ON i3.instance_id = i6.instance_id WHERE ((...

Explain plan without index:

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-------------------+---------+----------------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                            | key               | key_len | ref                        | rows    | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-------------------+---------+----------------------------+---------+----------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL   | NULL                                                                                                     | NULL              | NULL    | NULL                       |    NULL | Select tables optimized away                 |
|  2 | DERIVED     | i6    | index  | PRIMARY,instance_id                                                                                      | instance_id       | 8       | NULL                       |       1 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | i     | ALL    | NULL                                                                                                     | NULL              | NULL    | NULL                       | 1092060 | Using where; Using join buffer               |
|  2 | DERIVED     | i2    | eq_ref | PRIMARY,object_id,objects_name2_idx                                                                      | PRIMARY           | 8       | idoutils.i.object_id       |       1 | Using where                                  |
|  2 | DERIVED     | i3    | ref    | instance_id,service_object_id,services_i_id_idx,services_host_object_id_idx,services_combined_object_idx | service_object_id | 9       | idoutils.i.object_id       |       1 | Using where                                  |
|  2 | DERIVED     | i4    | ref    | host_object_id,hosts_host_object_id_idx                                                                  | host_object_id    | 9       | idoutils.i3.host_object_id |       1 | Using where; Using index                     |
|  2 | DERIVED     | i5    | eq_ref | PRIMARY,object_id,objects_name1_idx                                                                      | PRIMARY           | 8       | idoutils.i3.host_object_id |       1 | Using where                                  |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-------------------+---------+----------------------------+---------+----------------------------------------------+

Explain plan WITH index:

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                            | key                         | key_len | ref                           | rows | Extra                                        |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL   | NULL                                                                                                     | NULL                        | NULL    | NULL                          | NULL | Select tables optimized away                 |
|  2 | DERIVED     | i6    | index  | PRIMARY,instance_id                                                                                      | instance_id                 | 8       | NULL                          |    1 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | i5    | range  | PRIMARY,object_id,objects_name1_idx                                                                      | objects_name1_idx           | 131     | NULL                          |  247 | Using where; Using index; Using join buffer  |
|  2 | DERIVED     | i4    | ref    | host_object_id,hosts_host_object_id_idx                                                                  | host_object_id              | 9       | idoutils.i5.object_id         |    1 | Using where; Using index                     |
|  2 | DERIVED     | i3    | ref    | instance_id,service_object_id,services_i_id_idx,services_host_object_id_idx,services_combined_object_idx | services_host_object_id_idx | 9       | idoutils.i5.object_id         |    1 | Using where                                  |
|  2 | DERIVED     | i2    | eq_ref | PRIMARY,object_id,objects_name2_idx                                                                      | PRIMARY                     | 8       | idoutils.i3.service_object_id |    1 | Using where                                  |
|  2 | DERIVED     | i     | ref    | state                                                                                                    | state                       | 9       | idoutils.i2.object_id         | 5460 | Using where; Using index                     |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+----------------------------------------------+

Changesets

2012-01-29 16:15:42 +00:00 by Tommi 69e8bd7

idoutils: add index for BP Addon #2274
refs #2274

2012-02-03 19:09:57 +00:00 by Tommi 44111b7

idoutils: fix typo in sql #2274
refs #2274

[icinga-migration]

Updated by Tommi on 2012-01-28 17:46:48 +00:00

The index can be implemented, but you should check if the query itself need this much of joins-> Do you need really the number or a simple exists? Can you read reference tables (instance etc, which are more static) first into memory and join there?

Please keep in mind performance of your database can be effected because with an index every insert will cause additional IO and Storage. on oracle i can suggest usage of partitions by time, but this is an additional option to pay.

Additional i would like to ask you kindly to check your approach to store each and every status for months without any aggregation and than reading the whole database again and again. I would assume for such statistic datawarehouse technics (dimension and fact tables) are more applicable than the traditional querys. Especialy if this querys are generated by a framework which is not aware of such amount of data.

[icinga-migration]

Updated by Tommi on 2012-01-29 16:52:46 +00:00

• Category set to 24
• Status changed from New to Feedback
• Assigned to set to Tommi
• Target Version set to 1.7
• Done % changed from 0 to 100

Index added for mysql pgsql and oracle

[icinga-migration]

Updated by Tommi on 2012-03-30 15:52:59 +00:00

• Status changed from Feedback to Resolved

no comments for last 2months, assume resolved

[icinga-migration]

Updated by mfriedrich on 2012-04-28 09:15:24 +00:00

• Tracker changed from Bug to Feature

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:46:34 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 24 to IDOUtils