using cli-scripts especially add_graphs.php is very slow

[thomasw]

Hello,

I`m trying to use add_graphs.php from cacti-0.8.7a with a perl wrapper script around, but my results are very slow. mysqld stays on top of "top" and is consuming very high cpu-utilization.

My hardware is a dual xeon system with 4gb of RAM. os is debian 4.0, mysql is 5.0.32-7etch1and php version is 5.2.0-8+etch5~pu1.

I`ve tried to tune some mysql parameters (key_buffer_size, tmp_tables_size, etc) but with no luck.
In the beginning of running the script the time to create a graph was around 1 or 2 sec. but for now it takes about 20 minutes to create one datasource and and corresponding graph.

Has anybody seen the same behaviour of cli/add_graphs.php like me?

Any help is highly welcome!
thanks in advance,

Tom

[wriley]

Yes, I have noticed the same behavior. I am not a SQL expert at all but this query appears to be the culprit. It is taking at least 5 minutes to run and 15 in some cases.

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'))

I did some looking in the mysql logs and the GUI apparently does this operation very differently. Hopefullly this will get fixed soon, I have about 10,000 graphs to add to one of my servers.

[wriley]

I need to learn to search better, I found this thread 10 seconds after posting.

http://forums.cacti.net/viewtopic.php?t=24441