Lock table et problème perf avec moteur InnoDB

Bonjour à tous,

Après avoir longuement cherché sur internet des indices pouvant me faire avancer, je me suis inscrit sur ce cite et posté mon premier sujet sur le forum pour y trouver de l'aide.

J'espère ne pas poser une question déjà présente dans ce forum mais je n'ai pas trouvé d'informations pertinentes.

Je rencontre actuellement un problème de comportement (et de performance) sur des tables MySQL sous le moteur InnoDB.

Contexte
Nous avons un table contenant entre 20 et 30 millions d'enregistrement, chaque enregistrement a un identifiant auto incrémenté (PK).
Nous voulons purger quotidiennement cette table (sauvegarde puis suppression).

Constat
Au départ, la table était sous le moteur MyISAM.
MyISAM ne gérant que des locks par table, la table était bloquée durant nos requêtes de DELETE (30s de temps d'exécution).
Nous sommes passés sous le moteur InnoDB qui gère les locks par enregistrement.
Après modification du moteur, deux constats important:

• La table est toujours lockée: impossible de faire un INSERT ou un DELETE pendant l'exécution du DELETE de purge.
• La requête de DELETE de purge est passée de 30s d'exécution a 7m30

Remarque: deux démarches ont été testées pour le passage MyISAM->InnoDB, par un simple ALTER TABLE et par une suppression/création/peuplement de table, et les résultats sont identiques.

Pour information, la requête de DELETE est : DELETE FROM maTable ORDER BY ID LIMIT 500000 ;

Questions

• InnoDB gère-t-il bien les locks par enregistrement, même durant un DELETE important ?
• Y a-t-il des options particulières, voire des optimisations, à positionner pour passer en mode lock par enregistrement ?
• Comment optimiser ma requête de DELETE pour qu'elle redescende vers les 30s d'exécution ?

Merci d'avance aux experts MySQL.

Bonjour,

Citation:

---

La table est toujours lockée: impossible de faire un INSERT ou un DELETE pendant l'exécution du DELETE de purge.

---

http://dev.mysql.com/doc/refman/5.5/...locks-set.html

en particulier :

Citation:

---

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

---

=> vérifier le plan d'execution (et lisez le reste de l'article, il y a d'autre points à vérifier)

2eme piste :

Citation:

---

The transaction isolation level also can affect which locks are set; see Section 13.3.6, “SET TRANSACTION Syntax”.

---

Ensuite,

Citation:

---

La requête de DELETE de purge est passée de 30s d'exécution a 7m30

---

Avec innobdb vous êtes soumis à une validation de transaction. c'est un surcoup de travaille, mais qui permet de faire des rollback si besoin.

Avez-vous essayé de faire de plus petit delete ? Au lieu d'un seul de 500k faites-en 5 de 100K ?

Avez-vous une / des clefs étrangères de déclarée sur cette table ?

Si la purge est totale et que la disponibilité pendant la purge n'est pas obligatoire, vous pouvez aussi considérer de dropper et recréer la table.

Merci pour les réponses.

Pour information, j'ai une requête de DELETE qui ne bloque pas d'autres INSERT ou DELETE: DELETE FROM maTable WHERE ID between xx and yy;

Citation:

---

=> vérifier le plan d'execution (et lisez le reste de l'article, il y a d'autre points à vérifier)

---

Je vais vérifier le plan d'exécution mais avec le champ ID (autoincrémenté) qui est la PK de la table, normalement j'ai l'index qu'il faut.

Citation:

---

The transaction isolation level also can affect which locks are set; see Section 13.3.6, “SET TRANSACTION Syntax”.

---

Je vais aller voir ça aussi

Citation:

---

Avez-vous essayé de faire de plus petit delete ? Au lieu d'un seul de 500k faites-en 5 de 100K ?

Avez-vous une / des clefs étrangères de déclarée sur cette table ?

---

Je n'ai pas encore essayé de réduire le nombre d'enregistrement supprimé en un requête.
Aucune FK déclarée sur cette table.

Citation:

---

Si la purge est totale et que la disponibilité pendant la purge n'est pas obligatoire, vous pouvez aussi considérer de dropper et recréer la table.

---

La purge n'est pas totale, je dois garder un certains nombre d'enregistrement suivant leur ancienneté

Petit point sur mes questions posées:

• InnoDB gère-t-il bien les locks par enregistrement, même durant un DELETE important ?
  Oui. Mes requêtes de DELETE et d'INSERT était mal faites: la suppression ou l'insertion se faisaient dans la partie lockée par le DELETE de purge.
  Petit conseil pour ceux qui rencontrent le même phénomène: bien faire attention aux clauses where de vos requêtes, il peut y avoir des blocages.
  .
• Y a-t-il des options particulières, voire des optimisations, à positionner pour passer en mode lock par enregistrement ?
  Non, InnoDB gère bien les lock par enregistrements. Voir le lien donné dans le 2ème post pour comprendre la gestion des locks suivant les requêtes.
  .
• Comment optimiser ma requête de DELETE pour qu'elle redescende vers les 30s d'exécution ?
  Je n'ai pas encore trouvé de solution.
  Les 3 requêtes suivantes donnent le même temps d'exécution pour une suppression de 500.000 lignes:
  DELETE FROM maTable ORDER BY ID LIMIT 500000 ;
  DELETE FROM maTable WHERE ID < XXXXX ;
  DELETE FROM maTable WHERE ID between XXXXX and YYYYY;

Je ne marque pas la sujet résolu même si la partie "lock" est ok, je change le sujet de la discussion.

Moi ce qui m'embete dans votre démarche c'est que vous purgez la table sur un id.


Je ne comprend pas le sens fonctionnel à une telle purge.

Pouvez-vous élaboré un peu la structure table (index en particulier), à quoi elle sert, vos critères de purges, le process d'alimentation etc.

C'est une table recevant des événements.
Aujourd'hui la volumétrie est de 2250 événements/minute et on veut garder une profondeur d'une semaine (soit plus de 22 millions d'événements).

L'identifiant de ces événements est incrémental.
La purge s'accompagne d'un sauvegarde dans un fichier plat avec 500.000 événements par fichier pour générer des fichiers à volumétrie correcte.
Après avoir sauvegarder nos 500.000 plus vieux événements, on veut les purger.

Pourquoi ne partitionnez-vous pas votre table ?

A raison d'une partition par jour par exemple (ou une partition par range d'id), vous n'aurez qu'a dropper la partition la plus vieille => instantané

Parce que je ne connaissais pas ce principe de partition ;)

Je vais étudier cette solution.
Merci