Die Geschichte der physischen Löschung von 300 Millionen Datensätzen in MySQL

Einführung

Hallo. Ich bin ningenMe, ein Webentwickler.

Wie im Titel erwähnt, ist meine Geschichte die eines physischen Löschens von 300 Millionen Datensätzen in MySQL.

Ich war daran interessiert, also beschloss ich, eine Zusammenfassung (Anleitung) zu erstellen.

Beginn — Alarm

In dem Batch Server, den ich verwende und betreue, gibt es einen regelmäßigen Prozess, der einmal täglich die Daten des letzten Monats aus MySQL abruft.

Normalerweise dauert dieser Prozess etwa 1 Stunde, aber diesmal dauerte er 7 oder 8 Stunden, und der Alarm hörte nicht auf zu erscheinen...

Ursachenanalyse

Ich habe versucht, den Prozess neu zu starten und die Protokolle zu überprüfen, aber ich konnte nichts Ungewöhnliches feststellen.
Die Abfrage war richtig indiziert. Aber als ich darüber nachdachte, was schiefgeht, wurde mir klar, dass die Datenbank recht groß ist.

hoge_table | 350'000'000 |

350 Millionen Datensätze. Es scheint, dass die Indizierung richtig funktionierte, nur sehr langsam.

Die benötigte Datensammlung für den Monat betrug etwa 12.000.000 Datensätze. Es sieht so aus, als ob der SELECT-Befehl viel Zeit in Anspruch nahm und die Transaktion lange Zeit nicht ausgeführt wurde.

DB

Im Grunde genommen handelt es sich um eine Tabelle, die jeden Tag um etwa 400.000 Einträge wächst. Die Datenbank sollte nur die Daten des letzten Monats sammeln, entsprechend wurde angenommen, dass sie mit diesem Datenvolumen umgehen kann, aber leider wurde die Rotate-Operation nicht aktiviert.

Diese Datenbank wurde nicht von mir entwickelt. Ich habe sie von einem anderen Entwickler übernommen, weshalb das Gefühl bleibt, dass es sich um technischen Schulden handelt.

Der Zeitpunkt kam, als das Volumen der täglich eingefügten Daten groß wurde und schließlich einen Grenzwert erreichte. Man würde erwarten, dass man bei so großen Datenmengen diese trennen sollte, aber leider wurde das nicht gemacht.

Und hier kam ich ins Spiel.

Korrektur

Es wäre sinnvoller gewesen, die Datenbank selbst zu verkleinern und die Verarbeitungszeit zu verkürzen als die Logik zu ändern.

Die Situation sollte sich erheblich verändern, wenn 300 Millionen Einträge gelöscht werden, also entschied ich mich dafür… Ach, ich dachte, das würde auf jeden Fall funktionieren.

Aktion 1

Nachdem ich ein zuverlässiges Backup erstellt hatte, begann ich schließlich, Anfragen zu senden.

「Anfrage senden」

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

「…」

「…」

„Hmm... Keine Antwort. Vielleicht dauert der Prozess zu lange?“ — dachte ich, aber sicherheitshalber schaute ich in Grafana und sah, dass die Festplattenauslastung sehr schnell anstieg.
„Das ist riskant“ — dachte ich erneut und stoppte sofort die Anfrage.

Aktion 2

Nachdem ich alles analysiert hatte, stellte ich fest, dass das Datenvolumen zu groß war, um es auf einmal zu löschen.

Ich entschied mich, ein Skript zu schreiben, das etwa 1.000.000 Einträge löschen kann, und startete es.

「Skript implementieren」

„Jetzt wird es bestimmt funktionieren“, — dachte ich.

Aktion 3

Die zweite Methode funktionierte, war aber sehr arbeitsintensiv.
Um alles ordentlich und ohne unnötigen Stress zu erledigen, bräuchte es etwa zwei Wochen. Aber dennoch entsprach dieses Szenario nicht den Serviceanforderungen, weshalb ich davon Abstand nehmen musste.

Also, das habe ich beschlossen:

Tabelle kopieren und umbenennen

Aus dem vorherigen Schritt habe ich verstanden, dass das Löschen eines so großen Datenvolumens auch eine große Last erzeugt. Daher entschied ich mich, eine neue Tabelle von Grund auf mit INSERT zu erstellen und die Daten, die ich löschen wollte, dorthin zu verschieben.

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

Wenn ich eine neue Tabelle in der gleichen Größe wie oben angegeben erstelle, sollte die Datenverarbeitungsrate auch um 1/7 schneller sein.

Nachdem ich die Tabelle erstellt und umbenannt hatte, begann ich, sie als Mastertabelle zu nutzen. Jetzt, wenn ich die Tabelle mit 300 Millionen Datensätzen lösche, sollte alles in Ordnung sein.
Ich habe herausgefunden, dass truncate oder drop weniger Last erzeugt als delete und beschloss, diesen Ansatz zu verwenden.

Ausführung

「Anfrage senden」

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

「…」
「…」
„Ähm…?“

Aktion 4

Ich dachte, die vorherige Idee würde funktionieren, aber nach dem Senden der Insert-Anfrage trat ein mehrfacher Fehler auf. MySQL ist gnadenlos.

Ich bin so müde, dass ich anfing zu denken, dass ich keine Lust mehr darauf habe.

Ich habe nachgedacht und erkannt, dass es vielleicht bei einmal zu viele Insert-Anfragen waren…
Ich habe versucht, eine Insert-Anfrage mit der Datenmenge zu senden, die die Datenbank an einem Tag verarbeiten sollte. Es hat geklappt!

Nach diesem Vorgang senden wir weiterhin Anfragen mit dem gleichen Datenvolumen. Da wir ein Monatsdatenvolumen entfernen müssen, wiederholen wir diesen Vorgang etwa 35 Mal.

Umbenennung der Tabelle

Hier hatte ich Glück: alles lief glatt.

Alert verschwunden

Die Verarbeitungszeit hat sich verkürzt.

Früher dauerte dieser Prozess etwa eine Stunde, jetzt dauert es ungefähr 2 Minuten.

Nachdem ich sichergestellt hatte, dass alle Probleme gelöst waren, habe ich 300 Millionen Datensätze gelöscht. Ich habe die Tabelle entfernt und fühlte mich neu geboren.

Zusammenfassung

Mir wurde klar, dass beim Batch-Processing das rotate processing übersehen wurde, und das war das Hauptproblem. Ein solcher Architekturfefehl führt zu einer völligen Zeitverschwendung.

Machen Sie sich Gedanken über die Last beim Replizieren von Daten, während Sie Datensätze aus der Datenbank löschen? Lassen Sie uns MySQL nicht überlasten.

Diejenigen, die sich gut mit Datenbanken auskennen, werden mit einem solchen Problem sicher nicht konfrontiert. Ich hoffe, dieser Artikel war für die anderen hilfreich.

Vielen Dank für das Lesen!

Wir würden uns sehr freuen, wenn Sie uns mitteilen, ob Ihnen dieser Artikel gefallen hat, ob die Übersetzung verständlich war und ob er Ihnen hilfreich war.

Quelle: habr.com