[MySQL] Optimierung eines Kopiervorgangs der Daten zwischen Datenbanken.

[Romanticus]

Hallo alle,

ich habe folgende Situation:
eine Tabelle der Produktiven DB mit 641000 Datensätzen (Tabellengröße - 2,9 GB). Auf dieser Tabelle ist ein View definiert, der nur bestimmte Spalten anzeigt. Die Daten dieses Views müssen in eine andere Tabelle in einer anderen Datenbank geschrieben werden. Dies erledigt ein kleines Java-Programm, das die Daten per JDBC ausliest und schreibt. Da ich nicht die gesamte Tabelle auf einmal auslesen kann (OutOfMemory-Exception), lese ich die Daten in Blöcken von 1000 Datensätzen aus und schreibe sie in die Ziel-Tabelle.
Rein technisch ist es ein prepared statement mit einer LIMIT-Beschränkung. Die Limit-Begrenzungen werden in einer Schleife immer um 1000 erhöht bis das Ende der Tabelle erreicht ist.
Dies funktioniert auch soweit, nur bricht die Performance mit der Zeit drastisch ein - die ersten 100000 Datensätze wurden in wenigen Sekunden kopiert, bei 400.000 Datensätzen dauerte das Auslesen eines 1000er-Blocks z.T. mehr als 120 Sekunden.
Das ist auch soweit einleuchtend, da das Statement keinerlei Bezug auf Indizes liefert und somit für jeden 1000er-Block ein Fullscan der Tabelle gemacht wird.
Meine Frage - gibt es eine Möglichkeit diesen Vorgang zu beschleunigen? Auf jeden Fall ist mir klar, dass LIMIT an dieser Stelle absolut unpassend ist.

Viele Grüße,
Romanticus

 

[Thomas Darimont]

Hallo,

die Frage ist, ob das Java Programm nach dieser Datenmenge langsam macht (wegen Memory leak -> Garbage Collection)
oder ob MySQL dann so lange braucht.

Folgendes wird schneller sein:
1) Erzeuge in der Quelldatenbank eine neue temporäre Tabelle über ein Select * mit den Daten der View.
2) Exportiere die temporäre Tabelle mit mysqldump --extended-insert -compress -quick .... > dump.sql
3) Zip das dump.sql und kopiere es auf das Zielsystem
4) Auf dem Zielsystem extrahierst du dann das dump.sql.zip
5) Danach benutzt du mysqlimport um das dump.sql wieder einzuspielen.

Das kann man alles über ein, zwei kleine Skripts automatisieren.

Ansonsten hat squirrel sql noch ein dbcopy Plugin:
http://squirrel-sql.sourceforge.net/

Du kannst auch über ssh via mysqldump direkt vom zielsystem auf das quellsystem zugreifen.
http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html

Gruß Tom

 

[Romanticus]

Der Flaschenhals ist in diesem Fall die Datenbank - die Queries hängen ewig (>120 Sec) in der Processlist. Das Java-Programm belegt zwischen 15 und 17 Mb im Speicher, also nicht wirklich dramatisch. Danke für den Lösungstip - ich werde das mal ausprobieren.

Grüße,
Romanticus

 

[Romanticus]

Ich habe das wie folgt gelöst - ich dumpe die Ergebnisse des Views in ein File mit

SELECT * FROM view INTO OUTFILE "C:/temp/dump.sql"

und lade die Daten anschließend mit LOAD DATA INFILE in die Zieltabelle. Das Ganze dauert bei der größten Tabelle die ich habe etwa 3,5 Minuten, das Script brauchte da mehr als eine halbe Stunde.

Aber ich habe noch eine Frage zu den Oracle bzw. DB2 Experten - ist das Problem mit dem LIMIT in DB2 und Ora auch vorhanden oder sind die so intelligent und benutzen einen internen Zeilenzähler?