Discussion :

[Fiilou]

Bonjour,

je situe un peu le contexte.

J'utilise GLPI pour faire l'inventaire de mon parc informatique.
J'ai créé un plugin qui crée une table et qui stocke des informations sur les postes.

J'ai une table "Computers" avec une clef primaire sur un "id".
J'ai la table de mon plugin "MONPLUGIN" avec une clef primaire sur le "num_serie" du poste.

Quand je fais un bête SELECT pour afficher les informations de "MONPLUGIN" associées aux postes, la requête met 60 secondes à s'exécuter.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT COMPUTERS.name AS 'Nom', 
MONPLUGIN.societe AS 'Société', 
FROM glpi_computers AS COMPUTERS
LEFT JOIN glpi_plugin_champiws AS MONPLUGIN ON (COMPUTERS.serial = MONPLUGIN.num_serie )
WHERE COMPUTERS.is_deleted = '0' 
AND COMPUTERS.is_template = '0'
GROUP BY COMPUTERS.name

J'aimerais améliorer les performances de cette requête, mais je ne sais pas comment faire.

Merci par avance pour votre aide.

[StringBuilder]

Indexer ces colonnes ?

[Fiilou]

La requête finale est une requête créée à la volée pour afficher plusieurs champs de COMPUTERS et de MONPLUGIN.

Si je ne me trompe pas, l'index est créé pour une requête donnée. Si on modifie les champs du SELECT, l'index n'est plus utilisé ?

[StringBuilder]

Non, l'index est créé dans la base, et porte sur une collection de colonnes dans une table.
Il peut y avoir plusieurs index par table.

Ils permettent d'accéder plus rapidement aux données en filtrant dans un arbre plutôt que séquentiellement.
En contre-partie, ils alourdissent légèrement toutes les modifications de données sur la table (puisqu'on doit mettre à jour l'index).

Commence par poser un index sur la table la plus grosse (plus une table est petite, moins elle tire partie d'un index).

Un truc genre :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
create index idx_computer_serial on computers (serial);

is_template et is_deleted n'ont à priori pas besoin d'être indexés (un index est efficace quand l'arbre a beaucoup de feuilles... sur un booléen il n'y en a que deux, donc ça sert à rien).

[Fiilou]

Bonjour,

je n'ai pas répondu hier soir car je voulais tester de nouveau ce matin.
Hier soir je pensais avoir quelques secondes (passé de 77 à 73 sec), mais avec mes tests de ce matin je me rends compte qu'en fait les performances sont identiques avec ou sans index.

je viens de regarder les index sur la table avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SHOW INDEX FROM COMPUTERS

is_template et is_deleted ont déjà tous les deux un index.


P.S. : le temps d'exécution est de ~60 secondes en prod. Sur mon environnement de test, les performances sont moindres, ce qui explique les écarts de temps entre le premier message et celui-ci.

[Fiilou]

Je viens de regarder si les tables étaient fragmentées.
Les 2 tables en question ne l'étaient pas, mais j'ai défragmenté les tables de la BDD qui l'étaient.

Aucune amélioration.

[StringBuilder]

Y'a combien de milliards de lignes ?

PS : à mon sens, tes deux flag, tu peux les shooter de tes index. Ca ne gagne rien ou presque et risque d'induire MySQL en erreur (choix du mauvais index).

Tu as bien indexé la colonne serial ?

[Fiilou]

COMPUTERS : 6263 lignes
MONPLUGIN : 7411 lignes

Le problème c'est que ce n'est qu'avec cette table (ou une ou deux autres) que la requête est lente...

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT COMPUTERS.name AS 'Nom', MANUFACTURERS.name AS 'Fabricant', 
COMPUTERS.serial As 'Numéro de série', COMPUTERTYPES.name AS 'Type', 
COMPUTERMODELS.name AS 'Modèle', OPERATINGSYSTEMS.name AS "Système d'exploitation",
COMPUTERS.contact AS 'Usager', CONCAT(USERS.realname, ' ', USERS.firstname) AS 'Utilisateur',
GROUP_CONCAT(DISTINCT NETWORKS.ip SEPARATOR "$$$$") AS 'IP', DOMAINS.name AS 'Domaine'
FROM glpi_computers AS COMPUTERS
LEFT JOIN glpi_manufacturers AS MANUFACTURERS ON (COMPUTERS.manufacturers_id = MANUFACTURERS.id )
LEFT JOIN glpi_computertypes AS COMPUTERTYPES ON (COMPUTERS.computertypes_id = COMPUTERTYPES.id )
LEFT JOIN glpi_computermodels AS COMPUTERMODELS ON (COMPUTERS.computermodels_id = COMPUTERMODELS.id )
LEFT JOIN glpi_operatingsystems AS OPERATINGSYSTEMS ON (COMPUTERS.operatingsystems_id = OPERATINGSYSTEMS.id )
LEFT JOIN glpi_locations ON (COMPUTERS.locations_id = glpi_locations.id )
LEFT JOIN glpi_users AS USERS ON (COMPUTERS.users_id = USERS.id )
LEFT JOIN glpi_plugin_fusinvinventory_libserialization ON (COMPUTERS.id = glpi_plugin_fusinvinventory_libserialization.computers_id)
LEFT JOIN glpi_computers_devicenetworkcards ON (COMPUTERS.id = glpi_computers_devicenetworkcards.computers_id )
LEFT JOIN glpi_networkports AS NETWORKS ON (COMPUTERS.id = NETWORKS.items_id AND NETWORKS.itemtype = 'Computer' )
LEFT JOIN glpi_domains AS DOMAINS ON (COMPUTERS.domains_id = DOMAINS.id )
WHERE COMPUTERS.is_deleted = '0' 
AND COMPUTERS.is_template = '0'
GROUP BY COMPUTERS.name

Cette requête prend 0.0012 sec.

[StringBuilder]

Essaie d'indexer aussi "MONPLUGIN.num_serie".

Sinon, la requête en elle-même ne pose pas de problème.

Plus de 60 secondes pour une telle volumétrie me laisse perplexe. Même Access s'en sortirait mieux. Y'a beaucoup de trafic sur la base ?

[Fiilou]

Euh non pas de trafic, c'est une machine de test dont je suis le seul à "travailler" dessus.

En voulant l'indexer, j'ai regardé les index sur la table MONPLUGIN.
J'ai donc 2 index : (je l'avais créé quand je faisais des tests avant de poster sur ce forum)

MONPLUGIN 0 PRIMARY 1 num_serie A 7411 NULL NULL BTREE
MONPLUGIN 1 idx_monplugin_numserie 1 num_serie A 7411 NULL NULL BTREE

Je l'ai supprimé et recréé pour être sûr de lancer la bonne commande pour créer l'index.
Aucune amélioration non plus.

Quand je lance la 1ère requête, 1 VCPU est utilisé à 100% pendant toute la durée de l'exécution.

[Fiilou]

Quand je fais un explain sur la 1ère requête, j'obtiens

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | COMPUTERS | ALL | is_template,is_deleted | NULL | NULL | NULL | 6263 | Using where; Using temporary; Using filesort
1 | SIMPLE | MONPLUGIN | ALL | NULL | NULL | NULL | NULL | 7411 |


J'ai le souvenir de quand j'avais demandé aux systèmes (ceux qui s'occupent de la prod) de regarder pourquoi cette requête était lente, dans mysql il arrivait à afficher le nombre d'enregistrement parcouru et, si je ne dis pas de bêtises, était ~ "250 000 000", ce qui paraît fou...
Par contre je ne sais plus la commande utilisée.

[CinePhil]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT COMPUTERS.name AS 'Nom', MANUFACTURERS.name AS 'Fabricant', 
COMPUTERS.serial As 'Numéro de série', COMPUTERTYPES.name AS 'Type', 
COMPUTERMODELS.name AS 'Modèle', OPERATINGSYSTEMS.name AS "Système d'exploitation",
COMPUTERS.contact AS 'Usager', CONCAT(USERS.realname, ' ', USERS.firstname) AS 'Utilisateur',
GROUP_CONCAT(DISTINCT NETWORKS.ip SEPARATOR "$$$$") AS 'IP', DOMAINS.name AS 'Domaine'
-- ...
GROUP BY COMPUTERS.name

Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

Le GROUP_CONCAT est-il indispensable ? Vous obtenez une colonne multi-valuée, ce qui n'est pas vraiment top.
C'est probablement ça qui ralentit beaucoup. Pourtant...

COMPUTERS : 6263 lignes
MONPLUGIN : 7411 lignes

Vos tables sont toutes petites !

Les parenthèses autour des conditions de jointure sont inutiles.

Un numéro de série comme clé primaire, ce n'est vraiment pas top ! Une clé primaire sur une colonne VARCHAR, c'est une mauvaise idée !

[StringBuilder]

CinePhill > La requête que tu as quoté et critiqué est une requête qui fonctionne instantanément.

Celle qui déconne est "correctement" écrite, au détail près des parenthèses dans le ON.

Ce que je ne comprends pas, c'est qu'une requête aussi triviale soit aussi lente sur un volume de données aussi petit !

Jointure sur un varchar qui pose problème ? Quand même pas avec une telle volumétrie, si ?

[Fiilou]

Bonjour CinePhil,

la requête que vous prenez pour exemple est la requête qui a de bonnes performances (0.0012 sec)

La requête qui a de mauvaises performances (~70sec) est celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT COMPUTERS.name AS 'Nom', 
MONPLUGIN.societe AS 'Société'
FROM glpi_computers AS COMPUTERS
LEFT JOIN glpi_plugin_champiws AS MONPLUGIN ON (COMPUTERS.serial = MONPLUGIN.num_serie )
WHERE COMPUTERS.is_deleted = '0' 
AND COMPUTERS.is_template = '0'
GROUP BY COMPUTERS.name

La requête que vous présentez est une requête implémentée dans GLPI (solution d'inventaire) , donc je ne peux pas la modifier (enfin je le pourrais mais après y avoir passer énormément de temps pour trouver comment cela fonctionne, pour modifier, et ensuite pour tester).

P.S. : trop lent, StringBuilder a répondu avant moi.

Sinon oui c'est un varchar(255) comme clef primaire. Dans ma table, j'importe un fichier CSV exporter avec les informations dont on a besoin. Le numéro de série me paraissait bien comme clef primaire.

Si vous voulez, je peux tester en modifiant ma table et mon fichier d'import pour insérer un id unique et refaire les mêmes tests ? Seulement, la jointure portera toujours sur le numéro de série.

[Fiilou]

Je viens de modifier (suppression / création) la table MONPLUGIN.
J'ai ajouté un champ "id" qui devient clef primaire.

J'exécute la requête (~70sec).
J'ajoute un index sur la colonne "num_serie" de MONPLUGIN et j'exécute de nouveau la requête (~70sec).

J'obtiens exactement le même temps...


Ajout :
Je viens de remarquer quelque chose.

Est-ce que la propriété "Interclassement" du champ serial / num_serie (varchar(255)) est important ?

Je viens de remarquer qu'il différait entre les 2 tables.

[StringBuilder]

On va reprendre depuis le début.

Il y a un lien entre COMPUTER et PLUGIN.

PLUGIN est une table de référence.

Donc à partir de là, pour avoir de bonnes performances, COMPUTER doit contenir une clé étrangère vers COMPUTER.

Dans la bonne pratique, on utilise un ID de type int (si possible de la taille native du CPU ou plus petite => int16, int32 ou int64 sur un x64, mais surtout pas int64 sur un x32)

Et dans la table qui fait référence à la clé, on utilise ID et non une autre colonne (sinon on ne peut de toute façon pas créer la référence externe, qui pointe obligatoirement sur la clé primaire).

Dans votre cas, au vue de la volumétrie (et uniquement parce que la volumétrie est faible) vous pouvez tenter de rester avec une clé primaire sur votre numéro de série.

Mais surtout, ajoutez bien une référence à cette clé dans la table computer :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE computers
ADD FOREIGN KEY (serial)
REFERENCES plugin(num_serie)

Et dites-nous si ça améliore les choses.
Sinon, il faudra modifier la table plugin de ça façon suivante :

id PK de type int
num_serie (index unique)

Puis virer serial de la table computers, et la remplacer par serial_id de type int, avec un index non unique.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE computers
ADD FOREIGN KEY (serial_id)
REFERENCES plugin(id)

Et ensuite alimenter la table computer avec non pas le numéro de série du plugin, mais son id.

[StringBuilder]

L'interclassement est vital : déjà que comparer deux chaînes de caractères est très lent, mais si en plus il faut convertir le charset pour faire la comparaison, c'est pire.

C'est une des raisons principales pour lesquelles il ne faut jamais utiliser de colonne varchar pour faire des jointures.

PS : l'interclassement devrait être unique pour toute la base. Ca évite les mauvaises surprises de ce genre.

[Fiilou]

Bon je crois que j'ai trouvé avec l'interclassement.

Pour la table COMPUTERS j'ai un interclassement "utf8_unicode_ci".
Pour la table PLUGINS, j'ai un interclassement "latin1_swedish_ci".

En "latin1_swedish_ci", cela prend ~70 secondes et dès que je modifie en "utf8_unicode_ci" je tombe à 0.0013 seconde !

Bizarrement avec un explain, il prend la clef primaire maintenant pour rechercher :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	SIMPLE 	COMPUTERS 	ALL 	is_template,is_deleted 	NULL	NULL	NULL	6263 	Using where; Using temporary; Using filesort
1 	SIMPLE 	MONPLUGIN 	eq_ref 	PRIMARY 	PRIMARY 	767 	glpi.COMPUTERS.serial 	1

Je vais essayer de repartir de 0, en modifiant seulement ce paramètre.

Merci beaucoup à vous deux .

[Fiilou]

Une dernière petite question.

La commande suivante est-elle adéquate pour changer l'interclassement de la table svp :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table PLUGIN convert to character set utf8 collate utf8_unicode_ci;

Y a-t-il une contre indication de le faire "à chaud" ?

Encore merci.