Archivierungstechniken

[Leobahrt]

Hallo liebes Datenbankforum,
momentan arbeiten wir mit einer Datenbank welche alle Daten beinhaltet. Die Daten lassen sich in produktiv Daten, die täglich in Gebrauch sind und den histroischen Daten welche nur einmal im Schaltjahr aufgerufen werden, unterscheiden. Da Produktiv- und Archivsdaten momentan zusammen sind, gehen die SQL Abfragen auch immer über die historischen Daten, was eigentlich blödsinn ist. Somit will ich die historischen Daten in ein anderes Schema oder andere Datenbank auslagern.
So habe ich mir gedacht, dass wir Db1 haben mit den aktuellen Daten und Db2 mit den historischen Daten. Nun haben unsere Daten jedoch auch Beziehung. Zum Beispiel 1..n, wie Rolle zu Personen (Viele Personen können einer Rolle zugeordnet werden) oder n ...m wie Artikel zu Behälter (Viele Artikel können in einem Behalter sein, viele Behälter können Artikel haben). Wenn ich jetzt eine Person archiviere, dann geht mit die Relation zur Rolle, verloren somit müsste ich die Rolle ebenfalls ins Archiv speichern.

Habt ihr eine Idee wie die Archivierung aussehen könnte?

Wäre so froh wenn ihr mir da aushelfen könnte. Habe bereits drei graue Haare bekommen :-(

Liebe Grüße
Leobahrt

 

[akretschmer]

Habt ihr eine Idee wie die Archivierung aussehen könnte?

Typischerweise löst man das durch Partitionierung. Man erstellt z.B. je Jahr oder Monat eine einzelne Partition, die Abfrage enthält immer auch eine Eingrenzung auf ein Datum oder Datumsbereich. Damit kann die DB Partitionen ausschließen, wo sie eh nix finden kann (constraint exclusion). Du hast aber immer noch über alle einzelnen Partitionen Abfragen zu machen, kannst FK's einsetzen etc.

 

[Leobahrt]

Typischerweise löst man das durch Partitionierung. Man erstellt z.B. je Jahr oder Monat eine einzelne Partition, die Abfrage enthält immer auch eine Eingrenzung auf ein Datum oder Datumsbereich. Damit kann die DB Partitionen ausschließen, wo sie eh nix finden kann (constraint exclusion). Du hast aber immer noch über alle einzelnen Partitionen Abfragen zu machen, kannst FK's einsetzen etc.

Vielen Dank für deine Antwort Akretschmer.
Ou, das ist ja mal ein guter Einfall! So könnte man weiterhin alle Daten zusammen auf einer DB lassen, jede Partition wäre ein eigenes Schema und wenn man Daten haben möchte würde man ein Datum mit angeben und bekäme genau diese Daten aus der entsprechender Partition. Wie würde das nun mit 1 ...n bzw. n ...m Relationen funktionieren? Das blicke ich noch nicht so richtig. Könntest du das vielleicht an einem Beispiel (1 ... n / Rolle zu Person) bzw. n...m (Artikel zu Behäter) erklären.

 

[akretschmer]

test=*# create table kunden (id int primary key, name text);
CREATE TABLE
test=*# create table umsatz (kunde int references kunden, datum date, umsatz int) partition by range (datum);
CREATE TABLE
test=*# create table umsatz_2015 partition of umsatz for values from ('2015-01-01') to ('2016-01-01');
CREATE TABLE
test=*# create table umsatz_2016 partition of umsatz for values from ('2016-01-01') to ('2017-01-01');
CREATE TABLE
test=*# create table umsatz_2017 partition of umsatz for values from ('2017-01-01') to ('2018-01-01');
CREATE TABLE

ich fülle das mal:

test=# insert into umsatz select (1 + random() * 98)::int, '2015-01-01'::date + random() * 1000 * '1day'::interval, random()*1000 from generate_series(1,100000) s;
INSERT 0 100000

und frage ab:

test=*# explain analyse select * from umsatz where datum = '2017-05-01';
                                                    QUERY PLAN                                                   
------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..486.61 rows=110 width=12) (actual time=0.039..6.316 rows=110 loops=1)
   ->  Seq Scan on umsatz_2017  (cost=0.00..486.06 rows=110 width=12) (actual time=0.038..6.280 rows=110 loops=1)
         Filter: (datum = '2017-05-01'::date)
         Rows Removed by Filter: 27015
 Planning Time: 0.225 ms
 Execution Time: 6.362 ms
(6 rows)

Wie man sieht, wird nur die umsatz_2017 - Tabelle abgefragt. Da könnte man auf das Datum noch einen Index setzen etc., aber das ist ja hier nicht das Thema.

 

[akretschmer]

ich sollte noch erwähnen, mit was ich das mache:

test=*# select version();
                                                                      version                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11beta1 (Ubuntu 11~beta1-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)

test=*#

 

[Dukel]

Hier findet man das unter MSSQL:
Partitioned Tables and Indexes

 

[Leobahrt]

Vielen Dank @Dukel und @akretschmer.
Wenn ich das richtig verstehe bleibt aber immernoch das Problem, fals jemand auf Produktivdaten und histrische Daten zugreifen möchte. Bsp.: Gebe mir alle Mitarbeiter die jetzt im Unternehmen arbeiten und alle die im Unternehmen gearbeitet haben. So müsste man über alle Partitionen drüber gehen. Jetzt haben wir nur eine Datenbank, dies würde dann zur Folge haben, dass das Produktivsystem langsamer wird.

Im Grunde ist das ja auch nicht wirklich eine Archivierung.

 

[akretschmer]

Nun ja, dafür hast es aber im direkten Zugriff. Niemand hindert Dich, einzelne Partitionen auszulagern, nur mußt Du dann in solchen Fällen sowohl Archiv als als Prod abfragen und die Ergebnisse kombinieren.

 

[Dukel]

Vielen Dank @Dukel und @akretschmer.
Wenn ich das richtig verstehe bleibt aber immernoch das Problem, fals jemand auf Produktivdaten und histrische Daten zugreifen möchte. Bsp.: Gebe mir alle Mitarbeiter die jetzt im Unternehmen arbeiten und alle die im Unternehmen gearbeitet haben. So müsste man über alle Partitionen drüber gehen. Jetzt haben wir nur eine Datenbank, dies würde dann zur Folge haben, dass das Produktivsystem langsamer wird.

Der Vorteil der partitionierung ist, dass du Daten auf unterschiedliche Disks ablegen kannst. Aktuelle Daten auf SSDs, ältere Daten auf SATA Disks. Wenn alle Daten benötigt werden, dann wird die Abfrage entsprechend lang dauern, wenn man nur aktuelle Daten abfragt kommt die Antwort schneller. Wieso sollte das ganze System langsam werden? Es geht ja um einzelne Abfragen, die langsam oder schnell beantwortet werden.

 

[Walter]

gehen die SQL Abfragen auch immer über die historischen Daten, was eigentlich blödsinn ist

Das möchste ich eigentlich bezweifeln. Wenn die Indexe richtig liegen, kosten Dich grössere Datenmengen so gut wie keine Performance. Von welchen Datenmengen reden wir überhaupt?

 

[Leobahrt]

Hallo, nochmals vielen Dank für eure Antworten. Bin echt froh zu sehen, dass hier gute Aktivität herrscht
In meiner Ausgangsstellung habe ich das ein oder andere weggelassen, weil ich dachte, ich pick mir ein Problem raus und dann mache ich mich an das nächste ran, nach dem Motto "Teile Und Herrsche"

Hier das ganze etwas ausführlicher:
Unsere Software arbeitet auf einer JavaEE Architektur und benutzt den JBoss Application Server, um Zugriffe über das Web anzubieten. Der Application Server bringt das Open-Source Framework Hibernate von Haus aus mit, welches genutzt wird um Plain-Old-Java-Objects (POJOs) inklusive Relationen (1…n und n…m) in die relationale Datenbank von Microsoft persistent zu speichern. Derzeit arbeitet wir mit einer Datenbank auf dem alle Daten gespeichert werden. Diese Daten lassen sich in Produktivdaten und historische Daten klassifizieren. Wobei Produktivdaten oft und historische Daten selten verwendet werden. Wenn eine Abfrage an die Datenbank gesendet wird, werden auch historische Daten in dem Query berücksichtigt obwohl diese evtl. nicht angefragt wurden. Dies führt dazu, dass die Query länger braucht und mehr Performanz-Ressourcen verbraucht als notwendig. Zudem gibt es diverse Benutzergruppen, wie die Produktion welche mit der Datenbank arbeitet. Die Produktion braucht die Daten schnell um eine planmäßige Produktion und Fertigung zu gewährleisten. Erfolgen nun aber weitere Zugriffe auf die Datenbank durch andere Benutzergruppen, kann es dazu führen, dass das System verlangsamt und die Produktion stagniert (Ist ganz schlecht und darf nicht sein). Daher soll ein Archiv integriert werden, in das die historischen Daten ausgelagert werden sollen. Somit hätte man eine Möglichkeit die Zugriffe auf zwei Datenmedien zu lenken um die Last weiter zu verteilen.

Bei der Integrierung eines Archives ist auf die 1…n und m…n Beziehungen zwischen den einzelnen Entitäten der Daten zu achten. Da man die Relationen und die Entitäten nicht getrennt voneinander betrachten kann, müssen Entitäten inklusive deren Relationen ins Archiv gespeichert werden. Sonst wird man bei der Wiederherstellung der Entitäten diese nicht mehr entsprechend zuordnen können. Ein weiteres Problem bei der Integration eines Archives ist die Delegation der Anfragen an die richtige Datenbank. Denn Benutzer sollen die Möglichkeit haben, Daten anzufragen ohne die darunterliegende Struktur der Datenbanken zu kennen. Somit muss eine Schnittstelle existieren, die die Anfrage der Benutzer annimmt und an die richtige Datenbank weiterleitet ohne gesagt zu bekommen an welche Datenbank die Anfrage abzielt.

Mein Ansatz den ich jetzt verfolge ist es zwei Datenbanken (Db1 und Db2) zu haben. Db1 mit den Produktivdaten und Db2 mit den Produktivdaten + Archivdaten. Man würde die Daten von der aktuellen Datenbank Spiegeln und aus Db1 alle hostorischen Daten entfernen. Somit ist gewährleistet, dass wir auf beiden die gleiche Struktur haben und wir kommen mit den Relationen nicht in die Bredouille. Es löst auch das Problem, dass wenn man Daten die "alt" und "neu" haben möchte, man diese von der Db2 bekommt, da diese Datenbank alle Daten beinhaltet. Zudem wird es einen Mechanismus geben der Veränderungen an der Db1 mit der Db2 synchronisiert.

Jetzt henge ich nur noch an dem Problem des Zugriffs. Die Benutzer kennen die Architektur nicht. Sprich die würden z.B eine Anfrage schicken die so lauten könnte, "gibt mir diese expliziten 10 Benutzer". Was er nicht weiß ist, dass 8 davon "neu" sind und die anderen zwei sind "alt". Das Ziel wäre in diesem Fall die Db2, denn dort liegen ja alle Daten. Anfangs habe ich gedacht, dass meine Schnittstelle per Default die Anfragen immer an die Db1 sendet und im Falle fals nichts gefunden wird man die Db2 absucht. Dies hätte dann zur Folge, dass man im worst Case zwei Abfragen druchzuführen hat. Wenn die Tatsache aber so ist, dass diese Anfragen selten vorkommen, habe ich mir gedacht, wäre das ein Kompromiss den man eingehen könnte. Jedoch würde in diesem Fall in den worst-case szenarien die Performance noch mehr drunter leiden und die Produktion viel früher stagnieren, sodass diese Option wegfällt.

Vielleicht hat jemand eine Idee, wie ich diesen Ansatz zum laufen bringen könnte, bzw. evtl eine andere Idee, wie man dieses Problem lösen könnte.

Vielen Dank schonmal vorab.

PS: Vielleicht sollte ich den Anfangspost oben mit diesem hier erstzen?!

 

[Dukel]

Wieso versteifst du dich an deinen Ansatz mit zwei Datenbanken?
Dafür gibt es partitionierung um sich solche Gedanken nicht machen zu müssen.

Angenommen du hast Daten von dem Jahr 2018 bis 1950. Du hast zwei Partitionen. Daten aus 2018 und Daten aus 2017-1950.
Für den Anwender (die Applikation) sieht das aus wie eine Tabelle und du musst nichts ändern.
Wenn deine Abfrage gibt mir alle Daten aus dem aktuellen Monat, letzte zwei Monate,... dann wird nur die eine Partition abgefragt und sollte schnell sein. Hier müssen ggf. die Abfragen in der Applikation angepasst werden, dass die Abfragen das Datum immer mit abfragen (z.B. wenn keine explizite Anfrage nach älteren Daten geschieht soll max das aktuelle Jahr abgefragt werden).

 

[Leobahrt]

Der Gedanke mit den zwei Datenbanken kommt daher, dass eine Rahmenbedingung es war historische Daten in ein Archiv auszulagern (muss nicht eine andere Datenbank sein, könnte also auch theoretisch in Form einer Partitionierung sein). Jedoch haben wir in diesem Fall bei der Partitionierung das Problem, dass die "Produktion" ihre Daten schnell haben muss. Wenn jetzt X Leute Anfragen an die Partition 2018 senden, von der die Produktion ebenfalls ihre Daten her bezieht, dann kann es trotzdem sein, dass die Auslastung so hoch ist, dass die Produktion stagniert.

 

[akretschmer]

ich glaube, wir drehen uns im Kreis. Die Argumente wurde mehrfach diskutiert.

 

[Leobahrt]

Guten Morgen akretschmer, heißt das wir sind schon zu einer Lösung gekommen und ich sehe diese nicht?

Wie gesagt, momentan haben wir eine Datenbank welche alle Daten hällt. Die Produktion bezieht ihre Daten von dieser Datenbank und andere Benutzergruppen können diese Datenbank ebenfalls anfragen. Wenn nun viele Anfragen kommen, wird das System langsam und die Produktion bekommt ihre Daten nicht schnell genug, was nicht sein darf.

Wenn ich die Daten Partitinoiere hätte ich zwar weniger Daten die abgesucht werden, sprich das ganze wird schneller, was gut ist. Jedoch ändert es nichts daran, dass das Problem immer noch vorhanden ist.
Bsp.: Wenn wir jetzt die Partition 2018 und Partition 2017-1950 haben. Ein Tag vor einem Meeting greifen 200 Leute auf die Partition 2018 zu. Das System wird langsam, die Produktion die ebenfalls ihre Daten von der Partition 2018 bezieht muss warten und die Produktion steht still.