Edit Device taking too long to open the page

Post general support questions here that do not specifically fall into the Linux or Windows categories.

Moderators: Moderators, Developers

Post Reply
Author
Message
User avatar
dsmelk
Posts: 15
Joined: Thu Feb 14, 2019 7:10 am

Edit Device taking too long to open the page

#1 Post by dsmelk » Sun Dec 15, 2019 10:35 pm

Since I upgraded my cacti from 0.8.8b to 1.2.7 the Edit Host page is taking about 32 seconds to shows up.

After enable log for slow queries, I just realise that the problem is this query:
// $available_graph_templates = db_fetch_assoc_prepared("SELECT DISTINCT gt.id, gt.name
// FROM graph_templates AS gt
// LEFT JOIN snmp_query_graph AS sqg
// ON sqg.graph_template_id = gt.id
// INNER JOIN graph_templates_item AS gti
// ON gti.graph_template_id = gt.id
// INNER JOIN data_template_rrd AS dtr
// ON gti.task_item_id = dtr.id
// INNER JOIN data_template_data AS dtd
// ON dtd.data_template_id = dtr.data_template_id
// WHERE sqg.name IS NULL
// AND gti.local_graph_id = 0
// AND dtr.local_data_id = 0
// AND gt.id NOT IN (SELECT graph_template_id FROM host_graph WHERE host_id = ?)
// ORDER BY gt.name",
// array(get_request_var('id'))
// );

So I just changed the query for the 0.8.8b version and started to be fast again:
// $available_graph_templates = db_fetch_assoc("SELECT
// graph_templates.id, graph_templates.name
// FROM snmp_query_graph RIGHT JOIN graph_templates
// ON (snmp_query_graph.graph_template_id = graph_templates.id)
// WHERE (((snmp_query_graph.name) Is Null)) ORDER BY graph_templates.name");

My question is: Is there a problem if I keed the 0.8.8b query version?
Davyd Silva - 8)


User avatar
dsmelk
Posts: 15
Joined: Thu Feb 14, 2019 7:10 am

Re: Edit Device taking too long to open the page

#3 Post by dsmelk » Mon Dec 16, 2019 8:52 am

Hi netniV, thanks for the replay. I will change it back to the original query but could you please let me know what kind of issue I'm going to have if I didn't rollback? Do you know why it is taking too long?
Davyd Silva - 8)

netniV
Cacti Guru User
Posts: 3132
Joined: Sun Aug 27, 2017 12:05 am

Re: Edit Device taking too long to open the page

#4 Post by netniV » Tue Dec 17, 2019 9:45 am

Since you know the exact query that is causing the issue, have you run it through the mysql EXPLAIN ?

User avatar
dsmelk
Posts: 15
Joined: Thu Feb 14, 2019 7:10 am

Re: Edit Device taking too long to open the page

#5 Post by dsmelk » Tue Dec 17, 2019 11:16 am

netniV wrote:
Tue Dec 17, 2019 9:45 am
Since you know the exact query that is causing the issue, have you run it through the mysql EXPLAIN ?
As I have the slow_query_log function active, every time when I select a device to edit it usually takes from 25 to 35 seconds to open the page and the LOG shows:
  • # Time: 191217 16:09:57
  • # Thread_id: 35473408 Schema: cacti QC_hit: No
  • # Query_time: 27.781679 Lock_time: 0.000109 Rows_sent: 1460 Rows_examined: 56450
  • # Rows_affected: 0 Bytes_sent: 51036
  • SET timestamp=1576598997;
  • SELECT DISTINCT gt.id, gt.name FROM graph_templates AS gt LEFT JOIN snmp_query_graph AS sqg ON sqg.graph_template_id = gt.id INNER JOIN graph_templates_item AS gti ON gti.graph_template_id = gt.id INNER JOIN data_template_rrd AS dtr ON gti.task_item_id = dtr.id INNER JOIN data_template_data AS dtd ON dtd.data_template_id = dtr.data_template_id WHERE sqg.name IS NULL AND gti.local_graph_id = 0 AND dtr.local_data_id = 0 AND gt.id NOT IN (SELECT graph_template_id FROM host_graph WHERE host_id = '2661') ORDER BY gt.name;
So that's why I changed that query to the 0.8.8 version, but this time I just did a rollback. It will awesome if I could use another faster query because more than 15sec is too much for the users.
Davyd Silva - 8)

Post Reply