Feature #2274

Add Index for BP-AddOn in Large Environments

Added by jschanz over 2 years ago. Updated almost 2 years ago.

Status:ResolvedStart date:01/19/2012
Priority:LowDue date:
Assignee:Tommi% Done:

100%

Category:RDBMS
Target version:Icinga 1.x - 1.7

Description

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                     |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+----------------------------------------------+

Associated revisions

Revision 69e8bd7c
Added by Tommi about 2 years ago

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

Revision 44111b71
Added by Tommi about 2 years ago

idoutils: fix typo in sql #2274
refs #2274

History

#1 Updated by Tommi about 2 years ago

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.

#2 Updated by Tommi about 2 years ago

  • Category set to RDBMS
  • Status changed from New to Feedback
  • Assignee set to Tommi
  • Target version set to 1.7
  • % Done changed from 0 to 100

Index added for mysql pgsql and oracle

#3 Updated by Tommi about 2 years ago

  • Status changed from Feedback to Resolved

no comments for last 2months, assume resolved

#4 Updated by dnsmichi almost 2 years ago

  • Tracker changed from Bug to Feature

Also available in: Atom PDF