CLI Scripts unusable

[TheWitness]

For your size implementation that looks good, unless you have several other large databases on that system.

TheWitness

[Alice]

Neah... 2 cacti DB that are about the same size, and a syslog database (ex-haloe).

[chillywilly]

I added the same index, but things still run a bit slow. I am running things within a VM instance on the same box, but I only have ~6000 rows in the largest table. This is going to take a while to populate this new cacti 0.8.7a install with my script...

[chillywilly]

I have a tiny installation in comparison to the one mentioned in this *bug* and that same or similar query was giving me 200-300 second query times even after adding the index. I ended up adding all the graphs by hand to 120+ Linux/Solaris hosts as it was much faster to do so :-/.

[finn]

Initially after adding the index I was still seeing very slow times too.

What I found helped was doing an "OPTIMIZE TABLE `data_input_data`". Obviously it must have gotton quite fragmented or something as running this seems to have worked a treat. Afer that I was back into sub 5 second run of add_graphs vs the minutes it eventually got up to (this is on a test box, not on production hardware yet!).

Probably an idea to run it out of cron daily with a simple shell script such as:

Code: Select all

#!/bin/bash


TABLES=`echo "show tables;" | /usr/bin/mysql cacti --user=USER --password=PASSWORD  | tail +2`


for each in `echo $TABLES`; do
  echo "OPTIMIZE TABLE \`$each\`" | /usr/bin/mysql cacti --user=USER --password=PASSWORD
done

[thomasw]

Hello,

after optimizing the mysql tables and setting the index "ALTER TABLE `data_input_data` ADD INDEX ( `t_value` );" my script still needs approx. 10 - 13 seconds to insert one graph. Very much better than the runtime before, but not enough...
Because I have to add 10.000 Graphs it will take around 27 hours to complete, which is too much time...
through the web-interface one can create approx. 500 graphs within a minute or two!
Has anybody any more / new suggestions on how to optimize the overall runtime of the add_graphs.php script?

Any help is highly welcome!

thanks in advance,

Tom

[gandalf]

If you are able to add the "slow_queries" directive to your my.cnf MySQL configuration, it will tell you about possible bottlenecks in queries. This way you will get hints how to optimize indices. Sorry, but currently I have no time to investigate on my own
Reinhard

[thomasw]

Hello,

as Alice already pointed out the following query takes approx. 10-14 seconds to return results (found in the slow-query-log):

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

The explain command gives the following output:

+----+-------------+----------------------+--------+--------------------------+---------+---------+----------------------------------------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+--------------------------+---------+---------+----------------------------------------------------+--------+------------------------------+
| 1 | SIMPLE | graph_templates | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index; Using temporary |
| 1 | SIMPLE | data_input_data | ref | PRIMARY,t_value | t_value | 5 | const | 216 | Using where |
| 1 | SIMPLE | data_input_fields | eq_ref | PRIMARY,data_input_id | PRIMARY | 3 | cacti_lan_db.data_input_data.data_input_field_id | 1 | Using where |
| 1 | SIMPLE | data_template_data | eq_ref | PRIMARY,data_template_id | PRIMARY | 3 | cacti_lan_db.data_input_data.data_template_data_id | 1 | Using where |
| 1 | SIMPLE | graph_templates_item | ALL | graph_template_id | NULL | NULL | NULL | 156112 | Using where; Distinct |
| 1 | SIMPLE | data_template_rrd | eq_ref | PRIMARY,data_template_id | PRIMARY | 3 | cacti_lan_db.graph_templates_item.task_item_id | 1 | Using where; Distinct |
+----+-------------+----------------------+--------+--------------------------+---------+---------+----------------------------------------------------+--------+------------------------------+
6 rows in set (0.00 sec)

my first idea was to setup an index on graph_templates_item.graph_template_id, but it was already set...

is there a sql guru out which can give a hot hint on this?
any ideas are highly welcome!

thanks in advance,

tom

[thomasw]

Hello,

I found out if one adds one more index to graph_templates_item table on field 'task_item_id' the query is more fast than before.

without the index the query needs 10 - 14 seconds, with the index the query needs around 6 seconds, which is an improvement of 50 percent...

what I have done:
ALTER TABLE `graph_templates_item` ADD INDEX ( `task_item_id` ) ;

any suggestions on this or more like this?
I`m very interested...

thanks in advance,

best regards,

tom

[gandalf]

This is a good find. Due to multiple reasons it is planned to implement this index with the next cacti release. (in this case you will get an upgrade error on exactly this index, as it already exists for you)
Reinhard