Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Search in tree view unusable on larger installations #4806

Closed
rb83 opened this issue Jun 7, 2022 · 5 comments
Closed

Search in tree view unusable on larger installations #4806

rb83 opened this issue Jun 7, 2022 · 5 comments
Labels
bug Undesired behaviour confirmed Bug is confirm by dev team performance Performance related affects big sites resolved A fixed issue tree Issues related to the graph tree
Milestone

Comments

@rb83
Copy link
Contributor

rb83 commented Jun 7, 2022

While trying to use the web search field (tree view) on one of our smaller cacti installations, we get consistent timeouts.
This is an installation with ~6k devices and ~52k graphs.

So far, we've traced the issue back to the SQL query beginning on line 54 in graph_view.php. Executing this query manually through mysql demonstrates that it would take ages to complete (didn't wait for completion).

audit_database.php doesn't show any complaints.

To Reproduce

Steps to reproduce the behavior:

  1. Go to tree view on a sizeable installation
  2. Hit 'Search' with a trivial string

Additional context

  • Cacti: 1.2.21
  • OS: RHEL7
  • DB: MariaDB 5.5
@rb83 rb83 added bug Undesired behaviour unverified Some days we don't have a clue labels Jun 7, 2022
@rb83 rb83 changed the title (ajax) Search in tree view unusable on sizeable installations Search in tree view unusable on sizeable installations Jun 7, 2022
@rb83
Copy link
Contributor Author

rb83 commented Jun 7, 2022

Running the SQL query with some (i.e. a lot) patience shows a metric ton of duplicated rows. The query is missing a DISTINCT at the very least, and needs some closer inspection. The multiple joins are a bit smelly, and right now it looks like joining by site_id lets everything explode. site_id in this context a bit of a headscratcher anyway, since it essentially short-circuits the query to return the whole of graph_tree_items, even when limiting the query to a single node.

@rb83
Copy link
Contributor Author

rb83 commented Jun 7, 2022

We've simplified the query as follows:

SELECT gti.parent, gti.graph_tree_id
FROM graph_tree_items AS gti
LEFT JOIN host AS h
ON h.id=gti.host_id
LEFT JOIN graph_templates_graph AS gtg
ON gtg.local_graph_id=gti.local_graph_id AND gtg.local_graph_id > 0
WHERE 
  gtg.title_cache LIKE ?
OR h.description LIKE ?
OR h.hostname LIKE ?
OR gti.title LIKE ?

In essence, the doubled join with host on site_id was removed. The reasoning behind this join is unclear, and to my current understanding of the DB schema this would return all graph_tree_items that have the same site_id as the host (or host description, et cetera...) matching in the where clause. Which would return a cartesian product from hell, or at least have the query planner deal with it.

For us, this restores functionality, and searches through the web interfaces are now super fast again.

Not sure if this breaks functionality in other places, but if it's good, I'd prepare a quick PR later on.

@TheWitness
Copy link
Member

Communicate you changes through a pull request please. I can comment there.

rb83 pushed a commit to rb83/cacti that referenced this issue Jun 9, 2022
Removes one of the joins on the host table: by site_id. Logic behind
that join is unclear, and on first glance would return just the whole of
graph_tree_items. This behaviour may have gone unnoticed because a
default install of cacti comes without any sites defined (not even a
default one) and site_id may contain NULL. On the install where we
observed issue Cacti#4806, sites are defined however.
@rb83
Copy link
Contributor Author

rb83 commented Jun 9, 2022

Communicate you changes through a pull request please. I can comment there.

Done as requested

TheWitness added a commit that referenced this issue Jun 10, 2022
* improve SQL query to search for nodes in tree view

Removes one of the joins on the host table: by site_id. Logic behind
that join is unclear, and on first glance would return just the whole of
graph_tree_items. This behaviour may have gone unnoticed because a
default install of cacti comes without any sites defined (not even a
default one) and site_id may contain NULL. On the install where we
observed issue #4806, sites are defined however.

* add changelog entry

* Light correction to SQL

Co-authored-by: rb83 <>
Co-authored-by: TheWitness <thewitness@cacti.net>
@TheWitness
Copy link
Member

I've made a slight change to your pull request. If you would please test it and report back, I would appreciate it.

@TheWitness TheWitness added tree Issues related to the graph tree confirmed Bug is confirm by dev team performance Performance related affects big sites and removed unverified Some days we don't have a clue labels Jun 10, 2022
@TheWitness TheWitness added this to the v1.2.22 milestone Jun 10, 2022
@TheWitness TheWitness added the resolved A fixed issue label Jun 16, 2022
@netniV netniV changed the title Search in tree view unusable on sizeable installations Search in tree view unusable on larger installations Aug 14, 2022
@github-actions github-actions bot locked and limited conversation to collaborators Nov 28, 2022
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
bug Undesired behaviour confirmed Bug is confirm by dev team performance Performance related affects big sites resolved A fixed issue tree Issues related to the graph tree
Projects
None yet
Development

No branches or pull requests

2 participants