After upgrade from 22.04.7 to 22.10.1 the resource status page is locked by heavy queries

Hi @kozfelipe , we didn’t know about this issue but Centreon support only MariaDB 10.5.x and not 10.6.x

hi @Laurent, indeed we should have migrated database longtime ago, but the issue still remains. We had to comment this query in order to maintain the database cpu causing resources page to be unavailable. This version works fine in our dev environment. Is there any diagnostic test to run?

note: when I remove LEFT JOIN parent_resource, the query runs instantly

fixed with a DBA indexing work

thanks for the attention

hi, Wich index have you added ?

We have the same issue.

@Mguerchet hi,

Even with the indexation work, the query became endless by the fact we have 100k entries at centreon_acl table. I ran tests in dev enviroiment with 250 lines and the query execution took almost 4 seconds, so we had to comment the ACL part of the query by replacing /usr/share/centreon/src/Core/Resources/Infrastructure/Repository/DbReadResourceRepository.php (line 297) $pattern content to empty

https://github.com/centreon/centreon/blob/develop/centreon/src/Core/Resources/Infrastructure/Repository/DbReadResourceRepository.php#L297

$pattern = ' ';

@Laurent have you ever seen the performance this version have for resources with 5k+ centreon_acl rows?

Hi,

Issue is currently being discussed by the backend PHP Team so that we can optimize the query when ACL are implied.

We are deeply sorry for the inconvenience on Resource Status page.

We’ll get back to you solution is up on the rails

Regards,

Adrien

hi @Adrien Morais-Mestre  do you have some news to optomimize this query ?

Hi,

This is still a work in progress. We are currently analyzing the request and were able to reproduce the issue.

It needs know some time to find the best solution.

Stay tuned.

Regards,

Adrien

Hi all,

It’s ok with 22.10.02 update .

regards,

Matthieu

Hi @Mguerchet sorry forgot to let you know about the 22.10.2 that fixes the issue. Glad to here that everything is now working fine 💪

Regards,

Adrien