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

  • 13 November 2022
  • 11 replies
  • 239 views

Userlevel 1
Badge +7

Hi there,

After upgrading to 22.10.1, when we use resource staus page, the resource status query used by DbReadResourceRepository.php is taking longer than 2.5 minutes causing database queuing and topping database cpu

I thought it could be the way mysql handles the join queries, so we migrated from mysql 5.7 to mariadb 10.6.7, but it didnt make a difference to this query.

Does anyone have any tips

 


11 replies

Userlevel 6
Badge +18

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

Userlevel 1
Badge +7

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?

Userlevel 1
Badge +7

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

Userlevel 1
Badge +7

fixed with a DBA indexing work

thanks for the attention

Badge +3

hi, Wich index have you added ?

We have the same issue.

Userlevel 1
Badge +7

@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?

Userlevel 2
Badge +5

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

Badge +3

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

Userlevel 2
Badge +5

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

Badge +3

Hi all,

It’s ok with 22.10.02 update .

 

regards,

Matthieu

Userlevel 2
Badge +5

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

Reply