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 2019-04-15 14:00:35

LucaC
Member
Registered: 2012-04-10
Posts: 44

Slow login page

Hello,
recently updated to 9.4.2. After update, loading the front page takes 60 seconds.
Only front page has this issues, other pages load quickly..

Looking at mysql I found 2 queries that takes 30 seconds each to be executed.

SELECT DISTINCT `glpi_tickets`.`id`
                FROM `glpi_tickets`
                LEFT JOIN `glpi_tickets_users`
                     ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id`)               
			   LEFT JOIN `glpi_groups_tickets`
                     ON (`glpi_tickets`.`id` = `glpi_groups_tickets`.`tickets_id`) LEFT JOIN `glpi_itilsolutions`
                           ON (`glpi_itilsolutions`.`id` = (SELECT `last_solution`.`id`
                                    FROM `glpi_itilsolutions` as `last_solution`
                                    WHERE `last_solution`.`items_id` = `glpi_tickets`.`id`
                                    AND `last_solution`.`itemtype` = 'Ticket'
                                    ORDER BY `last_solution`.`id` DESC
                                    LIMIT 1))
                        WHERE  `glpi_tickets`.`is_deleted` = 0
                             AND ( (`glpi_tickets_users`.`users_id` = '455'
                            AND `glpi_tickets_users`.`type` = '2'))
                             AND `glpi_tickets`.`status` <> '6'
                             AND `glpi_itilsolutions`.`status` = '4'  ORDER BY `glpi_tickets`.`date_mod` DESC;
SELECT DISTINCT `glpi_tickets`.`id`
                FROM `glpi_tickets`
                LEFT JOIN `glpi_tickets_users`
                     ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id`)
                LEFT JOIN `glpi_groups_tickets`
                     ON (`glpi_tickets`.`id` = `glpi_groups_tickets`.`tickets_id`) LEFT JOIN `glpi_itilsolutions`
                           ON (`glpi_itilsolutions`.`id` = (SELECT `last_solution`.`id`
                                    FROM `glpi_itilsolutions` as `last_solution`
                                    WHERE `last_solution`.`items_id` = `glpi_tickets`.`id`
                                    AND `last_solution`.`itemtype` = 'Ticket'
                                    ORDER BY `last_solution`.`id` DESC
                                    LIMIT 1))
                        WHERE  `glpi_tickets`.`is_deleted` = 0
                             AND ( (`glpi_tickets_users`.`users_id` = '455'
                            AND `glpi_tickets_users`.`type` = '2'))
                             AND `glpi_tickets`.`status` <> '6'
                             AND `glpi_itilsolutions`.`status` = '4'  ORDER BY `glpi_tickets`.`date_mod` DESC LIMIT 0,30;

If I login with my user, I have the issue. If I login with built-in glpi admin user, page loads quickly (probably because that user has less than 10 tickets assigned...)
Don't know if this is related to my db (long time GLPI  install, since 2012) that need to be updated/rebuild.

Anyone out there experienced same behaviour?
Bye.

Offline

#2 2019-04-15 14:05:31

LucaC
Member
Registered: 2012-04-10
Posts: 44

Re: Slow login page

Just an update.
If I try to exec the query in mysql console, i receive this error

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'glpi.glpi_tickets.date_mod' which is not in SELECT list; this is incompatible with DISTINCT

I've to remove the ORDER BY glpi_tickets.date_mod to have query running..

Offline

#3 2019-04-15 14:50:35

LucaC
Member
Registered: 2012-04-10
Posts: 44

Re: Slow login page

Just a little update on the last message (ERROR 3065). Solved removing ONLY_FULL_GROUP_BY in sql_mode global option.
I found that GLPI "empties" sql_mode before connecting (inc/dbmysql.class.php  - function connect) so it won't receive this error.

So, this is not really an issue in GLPI nor in mysql installation.
Keep on searching on how to speed up the 2 queries above.

Offline

#4 2019-04-15 15:04:57

LucaC
Member
Registered: 2012-04-10
Posts: 44

Re: Slow login page

I found the offending issue within the inc/ticket.class.php under the "solution.rejected".
Then I commented the followinmg line in file inc/central.class.php

     if ($showticket) {

         if (Ticket::isAllowedStatus(Ticket::SOLVED, Ticket::CLOSED)) {
            Ticket::showCentralList(0, "toapprove", false);
         }

         Ticket::showCentralList(0, "survey", false);

         Ticket::showCentralList(0, "validation.rejected", false);
//         Ticket::showCentralList(0, "solution.rejected", false);
         Ticket::showCentralList(0, "requestbyself", false);
         Ticket::showCentralList(0, "observed", false);

         Ticket::showCentralList(0, "process", false);
         Ticket::showCentralList(0, "waiting", false);

         TicketTask::showCentralList(0, "todo", false);

      }

In my config solution are by default "accepted" and tickets go directly to CLOSE state, so there is no need to display such tickets.
Still not sure it's a problem with my config.. I'll open a bug in GIT with these notes.

Offline

#5 2019-04-29 09:04:59

Bruce79
Member
Registered: 2012-05-23
Posts: 34

Re: Slow login page

Hi, I just read this post ... (searching for a solution for another problem -> also a performance issue)

Did you do an explain plan for these 2 queries?

I've changed the users_id for the first query, and then it takes 6 seconds on my database:


explain plan:
id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1    PRIMARY    glpi_tickets    NULL    range    PRIMARY,date,closedate,status,priority,request_type,date_mod,entities_id,users_id_recipient,solvedate,urgency,impact,global_validation,users_id_lastupdater,type,itilcategories_id,is_deleted,name,locations_id,date_creation,time_to_own,internal_time_to_resolve,internal_time_to_own,time_to_resolve,ola_waiting_duration,olas_id_tto,olas_id_ttr,olalevels_id_ttr,slas_id_tto,slas_id_ttr,slalevels_id_ttr    status    4    NULL    2360    50.00    Using index condition; Using where; Using temporary; Using filesort
1    PRIMARY    glpi_itilsolutions            NULL    eq_ref    PRIMARY,status    PRIMARY    4    func    1    5.00    Using where; Distinct
1    PRIMARY    glpi_tickets_users    NULL    ref            unicity,user    unicity    12    glpi.glpi_tickets.id,const,const    1    100.00    Using index; Distinct
1    PRIMARY    glpi_groups_tickets    NULL    ref            unicity            unicity    4    glpi.glpi_tickets.id    1    100.00    Using index; Distinct
2    DEPENDENT SUBQUERY            last_solution    NULL    index    itemtype,item_id,item    itemtype    302    NULL    4    5.00    Using where; Backward index scan


for a explain plan just.
explain the-sql-statement

Offline

Board footer

Powered by FluxBB