Het verhaal van het fysieke verwijderen van 300 miljoen records in MySQL

Inleiding

Hallo. Ik ben ningenMe, webontwikkelaar.

Zoals de titel al zegt, mijn verhaal is het verhaal van het fysieke verwijderen van 300 miljoen records in MySQL.

Ik raakte hierin geïnteresseerd, dus besloot ik een handleiding te maken.

Begin — Alert

In de batch de server, die ik gebruik en onderhoud, is er een regelmatig proces dat eenmaal per dag gegevens van de afgelopen maand verzamelt uit MySQL.

Normaal gesproken duurt dit proces ongeveer 1 uur, maar deze keer duurde het 7 of 8 uur en de alert bleef maar verschijnen...

Oorzaak zoeken

Ik probeerde het proces opnieuw te starten, de logboeken te bekijken, maar ik zag niets ernstigs.
De query werd correct geïndexeerd. Maar toen ik nadacht over wat er misging, realiseerde ik me dat de volume van de database behoorlijk groot was.

hoge_table | 350'000'000 |

350 miljoen records. Het lijkt erop dat de indexering correct werkte, gewoon heel langzaam.

De vereiste gegevensverzameling voor de maand was ongeveer 12.000.000 records. Het lijkt erop dat de select-instructie veel tijd in beslag nam en de transactie lange tijd niet werd uitgevoerd.

DB

In wezen is dit een tabel die elke dag met ongeveer 400.000 records toeneemt. De database zou alleen gegevens van de afgelopen maand verzamelen, dus het was de bedoeling dat deze dat volume zou aankunnen, maar helaas was de rotate-operatie niet ingeschakeld.

Deze database is niet door mij ontworpen. Ik heb deze van een andere ontwikkelaar overgenomen, dus het blijft voelen als technische schuld.

Er kwam een moment dat de dagelijkse hoeveelheid ingevoerde gegevens groot genoeg werd en uiteindelijk de limiet bereikte. Aangenomen wordt dat je, als je met zo'n groot volume gegevens werkt, deze moet splitsen, maar dat is helaas niet gedaan.

En toen kwam ik in beeld.

Oplossing

Het was rationeler om de database zelf te verkleinen en de verwerkingstijd ervan te verkorten, dan de logica zelf te wijzigen.

De situatie zou aanzienlijk moeten veranderen als ik 300 miljoen records verwijder, dus besloot ik dat te doen... Ach, ik dacht dat dit zeker zou werken.

Actie 1

Nadat ik een betrouwbare back-up had gemaakt, begon ik eindelijk de verzoeken te verzenden.

「Verzoek verzenden」

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

「…」

「…」

“Hmm... Geen antwoord. Misschien duurt het proces erg lang?” — dacht ik, maar voor de zekerheid keek ik in Grafana en zag ik dat de schijfbelasting heel snel toenam.
“Dit is riskant” — dacht ik opnieuw en stopte onmiddellijk de aanvraag.

Actie 2

Na alles geanalyseerd te hebben, besefte ik dat het datavolume te groot was om alles in één keer te verwijderen.

Ik besloot een script te schrijven dat ongeveer 1.000.000 records kan verwijderen en startte het.

「ik implementeer het script」

“Nu moet het echt werken,” — dacht ik.

Actie 3

De tweede methode werkte, maar was erg arbeidsintensief.
Om alles netjes te doen, zonder onnodige zenuwen, zou ongeveer twee weken nodig zijn. Maar deze aanpak voldeed niet aan de servicevereisten, dus moest ik er vanaf zien.

Daarom besloot ik het volgende te doen:

We kopiëren de tabel en hernoemen deze.

Uit de vorige stap begreep ik dat het verwijderen van zo'n groot datavolume dezelfde hoge belasting creëert. Daarom besloot ik een nieuwe tabel vanaf nul te maken met behulp van insert en daarin de gegevens te verplaatsen die ik wilde verwijderen.

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

Als ik een nieuwe tabel maak met dezelfde grootte als hierboven vermeld, zou de verwerkingssnelheid ook 1/7 sneller moeten zijn.

Na de tabel te hebben gemaakt en hernoemd, begon ik deze als de mastertabel te gebruiken. Nu, als ik de tabel met 300 miljoen records verwijder, zou alles in orde moeten zijn.
Ik ontdekte dat truncate of drop minder belasting creëert dan delete en besloot die methode te gebruiken.

Uitvoering

「Verzoek verzenden」

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

「…」
「…」
「eh…？」

Actie 4

Ik dacht dat het vorige idee zou werken, maar na het indienen van de insert aanvraag verschenen er meerdere fouten. MySQL spaart niks.

Ik was al zo moe dat ik begon te denken dat ik hier niet meer mee verder wilde.

Ik heb even gezeten en nagedacht en begreep dat er misschien te veel insert aanvragen waren voor één keer...
Ik probeerde een insert-aanvraag in te dienen voor het datavolume dat de database in één dag moest verwerken. Het werkte!

En na dat blijven we aanvragen indienen voor hetzelfde datavolume. Aangezien we het maandelijkse datavolume willen verwijderen, herhalen we deze operatie ongeveer 35 keer.

Tabel hernoemen

Hier had ik geluk: alles ging soepel.

Alerts zijn verdwenen.

De snelheid van batchverwerking is verhoogd.

Eerder duurde dit proces ongeveer een uur, nu kost het ongeveer 2 minuten.

Nadat ik er zeker van was dat alle problemen zijn opgelost, heb ik 300 miljoen records verwijderd. Ik heb de tabel verwijderd en voelde me herboren.

Samenvatting

Ik besefte dat bij batchverwerking de rotate processing was gemist, en dat dit het belangrijkste probleem was. Een dergelijke fout in de architectuur leidt tot een verspilling van tijd.

Denkt u na over de belasting bij gegevensreplicatie wanneer u records uit de database verwijdert? Laten we MySQL niet overbelasten.

Degenen die goed thuis zijn in databases zullen met zo'n probleem zeker niet worden geconfronteerd. Voor de rest hoop ik dat dit artikel nuttig was.

Bedankt voor het lezen!

We zouden erg blij zijn als u ons vertelt of u dit artikel leuk vond, of de vertaling duidelijk was, en of het nuttig voor u was?

Bron: habr.com