You are not logged in.

Announcement

 Téléchargez la dernière version stable de GLPI      -     Et vous, que pouvez vous faire pour le projet GLPI ? :  Contribuer
 Download last stable version of GLPI                      -     What can you do for GLPI ? :  Contribute

#1 2018-06-18 10:54:21

Jean-Christophe
Moderator
Registered: 2007-08-22
Posts: 734

Lenteur extrême pour accéder aux "Network Devices"

Bonjour,

(GLPI 9.2.3 FusionInventory 9.2+2.0)

Quand j'accède à la liste des devices réseau, l'affichage de la page prend près d'une minute.
Je n'ai aucun souci de performance avec les autres types de device.

J'ai activé l'option de débug et j'ai identifié deux queries qui prennent près de 30 secondes chacunes.
Mes compétences en SQL étant ce qu'elles sont, je ne sais pas aller plus loin pour trouver le souci.

Voici les queries en question.

SELECT 'IWV877_admin' AS currentuser, `glpi_networkequipments`.`entities_id`, `glpi_networkequipments`.`is_recursive`, `glpi_networkequipments`.`name` AS `ITEM_0`, `glpi_networkequipments`.`id` AS `ITEM_0_id`, `glpi_states`.`completename` AS `ITEM_1`, `glpi_manufacturers`.`name` AS `ITEM_2`, `glpi_locations`.`completename` AS `ITEM_3`, `glpi_networkequipmenttypes`.`name` AS `ITEM_4`, `glpi_networkequipmentmodels`.`name` AS `ITEM_5`, `glpi_networkequipments`.`serial` AS `ITEM_6`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2`.`version`, '__NULL__'), '$#$',`glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2`.`id`) SEPARATOR '$$##$$') AS `ITEM_7`, `glpi_networkequipments`.`date_mod` AS `ITEM_8`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`name`, '__NULL__'), '$#$',`glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`id`) SEPARATOR '$$##$$') AS `ITEM_9`, `glpi_plugin_fusioninventory_configsecurities`.`name` AS `ITEM_10`, `glpi_plugin_fusioninventory_configsecurities`.`id` AS `ITEM_10_id`, `glpi_plugin_fusioninventory_networkequipments`.`last_fusioninventory_update` AS `ITEM_11`, `glpi_networkequipments`.`otherserial` AS `ITEM_12`, `glpi_networkequipments`.`id` AS `ITEM_13`, `glpi_networkequipments`.`id` AS id 
FROM `glpi_networkequipments`
LEFT JOIN `glpi_states` ON (`glpi_networkequipments`.`states_id` = `glpi_states`.`id` )
LEFT JOIN `glpi_manufacturers` ON (`glpi_networkequipments`.`manufacturers_id` = `glpi_manufacturers`.`id` )
LEFT JOIN `glpi_locations` ON (`glpi_networkequipments`.`locations_id` = `glpi_locations`.`id` )
LEFT JOIN `glpi_networkequipmenttypes` ON (`glpi_networkequipments`.`networkequipmenttypes_id` = `glpi_networkequipmenttypes`.`id` )
LEFT JOIN `glpi_networkequipmentmodels` ON (`glpi_networkequipments`.`networkequipmentmodels_id` = `glpi_networkequipmentmodels`.`id` ) 
LEFT JOIN `glpi_items_devicefirmwares` ON (`glpi_networkequipments`.`id` = `glpi_items_devicefirmwares`.`items_id` AND `glpi_items_devicefirmwares`.`itemtype` = 'NetworkEquipment' ) 
LEFT JOIN `glpi_devicefirmwares` AS `glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2` ON (`glpi_items_devicefirmwares`.`devicefirmwares_id` = `glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2`.`id` ) 
LEFT JOIN `glpi_ipaddresses` AS `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540` ON (`glpi_networkequipments`.`id` = `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`mainitems_id` AND `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`mainitemtype` = 'NetworkEquipment' AND `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`is_deleted` = 0 ) 
LEFT JOIN glpi_plugin_fusioninventory_networkequipments ON glpi_networkequipments.id = glpi_plugin_fusioninventory_networkequipments.networkequipments_id 
LEFT JOIN glpi_plugin_fusioninventory_configsecurities ON glpi_plugin_fusioninventory_networkequipments.plugin_fusioninventory_configsecurities_id = glpi_plugin_fusioninventory_configsecurities.id 
WHERE `glpi_networkequipments`.`is_deleted` = '0' AND `glpi_networkequipments`.`is_template` = '0' GROUP BY `glpi_networkequipments`.`id` 
ORDER BY ITEM_0 ASC LIMIT 0, 500

28 secondes

et

SELECT 'IWV877_admin' AS currentuser, `glpi_networkequipments`.`entities_id`, `glpi_networkequipments`.`is_recursive`, `glpi_networkequipments`.`name` AS `ITEM_0`, `glpi_networkequipments`.`id` AS `ITEM_0_id`, `glpi_states`.`completename` AS `ITEM_1`, `glpi_manufacturers`.`name` AS `ITEM_2`, `glpi_locations`.`completename` AS `ITEM_3`, `glpi_networkequipmenttypes`.`name` AS `ITEM_4`, `glpi_networkequipmentmodels`.`name` AS `ITEM_5`, `glpi_networkequipments`.`serial` AS `ITEM_6`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2`.`version`, '__NULL__'), '$#$',`glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2`.`id`) SEPARATOR '$$##$$') AS `ITEM_7`, `glpi_networkequipments`.`date_mod` AS `ITEM_8`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`name`, '__NULL__'), '$#$',`glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`id`) SEPARATOR '$$##$$') AS `ITEM_9`, `glpi_plugin_fusioninventory_configsecurities`.`name` AS `ITEM_10`, `glpi_plugin_fusioninventory_configsecurities`.`id` AS `ITEM_10_id`, `glpi_plugin_fusioninventory_networkequipments`.`last_fusioninventory_update` AS `ITEM_11`, `glpi_networkequipments`.`otherserial` AS `ITEM_12`, `glpi_networkequipments`.`id` AS `ITEM_13`, `glpi_networkequipments`.`id` AS id 
FROM `glpi_networkequipments`
LEFT JOIN `glpi_states` ON (`glpi_networkequipments`.`states_id` = `glpi_states`.`id` )
LEFT JOIN `glpi_manufacturers` ON (`glpi_networkequipments`.`manufacturers_id` = `glpi_manufacturers`.`id` )
LEFT JOIN `glpi_locations` ON (`glpi_networkequipments`.`locations_id` = `glpi_locations`.`id` )
LEFT JOIN `glpi_networkequipmenttypes` ON (`glpi_networkequipments`.`networkequipmenttypes_id` = `glpi_networkequipmenttypes`.`id` )
LEFT JOIN `glpi_networkequipmentmodels` ON (`glpi_networkequipments`.`networkequipmentmodels_id` = `glpi_networkequipmentmodels`.`id` ) 
LEFT JOIN `glpi_items_devicefirmwares` ON (`glpi_networkequipments`.`id` = `glpi_items_devicefirmwares`.`items_id` AND `glpi_items_devicefirmwares`.`itemtype` = 'NetworkEquipment' ) 
LEFT JOIN `glpi_devicefirmwares` AS `glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2` ON (`glpi_items_devicefirmwares`.`devicefirmwares_id` = `glpi_devicefirmwares_c5ad835b6516bc670a3da6ab06d0dcf2`.`id` ) 
LEFT JOIN `glpi_ipaddresses` AS `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540` ON (`glpi_networkequipments`.`id` = `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`mainitems_id` AND `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`mainitemtype` = 'NetworkEquipment' AND `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`is_deleted` = 0 ) 
LEFT JOIN glpi_plugin_fusioninventory_networkequipments ON glpi_networkequipments.id = glpi_plugin_fusioninventory_networkequipments.networkequipments_id 
LEFT JOIN glpi_plugin_fusioninventory_configsecurities ON glpi_plugin_fusioninventory_networkequipments.plugin_fusioninventory_configsecurities_id = glpi_plugin_fusioninventory_configsecurities.id 
WHERE `glpi_networkequipments`.`is_deleted` = '0' AND `glpi_networkequipments`.`is_template` = '0' GROUP BY `glpi_networkequipments`.`id` 
ORDER BY ITEM_0 ASC LIMIT 0, 500

Près de 28 secondes également.

J'ai rencontré un Bug il y a quelques semaines qui m'ajoutait des miliers de Firmwares dans l'inventaire. J'ai fait le ménage depuis. La table des firmware ne devrait plus poser de souci. Cependant, je ne peux pas garantir que le souci ne soit pas lié.

Si vous avez des pistes ou des idées, je suis preneur smile

Merci et bonne journée,
JC

Offline

#2 2018-06-18 11:00:09

Jean-Christophe
Moderator
Registered: 2007-08-22
Posts: 734

Re: Lenteur extrême pour accéder aux "Network Devices"

Je continue à creuser...
En lancant la même query dans phpMyAdmin avec l'option de profiling, 99.88% du temps passé l'est pour "Storing Result In Query Cache".
Mais je ne suis pas beaucoup plus avancé big_smile

Offline

#3 2018-07-16 16:55:05

Jean-Christophe
Moderator
Registered: 2007-08-22
Posts: 734

Re: Lenteur extrême pour accéder aux "Network Devices"

J'ai fini par trouver une cause.
Il y a quelques mois, il y avait un bug de Fusion qui ajoutait un nouveau firmware à chaque inventaire...
Le bug est résolu, j'ai fait le ménage dans la DB. J'ai, visiblement, un peu trop fait le ménage.
Bref, c'est l'affichage de firmware qui pose problème dans mon cas.
J'ai viré la colonne des firmwares et c'est de nouveau instantané.

Offline

Board footer

Powered by FluxBB