Performance problems with default search on tickets

[Dmtrkozl]

Code of Conduct

• I agree to follow this project's Code of Conduct

Is there an existing issue for this?

• I have searched the existing issues

Version

10.0.7

Bug description

Previously, this problem was discussed on the community form:
https://forum.glpi-project.org/viewtopic.php?pid=503280

I observe severe performance problems in one place:
I have a lot of closed tickets, about 40K. When loading a tickets page with "closed" status with any number of rows, it takes about 30 seconds. When switching to the second page it loaded 30 seconds again.

The standard search filter is used. I was told on a community forum that a limit should be used at the end of the SQL query, but that doesn't happen.

I did some tests:
Got a slow SQL expression from Debug mode and send direct SQL query in DB. In the case where I explicitly set the limit by 5 rows at the end of the query, the data fetch is 7.7 seconds faster, which is a significant performance improvement.

Get tickets without LIMIT:

Result:
32.355 seconds

Get tickets with LIMIT 0, 5:

Result:
24.618 seconds

Please, add a limit to the SQL query when receiving tickets with default search filter.

Relevant log output

SELECT DISTINCT
   `glpi_tickets`.`id` AS id,
   'kozlov' 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

Page URL

https://forum.glpi-project.org/viewtopic.php?pid=503280

Steps To reproduce

1. Have a large number of tickets in your database (about 40K)
2. Make a default search for tickets. Try to set different "rows / page". Make sure it doesn't affect performance at all.

Your GLPI setup information

GLPI 10.0.7 ( => /var/www/html)
Installation mode: TARBALL
Current language:en_GB

Operating system: Linux glpi.sample.com 3.10.0-1160.76.1.el7.x86_64 #1 SMP Wed Aug 10 16:21:17 UTC 2022 x86_64
PHP 8.0.23 apache2handler (Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, apache2handler, apcu, bcmath, bz2,
calendar, ctype, curl, date, dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, imap, intl, json, ldap, libxml,
mbstring, mcrypt, mysqli, mysqlnd, openssl, pcre, pdo_mysql, pdo_pgsql, pdo_sqlite, pgsql, posix, session, shmop, sockets,
sodium, sqlite3, standard, sysvmsg, sysvsem, sysvshm, tidy, tokenizer, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="60" memory_limit="1000M" post_max_size="8M" safe_mode="" session.save_handler="files"
upload_max_filesize="32M"
Software: Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips mod_auth_kerb/5.4 PHP/8.0.23 ()
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36
Server Software: MariaDB Server
Server Version: 10.8.4-MariaDB-log
Server SQL Mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Parameters: glpi@localhost/glpi
Host info: Localhost via UNIX socket

PHP version (8.0.23) is supported.PHP version (8.0.23) is supported.
Sessions configuration is OK.Sessions configuration is OK.
Allocated memory is sufficient.Allocated memory is sufficient.
mysqli extension is installed.mysqli extension is installed.
Following extensions are installed: dom, fileinfo, json, simplexml.Following extensions are installed: dom, fileinfo, json, simplexml.
curl extension is installed.curl extension is installed.
gd extension is installed.gd extension is installed.
intl extension is installed.intl extension is installed.
libxml extension is installed.libxml extension is installed.
zlib extension is installed.zlib extension is installed.
The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.
Database engine version (10.8.4) is supported.Database engine version (10.8.4) is supported.
No files from previous GLPI version detected.No files from previous GLPI version detected.
The log file has been created successfully.The log file has been created successfully.
Write access to /var/www/html/files/_cache has been validated. Write access to /var/www/html/config has been validated. Write access to /var/www/html/files/_cron has been validated. Write access to /var/www/html/files has been validated. Write access to /var/www/html/files/_dumps has been validated. Write access to /var/www/html/files/_graphs has been validated. Write access to /var/www/html/files/_lock has been validated. Write access to /var/www/html/files/_pictures has been validated. Write access to /var/www/html/files/_plugins has been validated. Write access to /var/www/html/files/_rss has been validated. Write access to /var/www/html/files/_sessions has been validated. Write access to /var/www/html/files/_tmp has been validated. Write access to /var/www/html/files/_uploads has been validated.Write access to /var/www/html/files/_cache has been validated.
Write access to /var/www/html/config has been validated.
Write access to /var/www/html/files/_cron has been validated.
Write access to /var/www/html/files has been validated.
Write access to /var/www/html/files/_dumps has been validated.
Write access to /var/www/html/files/_graphs has been validated.
Write access to /var/www/html/files/_lock has been validated.
Write access to /var/www/html/files/_pictures has been validated.
Write access to /var/www/html/files/_plugins has been validated.
Write access to /var/www/html/files/_rss has been validated.
Write access to /var/www/html/files/_sessions has been validated.
Write access to /var/www/html/files/_tmp has been validated.
Write access to /var/www/html/files/_uploads has been validated.
For security reasons, SELinux mode should be Enforcing.For security reasons, SELinux mode should be Enforcing.
OS and PHP are relying on 64 bits integers.OS and PHP are relying on 64 bits integers.
exif extension is installed.exif extension is installed.
ldap extension is installed.ldap extension is installed.
openssl extension is installed.openssl extension is installed.
Following extensions are installed: bz2, Phar, zip.Following extensions are installed: bz2, Phar, zip.
Zend OPcache extension is installed.Zend OPcache extension is installed.
Following extensions are installed: ctype, iconv, mbstring, sodium.Following extensions are installed: ctype, iconv, mbstring, sodium.
Write access to /var/www/html/marketplace has been validated.Write access to /var/www/html/marketplace has been validated.
Timezones seems loaded in database.Timezones seems loaded in database.

Anything else?

No response

[cedric-anne]

Hi,

An attempt to add LIMIT to queries was done in #9495. It requires to do a second query to get the total count of items to be able to build the pagination. We identified some specific cases on which result were pretty bad, so we did not validate this proposal.

According to the query, you are showing tickets description, followups description, tasks descriptions and solutions descriptions. As all this data is fetched by the mysqli driver for all rows that matches the query, it can explain the poor performances you have. You should try to hide these columns, it will improve performances.

In summary, it is a known performance issue on which we do not have a fix to propose yet. It does not mean that we will never fix it, but for the moment, the only solution is to remove some columns to reduce the complexity of the query and/or the amount of data to fetch.