Mantis Bugtracker
Main | My View | View Issues | Change Log | Roadmap | Docs
  

Viewing Issue Advanced Details [ Jump to Notes ] [ View Simple ] [ Issue History ] [ Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0001333 [Cacti] Database tweak always 2008-11-03 11:05 2010-08-19 15:42
Reporter BorisL View Status public  
Assigned To gandalf
Priority normal Resolution fixed Platform
Status resolved   OS
Projection none   OS Version
ETA none Fixed in Version 0.8.8 Product Version 0.8.7b
  Target Version Product Build
Summary 0001333: Lack of scheme indexes
Description Lack of indexes in default Cacti's scheme turns out as poor performance.
This set of indexes helps to improve performance on big environments 10x or even more (in web interface and polling):

data_input_data:
KEY `data_template_data_id` (`data_template_data_id`)

data_local:
KEY `host_id_snmp_query_id_snmp_index` (`host_id`,`snmp_query_id`,`snmp_index`)

data_template_rrd:
KEY `local_data_id_data_source_name` (`local_data_id`,`data_source_name`)

graph_templates_item:
KEY `graph_template_id_local_graph_id` (`graph_template_id`,`local_graph_id`)
KEY `local_graph_template_item_id` (`local_graph_template_item_id`)

host_snmp_cache:
KEY `host_id_snmp_query_id_snmp_index` (`host_id`,`snmp_query_id`,`snmp_index`)

poller_item:
KEY `local_data_id_rrd_path` (`local_data_id`,`rrd_path`)
KEY `host_id_rrd_next_step` (`host_id`,`rrd_next_step`)

Or in SQL syntax:
CREATE INDEX `data_template_data_id` ON `data_input_data` (`data_template_data_id`);

CREATE INDEX `host_id_snmp_query_id_snmp_index` ON data_local (`host_id`,`snmp_query_id`,`snmp_index`);

CREATE INDEX `local_data_id_data_source_name` ON data_template_rrd (`local_data_id`,`data_source_name`);

CREATE INDEX `graph_template_id_local_graph_id` ON graph_templates_item (`graph_template_id`,`local_graph_id`);
CREATE INDEX `local_graph_template_item_id` ON graph_templates_item (`local_graph_template_item_id`);

CREATE INDEX `host_id_snmp_query_id_snmp_index` ON host_snmp_cache (`host_id`,`snmp_query_id`,`snmp_index`);

CREATE INDEX `local_data_id_rrd_path` ON poller_item (`local_data_id`,`rrd_path`);
CREATE INDEX `host_id_rrd_next_step` ON poller_item (`host_id`,`rrd_next_step`);
Steps To Reproduce
Additional Information
Tags No tags attached.
Attached Files

- Relationships

-  Notes
(0003543)
TheWitness (developer)
2008-11-03 14:57

 Interesting. I would have thought these were already created.
(0003545)
BorisL (reporter)
2008-11-03 15:10

 Ahh, there are three more to create:
create index order_key3 on graph_tree_items (order_key(3));
create index order_key6 on graph_tree_items (order_key(6));
create index order_key9 on graph_tree_items (order_key(9));
(0003546)
TheWitness (developer)
2008-11-03 15:22

 Very interesting. I can see why those would be helpful. Personally, I don't like this order key thing, and I'm a developer. :)

TheWitness
(0003553)
BorisL (reporter)
2008-11-04 01:53

 Sorry, my fault. I forgot to include graph_tree_id column into index.

Just look at two explains:

mysql> explain select count(*) from graph_tree_items where order_key LIKE '011%%' and graph_tree_id=3 order by order_key;
+----+-------------+------------------+------+----------------------------------------------------------------------------------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+----------------------------------------------------------------------------------------------------+--------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | graph_tree_items | ref | graph_tree_id,order_key,graph_tree_id_order_key3,graph_tree_id_order_key6,graph_tree_id_order_key9 | graph_tree_id_order_key3 | 2 | const | 159 | Using where |
+----+-------------+------------------+------+----------------------------------------------------------------------------------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from graph_tree_items where order_key LIKE '011%%' and graph_tree_id=3 order by order_key;
+----+-------------+------------------+------+-------------------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+-------------------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | graph_tree_items | ref | graph_tree_id,order_key | graph_tree_id | 2 | const | 1021 | Using where |
+----+-------------+------------------+------+-------------------------+---------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

So graph_tree_id_order_key3 index may help in some situations :)
So as graph_tree_id_order_key6 and graph_tree_id_order_key9.
(0003644)
BorisL (reporter)
2008-11-12 14:00

 It seems I found another one that is needed (for host snmp reindex):
CREATE INDEX host_id_snmp_query_id ON host_snmp_cache (host_id,snmp_query_id);
(0003659)
BorisL (reporter)
2008-11-16 04:05

 One more
CREATE INDEX host_id_snmp_port ON poller_item (host_id,snmp_port);
(0004023)
BorisL (reporter)
2009-04-27 08:07

 One more
CREATE INDEX task_item_id ON graph_templates_item (task_item_id);
(0004231)
BorisL (reporter)
2009-07-23 07:37

 CREATE INDEX data_source_path ON data_template_data (data_source_path);
(0004738)
gandalf (developer)
2010-03-07 13:40

 Taking over because I'm just working in that area.
I will NOT implement the tree related indexes as Larry is just working on a new tree implementation.
Perhaps we will have to revisit them later on.
(0004739)
gandalf (developer)
2010-03-07 14:13

 SVN#5682
(0005284)
BorisL (reporter)
2010-08-19 13:58

 One more
ALTER TABLE graph_templates_item ADD INDEX local_graph_id_sequence (local_graph_id, sequence);

KEY `local_graph_id_sequence` (`local_graph_id`,`sequence`)
(0005285)
gandalf (developer)
2010-08-19 15:42

 SVN#6099

- Issue History
Date Modified Username Field Change
2008-11-03 11:05 BorisL New Issue
2008-11-03 14:57 TheWitness Note Added: 0003543
2008-11-03 14:57 TheWitness Status new => assigned
2008-11-03 14:57 TheWitness Assigned To => TheWitness
2008-11-03 15:10 BorisL Note Added: 0003545
2008-11-03 15:22 TheWitness Note Added: 0003546
2008-11-04 01:53 BorisL Note Added: 0003553
2008-11-12 14:00 BorisL Note Added: 0003644
2008-11-16 04:05 BorisL Note Added: 0003659
2009-04-27 08:07 BorisL Note Added: 0004023
2009-05-20 07:39 bernhardf Issue Monitored: bernhardf
2009-07-23 07:37 BorisL Note Added: 0004231
2009-08-08 12:10 Linegod Issue Monitored: Linegod
2010-03-07 13:39 gandalf Assigned To TheWitness => gandalf
2010-03-07 13:40 gandalf Note Added: 0004738
2010-03-07 14:13 gandalf Note Added: 0004739
2010-03-07 14:13 gandalf Status assigned => resolved
2010-03-07 14:13 gandalf Fixed in Version => 0.8.8
2010-03-07 14:13 gandalf Resolution open => fixed
2010-08-19 13:58 BorisL Note Added: 0005284
2010-08-19 15:42 gandalf Note Added: 0005285

Mantis 1.1.6[^]
Copyright © 2000 - 2008 Mantis Group
Powered by Mantis Bugtracker