[icinga-checkins] icinga.org: icinga-core/next: idoutils: add more indexes for sla reporting refs #3649

git at icinga.org git at icinga.org
Sun Apr 7 00:46:07 CEST 2013


Module: icinga-core
Branch: next
Commit: 14205ddc1e9017207cacb227f3e002d3c2eab8f3
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=14205ddc1e9017207cacb227f3e002d3c2eab8f3

Author: Michael Friedrich <michael.friedrich at netways.de>
Date:   Mon Mar 11 23:07:54 2013 +0100

idoutils: add more indexes for sla reporting refs #3649

Conflicts:
	Changelog
	module/idoutils/db/oracle/upgrade/oracle-upgrade-1.9.0.sql

---

 Changelog                                          |    1 +
 module/idoutils/db/mysql/mysql.sql                 |    5 +++++
 .../db/mysql/upgrade/mysql-upgrade-1.9.0.sql       |    6 +++++-
 .../db/oracle/create_icinga_objects_oracle.sql     |    5 +++++
 .../db/oracle/upgrade/oracle-upgrade-1.9.0.sql     |   12 +++++++++---
 module/idoutils/db/pgsql/pgsql.sql                 |    6 ++++++
 .../db/pgsql/upgrade/pgsql-upgrade-1.9.0.sql       |    6 +++++-
 7 files changed, 36 insertions(+), 5 deletions(-)

diff --git a/Changelog b/Changelog
index b82f215..44d8524 100644
--- a/Changelog
+++ b/Changelog
@@ -38,6 +38,7 @@ ENHANCEMENTS
 
 * idoutils: use transactions for large object dumps, opt-in config option #3527 - GB
 * idoutils: add experimental socket queue proxy; opt-in via config option #3533 - GB
+* idoutils: add more indexes for sla reporting #3649 - MF
 
 * install: set --enable-idoutils/event-broker to true as default configure flags #3813 - MF
 
diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index 71b76b0..e7b038d 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -1597,6 +1597,11 @@ CREATE INDEX sgmbrs_sgid_soid ON icinga_servicegroup_members (servicegroup_id,se
 CREATE INDEX scgrps_sid_cgoid ON icinga_service_contactgroups (service_id,contactgroup_object_id);
 CREATE INDEX tperiod_tid_d_ss_es ON icinga_timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec);
 
+-- #3649
+CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC);
+CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time);
+CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1);
+
 -- -----------------------------------------
 -- set dbversion
 -- -----------------------------------------
diff --git a/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.9.0.sql b/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.9.0.sql
index aedffec..097cb7a 100644
--- a/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.9.0.sql
+++ b/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.9.0.sql
@@ -8,9 +8,13 @@
 -- -----------------------------------------
 
 -- -----------------------------------------
--- 
+-- #3649 sla index
 -- -----------------------------------------
 
+CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC);
+CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time);
+CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1);
+
 -- -----------------------------------------
 -- update dbversion
 -- -----------------------------------------
diff --git a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
index 86c646d..31e681b 100644
--- a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
+++ b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
@@ -2002,6 +2002,11 @@ CREATE INDEX sgmbrs_sgid_soid ON servicegroup_members (servicegroup_id,service_o
 CREATE INDEX scgrps_sid_cgoid ON service_contactgroups (service_id,contactgroup_object_id) tablespace &&IDXTBS;
 CREATE INDEX tperiod_tid_d_ss_es ON timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec) tablespace &&IDXTBS;
 
+-- #3649
+CREATE INDEX sla_idx_sthist ON statehistory (object_id, state_time DESC) tablespace &&IDXTBS;
+CREATE INDEX sla_idx_dohist ON downtimehistory (object_id, actual_start_time, actual_end_time) tablespace &&IDXTBS;
+CREATE INDEX sla_idx_obj ON objects (objecttype_id, is_active, name1) tablespace &&IDXTBS;
+
 -- -----------------------------------------
 -- sequences
 -- -----------------------------------------
diff --git a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.9.0.sql b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.9.0.sql
index 47c9703..e38eec5 100644
--- a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.9.0.sql
+++ b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.9.0.sql
@@ -18,8 +18,9 @@ set feedback on;
 define ICINGA_VERSION=1.9.0
 
 -- --------------------------------------------------------
--- warning:edit this script to define existing tablespaces
--- this particular step can be skipped safetly if no new table or index included
+-- warning: edit this script to define existing tablespaces
+-- this particular step can be skipped safely if no new
+-- table or index included
 -- --------------------------------------------------------
 /* set real TBS names on which you have quota, no checks are implemented!*/
 define DATATBS='ICINGA_DATA1';
@@ -34,7 +35,12 @@ whenever sqlerror exit failure
 spool oracle-upgrade-&&ICINGA_VERSION..log
 
 -- -----------------------------------------
--- 
+-- #3649 sla index
+-- -----------------------------------------
+CREATE INDEX sla_idx_sthist ON statehistory (object_id, state_time DESC) tablespace &&IDXTBS;
+CREATE INDEX sla_idx_dohist ON downtimehistory (object_id, actual_start_time, actual_end_time) tablespace &&IDXTBS;
+CREATE INDEX sla_idx_obj ON objects (objecttype_id, is_active, name1) tablespace &&IDXTBS;
+
 -- -----------------------------------------
 -- finally update dbversion
 -- -----------------------------------------
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index ebf5def..4f9bc20 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -1625,6 +1625,12 @@ CREATE INDEX sgmbrs_sgid_soid ON icinga_servicegroup_members (servicegroup_id,se
 CREATE INDEX scgrps_sid_cgoid ON icinga_service_contactgroups (service_id,contactgroup_object_id);
 CREATE INDEX tperiod_tid_d_ss_es ON icinga_timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec);
 
+-- #3649
+CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC);
+CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time);
+CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1);
+
+
 -- -----------------------------------------
 -- set dbversion
 -- -----------------------------------------
diff --git a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.9.0.sql b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.9.0.sql
index a738658..db0818b 100644
--- a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.9.0.sql
+++ b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.9.0.sql
@@ -8,9 +8,13 @@
 -- -----------------------------------------
 
 -- -----------------------------------------
--- 
+-- #3649 sla index
 -- -----------------------------------------
 
+CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC);
+CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time);
+CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1);
+
 -- -----------------------------------------
 -- update dbversion
 -- -----------------------------------------





More information about the icinga-checkins mailing list