INFF Plus Release 1
DV-Konzept
Performance beim Datenbank Import
Juri Urbainczyk
Version 1.0 - 16. November 2000
Inhaltsverzeichnis
1 Überblick *
2 Problematik *
3 Die "Schrauben" *
3.1 Logische Datenbankstruktur
*3.2 Konkurrierende Zugriffe
*3.3 Indexe
*3.4 Constraints
*3.5 Trigger
*3.6 Transaktionen
*3.7 Physikalische Datenbankstruktur
*3.8 Prepared Statements
*3.9 Blobs
*3.10 Embedded SQL
*3.11 Array Insert
*3.12 Datentransport
*3.13 Parallele Imports
*3.14 Stored Procedure
*3.15 Insert Cursor
*3.16 Daten verdichten
*4 Fazit *
In Softwareprojekten besteht häufig die Notwendigkeit, Daten aus dem Filesystem in eine relationale Datenbank zu importieren. Es handelt sich dabei meist um Massendaten, d.h. der Umfang der einzulesenden Daten ist beträchtlich und geht in die Gigabyte.
Eine der wichtigsten Anforderungen an einen solchen Applikationsdatenimport ist eine gute Performance. Der Import muß in solchen Zeiten durchführbar sein, in denen das System heruntergefahren ist (z.B. nachts oder am Wochenende) oder es muß hinreichend schnell erfolgen, damit Daten in der gewünschten Frequenz aktualisiert werden können (z.B. 1 mal am Tag). Manchmal wird man auch nur am Altsystem gemessen. Wenn das eine Hostdatenbank ist, kann die Hürde hoch liegen.
Das vorliegende Paper beschäftigt sich damit, mit welchen Mitteln dieses Ziel erreicht werden kann. Es wird erklärt, an welchen "Schrauben" gedreht werden kann, um eine optimale Performance zu erzielen. Kenntnisse von RDBMS und SQL werden vorausgesetzt.
Dieses Dokument basiert auf Erfahrungen des Autors in den Projekten INFF Plus (iteratec) im Jahr 1999/2000 und LEU (o.tel.o/LION) in den Jahren 1994-1996.
Nun ist die Anforderung, performant zu sein, an sich erst mal nicht schwierig zu erfüllen. Die Problematik ergibt sich dadurch, daß i.allg. die Datenmenge, die importiert werden soll, so groß ist, daß durch triviale Implementierungen kein Import in zufriedenstellender Zeit möglich ist.
Die Situation wird zusätzlich dadurch erschwert, daß ein Import häufig folgende weitere Aufgaben zu erfüllen hat, die ebenfalls nicht trivial sind:
Diese zusätzlichen Anforderungen an die Funktionalität des Importprogramms verhindern i.allg. auch den Einsatz der mitgelieferten Import-Tools der Datenbankhersteller.
Aus diesem Spannungsfeld ergibt sich, daß eine gute Performance eines Importprogramms nur unter Berücksichtigung der Anderen Anforderungen und durch Einsatz der notwendigen Mittel erreicht werden kann.
Die Performance eines Datenimports in ein DBMS wird im wesentlichen von der Datenbank beeinflußt. Man muß also zuallererst die Datenbankseite des Problems optimieren, bevor man sich den anderen Bereichen (Parsen und Lesen der Datei, Logik) zuwendet.
Leider wird beim Datenbankentwurf nicht genügend an den Datenimport und dessen Performance gedacht. Deshalb findet man häufig die Situation vor, daß es bereits ein fertiges Datenmodell gibt, in das nun importiert werden muß.
Idealerweise sollte man jedoch nur in eine Tabelle importieren. Während des Imports sollte auch möglichst kein Lesezugriff auf andere Tabellen erfolgen. Sollte es trotzdem notwendig sein, schlage ich vor, dies zu Beginn durchzuführen und die notwendigen Daten zwischenzuspeichern.
Wenn die Tabellen zu groß werden (zu viele Attribute haben), kann dies ebenfalls die Performance beeinträchtigen. Manche relationale Datenbanken können z.B. Sätze mit mehr als 2kByte Größe nicht mehr sinnvoll handhaben. In einem solchen Fall empfiehlt sich also die Aufteilung der Tabelle in zwei kleinere.
Die Datentypen der Tabellenspalten müssen ebenfalls berücksichtigt werden. Global kann man sagen, daß alle dynamischen Datentypen (solche, die in der Länge variabel sind) den Datenbankzugriff verlangsamen. Datentypen fixer Länge (z.B. CHAR(32), INTEGER oder DECIMAL) sind unkritisch.
Sehr nutzbringend ist auch eine logische Partitionierung der Daten. Dabei wird eine große Tabelle nach den unterschiedlichen Einträgen in einer Spalte aufgeteilt. Eine Adreßtabelle könnte man z.B. in verschiedene Tabellen nach Bundesland aufteilen. Die neuen Tabellen sind kleiner und lassen sich auch beim Import leichter handhaben. Indexe werden z.B. schneller aufgebaut.
Es kann sehr negative Auswirkungen haben, wenn während des Imports ein anderer Prozeß auf die gleiche Tabelle zugreift. Der konkurrierende Zugriff erzeugt Sperren (Locks), die den Import am Einfügen der Daten hindern können. Dies wird u.U. durch Page Locks noch verschlimmert, da dann auch noch weitere, initial nicht betroffenen Sätze gesperrt werden. Dies sollte unter allen Umständen verhindert werden.
Eventuell ist es möglich, die konkurrierenden Zugriffe ganz auszuschalten. Dazu muß der Import jedoch in Zeiten verlegt werden, in denen kein Betrieb stattfindet und das ist aufgrund von fachlichen Restriktionen nicht immer durchführbar.
Eine mögliche Abhilfe besteht darin, den Isolation Mode der Datenbank auf Dirty Read zu setzen. Dann kann es allerdings dazu kommen, daß ein lesender Prozeß die gerade im Import befindlichen Sätze in einem unvollständigen Zustand liest. Das ist i.allg. nicht wünschenswert.
Eine andere Möglichkeit ist die Verwendung von Schattentabellen. Für den Import wird dann eine exakte Kopie der zu verwendenden Tabelle(n) erstellt. In diese Kopien (die sogenannten Schattentabellen) wird dann importiert. Für den Zeitraum des Imports stehen diese Tabellen dem Importprozeß exklusiv zur Verfügung, es gibt also keine konkurrierenden Zugriffe. Ist der Import erfolgreich, wird die alte (originale) Tabelle gedroppt und die neue auf den Namen der alten umbenannt.
Manche Datenbanken (z.B. Informix) haben keine Funktionalität, um Tabellen umzubenennen. Dort kann z.B. mit Synonymen gearbeitet werden. Dies sind Pseudo-Tabellen, die eigentlich nur auf echte Tabellen verweisen. Lesende Zugriffe dürften in einem solchen Fall also nur über die Synonyme erfolgen.
Indexe haben einen sehr großen Einfluß auf die Performance eines Imports. Normalerweise werden sie angelegt, um die Geschwindigkeit beim lesenden Zugriff zu steigern. Sollen jedoch neue Sätze in die Datenbank eingefügt werden, so müssen die Indizes regelmäßig erneuert werden, was natürlich Zeit kostet.
Es empfiehlt sich also dringend vor dem Import alle Indexe von der Tabelle herunterzunehmen und anschließend wieder anzulegen. Manche Datenbanken bieten auch die Möglichkeit, die Indexe nur auszuschalten und hinterher wieder zu aktivieren. Dies spart zusätzlich Zeit, da der Aufbau des Index selbst auch einige Zeit in Anspruch nimmt.
An dieser Stelle muß vor einem weiteren Problem gewarnt werden: falls Daten in die Datenbank importiert wurden, die aus irgendeinem Grund inkonsistent sind, kann der Index hinterher eventuell nicht mehr angelegt werden. Diese Vorgehensweise ist also nur dann sinnvoll, wenn eine Konsistenzprüfung im Importprogramm selbst implementiert wird.
Constraints haben genau wie Indexe einen großen Einfluß auf den Import. Constraints dienen hauptsächlich der Konsistenzsicherung (z.B. Foreign Key, NOT NULL). Intern werden von der Datenbank zur Umsetzung der Constraints wiederum Indizes angelegt, weshalb hier das gleiche wie auch das für Indizes gesagte gilt.
Die Umsetzung der Constraints, d.h. die Überprüfung der Eingabedaten auf ihre Korrektheit im Sinne des Constraints, benötigt ebenfalls Zeit, so daß die Performance hier noch um einen Weiteren Faktor absinkt.
An dieser Stelle muß vor einem weiteren Problem gewarnt werden: falls Daten in die Datenbank importiert wurden, die aus irgendeinem Grund inkonsistent sind, kann das Constraint hinterher eventuell nicht mehr angelegt werden. Diese Vorgehensweise ist also nur dann sinnvoll, wenn eine Konsistenzprüfung im Importprogramm selbst implementiert wird.
Mit einem Trigger bietet die Datenbank die Möglichkeit, als Folge eine bestimmten Datenbankoperation eine Sequenz anderer Operationen durchzuführen. Zum Beispiel kann beim Einfügen eines bestimmten Wertes in eine Spalte, der gleiche Wert in eine andere Spalte eingetragen werden, ohne das dies explizit vom Client angestoßen werden muß.
Die Durchführung der Aktivitäten eines Triggers benötigt Zeit, da die Datenbank einmal die neuen Daten analysieren, den passenden Code für den Trigger finden und diesen dann ausführen muß.
Es empfiehlt sich also dringend, bei Datenbankimports eventuell vorhandene Trigger auf den Gesichtspunkt Performance hin zu untersuchen. Ja nach verwendetem DBMS kann es sinnvoll sein, statt eines weiteren, eigenen Inserts diesen durch einen Trigger durchführen zu lassen. Ein Grund könnte sein, daß die Daten dann nicht mehr per Interprozeß-Kommunikation an den Datenbankserver übergeben werden müssen.
Eine generelle Regel läßt sich aber hier nicht angeben und es muß im Einzelfall analysiert werden.
Bei der fachlichen Analyse einer Importfunktionalität sollte man die Frage stellen, welche Menge an Daten man kontrollierbar in die Datenbank einstellen will. Will man eventuell entweder alle Daten importieren oder – im Fehlerfall – keine? Oder möchte man auf der Ebene des einzelnen Datensatzes entscheiden, welcher verworfen wird und welcher importiert?
Diese Frage ist von grundsätzlicher Bedeutung, da sich dann die Menge der Transaktionen um Größenordnungen unterscheiden kann. Transaktionen wiederum können die Geschwindigkeit eines Programms entscheidend beeinflussen – vor allem Rollbacks.
Global kann man sagen: je weniger Transaktionen, desto besser. Die Datenbank muß für jede neue Transaktion Speicher zur Verfügung stellen und sie in ihre interne Verwaltung aufnehmen – das kostet Zeit. Hat man jedoch nur eine große Transaktion um alle Datensätze herum, kann bei entsprechender Datenmenge der Rollback-Buffer des DBMS überlaufen. Dieses Problem kann man z.B. dadurch lösen, daß man gar keine echten Transaktionen verwendet, sondern Schattentabellen (s.o.).
Ist nicht unbedingt eine Transaktionsverwaltung auf Datensatzebene nötig, so kann man z.B. alle 1000 Sätze ein Rollback bzw. Commit durchführen. Dies erfordert in der Implementierung zwar einige Arbeit, lohnt sich aber aufgrund des Performancegewinns im Vergleich zur Ein-Satz-eine-Transaktion Lösung.
Muß dennoch die Transaktion auf Datensatzebene durchgeführt werden, so empfiehlt es sich, die Konsistenzprüfung der Daten im Programm selbst vor Beginn der Transaktion zu machen. Dann kann man einen fehlerhaften Satz bereits vorab verwerfen und hat gar keine Transaktion dafür gestartet. Vor allem spart man sich dann die zeitaufwendigen Rollbacks.
Unter diesem Titel verstehe ich die Verteilung der einzelnen Tabellen und deren Fragmente über verschiedene Festplatten. Die korrekte Fragmentierung einer einzelnen Tabelle bringt zwar einen Performancegewinn für das Auslesen der Daten, ist jedoch eher nachteilig beim Einfügen.
Auf der anderen Seite kann es sinnvoll sein, verschiedene Tabellen auf verschiedenen Platten anzuordnen. Wenn z.B. innerhalb eines Datensatzes in unterschiedliche Tabellen importiert werden muß, würde man aufgrund der unnötigen Bewegung des Schreibkopfes der Festplatte Zeit verlieren (Der Kopf müßte immer zwischen den beiden Tabellen hin und her wandern.). Hat man jedoch jede dieser Tabellen auf einer separaten Festplatte, kann der Schreibkopf jeder Platte dort verbleiben, wo er benötigt wird, nämlich bei der Tabelle, in die auch wieder beim nächsten Datensatz importiert wird.
Prepared SQL Statements bieten im Vergleich zu normalem SQL Inserts einen klaren Performance Vorteil. Der Punkt ist, daß das Statement nur einmal gelesen und nur einmal der Zugriffsweg bestimmt wird. Bei jeder folgenden Ausführung des Statements kann das DBMS ohne diese Schritte direkt an die Arbeit gehen.
Nichtsdestotrotz stellen Prepared Statements nur eine Notlösung dar. Array Insert und Insert Cursor bieten eine bessere Performance und sollten daher bevorzugt werden.
Binary Large Objects (Blobs) werden in RDBMS i.allg. durch den Datentyp BYTE oder TEXT dargestellt. Sie werden spätestens dann notwendig, wenn man umfangreiche Binärdaten (Bilder, Musik,...) importieren will. Aber schon bei Texten, die man nicht auf 64k beschränken will, kann der Einsatz von Blobs notwendig werden.
Aus Performancegesichtspunkten muß dringend von der Verwendung von Blobs abgeraten werden. Dieser Datentyp wird von DBMS am langsamsten gehandhabt. Auf jeden Fall sollte die Möglichkeit der Datenkompression betrachtet werden.
Der Standardweg, um 3GL und 4GL Sprachen mit einem DBMS zu verbinden ist nach wie vor Embedded SQL. Meine Empfehlung lautet, dieses auch zu verwenden. Embedded SQL hat in der Flexibilität und in der Performance immer noch Vorteile gegenüber den von den Herstellern angebotenen objektorientierten Schnittstellen und SDKs. Die Informix C++-SDK ist z.B. nicht in der Lage ein Array Insert zu unterstützen, weshalb man gezwungen ist auf Prepared Statements auszuweichen.
Array Inserts stellen eine Besonderheit von Oracle dar. Sie werden von Informix in dieser Weise nicht unterstützt (dort gibt es Insert Cursor, s.u.). Ein Array Insert stellt meiner Meinung nach den schnellsten möglichen Weg dar, um Daten aus einem Anwendungsprogramm in das DBMS zu bekommen (native Loader aus o.g. Gründen ausgeschlossen).
Bei einem Array Insert kann man ein Array mit Datensätzen befüllen und dieses als ganzes an das DBMS übergeben, welches dann die Daten in die Tabellen einfügt.
Der Nachteil ist, daß i.allg. keine Transaktionen innerhalb des Arrays möglich sind, d.h. man kann nur das gesamte Array importieren oder gar nicht. Auch das Reporting wird schwieriger, da man keine Fehlermeldungen für einzelne Datensätze mehr bekommt.
Bevor die Daten in die Datenbank importiert werden können, müssen sie i.allg. zum DBMS transportiert werden. Lange Transportzeiten durch das lokale Netzwerk von einem Rechner X zum Datenbankserver mindern die Performance. Es empfiehlt sich daher die Imports immer auf dem gleichen Rechner durchzuführen, auf dem die Datenbank läuft.
Unter diesem Titel verstehe ich nicht das Starten von parallelen Importprozessen. Hier ist gemeint, daß ein Importprozeß mehrere Verbindungen zur Datenbank öffnet, um z.B. gleichzeitig in verschiedene Tabellen zu schreiben.
Wie das obige Beispiel zeigt, ist diese Technik sehr sinnvoll, wenn für einen Datensatz in mehrere Tabellen importiert werden muß. Ein Performancegewinn von mindestens einem Faktor 1.5 ist denkbar! Für einen Import in nur eine Tabelle macht das keinen Sinn, da sich die beiden Verbindungen gegenseitig blockieren und so die Performance herabsetzen würden.
An dieser Stelle wird darauf hingewiesen, daß viele SDKs und Frameworks nur das Aufbauen einer Verbindung pro Applikationsprozeß zum DBMS erlauben.
Stored Procedures bieten die Möglichkeit, Applikationscode innerhalb des Datenbankservers ablaufen zu lassen. Soll z.B. durch eine Stored Procedure die Konsistenz von mehreren Datensätzen überprüft werden, so müssen die Daten nicht jedesmal aus dem Datenbankserver an die Applikation übermittelt werden. Netz und Anwendungsclient werden also entlastet. Dies stellt für bestimmte Aufgaben einen enormen Geschwindigkeitsvorteil dar.
Die Relevanz von SPs für den Importvorgang selbst ist jedoch gering. Zudem muß von der Verwendung von SPs abgeraten werden, da durch die Vermengung von Logik und Speicherung die Schichtenarchitektur durchbrochen wird.
Die Insert Cursor stellen die Informix Variante von Array Inserts dar. Sie sind der schnellste Weg (bei Informix), um Daten aus dem Anwendungsprogramm in die DB zu bekommen. Um Insert Cursor verwenden zu können, muß Embedded SQL verwendet werden.
Informix empfiehlt die Verwendung von Insert Cursor innerhalb einer Transaktion, obwohl auch Transaktionen innerhalb des Cursors möglich sind.
Manchmal gibt es alternative Darstellungsmöglichkeiten von Daten in der Datenbank. Ein Flugtarif z.B. kann mit einem oder auch mit mehreren Abflughäfen dargestellt werden. Hat man nun einen weiteren Tarif der sich nur durch den Abflughafen von dem ersten Tarif unterscheidet, so kann man Importzeit sparen, indem man den Abflughafen des zweiten Tarifs zum ersten hinzufügt und dann nur den ersten Tarif importiert.
Idealerweise sollte diese Datenreduktion schon bei der fachlichen Analyse durchdacht werden, da häufig fachliche (und rechtliche) Fragen eine Modifikation der Daten verhindern. Ist das jedoch möglich, hat man ein enorm wichtiges Werkzeug, das mehr Performancegewinn liefern kann als jeder Implementierungstrick.
Wie man sieht gibt es eine Menge Ansatzmöglichkeiten für Tuning beim Datenimport. Welche Punkte relevant sind muß im Einzelfall entschieden werden.
Grundsätzlich sollte man bei der Optimierung die 80% der Datensätzeund Importvorgänge betrachten, die am häufigsten vorkommen. Es macht keinen Sinn, die Datensätze mit Hochdruck zu tunen, die nur einmal im Jahr importiert werden.