Mysqld failure due to concurent DELETE queries while not linked computers inventory

[valentindragan]

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

Problem:
After migrating from GLPI version 9.2.3 to 10.0.7 and installing the GLPI agent on company computers, changing from OCS Fusion Inventory to built in inventory after a two years automatic inventory break, we encountered a significant bottleneck during peak hours, particularly around 8:30 in the morning. This bottleneck stems from the high volume of DELETE requests overwhelming the MySQL service. Upon investigation, it was found that these DELETE requests are primarily targeting GLPI agents and associated logs, and all the agents in these delete queries are installed on computers not able to link to its GLPI entry due mismatched UUID.

Root Cause:
The root cause of this bottleneck appears to be the presence of hundreds of computers within the GLPI database whose UUIDs do not match those reported by the GLPI agent. This discrepancy arises from manually inserting computers into the database prior to agent installation.
The application seem to generate tens of concurrent delete queries for the same glpi_agent in the glpi_agents log and the glpi_logs table, which leads to a queue of hundreds of DELETE queries until MySQL service crushes or needs a restart.

Impact:

High server load during inventory peak hours, leading to performance degradation and MySQL service failure.

Proposed Solution:

On our side, the problem can be solved by matching all computers UUID.
On GLPI side, the hundreds of DELETE queries for agents that are not able to link to their respective GLPI computer entry is surely not an intended behavior, which I think should be fixed.

Additional Information:

GLPI Version: 10.0.7
MySQL Version: 10.3.35-MariaDB
GLPI Agent version: 1.5
Frequency of Issue: Consistently observed during peak hours, each week day.

Note:
This issue description is aimed at addressing the bottleneck experienced during peak hours and the underlying UUID discrepancy problem.

Relevant log output

no relevant log output

Page URL

No response

Steps To reproduce

Steps to Reproduce:

• create x computers in GLPI, but insert a wrong UUID.
• install GLPI agent on the computers that have already been added, make sure they all sync at the same time.
• the computers will not be created automatically because the name already exists, and they will not be updated automatically because the UUID is not the same, which is expected.
  Observe high volume of DELETE requests targeting GLPI agents and associated logs.
  Monitor server performance metrics to confirm bottleneck.

Your GLPI setup information

Information about system installation and configuration
GLPI 10.0.7-git-master-e39a724 ( => /var/www/html/glpi/backend)
Installation mode: GIT
Current language:en_GB

Server

Operating system: Linux HOSTNAME.nettt.local 4.18.0-477.21.1.el8_8.x86_64 #1 SMP Thu Jul 20 08:38:27 EDT 2023 x86_64
PHP 7.4.33 fpm-fcgi (Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, bz2, calendar, cgi-fcgi, ctype, curl, date, dom,
exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, intl, json, ldap, libxml, mbstring, mysqli, mysqlnd, openssl, pcre,
pdo_mysql, pdo_sqlite, posix, session, shmop, sockets, sqlite3, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader,
xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="600" memory_limit="512M" post_max_size="64M" safe_mode="" session.save_handler="files"
upload_max_filesize="10M"
Software: Apache/2.4.37 (Red Hat Enterprise Linux) OpenSSL/1.1.1k mod_auth_gssapi/1.6.1 ()
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36
Server Software: MariaDB Server
Server Version: 10.3.35-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

Anything else?

No response

[trasher]

First try if you reproduce with latest GLPI release.

[trasher]

No feedback, I close