Skip to main content

Bonjour,

En prestation pour un client grand compte, je constate des lenteurs d’afficage lors de l'appel à la page downtimes  /centreon/main.php?p=21001
il se passe une bonne quinzaine de secondes, au  minimum, avant que les données apparaissent.

En recoupant avec un Expert DBA de mon client grand compte, il s'avère que la requête SQL de l'affichage des downtime ne semble pas optimale. 
La partie des filtres, notamment, ne sont pas correctement indexés (host, acteur, descriptions...)
De même, si les champs de recherche sont vides, il n'est pas nécessaire de mettre LIKE '%%'. 

la requete en question : 
(SELECT SQL_CALC_FOUND_ROWS DISTINCT 1 AS REALTIME, d.internal_id as internal_downtime_id, d.entry_time, duration,
        d.author as author_name, d.comment_data, d.fixed as is_fixed, d.start_time as scheduled_start_time,
        d.end_time as scheduled_end_time, d.started as was_started, d.host_id, d.service_id, h.name as host_name,
        s.description as service_description  FROM downtimes d, services s, hosts h WHERE d.host_id = s.host_id AND d.service_id = s.service_id AND s.host_id = h.host_id AND d.type = 1  AND d.cancelled = 0 AND s.description LIKE '%%' AND h.name LIKE '%%' AND d.comment_data LIKE '%%' AND d.end_time > '1732196593'  AND d.comment_data NOT LIKE '%Downtime cycle%'  AND d.author LIKE '%%') UNION (SELECT DISTINCT 1 AS REALTIME, d.internal_id as internal_downtime_id, d.entry_time, duration,
  d.author as author_name, d.comment_data, d.fixed as is_fixed, d.start_time as scheduled_start_time,
  d.end_time as scheduled_end_time, d.started as was_started, d.host_id, d.service_id, h.name as host_name,
   '' as service_description FROM downtimes d, hosts h  WHERE d.host_id = h.host_id AND d.type = 2  AND d.cancelled = 0 AND h.name LIKE '%%' AND d.comment_data LIKE '%%' AND d.end_time > '1732196593'  AND d.comment_data NOT LIKE '%Downtime cycle%'  AND d.author LIKE '%%') ORDER BY scheduled_start_time DESC LIMIT 0, 50

Pourriez-vous prendre en compte ces remarques pour vos futurs releases ?

Cordialement

 

Emanuel Bourel

ADN-Consulting

ticket de support associé : 

https://support.centreon.com/hc/fr/requests/79841

NewDiscussion ongoing