Une histoire sur la suppression physique de 300 millions d'enregistrements dans MySQL

Introduction

Bonjour. Je suis ningenMe, développeur web.

Comme indiqué dans le titre, mon histoire est celle de la suppression physique de 300 millions d'enregistrements dans MySQL.

Je me suis intéressé à ce sujet, alors j'ai décidé de rédiger une note (mode d'emploi).

Début - Alerte

Dans le lot le serveur, que j'utilise et maintiens, il existe un processus régulier qui collecte les données du mois dernier dans MySQL une fois par jour.

En général, ce processus se termine en environ 1 heure, mais cette fois, il n'a pas abouti pendant 7 ou 8 heures, et l'alerte a continué à apparaître...

Recherche de la cause

J'ai essayé de redémarrer le processus, de vérifier les logs, mais je n'ai rien vu d'inquiétant.
La requête était correctement indexée. Mais en réfléchissant à ce qui pourrait clocher, j'ai réalisé que la taille de la base de données était assez grande.

hoge_table | 350'000'000 |

350 millions d'enregistrements. Il semble que l'indexation fonctionnait correctement, juste très lentement.

La collecte de données pour le mois requièrait environ 12 000 000 d'enregistrements. Il semble que la commande select ait pris beaucoup de temps, et la transaction n'a pas été exécutée pendant longtemps.

DB

En fait, il s'agit d'une table qui augmente d'environ 400 000 enregistrements chaque jour. La base de données aurait dû collecter uniquement les données du mois dernier, donc on s'attendait à ce qu'elle puisse gérer ce volume, mais malheureusement, l'opération de rotation n'était pas activée.

Cette base de données n'a pas été développée par moi. Je l'ai reçue d'un autre développeur, donc il reste un sentiment de dette technique.

Il est arrivé un moment où le volume des données insérées quotidiennement est devenu important et a finalement atteint la limite. On suppose que lorsqu'on travaille avec un grand volume de données, il faudrait les diviser, mais cela n'a pas été fait.

Et c'est là que j'interviens.

Correction

Il était plus judicieux de réduire la base de données elle-même et de diminuer le temps de traitement plutôt que de changer la logique.

La situation devrait changer considérablement si je supprime 300 millions d'enregistrements, donc j'ai décidé de le faire... Ah, je pensais que cela fonctionnerait certainement.

Action 1

Après avoir préparé une sauvegarde fiable, j'ai enfin commencé à envoyer des requêtes.

「Envoi de la requête」

DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';

「…」

「…」

“Hmm… Pas de réponse. Peut-être que le processus prend beaucoup de temps ?” — pensais-je, mais par précaution, j'ai jeté un œil sur Grafana et vu que la charge du disque augmentait très rapidement.
«C'est risqué» — pensais-je encore une fois et j'ai immédiatement arrêté la requête.

Action 2

Après avoir analysé le tout, j'ai réalisé que le volume de données était trop important pour tout supprimer d'un coup.

J'ai décidé d'écrire un script qui pourrait supprimer environ 1 000 000 d'enregistrements et je l'ai lancé.

「je réalise le script」

«Ça va fonctionner cette fois», pensais-je.

Action 3

La deuxième méthode a fonctionné, mais s'est avérée très laborieuse.
Pour tout faire proprement, sans stress, cela prendrait environ deux semaines. Cependant, ce scénario ne correspondait pas aux exigences de service, donc j'ai dû abandonner.

Donc, voici ce que j'ai décidé de faire :

Nous copions la table et la renommons.

À partir de l'étape précédente, j'ai compris que la suppression d'un si grand volume de données crée une charge tout aussi importante. Par conséquent, j'ai décidé de créer une nouvelle table à partir de zéro avec insert et d'y déplacer les données que je comptais supprimer.

| hoge_table     | 350'000'000|
| tmp_hoge_table |  50'000'000|

Si je crée une nouvelle table de la même taille que celle indiquée ci-dessus, la vitesse de traitement des données devrait également être 1/7 plus rapide.

Après avoir créé la table et l'avoir renommée, j'ai commencé à l'utiliser comme table maîtresse. Maintenant, si je supprime la table avec 300 millions d'enregistrements, tout devrait bien se passer.
J'ai appris que truncate ou drop crée moins de charge que delete, et j'ai décidé d'utiliser cette méthode.

Exécution

「Envoi de la requête」

INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';

「…」
「…」
「euh…？」

Action 4

Je pensais que l'idée précédente fonctionnerait, mais après avoir envoyé la requête d'insertion, un message d'erreur multiple est apparu. MySQL ne fait pas de cadeaux.

J'étais tellement fatigué que j'ai commencé à penser que je ne voulais plus m'en occuper.

J'ai réfléchi un moment et pensé que peut-être pour une seule fois, il y avait trop de requêtes d'insertion…
J'ai essayé d'envoyer une requête d'insertion pour le volume de données que la base doit traiter en un jour. Ça a fonctionné !

Eh bien, après cela, nous continuons à envoyer des requêtes pour le même volume de données. Comme il faut supprimer le volume mensuel de données, nous répétons cette opération environ 35 fois.

Renommage de la table

Ici, la chance était de mon côté : tout s'est bien passé.

Les alertes ont disparu.

La vitesse de traitement par lots a augmenté.

Auparavant, ce processus prenait environ une heure, maintenant il ne prend plus qu'environ 2 minutes.

Après avoir vérifié que tous les problèmes étaient résolus, j'ai supprimé 300 millions d'enregistrements. J'ai effacé la table et je me suis senti renaître.

Résumé

J'ai compris que lors du traitement par lots, le traitement des rotations avait été négligé, et c'était là le principal problème. Une telle erreur d'architecture conduit à un gaspillage de temps.

Vous demandez-vous quelle sera la charge lors de la réplication des données en supprimant des enregistrements de la base ? Évitons de surcharger MySQL.

Ceux qui ont une bonne maîtrise des bases de données ne rencontreront certainement pas ce problème. J'espère que cet article a été utile au reste.

Merci pour votre lecture !

Nous serions très heureux que vous nous disiez si cet article vous a plu, si la traduction est claire et si elle vous a été utile ?

Source : habr.com