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

[TheWitness]

Hmm, this may be a problem. Innodb tables do help with things like this. However, you need to know what you are doing with Innodb settings. I would suspect that you already have a custom my.cnf file. Am I correct?

TheWitness

[_Dmitry_]

Hmm, this may be a problem. Innodb tables do help with things like this. However, you need to know what you are doing with Innodb settings. I would suspect that you already have a custom my.cnf file. Am I correct?

TheWitness

But why this happens only with CLI tolls ?
With Web UI I can create all Graphs in 2 seconds.

And yes I have custom my.cnf:

Code: Select all

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 32M
query_cache_size = 64M 
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
log_slow_queries


[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[TheWitness]

It's a long story. However, it may have something to do with the errors. Also, there may be a small design issue. However, it will have to wait till the weekend. Either Gandolf or myself will address it, time permitting.

TheWitness

[Crescens]

Hello,

is there any update?

I have the same problems.
I´m running 0.8.7a without any additional indexes.

Speed is ugly slow

# Query_time: 441 Lock_time: 0 Rows_sent: 3 Rows_examined: 103884973
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)=70) AND ((data_input_data.t_value)='on') AND ((data_input_fields.input_output)='in'));

If I run this query manually it will take nearly 9 minutes (no wonder if it scans more than 100 Million datasets).

*************************** 1. row ***************************
name: index_type
description: Index Type
default:
data_template_id: 43
data_input_field_id: 35
*************************** 2. row ***************************
name: index_value
description: Index Value
default:
data_template_id: 43
data_input_field_id: 36
*************************** 3. row ***************************
name: output_type
description: Output Type ID
default:
data_template_id: 43
data_input_field_id: 37
3 rows in set (9 min 8.04 sec)

a describe SELECT DISTINCT... looks like this:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: graph_templates
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: graph_templates_item
type: ref
possible_keys: graph_template_id
key: graph_template_id
key_len: 3
ref: const
rows: 15914
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: data_template_rrd
type: eq_ref
possible_keys: PRIMARY,data_template_id
key: PRIMARY
key_len: 3
ref: cacti_prod.graph_templates_item.task_item_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: data_template_data
type: ref
possible_keys: PRIMARY,data_template_id
key: data_template_id
key_len: 3
ref: cacti_prod.data_template_rrd.data_template_id
rows: 59
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: data_input_fields
type: ref
possible_keys: PRIMARY,data_input_id
key: data_input_id
key_len: 3
ref: cacti_prod.data_template_data.data_input_id
rows: 5
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: data_input_data
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 6
ref: cacti_prod.data_input_fields.id,cacti_prod.data_template_data.id
rows: 1
Extra: Using where
6 rows in set (0.00 sec)

This error makes no sense to me - I hope to get some debugging input.
If I find some time I will update the box and look into the svn..

[fmangeant]

Hi

is this an option to migrate to 0.8.7b, or add the 2 new indexes by hand ?

Code: Select all

function upgrade_to_0_8_7b() {
        /* add Task Item Id Index */
        db_install_execute("0.8.7b", "ALTER TABLE `graph_templates_item` ADD INDEX `task_item_id` ( `task_item_id` )");
        /* make CLI more responsive */
        db_install_execute("0.8.7b", "ALTER TABLE `data_input_data` ADD INDEX `t_value`(`t_value`)");
}

[Crescens]

I´ll do that tomorrow.
Thanks.

[Crescens]

Well done.
I added the indexes - rerun the query and it took about 0.80 sec.

[dpround]

From what I have understood from this discussion 0.8.7b should already have these index mods. The cli to add graphs was still very slow on my installation. A quick check of the database showed that the indexes were not present. Adding them has improved the cli speed considerably!

The rpm package above doesn't seem to include the cli .php files, which is odd. I had to install these separately.

[gandalf]

There's another (code) fix to get more speed. Fetch it from SVN
Reinhard

[Swordfish]

Hi,

We are using 087a and have the problem described here.
Moving forward I'd like to go straight to 087c (when it surfaces).

1. When is 087c scheduled to appear?
2. Is there an upgrade process to update the 087a databases with the new indices?

Rob

[gandalf]

You should use 087b.
A schedule for 087c is currently not planned. Next official release will be 088. But this _may_ change.
You may fetch latest cli code from SVN branch 087. Should work with 087a/b. Please save current scripts as a safeguard.
The latest improvement is NOT an additional index but a pure code change
Reinhard

[ptaylor874]

Wow! Major improvement with these indexes and the latest add_graphs.php!

I was in the process of setting up a new Cacti box running 0.87b (to replace an old machine that isn't up to the task) and I ran a script I wrote to add all the remote devices, each with two graphs. While it started out fast, it quickly slowed down to a crawl, and after adding devices for hours was down to about one device about every 6-8 minutes. (Over 16 hours later and it still wasn't done adding devices!)

I added the indexes in this post (that weren't in my 0.87b install) and grabbed the latest add_graphs.php from SVN, and now things are really cooking! Things were moving so fast, I deleted all the hosts added by my first attempt and let it start from scratch.

After only about 35 minutes, all 468 devices were added! Great work, guys!

[nydoofus]

Hi

is this an option to migrate to 0.8.7b, or add the 2 new indexes by hand ?

Code: Select all

function upgrade_to_0_8_7b() {
        /* add Task Item Id Index */
        db_install_execute("0.8.7b", "ALTER TABLE `graph_templates_item` ADD INDEX `task_item_id` ( `task_item_id` )");
        /* make CLI more responsive */
        db_install_execute("0.8.7b", "ALTER TABLE `data_input_data` ADD INDEX `t_value`(`t_value`)");
}

I added these indexes by hand to a brand new 0.8.7b install and wow, what a difference in performance. It appears the base cacti.sql does not contain these indexes however.

[Swordfish]

I think if you read closely, an update to the CLI code is all you need.

You should use 087b.
The latest improvement is NOT an additional index but a pure code change
Reinhard

Rob

[TheWitness]

Should we prep a C? Don't know. What's the ChangeLog look like?

Larry