Forum GLPI-Project

tmartin

Member

Registered: 2018-06-13

Posts: 1

Une certaine lenteur des requetes SQL de GLPI.

Bonjour,

Je migre mon GLPI de la version 0.83.31 à la version 9.2.3.

Jusqu'ici je n'ai pas trop de soucis (super boulot des devs au passage pour réussir la migration en partant d'une aussi vieille version ;).

Je n'ai qu'un vrai soucis: les requêtes SQL semblent plus lentes qu'auparavant.

En effet, en 0.83.31, quand j'affichais ma page "Ordinateurs" (avec ma vue personnalisée), la requête SQL principale prenait 1,2 sec environ:

SELECT 'tmartin' AS currentuser, `glpi_computers`.`name` AS ITEM_0, `glpi_computers`.`id` AS ITEM_0_2, `glpi_states`.`completename` AS ITEM_1, `glpi_computers`.`otherserial` AS ITEM_2, `glpi_locations`.`completename` AS ITEM_3, `glpi_computers`.`comment` AS ITEM_4, SUM(`glpi_computers_devicememories`.`specificity`) / COUNT(`glpi_computers_devicememories`.`id`) * COUNT(DISTINCT `glpi_computers_devicememories`.`id`) AS ITEM_5, SUM(`glpi_computers_deviceharddrives`.`specificity`) / COUNT(`glpi_computers_deviceharddrives`.`id`) * COUNT(DISTINCT `glpi_computers_deviceharddrives`.`id`) AS ITEM_6, GROUP_CONCAT(DISTINCT `glpi_infocoms`.`immo_number` SEPARATOR '$$$$') AS ITEM_7, `glpi_computermodels`.`name` AS ITEM_8, GROUP_CONCAT(DISTINCT `glpi_infocoms`.`buy_date` SEPARATOR '$$$$') AS ITEM_9, `glpi_computers`.`contact` AS ITEM_10, `glpi_operatingsystemversions`.`name` AS ITEM_11, `glpi_computers`.`serial` AS ITEM_12, `glpi_computers`.`id` AS id
FROM `glpi_computers`
LEFT JOIN `glpi_states` ON (`glpi_computers`.`states_id` = `glpi_states`.`id` )
LEFT JOIN `glpi_locations` ON (`glpi_computers`.`locations_id` = `glpi_locations`.`id` )
LEFT JOIN `glpi_computers_devicememories` ON (`glpi_computers`.`id` = `glpi_computers_devicememories`.`computers_id` )
LEFT JOIN `glpi_computers_deviceharddrives` ON (`glpi_computers`.`id` = `glpi_computers_deviceharddrives`.`computers_id` )
LEFT JOIN `glpi_infocoms` ON (`glpi_computers`.`id` = `glpi_infocoms`.`items_id` AND `glpi_infocoms`.`itemtype` = 'Computer' )
LEFT JOIN `glpi_computermodels` ON (`glpi_computers`.`computermodels_id` = `glpi_computermodels`.`id` )
LEFT JOIN `glpi_operatingsystemversions` ON (`glpi_computers`.`operatingsystemversions_id` = `glpi_operatingsystemversions`.`id` )
WHERE `glpi_computers`.`is_deleted` = '0' AND `glpi_computers`.`is_template` = '0' GROUP BY `glpi_computers`.`id`
ORDER BY ITEM_0 ASC LIMIT 0, 20

Maintenant quand j'affiche la page "Ordinateurs" (toujours avec ma vue personnalisée), cela prends 2.4 sec environ:

SELECT 'tmartin' AS currentuser, `glpi_computers`.`entities_id`, `glpi_computers`.`is_recursive`, `glpi_computers`.`name` AS `ITEM_0`, `glpi_computers`.`id` AS `ITEM_0_id`, `glpi_states`.`completename` AS `ITEM_1`, `glpi_computers`.`otherserial` AS `ITEM_2`, `glpi_locations`.`completename` AS `ITEM_3`, `glpi_computers`.`comment` AS `ITEM_4`, (SUM(`glpi_items_devicememories`.`size`) / COUNT(`glpi_items_devicememories`.`id`)) * COUNT(DISTINCT `glpi_items_devicememories`.`id`) AS `ITEM_5`, (SUM(`glpi_items_deviceharddrives`.`capacity`) / COUNT(`glpi_items_deviceharddrives`.`id`)) * COUNT(DISTINCT `glpi_items_deviceharddrives`.`id`) AS `ITEM_6`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_infocoms`.`immo_number`, '__NULL__'), '$#$',`glpi_infocoms`.`id`) SEPARATOR '$$##$$') AS `ITEM_7`, `glpi_computermodels`.`name` AS `ITEM_8`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_infocoms`.`buy_date`, '__NULL__'), '$#$',`glpi_infocoms`.`id`) SEPARATOR '$$##$$') AS `ITEM_9`, `glpi_computers`.`contact` AS `ITEM_10`, `glpi_operatingsystemversions_9719987b154aaf3b42c3db32aef59090`.`name` AS `ITEM_11`, `glpi_items_operatingsystems`.`license_number` AS `ITEM_12`, `glpi_computers`.`id` AS id
FROM `glpi_computers`
LEFT JOIN `glpi_states` ON (`glpi_computers`.`states_id` = `glpi_states`.`id` )
LEFT JOIN `glpi_locations` ON (`glpi_computers`.`locations_id` = `glpi_locations`.`id` )
LEFT JOIN `glpi_items_devicememories` ON (`glpi_computers`.`id` = `glpi_items_devicememories`.`items_id` AND `glpi_items_devicememories`.`itemtype` = 'Computer' )
LEFT JOIN `glpi_items_deviceharddrives` ON (`glpi_computers`.`id` = `glpi_items_deviceharddrives`.`items_id` AND `glpi_items_deviceharddrives`.`itemtype` = 'Computer' )
LEFT JOIN `glpi_infocoms` ON (`glpi_computers`.`id` = `glpi_infocoms`.`items_id` AND `glpi_infocoms`.`itemtype` = 'Computer' )
LEFT JOIN `glpi_computermodels` ON (`glpi_computers`.`computermodels_id` = `glpi_computermodels`.`id` )
LEFT JOIN `glpi_items_operatingsystems` ON (`glpi_computers`.`id` = `glpi_items_operatingsystems`.`items_id` AND `glpi_items_operatingsystems`.`itemtype` = 'Computer' )
LEFT JOIN `glpi_operatingsystemversions` AS `glpi_operatingsystemversions_9719987b154aaf3b42c3db32aef59090` ON (`glpi_items_operatingsystems`.`operatingsystemversions_id` = `glpi_operatingsystemversions_9719987b154aaf3b42c3db32aef59090`.`id` )
WHERE `glpi_computers`.`is_deleted` = '0' AND `glpi_computers`.`is_template` = '0' GROUP BY `glpi_computers`.`id`
ORDER BY ITEM_5 DESC LIMIT 0, 20

Après investigations, c'est le ORDER BY qui est très couteux (la quasi totalité des 2.4 secondes).

Avez-vous quoi que ce soit à me recommander pour améliorer ça (à part modifier ma vue perso ;) ?

Je précise que j'ai déjà lu et appliqué différents tutos pour optimiser les performances du serveur mysql/mariadb (j'utilise mariadb 10.1) mais que cela n'a rien donné.

Merci!