Performancesteigerung durch myexport / myimport
eine Alternative zu dbimport / dbexport
Zieht man eine Datenbank von einer Platform auf eine andere um, so sind die Informix-Tools dbimport und dbexport meistens die 1. Wahl. Ein Problem ist, das dbexport / dbimport deutlich länger braucht eine Datenbank umzuziehen als ein Backup/Restore über ontape oder onbar.
Es gibt aber Tools, die auch einen Plattform- und Versionsübergreifenden Umzug deutlich schneller erledigen können.
Es gibt die Empfehlung für den dbimport / dbexport einige onconfig-Parameter umzusetzen, allerdings erreicht man damit nur geringfügige Steigerungen, gerade auf Maschinen die schon optimal konfiguriert sind. Der einzige Parameter der auf Maschinen, wo er noch nicht hochgesetzt wurde eine deutliche Steigerung verspricht ist NON_PDQ_QUERY_MEM. Hiermit erhöht man den für Sortieroperationen zur Verfügung stehenden Speicher und man kann damit beim Indexerstellen einen deutlichen Performance-Schub herausholen.
Weitere performantere Methoden um eine Datenbank umzuziehen sind der High-Performanc-Loader, die Enterprise-Replikation, das Laden über direkten Serverconnect und das Laden über externe Tabellen.
Der High-Performance-Loader ist eine deutlich schnellere Variante Datenbanken umzuziehen, aber leider recht umständlich zu verwenden. Hierzu müssen Scripte erstellt werden, die Jobs für alle Tabellen definieren und diese dann ausführen.
Die Enterprise-Replikation ist eine sehr elegante Variante Datenbanken umzuziehen, weil sie eine Plattform- und Versionsübergreifende Replikation ist. Sie ist zwar nicht wirklich schneller als andere Methoden, aber da es während der Replikation keine Downtime gibt ist sie optimal um auch große Datenbestände umzuziehen. Sie erfordert aber leider einige Vorraussetzungen wie Primary Keys oder Unique Indizes auf allen Tabellen. Die Möglichkeit ERKeys zu verwenden lindert diese Problem, braucht aber auf großen Tabellen lange Laufzeiten und erheblichen Platz im log. Logfile. Da beim Erstellen die jeweilige Tabelle gesperrt wird, verliert man hierbei einige Vorteile der minimalen Downtime. Bei unseren Versuchen gelang es uns sogar auf einen Non-Logging-Datenbank beim "alter table add erkey"-Kommando einen Long-Transaction-Abort zu erzeugen. Das liegt wahrscheinlich daran, das der Inhalt der ERKEY-Spalte per Sequence erzeugt wird und diese Aktion zu denen zählt, die auch in einer Non-Logging-Datenbank geloggt werden. Der Log-Space kann hier erheblich sein und die notwendige Größe für große Tabellen ist schwer im voraus abzuschätzen. Zusätzlich braucht man einen SBSpace, dessen Größe mindestens die größte Tabelle der Datenbank fassen sollte.
Wenn die Enterprise-Replikation ohne Schema-Umstellung nutzbar ist, ist sie sicher der eleganteste Weg, der es erlaubt auch große Datenbanken mit minimaler Downtime umzuziehen. Der Transfer der Daten erfolgt während die primäre Instanz noch im Produktionsbetrieb ist und kann dann auch meist beliebig lang dauern. Sind beide Instanzen synchron, wir einfach umgeschaltet.
Das Laden über einen direkten Serverconnect geht genause direkt über die Netzwerkverbindung. Man generiert für jede Tabelle Statements "insert in entfernte Tabelle select * from lokaler Tabelle". Wegen der Konsuistenz der Datenbanken darf aber während der Übertragung nicht anderweitig geschrieben werden und man muss sich Gedanken über Trigger und Constraint-Prüfungen in der Datenbank machen.
Der schnellste Weg Daten in die Datenbank zu bekommen ist das laden der Daten über externe Tabellen. Allerdings hat man auch hier das Problem das es kein Informix-Tool gibt, das eine Datenbank komplett umzieht.
Bei meiner Suche nach einer schnelleren Möglichkeit Datenbanken umzuziehen bin ich bei der IIUG (www.iiug.org) auf ein Tool von Art S. Kagel gestoßen "myimport / myexport" das verspricht so einfach wie dbimport / dbexport eine komplette Datenbank per Laden über externe Tabellen oder High-Performance-Loader umzuziehen.
Da wir dringend mehr Performance brauchten als dbexport / dbimport bieten habe ich diese Tools erst einmal getestet. Meine Testmaschine war eine Virtual Machine mit openSUSE 13.1 und Informix 12.10.FC2 mit 2 CPU's und 2GB Memory. Meine Testdatenbank war eine TPCC-Datenbank, deren Export etwa 1,2GB Daten umfasste.
Die Tools von Art S. Kagel erlauben sowohl den export als auch den import über verschiedene Methoden. Das sind normaler load/unload (der default), über den High-Performance-Loader mit verschiedenen isolation Modes und über externe Tabellen. Weiterhin ist es möglich mehrere Tabellen parallel zu Laden / Entladen und es können gleichzeitig die unload-Dateien komprimiert werden.
Als Voraussezungen benötigt man:
- das myexport/myimport Paket von Art S. Kagel. (Download)
- das Paket utils2_ak von Art S. Kagel (Download)
- und das Paket sqlcmd von Jonathan Leffler (Download)
Alle Pakete sind über den Link von der Website der IIUG zu bekommen und werden dort von ihren Maintainern regelmäßig aktualisiert. Über den Info-Link bekommt man eine ausreichende Dokumentation zu den Parametern von myexport / myimport. Der Aufruf dieser Tools ohne Komandozeilen-Optionen gibt eine Kurzhilfe zu den Parametern aus.
Nach dem entpacken der Pakete ist sqlcmd per:
./configure
make
zu überseten. Dazu muss auf der Maschine im $INFORMIXDIR oder an anderer Stelle eine Client-SDK installiert sein. Das Paket utils2_ak muss ebenfalls mit make übersetzt werden. Auch im Paket myexport muss einmal make aufgerufen werden. Danach reicht es die PATH-Variable auf den Pfad zu den Tools zu setzen und den LD_LIBRARY_PATH auf $INFORMIXDIR/lib und $INFORMIXDIR/lib/esql.
Da laut Aussage der IBM der schnellste Weg die externen Tabellen sind, haben wir nur diesen Weg getestet.
Den import und export habe ich mit folgenden Kommandos gestartet:
myimport -d data -l nolog -m -E tpcc
bzw.
myimport -l nolog -E -p -r -M 8 -m -d data tpcc
Das zweite Kommando begrenzt das parallele Laden von Tabellen auf 8 gleichzeitige - Standard ist 10. Damit kann man das parallele Laden an seine Maschinenkonfiguration anpassen. In meinem Test waren die CPU-VP's damit voll ausgelastet. Wie viele parallel geladenen Tabellen man einer CPU-VP zumuten kann, ist sicher von mehrerne Aspekten abhängig. Man darf auf keinen Fall die Performance des I/O außer Acht lassen. Am Besten man prüft durch kopieren einer sehr großen Datei eines anderen Dateisystems mittels cp auf das Dateisystem in denen die Chunks liegen und paralleler Messung mit vmstat die Schreibgeschwindigkeit des I/O Subsystems. Hat man während des Ladens diese Geschwindigkeit in etwa erreicht, so kann auch das parallele Laden von mehr Tabellen nicht schneller werden, auch wenn noch CPU-Leistung verfügbar ist.
Typisches Aussehen eines dbimport im Linux-Tool top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22003 informix 20 0 33988 2848 2180 R 60.50 0.139 0:13.86 dbimport
4141 informix 10 -10 1299196 1.057g 1.053g S 35.24 54.13 4:27.99 oninit
Typisches Aussehen eines myimport mit der Option -p (parallel) und -E (external Tables) im Linux-Tool top (der myimport wird nicht mehr angezeigt, da er keine Zeit aufnimmt):
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22839 informix 10 -10 1299200 0.996g 0.990g S 26.24 51.02 1:06.57 oninit
22744 informix 10 -10 1297840 1.138g 1.133g S 21.59 58.28 1:38.43 oninit
Der erste dbimport in die Datenbank aus einem dbexport von einer anderen Maschine brauchte hierbei 18:29 Minuten. Nach dem Import habe ich als nächstes den export getestet. Der dbexport lief hier in 1:35 Minuten. Der myexport über externe Tabellen im sequentiellen Mode brauchte 46 Sekunden, beim parallelen entladen von mehrerne Tabellen sogar nur 41 Sekunden. Weniger als die Hälfte beim export klingt vielversprechend.
| Aktion | dbexport/dbimort | myexport/myimport |
| sequentiell Laden | 18:29 Min. | 4:16 Min. |
| parallel Laden | - | 4:10 Min. |
| sequentiell Entladen | 1:35 Min. | 0:46 Min. |
| parallel Entladen | - | 0:41 Min. |
Der erste Versuch mit myimport und dem originalen dbexport endete mit Fehlern. Bei der Analyse musste ich feststellen das ich Foreign Key Constraints in meiner Datenbank habe, die beim Erstellen der Datenbank angelegt werden und dann das Laden einzelner Tabellen zuverlässig verhindern.
Art S. Kagel hat dazu einen Schalter -m für den myexport eingebaut, der das Script das die Datenbank erzeugt in zwei Teile trennt. Das Erstellen der Tabellen und das Erzeugen der Indizes, Trigger usw.
Dazu muss aber der Export auch mit myexport erzeugt werden. Nachdem ich den export so erzeugt habe und meinen myimport (auch m,it -m) gestartet habe, hat mich das Ergebnis doch überrascht. Der myimport war nach 4:16 Minuten fertig. Eine Überprüfung der Datenbank zeigte, das alle Sätze geladen sind.
Um die wahre Steigerung der Performance des Ladens richtig zu bewerten muss man wissen, das sowohl in den 18:30 Minuten des dbimports wie auch in den 4:16 Minuten des myimports ca. 2:30 Minuten für das Erzeugen der Indizes stecken. Effektiv muss man für das Laden also 16:00 Minuten mit 2:01 Minuten vergleichen. Das sind nur noch ungefähr 13% der Ladezeit des dbimport.
Mit Indexerzeugung ist das Ergebnis immer noch phänomenal. Nur ca 25% der kompletten dbimport Zeit. Aber sind die Datenbanken auch wirklich gleich?
Welche Unterschiede gibt es in der Datenbank zwischen dbimport und myimport?
Wenn ich eine dbexport ohne den Schalter -ss ausführe, verhält sich der dbimport so geschickt, das er einen ersten Extent generiert, der die gesamte Tabelle fasst (wenn es der Platz im dbspace erlaubt). Das tut der myimport, wenn er auf einen solchen dbexport angewendet wird nicht. Der myexport erzeugt eine SQL-Datei immer so, als wäre ein bexport -ss gelaufen. Dadurch werden die gesetzten Einstellungen pro Tabelle für den dbspace und die Größe der Extents immer übernommen. Hier müssen die dbSpaces der neuen Instanz aber zumindest genauso benannt sein wie in der alten Instanz oder ein nachbearbeiten des Scriptes ist notwendig.
Ein zweiter Unterschied ist die Art, wie Constraints erstellt werden. Während der dbexport / dbimport einen Primary Constraint in das "create table" - Statement schreiben, erzeugt das myimport erst einen "Unique Key" auf dem Feld:
CREATE INDEX "<user>".R107_17 ON "<user>".<tabellenname> (
<Spaltenname> ASC
) USING btree IN <dbspacename>;
und erzeugt erst danach einen Primary Key. Das erfolgt auch erst im 2.Teil des SQL-Scriptes bei der Indexerstellung und beschleunigt damit auch noch das Laden der Tabelle. Nach dem Erstellen des Index wird mit:
ALTER TABLE "<user>".<tabellenname> ADD CONSTRAINT PRIMARY KEY (
<spaltenname>
) ;
der Primary Key erstellt. Mit Foreign-Keys wird genauso verfahren.
CREATE INDEX "<user>".R107_17 ON "<user>".<tabellenname 1> (
<spalte tabelle 1>
) USING btree IN data;
ALTER TABLE <tabellenname 1> ADD CONSTRAINT FOREIGN KEY (
<spalte tabelle 1>
) REFERENCES <tabellenname 2> (
<spalte tabelle 2>
) ;
Der Unterschied ist, das ich im Fall dbimport einen automatischen Index und im Fall von myimport einen benannten Index habe.
Lösche ich den Constraint, so wird ein automatischer Index auch automatisch gelöscht. Der benannte manuell erstellte Index bleibt. Das ist ein Verhalten, das man als DBA kennen muss. Es ist auch nicht problematisch.
Der benannte Index hat aber einen großen Vorteil. Diese Art von Index kann ich in einem Optimizer-Hint benutzen. Automatische Indexe kann ich hier nur sehr eingeschränkt nutzen, da ihr Name mit einen Leerzeichen beginnt.
Trotz Nutzung von manuellen Indexen erzeugt ein Aufruf von dbschema oder dbexport wieder automatische Indexe in der Schema-Datei.
dbexport erzeugt auch immer eine Liste der Distributions (update statistics) am Ende der SQL-Datei. myexport tut das Standard-mäßig nicht. Man kann das Erzeugen der Distributions aber per Schalter -u erzwingen. Dabei entsteht eine 3. SQL-Datei, die man nach dem myimport aber manuell ausführen muß. Interessant ist die Möglichkeit beim myimport per Schalter -U die Distributions mittels des dostats-Tools erstellen zu lassen. Dieses Tool erzeugt die Statistiken nach dem von der IBM und der IIUG empfohlenen Methoden in Abhängigkeit von den Indizes auf den einzelnen Tabellen. Dieses Tool ist in den utils2_ak enthalten.
Probleme bei der Nutzing vom myexport / myimport
Man muss sich bewußt sein, das das myexport Tool die Datenbank nicht wie dbexport sperrt. Wird in eine solche Datenbank während des exports geschrieben, so kann es zu inkonsitenten Exports kommen.
Auch wenn man prinzipiell myimport mit einem dbexport benutzen kann, sollte man dies nicht tun. Zumindest nicht, wenn der dbexport ohne Schalter -ss lief, da hierbei wichtige Settings wie Positionierung von Tabellen und Indizes in bestimmten DBSpaces und Informationen über extent-Größen verloren gehen.
Enthält die Datenbank Referentielle Integrität, so funktioniert ein myimport auf einer dbexport-Ausgabe nicht, da die Indizes und die Foreign-Keys schon beim Erstellen der Datenbank erzeugt werden und damit die Daten nicht mehr sicher geladen werden können.
Unsere Empfehlung ist myexport / myimport immer im Paar einzusetzen.
Einen kleine Fehler im Script fand ich bei der Option zum parallelen Starten der imports ohne die -r Optio, die die Anzahl der parallelen Tabellen einschränkt. Hierbei wird das "&"-Zeichen um einen Prozess in den Hintergrund zu stellen in eine Variable geschrieben und so hinter die Befehlszeile gestellt. Das interpretiert die bash alledings nur als Parameter und nicht als Start im Hintergrund.
Die Zeile 388 von myimport muss entsprechend verändert werden:
statt:
sqlcmd -d $DBASE $ext_file $SEP
schreibt man:
if [[ $SEP = "&" ]] ; then
sqlcmd -d $DBASE $ext_file &
else
sqlcmd -d $DBASE $ext_file $SEP
fi
Dadurch werden mehrere Tabellen gleichzeitig geladen. Beobachtet man das mittels top, sieht man das alle CPU-VP's (oninit-Prozesse) in etwa die gleiche CPU-Last erzeugen, also die Prozesse auch wirklich parallel laufen.
Weiter Erweiterungen von myimport / myexport
Zusätzlich zu den bis hier besprochenen Erweiterungen bieten myimport / myexport noch weiter interessante Features.
myexport:
Option -e - hierbei werden schon beim myexport die SQL-Dateien erzeugt, die das Laden über external Tables steuern. Statt des DeLuxe-Mode des myimport wird der Express-Mode genutzt. In meinem Beispiel erzielte ich für das parallele Daten der Tabellen ein Ergebnis von 4:06 Minuten. In meiner Version war da allerdings noch ein kleiner Fehler. Im Script myexport Zeile 99 fehlte der Parameter 'e' in der Liste der erlaubten Parameter. Fügt man ihn hinzu funktioniert die Option wie beschrieben.
Option -F hierbei werden die Scripte so generiert, das Sätze die einem Constraint wiedersprechen in eine Violation Table gespeichert werden. Für jede Tabelle wird zusätzlich folgendes nach dem create table generiert:
START VIOLATIONS TABLE FOR <tabellenname>;
REVOKE ALL ON "<user>".<tabellenname> FROM public;
CREATE UNIQUE INDEX "<user>".p100_1 ON "<user>".<tabellenname> (
<spaltenname> ASC
) USING btree IN <dbspacename>;
SET INDEXES FOR <tabellenname> FILTERING WITHOUT ERROR;
Option -i -definiert den isolation Level, mit dem myexport liest. myexport sperrt die Datenbank nicht wie dbexport Exclusiv. Ein export einer Datenbank die aktiv in Nutzung ist, kann man aber in keinem Fall empfehlen, da man hier keine konsistenten Ergebnisse bekommt.
Option -D, -C, -S - hier wird mittels High-Performance-Loader entladen. 3 Option weil in Kombination mit dem isolation Mode.
Option -Z - Die unload-Dateien werden komprimiert. Dabei schluckt gzip in meinen Fall den größten Anteil an CPU-Leistung. Die export-Zeit verlängert sich deutlich auf 2:22 Minuten für den parallelen export. Da das gzip-Tool hier mehr CPU-Leistung braucht als der oninit sollte man mit dieser Option die Anzahl paralleler Tabellen reduzieren.
Option -U - Nutze zum export sqlunload.Damit entlädt myexport mit der selben Methode wie dbexport. Die Performance müsste dann auch in etwa auf das Gleiche Maß sinken.
Option -L und -P - Beide Optionen dürfen nur Gemeinsam verwendet werden. Es wird ein Benutzername -U und ein Passwort -P für den Connect übergeben. Achtung: da das per Kommandozeile erfolgt kann damit das Passwort auch per ps oder anderer Tools ausgespäht werden.
Option -m - Die Erstellung von Indizes und Constraint wird per separater Datei gemacht. Diese Option ist unbedingt zu empfehlen.
Option -u - Es wird eine dritte SQL-Datei erzeugt die die update statistics Befehle enthält um die Distributions zu erzeugen.
Option -v - Die update statistics Statements werden dabei an das Ende der 1. SQL-Datei generiert. Da bei zusammen mit Option -m die Index und Constrainterstellung in die 2. SQL-Datei generiert wird, werden so die update statistics Statements vor der Erstellung der Indizes ausgeführt. Hier sollte man lieber auf eine der myimport-Option zur Erstellung der Distributions ausweichen.
myimport:
Auch myimport hat einige zusätzliche Optionen, die zum Teil einen erheblichen Mehrwert darstellen.
Option -T - gefolgt von einem Dateinamen. Die Datei muss Paare von DBSpace-Name enthalten. Es wird immer der 1. DBSpace-Name in den SQL-Scripten durch den 2. ersetzt. Dadurch kann ein myexport / dbexport auch auch auf anders konfigurierten Instanzen eingespielt werden.
Option -e - Hierbei wird das SQL-Script nicht ausgeführt. Die Datenbank mit allen Tabellen muss existieren.
Option -p - Wird -p angegeben, so werden alle Tabellen parallel geladen. Wir empfehlen hierbei immer die Option -r anzugeben um die maximale Anzahl Tabellen, die parallel geladen werden bestimmen zu können.
Option -E - zum Laden werden externe Tabellen benutzt. Das ist der Fall, der in diesem Beitrag auf Performance untersucht wurde. Nutzen Sie diesen Parameter um die maximale Performance zu erreichen.
Option -H - zum Laden wird der High Performance Loader benutzt.
