Löschen vieler Datensätze mit Hibernate

[Olel]

Hallo,

ich habe eine Hibernate-Anwendung in der sehr viele Datensätze regelmäßig in eine Tabelle geschrieben werden. Man kann sich diese Tabelle als Log-Tabelle vorstellen, wo z.B. jede vom Benutzer ausgeführte Aktion festgehalten wird.

Diese Tabelle wird nach einem Jahr ca. 120 Mio Datensätze enthalten und wächst pro Woche um ca. 2,5 Mio Datensätze. Um die Tabelle nicht zu groß werden zu lassen, sollen einmal pro Woche "alte" Datensätze (solche die älter als ein Jahr sind) automatisch gelöscht werden. D.h. es müssen bei jedem Löschvorgang ca. 2,5 Mio Datensätze aus einer 120 Mio Datensätze großen Tabelle gelöscht werden.

Es wäre natürlich möglich diese Operation per Hibernate (HQL) durchzuführen. Ich vermute aber, dass die Laufzeit dieser Operation viel zu lang wäre und somit die Anwendung blockiert (die ja zudem auch noch sekündlich versucht in die gleiche Tabelle zu schreiben).
Ich bin also auf der Suche nach dem performantesten Weg dieses Löschen durchzuführen.

Als Alternative ist mir bisher eine Stored Procedure eingefallen, aber ist die dann wirklich schneller? Außerdem habe ich in der Hibernate Dokumentation gelesen, dass man Stored Procedures nicht mit setMaxResults() beschränken kann. Ich denke aber, dass es nötig sein wird, die Löschoperation in mehr als eine Transaktion aufzuteilen, weil die Transaktion sonst erstens sehr lange dauert (und für diese Zeit die Tabelle ggf. gelockt wird, wenn das ROW-Locking nicht ausreicht) und zweitens das Transaktions-Log der DB (DB2) zu klein sein könnte.

Hat jemand eine gute Idee wie so etwas zu lösen ist?

 

[homer65]

Einfach die Datensätze mit einem Timestamp versehen. Dann kann man das löschen mit einem einzigen SQL Befehl ausführen:
delete from logtable where datum < ...

 

[Olel]

Einfach die Datensätze mit einem Timestamp versehen. Dann kann man das löschen mit einem einzigen SQL Befehl ausführen:

Die Datensätze haben bereits einen Timestamp und das Delete-Statement sieht genauso aus. Bei 2,5 Mio Datensätzen dauert dieses Delete aber dennoch sehr lange oder kann ggf. gar nicht durchgeführt werden, da das Transaktion-Log für das Löschen so einer großen Datenmenge sehr groß wird und dessen Schreiben recht langsam ist.

Deswegen die Idee die Operation in mehrere Transaktionen aufzusplitten.

 

[SlaterB]

wenn du die Objekte nicht lädst sondern per SQL löschst (einmalige Umwandlung HQL in SQL stört auch nicht),
dann ist Hibernate gar nicht beteiligt, falls du das als Langsamkeit in der Diskussion ansiehst?

teste mit einem separaten DB-Tool, geht es dort schneller?
ob Timestamp gut verwendet werden kann liegt doch bestimmt an Indexen, aber das braucht man wohl gar nicht mehr erst ansprechen?

falls das mit dem Löschen fest ist, könnte zur Vereinfachung vielleicht auch auf Timestamp verzichtet werden und eine zusätzliche Spalte mit einer einfachen Zahl, Kalenderwoche 1-5x oder ähnliches gefüllt werden?
die Extremvariante wären 5x einzelne Tabellen..

> Deswegen die Idee die Operation in mehrere Transaktionen aufzusplitten.
ganz einfach testen ob 5x 500.000 oder 25x 100.000, so gut man das hinbekommt,
schneller, langsamer oder gleich sind?

 

[Olel]

wenn du die Objekte nicht lädst sondern per SQL löschst (einmalige Umwandlung HQL in SQL stört auch nicht),
dann ist Hibernate gar nicht beteiligt, falls du das als Langsamkeit in der Diskussion ansiehst?

Macht Sinn. Ob man Hibernate benutzt ist an dieser Stelle wohl egal.

teste mit einem separaten DB-Tool, geht es dort schneller?
ob Timestamp gut verwendet werden kann liegt doch bestimmt an Indexen, aber das braucht man wohl gar nicht mehr erst ansprechen?

Nein, geht es nicht. Wenn das Löschen in einer Transaktion erfolgt, ist es einfach langsam. Ein Index ist auf den Timestamp natürlich gesetzt. Es liegt wohl wie gesagt am Transaktionslog. Ich bin aber ehrlich gesagt auch überrascht, dass das wirklich ein Problem darstellt.

ganz einfach testen ob 5x 500.000 oder 25x 100.000, so gut man das hinbekommt,
schneller, langsamer oder gleich sind?

In einem DB-Tool einfach machbar, aber könnte man sowas auch in einer StoredProcedure hinbekommen? Das in Hibernate selbst zu bauen, ist zwar machbar, aber auch nicht ganz ohne Aufwand. Außerdem würde ich in unserer Anwendung gerne bei Transaction by Annotation (via Spring) bleiben und keine eigenen programmatischen Transaktionen verwenden.

 

[SlaterB]

ich hab zwischendurch editiert, auch wenn vielleicht nicht so wichtig bei Transaktionslog, was immer das ist

falls das mit dem Löschen fest ist, könnte zur Vereinfachung vielleicht auch auf Timestamp verzichtet werden und eine zusätzliche Spalte mit einer einfachen Zahl, Kalenderwoche 1-5x oder ähnliches gefüllt werden?
die Extremvariante wären 5x einzelne Tabellen..

ansonsten kann ich sicher nichts mehr beitragen,
eine Datenbank muss ja wohl löschen können oder eben nicht,
Konfigurationen dort sind nicht mein Gebiet

 

[homer65]

Wenn du das auf mehrere Transaktionen aufteilen willst - warum auch immer - kannst du mehrere SQL Statements absetzen:
delete from logtable where datum < x1
delete from logtable where datum < x2
delete from logtable where datum < x3
...
Wobei x1 < x2 < x3 ...
Wenn du die xn geschickt wählst kommen nicht zu große Transaktionen dabei raus.

 

[maki]

Batches können wie immer helfen:
Chapter 13. Batch processing

Batch- und Transaktionsgröße sollte angepasst werden bis die gewünschte/akzeptable Performance erreicht ist.

 

[SlaterB]

alles zu
- hibernate.jdbc.batch_size 20
- session.flush();
- second_level_cache
- alles von Hibernate allgemein
usw. von der Seite spielt beim Löschen wie hier beschrieben aber keine Rolle oder?
dann etwas merkwürdiger Vorschlag,
oder gibt es doch eine relevante 'Transaktionsgröße' für diese Frage bei Hibernate?

 

[nillehammer]

Ich hatte ein ähnliches Verhalten beim Löschen sehr vieler Datensätze in einer MySql-DB. Wir haben am Ende mit mehreren Wochenweise aufgeteilten Tabellen gearbeitet und zum Löschen einfach die entspr. Tabelle gedroppt und wieder created. Das war zwar etwas exotisch aber dafür unschlagbar schnell.

 

[bERt0r]

Die Lösung von nillehammer finde ich mit einem Nested Table gar nicht mal so exotisch.

 

[JanHH]

Ich hatte das Problem auch mal, allerdings gings da nur um 5000 Datensätze, löschen (per SQL) trotzdem langsam. Grund war, dass die per @OneToMany oder @ManyToOne `(oder irgendwas in der Art halt) mit anderen Datensätzen verknüft waren und die Datenbank dann nach dem Löschen jedes Datensatzes erstmal irgendeine andere Tabelle komplett durchsucht hat. Das war die Bremse. Also vielleicht hast Du ja auch solche Verknüpfungen in Deiner Tabelle.