Forum GLPI-Project

jaferrer

Member

Registered: 2012-07-23

Posts: 18

Can't display user list or takes too long and timeout

Hi Guys;

    I've been a long time GLPI users, but now I'm facing a problem that I will need help with. I have a GLPI with around 20 local users, 6000+ tickets and 1200+ computers. But now when I try to see the user list, GLPI takes forever to display it until apache or php get me a timeout error. Recently I updated to the latest version (0.84) but the problem persists. I think this could be an issue with MySQL performance, but everything works normal except for users list. I have traced down this problem to the following query:

SELECT 'glpi' AS currentuser,
                         GROUP_CONCAT(DISTINCT CONCAT(`glpi_users`.`name`, '$$' ,
                                                        `glpi_users`.`id`) SEPARATOR '$$$$')
                                       AS ITEM_0,
                            GROUP_CONCAT(`glpi_entities_69a91398655a63f4c04eb186c99faef6`.`completename` SEPARATOR '$$$$')
                                    AS ITEM_1,
                        GROUP_CONCAT(`glpi_profiles_users`.`profiles_id` SEPARATOR '$$$$')
                                    AS ITEM_1_2,
                        GROUP_CONCAT(`glpi_profiles_users`.`is_recursive` SEPARATOR '$$$$')
                                    AS ITEM_1_3,
                        `glpi_users`.`realname` AS ITEM_2,
               GROUP_CONCAT(DISTINCT CONCAT(`glpi_useremails`.`email`,'$$',`glpi_useremails`.`id`) SEPARATOR '$$$$')
                              AS ITEM_3,
                  `glpi_users`.`phone` AS ITEM_4,
              `glpi_locations`.`completename` AS ITEM_5,
              `glpi_users`.`is_active` AS ITEM_6,
               COUNT(DISTINCT `glpi_tickets_b159409825aa5de567851f6f7858e693`.`id`) AS ITEM_8,
                      COUNT(DISTINCT `glpi_tickets_8cd91c19de6a496705aac91d5c5a8c72`.`id`) AS ITEM_9,
                     `glpi_users`.`id` AS id  FROM `glpi_users` LEFT JOIN `glpi_profiles_users` 
                                             ON (`glpi_users`.`id` = `glpi_profiles_users`.`users_id`
                                                 )LEFT JOIN `glpi_entities`  AS glpi_entities_69a91398655a63f4c04eb186c99faef6
                                          ON (`glpi_profiles_users`.`entities_id` = `glpi_entities_69a91398655a63f4c04eb186c99faef6`.`id`
                                              ) LEFT JOIN `glpi_useremails` 
                                             ON (`glpi_users`.`id` = `glpi_useremails`.`users_id`
                                                 )LEFT JOIN `glpi_locations` 
                                          ON (`glpi_users`.`locations_id` = `glpi_locations`.`id`
                                              ) LEFT JOIN `glpi_tickets`  AS glpi_tickets_b159409825aa5de567851f6f7858e693
                                             ON (`glpi_users`.`id` = `glpi_tickets_b159409825aa5de567851f6f7858e693`.`users_id_recipient`
                                                 ) LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_72195e5ab221184a948346f8ce2ee69e
                                             ON (`glpi_users`.`id` = `glpi_tickets_users_72195e5ab221184a948346f8ce2ee69e`.`users_id`
                                                 AND `glpi_tickets_users_72195e5ab221184a948346f8ce2ee69e`.`type`
                                                                        = 1 )LEFT JOIN `glpi_tickets`  AS glpi_tickets_8cd91c19de6a496705aac91d5c5a8c72
                                          ON (`glpi_tickets_users_72195e5ab221184a948346f8ce2ee69e`.`tickets_id` = `glpi_tickets_8cd91c19de6a496705aac91d5c5a8c72`.`id`
                                              ) WHERE  ( `glpi_profiles_users`.`entities_id` IN ('0', '1')  )  AND `glpi_users`.`is_deleted` = '0'  GROUP BY `glpi_users`.`id` ORDER BY `glpi_users`.`name` ASC LIMIT 0, 20

I've done some tweaking to MySQL and lower the execution time from 450 Sec. to 40 Sec. But this is still too much time for displaying users.

Also, tracing the MySQL when querying for users, its displays several lines like:

SELECT *
                   FROM `glpi_profiles`
                   WHERE `id` = '2'

I've temporarily solved the problem by removing the total tickets solved/requested per user columns in the user list.

So can anyone give me a hint of what can I tweak to get a better performance?

Thanks in advance.