Poor performance showing a device's graphs on a tree

[sysres-dev]

Describe the bug
Displaying graphs in /graph_view.php?action=tree_content page is very slow with 26900 Devices and 458980 graphs.
This is due to a for loop in lib/html_tree.php, in the get_host_graph_list() function which produces 2 SQL request for each graph_template.

In our case this for loop iterates 900 times resulting in 1800 queries being executed, cumulating to an average of 12 seconds for the http request to complete.

Here is the extract of the code in lib/html_tree.php related to the issue (start at line 1409):

    if (cacti_sizeof($final_templates)) {
            foreach ($final_templates as $graph_template) {
                    $sql_where = '';

                    if (get_request_var('rfilter') != '') {
                            $sql_where = " (gtg.title_cache RLIKE '" . get_request_var('rfilter') . "')";
                    }

                    if ($host_id > 0) {
                            $sql_where .= ($sql_where != '' ? ' AND ':'') . 'gl.host_id=' . $host_id;
                    }

                    $sql_where .= ($sql_where != '' ? ' AND ':'') . 'gl.graph_template_id=' . $graph_template['id'];

                    $graphs = get_allowed_graphs($sql_where);
                    $agg    = get_allowed_aggregate_graphs($sql_where);
                    $graphs = array_merge($graphs, $agg);

                    /* let's sort the graphs naturally */
                    usort($graphs, 'naturally_sort_graphs');

                    if (cacti_sizeof($graphs)) {
                            foreach ($graphs as $graph) {
                                    $graph['graph_template_name'] = $graph_template['name'];
                                    array_push($graph_list, $graph);
                            }
                    }
            }
    }

These queries are executed in:

• lib/auth.php, get_allowed_graphs();
• lib/auth.php, get_allowed_aggregate_graphs();

To Reproduce
Performance issue is due vast amount of graphs, devices and graph_templates in database resulting on many SQL query being executed.

1. Go to 'Graphs' tab
2. Click on a host in tree view mode to display its graphs

Expected behavior
Decent loading time of graph_view.php when displaying list of graphs.

Screenshots

Additional context

Technical Support [Résumé]
Informations générales
Date | Tue, 19 Mar 2019 15:56:54 +0100
Cacti Version | 1.2.3
Cacti OS | unix
RRDtool Version Configuré | 1.4.0+
RRDtool Version Trouvé | 1.4.8
Équipements | 26425
Graphiques | 445487

Note
I have a fix ready to be evaluated that I will present in a PR soon.

[sysres-dev]

I've done the following PR regarding this issue : #2544.

[sysres-dev]

Thank you, I will run a check and close the issue.

[netniV]

Thanks for your contriubtion @sysres-dev