SOLVED: db query execution timeof 55 sec in add_graphs.php

[TheBigOne]

I have a problem adding graphs with the command line scripts.
Im adding the devices with a perl script and use as api the cli from cacti.
My problem is that i get extremely long query times after adding the devices.
After 18 Devices the Query time of this query takes 8 seconds!

The ENV:
8 GB RAM 2x CPUs @3 GHZ
Raid 5
Memory limit 1GB in php.ini and config files (changed in the second try after deleting the Database)

Can anybody give me a hint?

This was the worst query i ever got.

/usr/libexec/mysqld, Version: 4.1.20-log. started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 081217 9:10:21
# User@Host: root[root] @ localhost
# Query_time: 54 Lock_time: 0 Rows_sent: 3 Rows_examined: 7360371
use cacti;
SELECT DISTINCT data_input_fields.data_name AS name, data_input_fields.name AS description, data_input_data.value AS default, data_template_data.data_template_id, data_input_fields.id AS data_input_field_id FROM data_input_data INNER JOIN (((data_template_rrd INNER JOIN (graph_templates INNER JOIN graph_templates_item ON graph_templates.id = graph_templates_item.graph_template_id) ON data_template_rrd.id = graph_templates_item.task_item_id) INNER JOIN data_template_data ON data_template_rrd.data_template_id=data_template_data.data_template_id) INNER JOIN data_input_fields ON data_template_data.data_input_id=data_input_fields.data_input_id) ON (data_input_data.data_template_data_id = data_template_data.id) AND (data_input_data.data_input_field_id = data_input_fields.id) WHERE (((graph_templates.id)=26) AND ((data_input_data.t_value)='on') AND ((data_input_fields.input_output)='in')) LIMIT 0, 30;

EDIT1:
The query comes from lib/api_automation_tools.php
from the function getInputFields(){}

And only if the graphTemplateID=26
7489e44466abee8a7d8636cb2cb14a1a ucd 90 Filesystems

There must be something wrong

EDIT2:
Update to Mysql 5.1 with new intallation of cacti.db brought no success.

EDIT3:
This result is funny.


State Duration
starting 0.000077
Opening tables 0.000236
System lock 0.000007
Table lock 0.000014
init 0.000014
optimizing 0.000008
statistics 0.000015
preparing 0.000013
executing 0.000250
Sending data 0.000044
end 0.000007
query end 0.000006
freeing items 0.000018
removing tmp table 0.000053
closing tables 0.000010
logging slow query 0.000006
cleaning up 0.000006

Zeige Datensätze 0 - 2 (3 insgesamt, die Abfrage dauerte 59.7280 sek.)

Why does it takes 59 sec and the tasks takes only a part of it?

[TheBigOne]

I found the solution for the problem:

First i indexed some tables:

Code: Select all

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`);

Then figured out that there was an INDEX t_value missing on data_input_data

CREATE INDEX `t_value` ON `data_input_data` (`t_value`);

was the solution!

[oxo-oxo]

How much of the indexing work has come into cacti ...?
- as in: is the previous post OK for the developers?

I did a quick dig into cacti.sql and found a hit and a miss ....

Code: Select all

ob@oxo-o:~/main> grep data_template_data_id cacti.sql
  data_template_data_id mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY (data_input_field_id,data_template_data_id),
  local_data_template_data_id mediumint(8) unsigned NOT NULL default '0',
  data_template_data_id mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (data_template_data_id,rra_id),
  KEY data_template_data_id (data_template_data_id)
ob@oxo-o:~/main> grep t_value cacti.sql
  t_value char(2) default NULL,
  KEY t_value (t_value)
  t_unit_value char(2) default '0',
  unit_value varchar(20) default NULL,
  t_unit_exponent_value char(2) default '0',
  unit_exponent_value varchar(5) NOT NULL default '',
  assert_value varchar(100) NOT NULL default '',

[gandalf]

Then figured out that there was an INDEX t_value missing on data_input_data

CREATE INDEX `t_value` ON `data_input_data` (`t_value`);

was the solution!

This is part of cacti.sql since 087b and part of upgrade from 087a to 087b.. So I wonder which version you are on.

Code: Select all

/* make CLI more responsive */
db_install_execute("0.8.7b", "ALTER TABLE `data_input_data` ADD INDEX `t_value`(`t_value`)");

Code: Select all

--
-- Table structure for table `data_input_data`
--

CREATE TABLE data_input_data (
  data_input_field_id mediumint(8) unsigned NOT NULL default '0',
  data_template_data_id mediumint(8) unsigned NOT NULL default '0',
  t_value char(2) default NULL,
  value text,
  PRIMARY KEY (data_input_field_id,data_template_data_id),
  KEY t_value (t_value)
) TYPE=MyISAM;

Reinhard

[TheBigOne]

Hi Gandalf,

i was wondering too, cause on the testserver i never ran into problems. Suddenly on the prod i got this kind of probs. I deleted the db twice and tried to import the db of 0.8.7b but always with the same problem with long queries.

Now it works and i added it into my docu for later installations

It seems that this Index was not insertet by default. I will check that again.

Stefan

[gandalf]

So you do not have the table settings when runtime was bad? I would have been interested in the index definitions seen at that time. In case, the update script has an error ...
Reinhard

[TheBigOne]

I imported the cacti.sql again and got again no t_value index in the data_input_data table

PRIMARY PRIMARY 178 Bearbeiten Löschen data_input_field_id
data_template_data_id

See the Attachment cacti.sql

[gandalf]

Where is this import from? It seems to stem from a mysqldump. This is NOT the cacti.sql provided by cacti
Reinhard

[TheBigOne]

Are you sure? i only downloaded version 0.8.7.b and never edited the cacti.sql.
Now i attached the package. The cacti.sql in the package is the same i provided bevore. (tested with diff)

EDIT// I downloaded the package again from sourceforge and cacti.net and the cacti.sql is the same like my cacti.sql.txt from the first message.

[X-il3]

Hi

I noticed that you guys are discussing the long execution time when adding devices into Cacti. I have created a php script that adds a device and then creates graphs for all interfaces on that device. I use add_device.php, add_tree.php and add_graphs.php in that script. However it takes about 15-25 minutes to add one device to Cacti. I can see that data_input_data has the t_value field but I noticed that sometimes that field has "on", "null" or just empty as values, seemingly randomly. What is the correct value for t_value?

I am running Cacti 0.8.7b

EDIT: I noticed that it takes about 10 seconds or so to add around 30 graphs for the same device using the web interface while it takes around 15 minutes to add those same graphs using the add_graphs.php

With regards
Elvar

[TheBigOne]

Hi, i wrote the same script in perl and ran into the same problem

t_value is "on" or nothing in my table and i think this is normal.

You need to add an index key for the table. That will fix the problem

CREATE INDEX `t_value` ON `data_input_data` (`t_value`);

Hope this will help you.

[X-il3]

I ran this command earlier today

CREATE INDEX `t_value` ON `data_input_data` (`t_value`);

and it made no difference. Should I try creating indexes on the other tables that you mentioned in one of your earlier posts?

With regards,

Elvar


EDIT: I have added all the indexes previously mentioned in this post and it does not seem to make any difference. Running mytop on the server I can see that the query taking up most of the cpu is:

Code: Select all

SELECT DISTINCT data_input_fields.data_name AS `name`, data_input_fields.name AS `description`, data_input_data.value AS `default`, data_template_data.data_template_id, data_input_fields.id AS `data_input_field_id` FROM data_input_data INNER JOIN (((data_template_rrd INNER JOIN (graph_templates INNER JOIN graph_templates_item ON graph_templates.id = graph_templates_item.graph_template_id) ON data_template_rrd.id = graph_templates_item.task_item_id) INNER JOIN data_template_data ON data_template_rrd.data_template_id=data_template_data.data_template_id) INNER JOIN data_input_fields ON data_template_data.data_input_id=data_input_fields.data_input_id) ON (data_input_data.data_template_data_id = data_template_data.id) AND (data_input_data.data_input_field_id = data_input_fields.id) WHERE (((graph_templates.id)=2) AND ((data_input_data.t_value)='on') AND ((data_input_fields.input_output)='in'))

[TheBigOne]

Hmm, did you checked the indexes of data_input_data?
After adding new Indexes i had problems with double index.

Create a new DB and import the cacti.sql, then add the t_value index and compare it with your tables. I also needed a bit time to discover the wrong index but it was exactly the same problem.

With only a few devices everything is ok, but if you add more, the execution and cpu time is growing up. Thats cause the table grows and if there is no index.

[X-il3]

Ok I will try that but one thing that is bugging is the fact that the web interface is so much faster adding devices compared to the add_graphs.php script.

With regards,
Elvar

EDIT: Just tried to delete all devices from cacti and tried adding a new device using my script. Then it ran much faster but I could notice that it got a little bit slower as more graphs were added to the database.


EDIT1: I have now included the indexes of the data_input_data table

[TheBigOne]

try to delete data_template_data_id cause this index is double (primary index)
check table data_input_fields for index: data_input_id

Check the sql dump of my table structure and compare it with yours. Perhaps it will help you. This is my prod DB structure.