mysql - Suppression de Lignes de InnoDB est très lent

J'ai une base de données mysql avec env. 1 to de données. Tableau fuelinjection_stroke a env. 1.000.000.000 lignes. ID est la clé primaire qui est automatiquement incrémenté à chaque insertion.

Je suis en train de supprimer la première 1.000.000 de lignes à l'aide d'une déclaration très simple:

Delete from fuelinjection_stroke where DBID < 1000000;

Cette requête est takeing très long (>24h) sur mon dédié 8core Xeon Serveur (32 GO de Mémoire de Stockage SAS).

Aucune idée de savoir si le processus peut être accéléré?

1 milliard de lignes, c'est beaucoup. Avez-vous des indices sur la table, en plus de la pkey? Lorsque vous supprimez des lignes, les indices doit être mis à jour, ce qui est probablement ce qui prend du temps. Je ne sais pas si ça marchera dans ce cas, mais pourriez-vous essayer de faire le supprimer au sein d'une transaction et de voir si cela fonctionne mieux?
Pour l'OP, pouvez-vous nous informer sur le résultat de tout cela, je suis vraiment intéressé. 🙂
La suppression est très lent. L'approche de supprimer des portions de 10.000 s fonctionne - mais ce n'est pas d'accélérer le processus dans son ensemble. J'ai fini par faire le suivant: j'ai viré la table dans un fichier à l'aide de la commande mysqldump. J'ai ensuite utilisé sed -i '1,1000000 d' un fichier.sql pour supprimer des lignes du fichier de vidage. J'ai tronqué la table et rechargé le fichier de vidage ... qui s'est avéré être la méthode la plus rapide ...
OP mentionne dans un commentaire à Uriil la réponse de "n'avoir pas d'indices ou les clés étrangères de la table." Évidemment, si votre condition est l'utilisation d'une colonne sans index, puis MySQL a pour numériser l'ENSEMBLE de la TABLE afin d'être sûr qu'il a toutes les lignes qui sont DBID < 1000000. Alors que si DBID a un index sur elle, MySQL ne pas avoir à le faire.
Quel est le pourcentage de lignes supprimées dans ce cas? Si c'est plus de 30% des lignes, alors il serait préférable de copier les 70% restants dans la nouvelle table, puis renommez la nouvelle table d'origine et de renommer l'original pour vieux et puis chute de l'ancien tableau.

OriginalL'auteur user1938509 | 2014-04-01

18

Je crois que vous table est verrouillée. J'ai été confronté à un même problème et savoir qui peut supprimer 10k enregistrements assez rapide. De sorte que vous pouvez écrire des scripts simples/programme qui permet de supprimer des enregistrements en morceaux.
```
   DELETE FROM fuelinjection_stroke WHERE DBID < 1000000 LIMIT 10000;
```
Et de garder son exécution jusqu'à ce qu'il supprime tout ce

L'enquête en cours fait dans deux directions: 1) Depuis la table est vraiment très grand, il suffit juste d'une longue période de temps. 2) l'instance mysql est maître pour une réplication maître-esclave. En dépit de ne pas avoir tous les indices ou les clés étrangères de la table, le binlog la réplication entre deux serveurs semble ralentir le processus. La solution proposée ici, fonctionne depuis une seule déclaration n'est pas une fin.
toujours pas sûr de savoir pourquoi cette solution fonctionne.

OriginalL'auteur Uriil
5

Êtes-vous à l'espace privé? Est temps impossible?

Si non, vous pourriez tenir dans une nouvelle INT longueur de la colonne 1 et par défaut à 1 pour "active" (ou quel que soit votre terminologie), et de 0 pour les "inactifs". En fait, vous pouvez utiliser de 0 à 9 de 10 états différents si nécessaire.

L'ajout de cette nouvelle colonne va prendre un looooooooong moment, mais une fois que c'est fini, vos Mises à jour devrait être aussi rapide que l'éclair, tant que vous ne la sortez du PRIMAIRE (comme vous le faites avec votre SUPPRIMER) et vous n'avez pas l'indice de cette nouvelle colonne.

La raison pour laquelle InnoDB prend tellement de temps à SUPPRIMER sur une table massive que le vôtre est en raison de l'index cluster. Physiquement les commandes de votre tableau basé sur votre premier (ou la première dans son genre il trouve...ou que ce soit, il se sent comme si il ne peut pas trouver de PRIMAIRE ou UNIQUE), de sorte que lorsque vous tirez une ligne, il réorganise l'ENSEMBLE de votre table physiquement sur le disque pour la vitesse et la défragmentation. Il n'est donc pas le SUPPRIMER, ça prend si longtemps. C'est de la physique de réorganisation après que la ligne est supprimée.

Lorsque vous créez une nouvelle colonne INT avec une valeur par défaut, l'espace sera rempli, de sorte que lorsque vous mettez à JOUR, il n'y a pas besoin de la physique, de la réorganisation de l'ensemble de votre table immense.

Je ne suis pas sûr exactement ce que votre schéma est exactement, mais à l'aide d'une colonne pour une ligne de l'état est beaucoup plus rapide que DELETEing; toutefois, il prendra plus de place.

Essayer valeurs de réglage:
```
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT (for non-windows machine)
innodb_buffer_pool_size=25GB (currently it is close to 21GB)
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0...1000 (try different values, beginning with 200)
```
Références:

MySQL docs pour la description des différentes variables.

Serveur MySQL Paramètre De Réglage

MySQL Optimisation de la Performance des bases

http://bugs.mysql.com/bug.php?id=28382

Je suis en train de libérer de données parce que mon sas san est proche de 100% - c'est seulement de 4 to.
Sur cette base, ce que je ne comprends pas est pourquoi la supprimer est plus rapide, le moins de lignes que vous supprimez. Ce n'est sûrement pas la ré-organisation de la table N fois lorsque vous supprimez N lignes (dans un seul DELETE)? Parce que j'ai juste ajouté un MarkedForDeletion colonne comme vous le suggérez, mais allé plus loin et a ajouté une opération asynchrone qui (peu à peu) supprimer physiquement présentes dans lil' lots (que l'on a accepté de répondre à l'indique, et comme je l'ai entendu ailleurs)... et il fonctionne très bien!

OriginalL'auteur jmail
3

Quels indices avez-vous?

Je pense que votre problème est que la suppression est la reconstruction de l'index à chaque itération.

J'aimerais supprimer l'index le cas échéant, de les supprimer, puis ajouter à nouveau l'index. Il va être beaucoup plus rapide, (je pense).

Également désactiver les clés étrangères, les références à ce tableau, le cas échéant.

OriginalL'auteur i-CONICA
1

J'ai eu le même problème, et ma table a plusieurs indices que je n'avais pas envie de laisser tomber et de le recréer. J'ai donc fait la suivante:
```
create table keepers
select * from origTable where {clause to retrieve rows to preserve};
truncate table origTable;
insert into origTable null,keepers.col2,...keepers.col(last) from keepers;
drop table keepers;
```
Environ 2,2 millions de lignes ont été traitées dans environ 3 minutes.

OriginalL'auteur user5883982
0

Votre base de données peut être de vérifier les enregistrements qui doivent être modifiés dans une clé étrangère (cascades, supprimer).

Mais j'-Conica réponse est un bon point(+1). Le processus de suppression d'un enregistrement unique et mise à jour d'un lot de l'index pendant fait 100000 fois est inefficace. Il suffit de déposer l'index, supprimer tous les enregistrements et de le créer encore une fois.

Et, bien sûr, vérifier si il y a tout type de serrure dans la base de données. Un utilisateur ou une application peut verrouiller un enregistrement ou d'une table et votre requête sera en attente jusqu'à ce que l'utilisateur de libération de la ressource ou il atteint un dépassement de délai. Une façon de vérifier si votre base de données est en train de faire un réel travail ou tout simplement d'attente est de la lauch la requête à partir d'une connexion qui définit l' --innodb_lock_wait_timeout paramètre de quelques secondes. Si elle échoue, au moins vous savez que la requête est OK et que vous avez besoin pour trouver et relâchez le verrou. Exemples de serrures Select * from XXX Pour la mise à jour et uncommited transactions.

Je ne suis pas sûr si je comprends cette réponse. Ma table n'ont pas d'indices. Juste une clé primaire ID (autoincremented insert). Ma compréhension est que innodb ne fonctionne pas sans au moins un index unique, si ce n'est attribué explicitement qu'il va créer sur implicitement: "Si la table n'a pas de CLÉ PRIMAIRE ou adapté index UNIQUE, InnoDB en interne génère un caché index cluster sur une synthèse de la colonne contenant l'ID de ligne valeurs. Les lignes sont commandés par l'ID que InnoDB attribue des lignes dans cette table. "
Si vous avez seulement un index de clé primaire je suppose que le problème n'est pas l'index. Veuillez vérifier qu'il n'y a pas de relation de clé étrangère. (Par exemple, si vous avez un fuelinjection_stroke_history chaque fois que vous supprimez un enregistrement, vous devrez vérifier les orphelins dans cette table secondaire. Que serait 1000.000 requêtes) A la requête fini? Si non, je suppose que c'est un verrou.
La base de données de l'ist a répliqué (maître/esclave) - c'est de ralentir le processus (binlogs). La table n'a pas de clés étrangères etc. ...

OriginalL'auteur borjab
0

Pour de longues tables, je préfère utiliser MYISAM, spécialement si il n'y a pas beaucoup de transactions nécessaires.

OriginalL'auteur user6850438

-6

Je ne sais pas exacte sna pour ur québec. Mais l'écriture d'une autre façon de supprimer ces lignes, les pls essayer cette.

delete from fuelinjection_stroke where DBID in
(
    select top 1000000 DBID  from fuelinjection_stroke 
    order by DBID asc
)

OriginalL'auteur AK47

Vous devez vous connecter pour publier un commentaire.