InnoDB: HDD-Platz nach Löschen vieler Datensätze wiederbekommen

[Medium]

Hallo liebe DP!

Ich habe gerade eine Datenbank vor der Brust, die Energiemessdaten speichert. Ich war leider so nachlässig am Anfang, dass ich keine regelmäßige Löschung alter Daten gleich mit eingebaut habe, und sitze jetzt auf einer rund 3,5TB großen Datenbank... Und die Platte hat noch 20GB frei.

Ich habe bereits angefangen alle sekündlichen Daten, die älter als März sind auf minütlich zu konsolidieren. Leider gibt InnoDB aber den Platz gelöschter Zeilen nicht wieder frei. Ich bin auf diesen Artikel hier gestoßen, und zum Glück habe ich genau die Version 10.1. Nach einschalten der Defragmentierung und Hochsetzen von innodb_defragment_n_pages auf 32 und anschließendem OPTIMIZE TABLE verringert sich aber die (von MariaDB gemeldete) Größe von 3,0GB auf gerade mal 2,9GB pro Tabelle in der die massiven Löschungen passiert sind, und Windows meldet stetig sinkenden freien Platz auf der Platte.
Das ganze geht noch vielleicht 3-4 Tage gut, aber dann wird's eklig. Zumal die Datenerfassung, zumindest unter der Woche, nicht für längere Zeit unterbrochen werden darf.

Gibt es hier keine andere Lösung als einen kompletten Dump und Rebuild?

Vielen Dank schon mal!

[Jasocul]

gelöscht, weil nicht genau gelesen

[DeddyH]

Ich habe es nur überflogen, aber hilft Dir das vielleicht weiter? https://dev.mysql.com/doc/refman/5.7...ize-table.html

[jobo]

Hast Du denn das Problem, dass die Rows schwankende Größen haben?

Und ja, so wie beschrieben, benötigt dieser Prozess zunächst mehr Festplatten Speicherplatz.
Wir war denn das Laufzeitverhalten bei start des Befehls? Läuft noch? Abgeschlossen?
Ich vermute mal, dass in einem laufenden Production System 3TB belegter Platz nicht mal eben auf Knopfdruck reorganisiert werden. Da sehe ich eher Stunden.

Und wenn Platz auch nicht an das OS / HDD zurückgegeben wird, kann er von der DB dennoch wieder genutzt werden oder? Also egal, wenn nichts frei wird?!

Notfalls kurzzeitig runterfahren, Zusatzplatte rein und wieder hoch. Dann vielleicht neue Datafiles an den Tablespace hängen und lustig reorganisieren.
Ich kenne mysql nicht wirklich, vor allen Dingen nicht diese Szenarien, also alle meine Hinweise sind mit Vorsicht zu genießen.

[Medium]

@DeddyH: Leider nein. Ich habe leider die DB nicht mit der "one file per table" Option erstellt, wodurch ein OPTIMIZE ohne Defragmentierung letztlich genau das Gegenteil von dem macht was ich erreichen will (muss!): Die DB belegt nachher um die Länge der Tabelle MEHR Speicherplatz.

@jobo: Meine Datensätze sind alle gleich lang. Alle ~500 Tabellen mit ihren je 1-50mio. Einträgen nutzen dieselbe Satzlänge. Das OPTIMIZE mit aktiver Defragmentierung ist auf einer Tabelle mit knapp 50mio Einträgen komischerweise nach gut einer Minute durch. (Es ist zwar ein RAID10 mit ordentlich flotten HDDs dahinter, aber SO gut wird's nicht sein ) Die Statistiken (Innodb_defragment_count) sagen mir allerdings nachher, dass durchaus Defrag-Schritte unternommen wurden. Allerdings nur ein paar Zig - da ich diesen Wert aber nicht zu interpretieren weiß (sind das Pages? Sätze? Elefanten?)... was sagt mir das?

Es wurden übrigens rund 90% der Datensätze aus den Tabellen gelöscht. (Genau gesehen 98,3% aller Daten älter als 01.03.2017, und es sind knapp 2 Jahre an Daten bisher angefallen.)

Zitat:

Und wenn Platz auch nicht an das OS / HDD zurückgegeben wird, kann er von der DB dennoch wieder genutzt werden oder? Also egal, wenn nichts frei wird?!

Gut möglich dass die DB das kann, aber ich habe keinen Weg gefunden dies mit Sicherheit so auch sagen zu können. Auch weiß ich nicht, wie sie das DBMS verhält wenn die Platte randvoll ist. Ich könnte natürlich auf gut Glück alles laufen lassen erstmal, aber mir ist dabei ganz schön unwohl.

Nuja. Noch bin ich beim DELETEn. Das dauert immerhin auch schon 30min pro Tabelle, und es sind ein paar hundert... Ich hatte nur gehofft zwischendurch etwas Platz schaffen zu können um den Betrieb aufrecht halten zu können bis ich alle Löschungen durch habe. (Ja, das dauert Tage. Das ist klar.) Am Ende steht vermutlich eh ein Rebuild mit file-per-table aktiv, aber das würde ich bevorzugt an einem langen Wochenende machen dann.

Am Rande übrigens: Das Löschen scheint nicht nur keinen Platz freizugeben, nein! Es sieht aktuell so aus als würde es sogar noch Platz ZUSÄTZLICH futtern! Dadurch wird der Count-Down bis zum GAU ganz schön kurz

[Jumpy]

Bei Oracle sind gelöschte Daten erstmal nicht gleich weg, da muss man zusätlich noch Trunc oder sowas in den Deletebefehl mit einbauen. Andernfalls ist das wie der Papierkorb und man kann das wiederherstellen, weswegen es aber noch Platz belegt.

Ist das in Mysql vllt. ähnlich?

[jobo]

Also ich würde sagen egal welche DB, ein Delete von 50T records plus commit muss letztlich Speicherplatz freigeben, egal welches System.

Bei Oracle entstehen bei sowas logfiles (außer bei einem NO_LOGGING Tablespace oder wenn das gar nicht aktiviert ist), die zusätzlich Platz benötigen und idR gleich auf einer separaten Partition liegen.

Weiß nicht wie das bei mysql ist.

[nahpets]

passende Info eventuell hier: https://lists.mysql.com/mysql-de/1002

https://lists.mysql.com/mysql-de/1391

https://www.alexander-langer.de/2014...speichern.html

https://www.thomas-krenn.com/de/wiki...Optimize_Table

[Medium]

Indirekt bin ich dank der Links und jobo's Hinweis zur Lösung gekommen! Was mir dermaßen die Platte voll gejodelt hat waren NICHT die eigentlichen Daten, sondern das binary logging! Da ich auf der DB die entsprechenden Funktionen die mir dieses bietet nicht unbedingt benötige, habe ich mich dazu durchgerungen mal beherzt RESET MASTER in die Konsole zu tippern. Und siehe da: Von vormals noch gerade 19,8GB sind nun wieder 2,77TB freier Speicher geworden. (In schlappen 2,8sek sogar nur )

Ich werde das bin-logging wohl mal abschalten jetzt. Und im selben Zuge wenn Zeit ist auf file-per-table umstellen. Besten Dank liebe DP!