You are not logged in.
Pages: 1
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
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
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
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
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
Pages: 1