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

git at icinga.org git at icinga.org
Wed Jan 26 18:07:37 CET 2011


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

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

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

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

refs #1055

---

 Changelog                                          |    1 +
 module/idoutils/db/mysql/mysql-upgrade-1.3.0.sql   |    6 ++++++
 module/idoutils/db/mysql/mysql.sql                 |    1 +
 module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql |    6 ++++++
 module/idoutils/db/oracle/oracle.sql               |    1 +
 module/idoutils/db/pgsql/pgsql-upgrade-1.3.0.sql   |    7 +++++++
 module/idoutils/db/pgsql/pgsql.sql                 |    2 ++
 7 files changed, 24 insertions(+), 0 deletions(-)

diff --git a/Changelog b/Changelog
index 3ec08a8..12119c7 100644
--- a/Changelog
+++ b/Changelog
@@ -48,6 +48,7 @@ ENHANCEMENTS
 	- reduced number of fired SQL statements by 50%
 	- changed 1 SQL statement for each rdbms, making it smaller and saving index lookup (and virtual table by merge in oracle)
 	- index savings, and dropped unique keys - check upgrade sql scripts for mysql/postgresql/oracle
+* idoutils: add indexes for logentries, preventing heavy load #1055
 
 * contrib: bring daemonchk.cgi building into shape (Elan Ruusamäe) #1051
 
diff --git a/module/idoutils/db/mysql/mysql-upgrade-1.3.0.sql b/module/idoutils/db/mysql/mysql-upgrade-1.3.0.sql
index ae09df3..be4ff82 100644
--- a/module/idoutils/db/mysql/mysql-upgrade-1.3.0.sql
+++ b/module/idoutils/db/mysql/mysql-upgrade-1.3.0.sql
@@ -26,6 +26,12 @@ INSERT INTO icinga_dbversion (name, version) VALUES ('idoutils', '1.3.0') ON DUP
 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);
+
+-- -----------------------------------------
 -- drop unique keys for check history
 -- -----------------------------------------
 
diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index f83dfed..eff7472 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -1533,6 +1533,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.3.0.sql b/module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql
index 0157729..eb03c13 100644
--- a/module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql
+++ b/module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql
@@ -30,6 +30,12 @@ INSERT (id, name, version) VALUES ('1', 'idoutils', '1.3.0');
 
 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 09b836f..d127e39 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -1617,6 +1617,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.3.0.sql b/module/idoutils/db/pgsql/pgsql-upgrade-1.3.0.sql
index fced86e..f3dc4b0 100644
--- a/module/idoutils/db/pgsql/pgsql-upgrade-1.3.0.sql
+++ b/module/idoutils/db/pgsql/pgsql-upgrade-1.3.0.sql
@@ -41,6 +41,13 @@ SELECT updatedbversion('1.3.0');
 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);
+
+
+-- -----------------------------------------
 -- drop unique keys for check history
 -- -----------------------------------------
 
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index f931ba3..cbab23e 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -1564,6 +1564,8 @@ 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