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

[X-il3]

I deleted my database and imported the one you posted earlier. You can see in the following image the structure of data_input_data now. I notice no change in performance. The time between adding graphs increases a little bit after each added graph and we are only taking about 20-50 graphs.

With regards,

Elvar

[TheBigOne]

Hmm, thats strange, It should be better with the correct Index and the screenshot looks ok.

and i also changed some values in the php.ini (but cant remember)
memory_limit = 64M (or try more)

Try to reindex the tables and try to use the optimize- cli scripts of cacti in the cli folder. Its a little bit of testing but i think youre on the way

Check the DB dump i posted on the last page. cacti-structure.sql.txt

The first dump i postet is the wrong one (its the original cacti.sql i got from the package where i had problems )

This is my.cnf

Code: Select all

[mysqld]
tmpdir=/mysqld/tmp
datadir=/mysqld/data
old_passwords=1
skip-locking
key_buffer = 512M
query_cache_size = 128M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 128M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 8M
max_heap_table_size = 4G
tmp_table_size=1G;
long_query_time = 2
log_long_format
innodb_buffer_pool_size = 256M
log-bin
log=/mysqld/mysqld.log
log-slow-queries=/mysqld/slow-queries.log

[X-il3]

Hi again.

First I would like to thank you, Stefan, for all your help.

I have now compared the structure of your database to mine and they are now identical. I added all the stuff you had in your my.cnf into my my.cnf. I even changed the value of memory_limit to 64 in php.ini. All these changes and still it is slow.

I deleted all devices from cacti except the localhost. Then I ran my script where I am adding a single device with around 100 interfaces. The first few graphs are added really fast but as more graphs are added then the time taken to create a graph grows considerably fast. So using this script with hundreds of hosts where each host has tens or hundreds of interfaces is not an option.

I think I will now compare how the web interface adds a graph compared to how cli version does it. At least the time difference between the two methods is huge.

With regards,
Elvar

EDIT: You mentioned something about optimizing cacti using a script in the cli folder. The only script I found there that could resemble optimizing was the repair_database script, I ran that and nothing changed.

[TheBigOne]

File lib/api_automation_tools.php
function getInputFields($templateId) is producing the slow query. Perhaps it will help you discovering the problem. If you run the same query twice the query should be fast. Im still thinking that it is an index problem.

You will find the optimize.php script in the attachment. This script will optimize the db tables (like phpMyAdmin would do that)


Another point is to try the new api_automation_tools.php from svn. There are some other new files. but you should only use files from cacti 0.8.7.c not 0.8.8.

http://svn.cacti.net/viewvc/cacti/branches/0.8.7/

Perhaps you will find your bugfix (check the cacti.sql)

PS: i like to help you cause i had the same problem and didnt knew what to do. Its easier if someone is there who can give some ideas.

[X-il3]

Hi again.

I decided to download the unstable version of cacti, 0.8.7c. I ran the update script from 0.8.7b to 0.8.7c. This script alters some tables in the database. I also noticed that add_graphs.php seems to have changed from the previous version.

Then I tried my script and it ran like charm. No more issues with slowdowns.

So in a nutshell, I did the following:

1. Copied 0_8_7b_to_0_8_7c.php from the install folder of the unstable version to the install folder of my current 0.8.7b
2. Copied all the scripts from the cli folder of the unstable version to the cli folder of the stable version
3. Ran 0_8_7b_to_0_8_7c.php
4. After that everything worked like a charm.

PS. Before I ran the update script, my cacti.sql file looked exactly like the one that you posted after you made some modifications to it. Whether that matters or not, I do not know.

With regards,
Elvar

[TheBigOne]

I think they fixet the issue, cause i was not the first one with this problem. Hope this thread will help others too.

Best regards
Stefan

[X-il3]

But anyway, thanks for all the help

With regards,
Elvar

[yozik]

sorry to revive a solved thread, just wanted to note my experience in-case someone comes across this in their searching for a solution.

for what its worth, I ended up adding key on data_template_data_id

mysql> alter table data_input_data add key(data_template_data_id) ;


timing without the key

mysql> SELECT data_input_field_id,t_value,value FROM data_input_data WHERE data_template_data_id=38 ;
+---------------------+---------+-------+
| data_input_field_id | t_value | value |
+---------------------+---------+-------+
| 7 | | |
| 8 | | |
| 9 | | |
| 10 | | |
| 11 | | |
| 12 | on | |
| 13 | on | |
| 14 | on | |
| 39 | | |
| 44 | | |
| 45 | | |
| 46 | | |
| 47 | | |
+---------------------+---------+-------+
13 rows in set (5.08 sec)

mysql>



timing with key

mysql> SELECT data_input_field_id,t_value,value FROM data_input_data WHERE data_template_data_id=38 ;
+---------------------+---------+-------+
| data_input_field_id | t_value | value |
+---------------------+---------+-------+
| 7 | | |
| 8 | | |
| 9 | | |
| 10 | | |
| 11 | | |
| 12 | on | |
| 13 | on | |
| 14 | on | |
| 39 | | |
| 44 | | |
| 45 | | |
| 46 | | |
| 47 | | |
+---------------------+---------+-------+
13 rows in set (0.00 sec)

mysql>


I'll point out that query cache is disabled, so the quicker response time is not due to caching.


running poller_reindex_hosts.php without the key on a host that has 687 datasources

>time /opt/cacti/php/bin/php /opt/cacti/cacti/cli/poller_reindex_hosts.php --debug --id=19919
WARNING: Do not interrupt this script. Reindexing can take quite some time
DEBUG: There are '2' data queries to run
DEBUG: Data query number '1' host: '19919' SNMP Query Id: '1' starting
DEBUG: Data query number '1' host: '19919' SNMP Query Id: '1' ending
DEBUG: Data query number '2' host: '19919' SNMP Query Id: '20' starting
DEBUG: Data query number '2' host: '19919' SNMP Query Id: '20' ending

real 3m17.020s
user 0m2.572s
sys 0m0.596s



now with the key

>time /opt/cacti/php/bin/php /opt/cacti/cacti/cli/poller_reindex_hosts.php --debug --id=19919

WARNING: Do not interrupt this script. Reindexing can take quite some time
DEBUG: There are '2' data queries to run
DEBUG: Data query number '1' host: '19919' SNMP Query Id: '1' starting
DEBUG: Data query number '1' host: '19919' SNMP Query Id: '1' ending
DEBUG: Data query number '2' host: '19919' SNMP Query Id: '20' starting
DEBUG: Data query number '2' host: '19919' SNMP Query Id: '20' ending

real 2m23.927s
user 0m2.429s
sys 0m0.576s


true the timings are from only two runs so there will be differences in timing, I would suspect, depending on how busy the cacti server and remote node are.

now where having the key shines is when I reindex multiple hosts at the same time or when the host/db is under load, the following query would take anywhere from 7 - 20 seconds to complete, now it completes almost instantly thus allowing the program flow to continue.

SELECT data_input_field_id,t_value,value FROM data_input_data WHERE data_template_data_id=........


this was causing the reindex to take a very long time to complete and things would just slow to a crawl.

adding in query caching seems to help some as well, but I didn't want to introduce new variables while working out a solution.



cacti version

About Cacti
Version 0.8.8b


mysql> show variables like '%ersion%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 26
Current database: cacti

+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.1.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.21 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>


final table layout


mysql> show create table data_input_data;
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create 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`),
KEY `data_template_data_id` (`data_template_data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

EDIT:

I should note that autom8 is in play here so times maybe longer than others are used to


yozik