Command line tools (cacti 0.8.7a) horribly slow.Problem back

[_Dmitry_]

I have following Problem.

At the beginning CLI (command line tools) were really fast, almost the same as Cacti GUI.
But now they are horribly slow. Sometimes it takes 10 minutes to create 1 Graph with CLI where the same Graph creation takes 10 seconds in Cacti GUI.

Anybody has the same Problems ? Any ideas ?

update, just for info:

select count(*) from data_template_rrd ----------142248
select count(*) from graph_templates_graph --- 16117
select count(*) from graph_templates_item ----- 161205

[gandalf]

There will be some more indices that speed up cli commands. You'll find them in the current SVN, main branch, install directory as upgrade_from_087b_to_088.php (or the like)
Reinhard

[_Dmitry_]

Thank you very much

Host creation with all Graph before: 2000 seconds
Host creation with all Graph after: 25 seconds

[gandalf]

That's still too slow. I will have to perform some debugging on our prod installation. But this is still 086-type. If you are familiar with mysql slow_queries option, it would be nice if you can post those results related to cli scrupt add_hosts.php.
Reinhard

[fmangeant]

Reinhard,

maybe a patch for 0.8.7a could be issued for these new indexes ?

What do you think of it ?

[gandalf]

Next bug fix release will include the already known index optimizations. If I only have the output from mysql_slow_queries, I'd be able to add more.
This weekend, I will tackle open cli issues. Perhaps but not most likely I will be able to tackle performance issues.
Reinhard

[_Dmitry_]

That's still too slow. I will have to perform some debugging on our prod installation. But this is still 086-type. If you are familiar with mysql slow_queries option, it would be nice if you can post those results related to cli scrupt add_hosts.php.
Reinhard

I'll add today arround 300 hosts with plenty of graphs. After this I'll attach slow_queries report here.

You mean add_device.php and not add_hosts,php, right ?

To create all new Hosts with all Graphs I create first device with add_device.php and then each graph with add_graph.php.

Can I create Host with all Graphs only with add_device.php ?

[_Dmitry_]

Problem is back again. I've had 0.8.7 before and now 0.8.7a.

I've checked all additional indexes, all exist.
During Graphs creation I can see following Errors in cacti.log

02/12/2008 02:14:27 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT DISTINCT data_template_rrd.local_data_id FROM graph_templates_item, data_template_rrd WHERE graph_templates_item.local_graph_id = AND graph_templates_item.task_item_id = data_template_rrd.id"

Here is mysql_slow_queries:

Code: Select all

# Time: 080212 14:11:56
# User@Host: cactiuser @ localhost []
# Query_time: 50  Lock_time: 0  Rows_sent: 0  Rows_examined: 4887200
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)=35) AND ((data_input_data.t_value)='on') AND ((data_input_fields.input_output)='in'));
# Time: 080212 14:12:42
# User@Host: cactiuser @ localhost []
# Query_time: 45  Lock_time: 0  Rows_sent: 0  Rows_examined: 4887200
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)=37) AND ((data_input_data.t_value)='on') AND ((data_input_fields.input_output)='in'));

[gandalf]

Problem is back again. I've had 0.8.7 before and now 0.8.7a.

You will hate me. But 087a still does not have those indices. We've just release 087b including an upgrade script creating those indices. But if you UPGRADED to 087a (NOT newly installed), the index will still be there as you've added them manually.
Thank you for posting the slow query stuff.
This weekend, I've spent some time on add_device.php to make it faster. But unfortunalety, all my tests on a DB of about 60 MB size did not take longer than a few seconds for 10 hosts.
It will take 'til weekend to find time to examine it in more detail.
Reinhard

[_Dmitry_]

Problem is back again. I've had 0.8.7 before and now 0.8.7a.

You will hate me. But 087a still does not have those indices. We've just release 087b including an upgrade script creating those indices. But if you UPGRADED to 087a (NOT newly installed), the index will still be there as you've added them manually.

Yes. That's the point. I've upgraded CACTI. And then checked again this Indexes.

Thank you for posting the slow query stuff.
This weekend, I've spent some time on add_device.php to make it faster. But unfortunalety, all my tests on a DB of about 60 MB size did not take longer than a few seconds for 10 hosts.
It will take 'til weekend to find time to examine it in more detail.
Reinhard

Maybe you misunderstood me. Main bottleneck is add_graph.php.

I can add new Host with add_device.php, this takes 1 or less second. But add_device.php doesn't activate all graphs. I am getting new Host in Cacti but with all Graphs disabled.

After that I am calling add_graph.php for each graph I want to activate for this host.
And this step takes longer,10-50 seconds for each Graph.

[gandalf]

Maybe you misunderstood me. Main bottleneck is add_graph.php.

Oops
Thank you for waking me up.
Still, it will take 'til weekend. But in this case, I hope to find anything. Did NOT spend time on add_graphs.php
Sorry again for missing it
Reinhard

[TheWitness]

Execute the following:

Code: Select all

ALTER TABLE `data_input_fields` ADD INDEX `input_output`(`input_output`)

Code: Select all

ALTER TABLE `data_input_data` ADD INDEX `t_value`(`t_value`)

Code: Select all

ALTER TABLE `data_input_fields` ADD INDEX `data_name`(`data_name`)

Code: Select all

ALTER TABLE `data_input_data` MODIFY COLUMN `value` VARCHAR(255) DEFAULT NULL, ADD INDEX `value`(`value`)

See if it has a positive impact. If you are concerned about the change of type for "value" to varchar, execute the following:

Code: Select all

select max(length(value)) from data_input_data

If running MySQL 5.0.x, you can change the width to much more than 255.

TheWitness

[_Dmitry_]

Execute the following:

Code: Select all

ALTER TABLE `data_input_fields` ADD INDEX `input_output`(`input_output`)

Code: Select all

ALTER TABLE `data_input_data` ADD INDEX `t_value`(`t_value`)

Code: Select all

ALTER TABLE `data_input_fields` ADD INDEX `data_name`(`data_name`)

Code: Select all

ALTER TABLE `data_input_data` MODIFY COLUMN `value` VARCHAR(255) DEFAULT NULL, ADD INDEX `value`(`value`)

See if it has a positive impact. If you are concerned about the change of type for "value" to varchar, execute the following:

Code: Select all

select max(length(value)) from data_input_data

If running MySQL 5.0.x, you can change the width to much more than 255.

TheWitness

nope. Graph creation time for 1 Graph is the same ~ 30-50 sec.

Adding test (test.xxxxxxxxx.net) as "xxxxxxxxxxxxxxx" using SNMP v2 with community ""
Success - new device-id: (xxxx)
Device.creation time: 1 sec
php add_graphs.php --graph-type=cg --graph-template-id=xx --host-id=xxxx

Graph Added - graph-id: (xxxxx) - data-source-id: (xxxxx)
Gr.creation time: 53 sec

Code: Select all

02/13/2008 08:44:59 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT DISTINCT  data_template_rrd.local_data_id  FROM graph_templates_item, data_template_rrd  WHERE graph_templates_item.local_graph_id =  AND graph_templates_item.task_item_id = data_template_rrd.id"

Code: Select all

# Time: 080213  8:44:59
# User@Host: cactiuser @ localhost []
# Query_time: 52  Lock_time: 0  Rows_sent: 0  Rows_examined: 7257000
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)=35) AND ((data_input_data.t_value)='on') AND ((data_input_fields.input_output)='in'));

[TheWitness]

Well,

As you indicate, this is a problem:

02/13/2008 08:44:59 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT DISTINCT data_template_rrd.local_data_id FROM graph_templates_item, data_template_rrd WHERE graph_templates_item.local_graph_id = AND graph_templates_item.task_item_id = data_template_rrd.id"

What does your add_graph.php statement look like? Please attach your template XML.

TheWitness

[_Dmitry_]

Well,

As you indicate, this is a problem:

02/13/2008 08:44:59 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT DISTINCT data_template_rrd.local_data_id FROM graph_templates_item, data_template_rrd WHERE graph_templates_item.local_graph_id = AND graph_templates_item.task_item_id = data_template_rrd.id"

What does your add_graph.php statement look like?

php add_graphs.php --graph-type=cg --graph-template-id=xx --host-id=xxxx

Please attach your template XML.

TheWitness

In attachment you can find template XML for 1 of the graphs. (All Datasources are with Data Input Method set to "none". All RRD's for this Graph are updated via external interface.)

Maybe the Problem is my Pooling Interval ? I have 20 seconds.
Probably Pooler(spine) blocks something and add_graph need this ?