Performance problems, long/complex SQL queries

hibawed347 · 2023-07-17 16:34:36

Greetings!
Please help with GLPI optimization.
I observe severe performance problems in one place:
When loading a tickets page with "closed" status with any number of lines, it takes about 35 seconds.
When switching to the second page it loaded 35 seconds again.
FluxBB bbcode test

No complex filters are used.
GLPI is hosted on the vitual machine - 4 cores and 8GB RAM.
GLPI only uses the following plugins: Mail Analyzer and GLPI Inventory.
The GLPI has been installed for a long time ago and gradually updated to the latest version. Now i use GLPI 10.0.7.

Perhaps the problem is that there are about 50k closed tickets in total, but why does this affect the query speed at all if I only need to get 5 rows?

Here is slow SQL-Query from Debug mode:

SELECT DISTINCT
   `glpi_tickets`.`id` AS id,
   'ivanov' AS currentuser,
   `glpi_tickets`.`id` AS `ITEM_Ticket_2`,
   `glpi_tickets`.`name` AS `ITEM_Ticket_1`,
   `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`,
   `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`,
   `glpi_tickets`.`content` AS `ITEM_Ticket_1_content`,
   `glpi_tickets`.`status` AS `ITEM_Ticket_1_status`,
   `glpi_entities`.`completename` AS `ITEM_Ticket_80`,
   `glpi_tickets`.`status` AS `ITEM_Ticket_12`,
   GROUP_CONCAT(DISTINCT `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_4`,
   GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id`, ' ', `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`alternative_email`) SEPARATOR '$$##$$') AS `ITEM_Ticket_4_2`,
   `glpi_tickets`.`date` AS `ITEM_Ticket_15`,
   `glpi_tickets`.`content` AS `ITEM_Ticket_21`,
   GROUP_CONCAT(DISTINCT `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_5`,
   GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id`, ' ', `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`alternative_email`) SEPARATOR '$$##$$') AS `ITEM_Ticket_5_2`,
   `glpi_tickets`.`date_mod` AS `ITEM_Ticket_19`,
   `glpi_users_users_id_lastupdater`.`name` AS `ITEM_Ticket_64`,
   `glpi_users_users_id_lastupdater`.`realname` AS `ITEM_Ticket_64_realname`,
   `glpi_users_users_id_lastupdater`.`id` AS `ITEM_Ticket_64_id`,
   `glpi_users_users_id_lastupdater`.`firstname` AS `ITEM_Ticket_64_firstname`,
   GROUP_CONCAT( DISTINCT CONCAT( IFNULL(`glpi_itilfollowups`.`content`, '__NULL__'), '$#$', `glpi_itilfollowups`.`id` ) 
ORDER BY
   `glpi_itilfollowups`.`date` DESC SEPARATOR '$$##$$' ) AS `ITEM_Ticket_25`,
   GROUP_CONCAT( DISTINCT CONCAT( IFNULL(`glpi_tickettasks`.`content`, '__NULL__'), '$#$', `glpi_tickettasks`.`id` ) 
ORDER BY
   `glpi_tickettasks`.`date` DESC SEPARATOR '$$##$$' ) AS `ITEM_Ticket_26`,
   GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_itilsolutions`.`content`, '__NULL__'), '$#$', `glpi_itilsolutions`.`id`) 
ORDER BY
   `glpi_itilsolutions`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_24`,
   `glpi_tickets`.`priority` AS `ITEM_Ticket_3`,
   `glpi_itilcategories`.`completename` AS `ITEM_Ticket_7`,
   `glpi_tickets`.`time_to_resolve` AS `ITEM_Ticket_18`,
   `glpi_tickets`.`solvedate` AS `ITEM_Ticket_18_solvedate`,
   `glpi_tickets`.`status` AS `ITEM_Ticket_18_status`,
   IF(`glpi_tickets`.`time_to_resolve` IS NOT NULL 
   AND `glpi_tickets`.`status` <> 4 
   AND 
   (
      `glpi_tickets`.`solvedate` > `glpi_tickets`.`time_to_resolve` 
      OR 
      (
         `glpi_tickets`.`solvedate` IS NULL 
         AND `glpi_tickets`.`time_to_resolve` < NOW()
      )
   )
, 1, 0) AS `ITEM_Ticket_82`,
   `glpi_tickets`.`actiontime` AS `ITEM_Ticket_45` 
FROM
   `glpi_tickets`
   LEFT JOIN
      `glpi_entities` 
      ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` ) 
   LEFT JOIN
      `glpi_tickets_users` AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24` 
      ON (`glpi_tickets`.`id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id` 
      AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' )
   LEFT JOIN
      `glpi_users` AS `glpi_users_af1042e23ce6565cfe58c6db91f84692` 
      ON (`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id` = `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` ) 
   LEFT JOIN
      `glpi_tickets_users` AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5` 
      ON (`glpi_tickets`.`id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id` 
      AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )
   LEFT JOIN
      `glpi_users` AS `glpi_users_b1b92f6be5e70531688d870931e94a65` 
      ON (`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id` = `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` )
   LEFT JOIN
      `glpi_users` AS `glpi_users_users_id_lastupdater` 
      ON (`glpi_tickets`.`users_id_lastupdater` = `glpi_users_users_id_lastupdater`.`id` ) 
   LEFT JOIN
      `glpi_itilfollowups` 
      ON (`glpi_tickets`.`id` = `glpi_itilfollowups`.`items_id` 
      AND `glpi_itilfollowups`.`itemtype` = 'Ticket' ) 
   LEFT JOIN
      `glpi_tickettasks` 
      ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id` ) 
   LEFT JOIN
      `glpi_itilsolutions` 
      ON (`glpi_tickets`.`id` = `glpi_itilsolutions`.`items_id` 
      AND `glpi_itilsolutions`.`itemtype` = 'Ticket' ) 
   LEFT JOIN
      `glpi_itilcategories` 
      ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` ) 
WHERE
   `glpi_tickets`.`is_deleted` = 0 
   AND 
   (
      `glpi_tickets`.`status` IN 
      (
         '6'
      )
   )
GROUP BY
   `glpi_tickets`.`id` 
ORDER BY
   `ITEM_Ticket_19` DESC

Debug info:
Execution time
37.879 seconds
Memory usage
25.88 Mb
SQL queries count
7610
SQL queries duration
33.946 seconds

How normal is it that so many join are used? It seems to me that for some reason GLPI reads information on all 50,000 tickets (although I ask for only 5 lines).
I think that the request is too heavy, but I have not yet found a way to influence this. Does your GLPI building same big SQL Query to receive tickets?

If this behavior is normal, I will dig in the direction of optimizing the database.

Last edited by hibawed347 (2023-07-17 16:40:31)

cconard96 · 2023-07-18 00:28:26

I confirm the behavior. Oddly, it seems like it has been this way for a while. It turns out that the start (page) and limit seems to only be applied after all the data is returned if there are any search filters.
For example, the default search for tickets is Status Is Not Solved, so the "no_search" flag is false and all results are returned. The default search for users is Items Seen (blank) which isn't treated as a real criteria so "no_search" is true and the limit is placed in the SQL request.

This may have been done to work around some kind of issue years ago. I'm not sure if it is still required or not. It will require a lot more investigation and testing.
Still, taking 30 seconds for that many items seems like a lot of time.
The number of joins is to be expected given that some of the columns have data in other tables besides the main tickets table.

cconard96 · 2023-07-18 00:44:47

Testing with the users search, it is taking approximately 260ms for the query when using the default empty criteria (fetching only the current page) and around the same amount of time when searching Items Seen contains "test" (fetching everything). I have 16,000 users in my database. I'm not really sure there is much performance to be gained here or if that is the cause of your issue.

If you run that query directly in your database, is it just as slow? Then, try manually adding the "LIMIT 0, 5" to the end of query. Has it noticeably improved the performance?

hibawed347 · 2023-07-18 10:01:52

Thank you for your reply.
I tested a direct SQL query in DB.

I have tried the following:
Direct query without limits

Result:
32.355 seconds

Direct query with limit by 5 lines

Result:
24.618 seconds

Direct query with limit by 20 lines
Direct query with limit by 20 lines

Result:
27.808 seconds

Seems in the case of a limit of 5 lines, this gives a performance increase of 7.7 seconds.

cconard96 · 2023-07-18 12:35:31

Can you try running the following command from the root of your glpi folder to see if there is are any missing indexes?
bin/console database:check_schema_integrity

hibawed347 · 2023-07-18 13:08:20

Command output say, that the two tables are different, but I don't see this as a problem. As far as I understand, they have nothing to do with tickets tables.

bin/console database:check_schema_integrity
Схема таблицы отличается в таблице "glpi_knowbaseitems".
--- Ожидаемая схема БД
+++ Текущая схема БД
@@ @@
   FULLTEXT KEY `answer` (`answer`),
   FULLTEXT KEY `fulltext` (`name`,`answer`),
   FULLTEXT KEY `name` (`name`),
-  KEY `begin_date` (`begin_date`),
   KEY `date_creation` (`date_creation`),
   KEY `date_mod` (`date_mod`),
-  KEY `end_date` (`end_date`),
   KEY `is_faq` (`is_faq`),
   KEY `users_id` (`users_id`)
 )
Схема таблицы отличается в таблице "glpi_users".
--- Ожидаемая схема БД
+++ Текущая схема БД
@@ @@
   UNIQUE KEY `unicityloginauth` (`name`,`authtype`,`auths_id`),
   KEY `authitem` (`authtype`,`auths_id`),
   KEY `auths_id` (`auths_id`),
-  KEY `begin_date` (`begin_date`),
   KEY `date_creation` (`date_creation`),
   KEY `date_mod` (`date_mod`),
   KEY `default_requesttypes_id` (`default_requesttypes_id`),
-  KEY `end_date` (`end_date`),
   KEY `entities_id` (`entities_id`),
   KEY `firstname` (`firstname`),
   KEY `groups_id` (`groups_id`),

cconard96 · 2023-07-18 16:08:44

No, these missing keys shouldn't affect the performance in this case.

Forum GLPI-Project

Announcement

#1 2023-07-17 16:34:36

Performance problems, long/complex SQL queries

#2 2023-07-18 00:28:26

Re: Performance problems, long/complex SQL queries

#3 2023-07-18 00:44:47

Re: Performance problems, long/complex SQL queries

#4 2023-07-18 10:01:52

Re: Performance problems, long/complex SQL queries

#5 2023-07-18 12:35:31

Re: Performance problems, long/complex SQL queries

#6 2023-07-18 13:08:20

Re: Performance problems, long/complex SQL queries

#7 2023-07-18 16:08:44

Re: Performance problems, long/complex SQL queries

Board footer