Discussion :

[aidonia]

Bonjour,

J'ai une table de base de données comptant environ 40 millions de rows.

J'ai un INDEX UNIQUE en CHAR (76) qui correspond à une chaine unique représentative et synthétisant l'intégralité de la ligne.

Tous les jours à heures précises, des dizaines d'utilisateurs tentent de créer la même ligne au même moment, et donc le même index unique.

Cette solution d'INDEX UNIQUE me permet d'éviter que plusieurs utilisateurs puissent créer la même ligne.

Le problème est que les utilisateurs tentent de créer cette ligne à la seconde près en même temps, cette concurrence des INSERT créer un goulot d'étranglement avec un load average très élevé (>50) et de plus les INSERTS deviennent très très lents à finaliser durant cette période.

D'où peux venir ce ralentissement? Est-ce l'INDEX UNIQUE qui est trop gros (char(76))? Est-ce dû au fait que la base contient plusieurs millions de rows?

Existe t'il une autre solution optimisée qu'un index unique qui permettrait de s'assurer que plusieurs utilisateurs ne puissent pas créer des doublons en concurrence, sachant que tous les jours les utilisateurs se ruent par exemple à 7h00:01 pour créer les mêmes lignes..

Merci d'avance

Hector

[tatayo]

Bonjour,
Quelle est le SGBD utilisé ?
Quelle est la structure de la table ?
Quels sont les (éventuels) autres indexes présents ?
Est-ce que des contraintes sont définies sur/vers cette table ?

Quel est le niveau d'isolation (au cas où) ?
Est-ce que les INSERTs sont faits dans une transaction, avec d'autres mises à jour/requêtes ? Dans quel niveau d'isolation ?
Est-ce que d'autres traitements sont faits pendant cette transaction ?

Tatayo.

[SQLpro]

Bonjour,
Quelle est le SGBD utilisé ?
Quelle est la structure de la table ?
Quels sont les (éventuels) autres indexes présents ?
Est-ce que des contraintes sont définies sur/vers cette table ?

Quel est le niveau d'isolation (au cas où) ?
Est-ce que les INSERTs sont faits dans une transaction, avec d'autres mises à jour/requêtes ? Dans quel niveau d'isolation ?
Est-ce que d'autres traitements sont faits pendant cette transaction ?

Tatayo.

À mon avis empilement de transactions non refermées...

A +

[aidonia]

Bonjour à tous et merci pour vos réponses!

Quelle est le SGBD utilisé ?

Je suis sur Maria DB 10.1, table en MyISAM.

Quelle est la structure de la table ?

Voici un aperçu des champs de la table :
account_id, unique_id, type, date_start, date_end, aire_id, creator_id, user1, user2, user3, user4, date_create, date_cancel

Le champ "unique_id" contient une chaine de 76 caractères maximum rassemblant les infos qui garantissent l'unicité de la ligne, par exemple :

account_id-date_start-aire_id

Quels sont les (éventuels) autres indexes présents ?

Il y a un autre INDEX simple qui permet de faire des SELECT plus rapidement, cet index contient une dizaine de colonnes : account_id, type, date_start, date_end, aire_id, creator_id, user1_id, user2_id, user3_id, user4_id.

Est-ce que des contraintes sont définies sur/vers cette table ?

C'est une table MyISAM donc pas de contrainte type foreign key.

Quel est le niveau d'isolation (au cas où) ?

Aucune à ma connaissance.

Est-ce que les INSERTs sont faits dans une transaction, avec d'autres mises à jour/requêtes ? Dans quel niveau d'isolation ?

Je ne suis pas sûr de saisir bien la question, je ne suis (malheureusement) pas expert en architecture SGBD..

Est-ce que d'autres traitements sont faits pendant cette transaction ?

Non il n'y a pas d'autres traitements qui pourraient provoquer ce type de ralentissement. Je précise que les problèmes ont commencés il y a quelques semaines seulement.

Plutôt que d'utiliser une chaine de CHAR(76), déjà un VARCHAR (76) améliorerait les choses.

Effectivement cela pourrait faire gagner un peu de place dans l'index size et optimiser un peu tout ça, mais ça ne suffira pas je pense.

Mieux serait l'utilisation du hachage sur cette information.... par exemple un MD5 ou mieux encore, si vous êtes sous SQL Server un simple CHECKSUM qui renvoi un entier 32 bits !

Le MD5 me semble un bon compromis pour limiter la longueur de la chaine et avoir une taille fixe, mais j'avais opté pour une chaine "en clair" pour éviter de devoir à calculer un MD5 à chaque requête. Ceci dit un MD5 est très rapide à calculer je pense..

Comment se fait il que la même ligne fonctionnelle soit tentée en insertion par plusieurs utilisateurs ? C'est très curieux...

Il s'agit d'un site où les gens peuvent prendre des rendez-vous. Par exemple le docteur DURAND les réservations sont possibles à partir de 8h00, donc tout le monde se connecte à 8h00 pour avoir le meilleur créneau disponible. Il y a quelques années je n'avais pas d'index unique et je me retrouvais avec des gens qui arrivait à réserver le même créneau, nonosbstant le fait que je faisais une vérification avant l'INSERT pour vérifier si le créneau était déjà pris... L'ajout de l'INDEX unique à résolu ce problème mais depuis quelques semaines ça devient problématique niveau performances, sûrment dû à l'afflux de visiteurs montant..

C'est vrai que CHAR(76) c'est assez lourd : avec une CPU 64 bits ça fait 10 cycles pour transporter cette clef, une routine de calcul d'un integer serait la bienvenue.

Effectivement il serait sans doute plus optimiser de calculer un integer unique représentant chaque créneau plutôt que du texte brut et variable...

Merci encore pour vos réponses!

[SQLpro]

Je suis sur Maria DB 10.1, table en MyISAM.

Voici un aperçu des champs de la table :
account_id, unique_id, type, date_start, date_end, aire_id, creator_id, user1, user2, user3, user4, date_create, date_cancel

Donc, base de données non normalisée du fait du viol de la première forme normale par apocope : les colonnes user1, user2, user3, user4 devraient être dans une table fille

Le champ "unique_id" contient une chaine de 76 caractères maximum rassemblant les infos qui garantissent l'unicité de la ligne, par exemple :
account_id-date_start-aire_id

Donc, à nouveau base de données non normalisée du fait de la redondance ! La solution passe par un calcul de hachage sur lequel se fera l'unicité

Il y a un autre INDEX simple qui permet de faire des SELECT plus rapidement, cet index contient une dizaine de colonnes : account_id, type, date_start, date_end, aire_id, creator_id, user1_id, user2_id, user3_id, user4_id.

Un index d'une dizaine de colonne ne sert pas à grand chose, sauf à diminuer les temps de réponse des INSERT, UPDATE, DELETE...

C'est une table MyISAM donc pas de contrainte type foreign key
[....]
Il s'agit d'un site où les gens peuvent prendre des rendez-vous. Par exemple le docteur DURAND les réservations sont possibles à partir de 8h00, donc tout le monde se connecte à 8h00 pour avoir le meilleur créneau disponible. Il y a quelques années je n'avais pas d'index unique et je me retrouvais avec des gens qui arrivait à réserver le même créneau, nonosbstant le fait que je faisais une vérification avant l'INSERT pour vérifier si le créneau était déjà pris...

Il est strictement impossible de simuler une contrainte portant sur une table par du code applicatif du fait de la concurrence d'accès... Sauf à revenir un un seul utilisateur accèdant à la base à la fois !

L'ajout de l'INDEX unique à résolu ce problème mais depuis quelques semaines ça devient problématique niveau performances, sûrment dû à l'afflux de visiteurs montant..

Il faudrait surtout revoir complétement votre modèle de données !

A +

[escartefigue]

Il faudrait surtout revoir complétement votre modèle de données !

D'accord avec tous les arguments de Fréderic, et, en plus de revoir le modèle, profitez-en pour abandonner MyIsam qui est une véritable bouse !
Un SGBD qui ne gère pas les contraintes d'intégrité, c'est une hérésie. Si vous voulez rester sur MySQL, utilisez InnoDB ce sera un moindre mal !

[escartefigue]

Tous les jours à heures précises, des dizaines d'utilisateurs tentent de créer la même ligne au même moment, et donc le même index unique.

Comment se fait il que la même ligne fonctionnelle soit tentée en insertion par plusieurs utilisateurs ? C'est très curieux...
C'est vrai que CHAR(76) c'est assez lourd : avec une CPU 64 bits ça fait 10 cycles pour transporter cette clef, une routine de calcul d'un integer serait la bienvenue.

[SQLpro]

Plutôt que d'utiliser une chaine de CHAR(76), déjà un VARCHAR (76) améliorerait les choses.

Mieux serait l'utilisation du hachage sur cette information.... par exemple un MD5 ou mieux encore, si vous êtes sous SQL Server un simple CHECKSUM qui renvoi un entier 32 bits !

A +