mysql insert - performance/robustheit, "best practice"

[dermoritz]

ich hab folgendes Problem: aus einer Textdatei(ca. 100MB!) sind Zeilenweise Einträge in eine DB(MySq) zu schreiben. Jede Zeile wird im Moment per RegExp geprüft. Aus einem bestimmten Teil einer Zeile (Zahlen Code) wird zudem ein Objekt erstellt - falls diese Erstellung gut geht ist der Code korrekt.
Mein Paln ist im Moment die Textdatei per "Scanner" einzulesen, das Objekt zu erstellen, dann daraus ein String der Form "(wert1,wert2,....)" zu machen und diesen einer Stringliste hinzuzufügen. Mit dieser Liste macht man dan eine "Insert into ... (spalte 1,spalte 2) values list[1],list[2]...." -Abfrage.

Wäre das schonmal ok soweit?

Nun wird diese Textdatei in regelmäßigen Abständen eingelesen und ab dem 2. einlesen werden ca. 99% der Einträge "MySQLIntegrityConstraintViolationException" werfen ( Duplicate entry '1846715-01-3' for key 1, der primary key ist zusammengesetzt). da frage ich mich ob ich diese abfangen soll oder ob es besser ist vorher per select statement eine "Blacklist" zu erstellen und dann das insert zu machen. Fall man solch ein "multi row insert" auf der mySql-Konsole macht bekommt man ja eine Zusammenfassung - wievile gesamt, wieviel eduplicate errors und wieviele warnings. Ich hätte gerne ein ähnliches Verhalten in Java. Bieten einem die Exception(s) sowas oder muss man selbst mitzählen?

 

[dermoritz]

Ich hab per Profiler mir die Performance mal angeschaut. Sehr viel Zeit eht bei mir durch die Instanziierung/Prüfung der Objecte drauf die dann in die DB rein sollen. Also hab ich mir gedacht, ich prüfe vor dieser Instanziierung ob das Objekt schon in der DB ist (anhand von 3 primary keys). Diese überprüfung (Select count(*) from tabelle where primK1=x and primK2=y and primK3=z) dauert aber in der Summe viel länger als das instanziieren der Objekte.
Also scheint es das schnellste zu sein bei jedem Mal alles erneut "zu-inserten" bzw. die verarbeiteten Zeilen zu löschen, zu markieren oder die gesamte Datei am Ende "wegzukopieren"?!

 

[SlaterB]

lade den alten DB-Inhalt bzw. die Index-Kombinationen in Java und dort in eine HashMap oder so,
in der Zeit eines DB-Zugriffs kannst du speicherintern tausend Dinge prüfen

und in MYSQL kann man glaube ich schneller Daten aus einer Textdatei importierten:
MySQL :: MySQL 5.1 Referenzhandbuch :: 3.3.3 Daten in Tabellen einladen

also:
im Programm prüfen was zu importieren ist, diese Befehle alle in eine Textdatei,
die am Ende einmal importieren

 

[dermoritz]

Danke für die Hinweise,

der 2. Tip kommt aber leider nicht in Frage, da die Daten aus der Textdatei nicht 1:1 in der DB landen. Vielmehr muss ich sie überprüfen(inkl Prüfsummenberechnung) und leicht umformatieren. Und dieser Prozess ist recht Zeitaufwendig. Der erst Import(DB noch leer) wird demnach lange dauern.
Was ich mich Frage ist was schneller/sicherer geht: Im Moment lade ich wie gesagt alle "Values" Klauseln(100000+ a 5 int-Werte) in ein riesiges String-Array (ist das Speichereffizient?) und baue (Stringbuilder) dann daraus eine entsprechend große Insert -Abfrage. Ist das prinzipiell OK? Gibt es eventuell etwas effizienteres als String-Array und StringBuilder? Oder sollte man prinzipiell in Blöcken arbeiten?

Das Problem mit dem wiederholten Insert schon vorhandener Einträge hab ich übrigens über umbenennung der Textdatei gelöst. Neu Einträge landen dann immer in einer neuen/leeren Textdatei und nur diese wird an das Programm verfüttert.

 

[SlaterB]

> der 2. Tip kommt aber leider nicht in Frage, da die Daten aus der Textdatei nicht 1:1 in der DB landen. Vielmehr muss ich sie überprüfen(inkl Prüfsummenberechnung) und leicht umformatieren.

nachdem du mit diesem Schritt fertig bist hast du x000 Datensätze im Java-Speicher,
diese kannst du nun direkt an die Datenbank schicken (einzeln sicher schlecht, zusammen vielleicht machbar, weiß nicht),
oder in eine neue Textdatei schreiben und dann diese von MySQL importieren lassen

-------

was du da mit String[] und tausenden Datensätzen macht kann wohl niemand aus den wenigen Sätzen beurteilen,
StringBuilder klingt aber gut,

kleine Performance-Test-Hilfe: kommentiere einzelne Programmteile aus, z.B. das Datei einlesen, das Formatieren, was immer das ist oder alles mit DB,
je nachdem wie lange der Rest dauert kann man ungefähr vermuten was ein Teil kostet,
wenn du alles ausklammerst nur nur noch das Einlesen und String[]/Builder-Aktionen drin hast und das recht schnell ist,
dann musst du über generelles String-Management nicht weiter nachdenken

 

[dermoritz]

Was mir im Moment sorgen macht ist der Speicherverbrauch. wenn die Textdatei 15-20mb groß ist, ist dann ein StringArray was das ganze mehr oder weiniger speichern soll ca. genauso groß? Das wäre ja kein Problem. Der Abfrag-Insert-String hätte dann nochmal dieselbe Größe - wäre auch noch kein Problem.

Es Zeile für Zeile nocheinmal in eine Textzeile schreiben wäre nur sinnvoll wenn ich mit dem Speicher nicht hinkäme?! Das würde dann alles sehr ewig dauern - alleine das Zählen der Zeilen einer Textdatei mit 150000 Zeilen dauert ewig (Programm läuft seid ca. 10min und ist noch nicht fertig, ich benutze wie gesagt die "Scanner"-Klasse)

 

[SlaterB]

deswegen einzelne Programmteile testen,
hier ein Testprogramm:

public class Test
{
    public static void main(String[] args)
        throws Exception
    {
        long time = System.currentTimeMillis();
        System.out.println("start");
        File f = new File("test.txt");
        BufferedWriter b = new BufferedWriter(new FileWriter(f));
        for (int i = 0; i < 160000; i++)
        {
            b.write("eine lange Zeile1");
            b.write("eine lange Zeile2");
            b.write("eine lange Zeile3");
            b.write("eine lange Zeile4");
            b.write("eine lange Zeile5");
            b.newLine();
        }
        b.close();
        System.out.println((System.currentTimeMillis() - time) + " file size: " + f.length());
        Scanner s = new Scanner(f);
        List<String> lines = new ArrayList<String>();
        while (s.hasNext())
        {
            lines.add(s.nextLine());
        }

        Runtime r = Runtime.getRuntime();
        long mem = r.totalMemory() - r.freeMemory();
        System.out.println((System.currentTimeMillis() - time) + " lines: " + lines.size() + ", mem: " + mem);
    }
}

Ausgabe:

start
219 file size: 13920000
2844 lines: 160000, mem: 36954632

das Programm erzeugt sich gar erst die Textdatei, 160.000 Zeilen, 14 MB,
danach alles eingelesen, 37 MB Speicherverbrauch (Faktor 2-5 Mehrverbrauch kann man ruhig annehmen)

alles zusammen unter 3 Sekunden

-------

> Es Zeile für Zeile nocheinmal in eine Textzeile schreiben wäre nur sinnvoll wenn ich mit dem Speicher nicht hinkäme?!

ich beziehe mich hauptsächlich auf die Dauer der Übertragung an die DB,
jedes Insert einzeln wäre ewig langsamer, aber vielleicht kann man auch aus Java tausende übergeben, 'Batch' und so,
genaues kann ich da nicht sagen

was ich weiß ist, wie oben zu sehen, dass man in wenigen Sekunden die halbe Festplatte an Text vollschreiben und auch lesen kann

 

[fastjack]

Hört sich nach einem normalen Datenimport an. Du bekommst eine Eingabedatei, baust daraus Objekte und fügst diese dann in eine Datenbank ein.

Da gibt es mehrere Möglichkeiten zur Steigerung der Performance, die auch ein wenig von den eigentlichen Daten abhängen :

* Eingabedatei besteht aus alten Daten (verändert oder gleichbleibend) und neuen :
Du könntest die Tabelle löschen und einfach alles wieder inserten. Bei MySql habe ich mit dieser Strategie gute Erfahrungen gemacht. --> keine Duplicate-Key Errors. Nicht empfehlenswert, wenn Du einige Millionen Zeilen einfügen willst
Oder Du verwendet die Syntax "insert into ... on duplicate key update ...". Hierbei wird bei einem doppelten Schlüssel geupdatet und Du erhälst keinen Fehler.

* Eingabedatei besteht nur aus neuen Daten :
Tja, der beste Fall. Einfach nur inserten

* Allgemein :
In Paketen arbeiten. Das bedeutet z.B. 1000 Zeilen der Eingabedatei bearbeiten, 1000 Objekte erzeugen und einfügen/updaten. Speicher aufräumen (Listen/Objekte leeren/nullen, VM zur GC ankitzeln). Mit den nächsten 1000 weitermachen usw. 1000 Soll nur ein Beispiel sein, je nach Power kann man das natürlich varieren.
Wenn möglich, Transaktion(en) nutzen. Oder, wenn auf der Datentabelle/mit der Datenbank zusätzlich noch gearbeitet wird, Pausen einplanen. Ein paar Millisekunden reichen schon aus, um das Dauerfeuer auf die Datenbank zu unterbrechen/pausieren. Andere Nutzer werden dir es danken

Die Datenbanktabellen regelmäßig durch "optimize" optimieren, Indizes setzen etc.

Mit MySql (Diablo-Java, MyIsam /DB-Server: FreeBSD, DualCore ca. 2*3Ghz, 16GB) habe ich bis zu 50k-60k Inserts pro Minute durch Firmennetz erreicht.

 

[dermoritz]

danke für die tips!

in meinem fall (komplett neue daten) ist das Hauptproblem das erzeugen der Objekte. Da ihr sehr hohe Performance erreicht scheint da bei mir noch Optimierungspotential zu sein (bei mir brauchen 1000 inserts 2min). Das Problem ist das ich mir nicht sicher bin ob ich O(n) habe bei speicher und cpu . Denn wenn irgendwann der Garbage -Collector beschäftig ist wird unberechenbar.
Lange Rede kurzer Sinn ich werde noch etwas Testen und eure Tips einfließen lassen und mich melden...

 

[LCS]

Hallo
Sorry wenn ich mich hier einfach so dranhänge, aber das Thema passt so gut, weil ich gerade auch so was mache. Ich hab in den letzen Jahren fast ausschließlich in Delphi/Pascal programmiert und schwenke jetzt gerade wieder auf Java um.

Wenn möglich, Transaktion(en) nutzen. Oder, wenn auf der Datentabelle/mit der Datenbank zusätzlich noch gearbeitet wird, Pausen einplanen. Ein paar Millisekunden reichen schon aus, um das Dauerfeuer auf die Datenbank zu unterbrechen/pausieren. Andere Nutzer werden dir es danken

Genau mein Problem. Könnte mir jemand ein paar Stichworte zur Transaktionssteuerung Java/MySQL geben? Ich brauch erst mal nen Ansatz zum experimentieren. Und ein kleiner Tipp zur Pause innerhalb der Verarbeitungsschleife wäre auch ganz nett.

Gruss
Lothar

 

[dermoritz]

Aus meiner wirklich bescheidenen Erfahrung würde ich sagen, falls die Datenbankanwendung komplexer wird und man Transaktionen braucht, sollte man eventuell JPA2.0 (Java Persistence Api) oder was ähnlcihes in Betracht ziehen. Bei mir ist es wirklich ganz ganz billig - eine Tabelle mit 5 oder 6 Werten.

 

[fastjack]

Bei MySQL mußt Du die Tabellen mit dem Typen InnoDB anlegen, damit sie Transaktionen unterstützen. Standard ist MyISAM. Eine Pause in der Verarbeitungsschleife kannst Du z.B. mit

long millisekunden = 1000;
Thread.sleep(millisekunden);

machen.

 

[LCS]

Danke, das sleep hatte ich mittlerweile selbst gefunden

Die Tabellen in meiner Datenbank sind alles InnoDB Tabellen. Dass es im Connection Objekt commit() und rollback() gibt, hab ich inzwischen auch schon rausbekommen. Die Frage die bleibt ist, wie starte ich die Transaktion?

Gruss
Lothar

 

[SlaterB]

public void setAutoCommit(boolean autoCommit)
throws SQLException

Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.

Connection (Java 2 Platform SE v1.4.2)

kein Tutorial?..

 

[LCS]

Das war genau das, was noch gefehlt hat. Danke.

[OffTopic]
Tutorials bringen mich meist nicht so recht weiter. Meine Java Grundlagen liegen zwar schon 10 Jahre zurück, aber das kommt ganz gut wieder. Was ich bräuchte wäre eine Suchmaschine wo ich eine Delphi Klasse oder Methode eingebe und als Ergebnis kommt die Java Äquivalent raus. Wenn du sowas kennst.. :meld:
[/OffTopic]

Gruss
Lothar

 

[fastjack]

Alternativ kannst Du die Transaktionssteuerung auch manuell mit den Methoden der Statement-Klasse als normale Queries absetzen :

begin - startet die Transaktion
commit - führt sie aus
rollback - setzt sie zurück

Es kann sein, das Du vorher das Auto-Commit abschalten mußt, das weis ich jetzt nicht so genau. Das geht dann über Connection.