[icinga-checkins] icinga.org: icinga-core/r1.2: idoutils: add indexes for logentries, preventing heavy load #1055

git at icinga.org git at icinga.org
Wed Jan 26 18:11:28 CET 2011


Module: icinga-core
Branch: r1.2
Commit: b79dba31c7cac98c7f0f5c3143745951f9824693
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=b79dba31c7cac98c7f0f5c3143745951f9824693

Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date:   Wed Jan 26 18:11:09 2011 +0100

idoutils: add indexes for logentries, preventing heavy load #1055

...for icinga-web in this use case.

refs #1055

---

 Changelog                                          |    1 +
 module/icinga-api                                  |    2 +-
 module/idoutils/db/mysql/mysql-upgrade-1.2.2.sql   |    5 +++++
 module/idoutils/db/mysql/mysql.sql                 |    1 +
 module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql |    6 ++++++
 module/idoutils/db/oracle/oracle.sql               |    1 +
 module/idoutils/db/pgsql/pgsql-upgrade-1.2.2.sql   |    5 +++++
 module/idoutils/db/pgsql/pgsql.sql                 |    1 +
 8 files changed, 21 insertions(+), 1 deletions(-)

diff --git a/Changelog b/Changelog
index 67aa705..7201747 100644
--- a/Changelog
+++ b/Changelog
@@ -19,6 +19,7 @@ ENHANCEMENTS
 * install: add sysconfig source to init script #1015
 
 * idoutils: add index to table icinga_statehistory for usage in reporting module #957
+* idoutils: add indexes for logentries, preventing heavy load #1055
 
 FIXES
 * core: fix problem with extra whitespace in command definition and ePN (Jason Young) #918
diff --git a/module/icinga-api b/module/icinga-api
index 83d6f5b..8c97831 160000
--- a/module/icinga-api
+++ b/module/icinga-api
@@ -1 +1 @@
-Subproject commit 83d6f5bd8e8e4b89314345bec540d01a2bf44c20
+Subproject commit 8c97831e03c97521946d34b05e1930ca620066c1
diff --git a/module/idoutils/db/mysql/mysql-upgrade-1.2.2.sql b/module/idoutils/db/mysql/mysql-upgrade-1.2.2.sql
index 6b5cd59..26b823f 100644
--- a/module/idoutils/db/mysql/mysql-upgrade-1.2.2.sql
+++ b/module/idoutils/db/mysql/mysql-upgrade-1.2.2.sql
@@ -16,3 +16,8 @@
 
 CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time);
 
+-- -----------------------------------------
+-- add index for logentries
+-- -----------------------------------------
+
+CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC);
diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index 5d7906c..61b8168 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -1535,6 +1535,7 @@ CREATE INDEX objects_inst_id_idx ON icinga_objects(instance_id);
 -- #236
 CREATE INDEX loge_time_idx on icinga_logentries(logentry_time);
 -- CREATE INDEX loge_data_idx on icinga_logentries(logentry_data);
+CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC);
 
 -- commenthistory
 -- CREATE INDEX c_hist_instance_id_idx on icinga_logentries(instance_id);
diff --git a/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql b/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql
index 4c11744..4088c6d 100644
--- a/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql
+++ b/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql
@@ -15,6 +15,12 @@
 CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on statehistory(instance_id, object_id, state_type, state_time);
 
 -- -----------------------------------------
+-- add index for logentries
+-- -----------------------------------------
+
+CREATE INDEX loge_inst_id_time_idx on logentries (instance_id ASC, logentry_time DESC);
+
+-- -----------------------------------------
 -- fix NO_DATA_FOUND exception
 -- -----------------------------------------
 
diff --git a/module/idoutils/db/oracle/oracle.sql b/module/idoutils/db/oracle/oracle.sql
index 7c8bc2f..39bd2d5 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -1620,6 +1620,7 @@ CREATE INDEX objects_inst_id_idx ON objects(instance_id);
 -- #236
 CREATE INDEX loge_time_idx on logentries(logentry_time);
 -- CREATE INDEX loge_data_idx on logentries(logentry_data);
+CREATE INDEX loge_inst_id_time_idx on logentries (instance_id ASC, logentry_time DESC);
 
 -- commenthistory
 -- CREATE INDEX c_hist_instance_id_idx on logentries(instance_id);
diff --git a/module/idoutils/db/pgsql/pgsql-upgrade-1.2.2.sql b/module/idoutils/db/pgsql/pgsql-upgrade-1.2.2.sql
index 6b5cd59..26b823f 100644
--- a/module/idoutils/db/pgsql/pgsql-upgrade-1.2.2.sql
+++ b/module/idoutils/db/pgsql/pgsql-upgrade-1.2.2.sql
@@ -16,3 +16,8 @@
 
 CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time);
 
+-- -----------------------------------------
+-- add index for logentries
+-- -----------------------------------------
+
+CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC);
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index ffcbfe2..a81316e 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -1543,6 +1543,7 @@ CREATE INDEX objects_inst_id_idx ON icinga_objects(instance_id);
 -- #236
 CREATE INDEX loge_time_idx on icinga_logentries(logentry_time);
 -- CREATE INDEX loge_data_idx on icinga_logentries(logentry_data);
+CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC);
 
 -- commenthistory
 -- CREATE INDEX c_hist_instance_id_idx on icinga_logentries(instance_id);





More information about the icinga-checkins mailing list