Schleife in MySQL Abfrage

[Gabbahead]

Kann bitte jemand für mich die folgende Schleife in einem MySQL Befehl umsetzen ?

Der Hintergrund, ich habe eine wirklich riesige Datenbank (ca. 120GB), diese liegt mir sowohl als ca. 500 MySQL Dateien, als auch als 500CSV Dateien vor.

Aktuell spiele ich die MySQL Dateien auf unserem Linux Server per Schleife ein, das funktioniert soweit ganz gut, aber dauert bei der aktuellen Geschwindigkeit mindestens noch 1 1/2 Wochen.

Hier die dafür genutzte Schleife für die Shell:

for i in /var/www/vhosts/domain/sql_dump/*.sql; 
do   echo "file=$i";   
mysql -f -p -u username -ppassword dbname < $i; 
done

Ich wollte nun Local parallel die schnellere Funktion LOAD DATA INFILE nutzen, welche ebenfalls ganz gut funktionoiert. Nutze dafür aktuell HeidiSQL für Windows

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA INFILE 'G:/Users/User/Desktop/CSV-Dateien/Datein1.csv' IGNORE INTO TABLE tablename
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Da ich bisher noch keine Schleifen in einer MySQL Abfrage genutzt habe, stehe ich aktuell nen bissel auf dem Schlauch, und da ich ja Zeit sparen muss, wollte ich daher hier um eure Hilfe bitten mit die MySQL Abfrage so anzupassen, das alle 500 CSV Dateien automatisch per MySQL Abfrage importiert werden.

Ich hoffe ihr könnt mir da an dieser Stelle unter die Arme greifen.

Gruß Gabbahead

[erc]

Mysql bietet keine funktionalität um aus Dateisystem zuzugriefen. Wenn machst du das von aussen:

for i in /var/www/vhosts/domain/sql_dump/*.sql; 

mysql -f -p -u username -ppassword dbname -e "SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA INFILE '´$i´' IGNORE INTO TABLE tablename
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;"; 
done

PS: fürs einspielen von Dumps kannst du auch innodb_flush_log_at_trx_commit = 2 setzen. Das beschleunigt die Schreib Performance um Faktor 100 (gefühlt). Der Nachteil ist, dass die Datenbank nicht mehr ACID ist. Beim einspielen eines Dumps spielt das aber keine Rolle.

[Gabbahead]

Erstmal danke für deine schnelle Antwort.
Zu deiner Aussage, in wie fern meinst du das ?
Ich greife doch schon auf das Dateisystem zurück mit:

LOAD DATA INFILE 'G:/Users/User/Desktop/CSV-Dateien/Datein1.csv' IGNORE INTO TABLE tablename

Also einzeln klappt es ja wunderbar, er soll das ganze halt nur in einer Schleife für alle machen.

Oder was genau meinst du damit ? Kann MySQL den Ordner nicht in einer Schleife auslesen ?

Kann ich innodb_flush_log_at_trx_commit = 2 direkt in der Anweisung setzen, oder muss ich das zwingend in der my.cnf machen ?

EDIT: Das weiter Problem ist, die CSV Dateien liegen nur Local vor, auf dem Linux Server hab ich nur die SQL Dateien, daher würde erstmal auf die schnelle nur die Microsoft Konsole oder eben HeidiSQL funktionieren.
Dein Beispiel ist ja für Linux. Wo im übrigen noch *.sql steht

EDIT2: Ich hab den Wert nun direkt per phpmyadmin mit SET GLOBAL innodb_flush_log_at_trx_commit=2; gesetzt. Reicht das aus oder muss der MySQL Server dafür nochmal neu gestartet werden ?

[erc]

Oder was genau meinst du damit ? Kann MySQL den Ordner nicht in einer Schleife auslesen ?

Mysql bietet dafür keine Möglichkeit. Um das zu automatisieren brauchst du eine andere Sprache/Tool.

EDIT: Das weiter Problem ist, die CSV Dateien liegen nur Local vor, auf dem Linux Server hab ich nur die SQL Dateien, daher würde erstmal auf die schnelle nur die Microsoft Konsole oder eben HeidiSQL funktionieren.
Dein Beispiel ist ja für Linux. Wo im übrigen noch *.sql steht

Auch cmd unter Windows bietet dir möglichkeiten Dinge zu automatisieren.

EDIT2: Ich hab den Wert nun direkt per phpmyadmin mit SET GLOBAL innodb_flush_log_at_trx_commit=2; gesetzt. Reicht das aus oder muss der MySQL Server dafür nochmal neu gestartet werden ?

Ich weiß nicht ob das damit wirksam ist. Merken wirst dus auf jedenfall dran das der Import schneller ist.
Mit einem Neustart werden die Option die mit SET GLOBAL gesetzt wurden auf jedenfall verworfen.

[Gabbahead]

Aber MYSQL beherscht doch auch Schleifen...warum kann denn dann nicht die Abfrage in eine Schleife gepackt werden ? *grübel*

Über das cmd würde es natürlich auch gehen, aber irgendwie wollten die schleifen dort letztens nicht so recht, da ich bisher nie welche bei Windows gebraucht habe. Vielleicht hast du ja hier zu auch ein Tip

innodb_flush_log_at_trx_commit hab ich nun in der my.cnf gesetzt und den Server neu gestartet. Aber so richtig schneller ist es nicht geworden, auch wenn ich darüber nun inzwischen viel gelesen habe.

ca. 200.000 Zeilen liegt aktuell bei 12-15min. Dateigröße ca. 300Mb.

[akretschmer]

Aber MYSQL beherscht doch auch Schleifen...

SQL kennt keine Schleifen.

[erc]

ca. 200.000 Zeilen liegt aktuell bei 12-15min. Dateigröße ca. 300Mb.

Das geht ab wie eine Rakete, fast jedenfalls. Verwendest du überhaupt InnoDB? Ist eigentlich irgendwas konfiguriert wurden?

[Gabbahead]

SQL kennt keine Schleifen.

Wer sagt das ?

Es gibt für SQL WHILE und FOR Schleifen (LOOP)

http://wikis.gm.fh-koeln.de/wiki_db/...n/FOR-Schleife
http://wikis.gm.fh-koeln.de/wiki_db/...WHILE-Schleife
http://dev.mysql.com/doc/refman/5.0/en/loop.html
http://dev.mysql.com/doc/refman/5.0/en/while.html

@erc
War das jetzt ironisch gemeint ? ^^
Und ja die Datenbank läuft komplett auf InnoDB.



Also die Beispiele die ich dafür gesehen hab müssten ca. 80000 Zeilen bei ca. 3-4min liegen. Ich finde es daher immer noch ziemlich langsam. Dabei ist der Server selber ne Rakete. 50GB Ram 16 Kerne und 32 Threads. Load liegt bei 1,5 im Schnitt.

Für InnoDB hab ich aktuell folgenden Conf drin:

# * InnoDB

innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method= O_DIRECT
innodb_lock_wait_timeout = 180


Na ja aktuell bin ich bei 20 Millionen Zeilen, ein Ende ist daher bald in Sicht, aber an einer schnelleren Lösung bin ich nach wie vor interessiert.

[erc]

Ja, da war ein wenig Ironie mit dabei. Das ist sehr langsam. Man kann aber auch keine pauschele Aussage dazu treffen. Da sind zuviele unbekannte im Spiel. Von einer Minute pro GB bis 20 Minuten ist alles realistisch. Je mehr Indizes gesetzt sind desto langsamer wird das ganze.

PS: 50GB RAM und das Herzstück der 120GB Datenbank bekommt davon 1GB?

[lstegelitz]

Für den Import die Indexerstellung abschalten... weil sonst mit jedem neuen Schub Daten auch neue Indizes berechnet werden, und wenn das zu Kollisionen kommt, müssen auch vorhandene Indizes neu berechnet werden.. je mehr Daten, desto wahrscheinlicher sind Kollisionen (desto langsamer wird der Importprozess).

Ein paar Tipps für große Imports:
https://support.tigertech.net/mysql-large-inserts

[Gabbahead]

Stimmt du hast recht, den Wert hab ich garnicht so genau beachtet. Man sagt wohl er sollte ca. 80% des physikalischen Speichers haben.

innodb_buffer_pool_size = 40G
innodb_additional_mem_pool_size = 4G (?)
sollte daher der bessere Wert sein.

[Gabbahead]

Für den Import die Indexerstellung abschalten... weil sonst mit jedem neuen Schub Daten auch neue Indizes berechnet werden, und wenn das zu Kollisionen kommt, müssen auch vorhandene Indizes neu berechnet werden.. je mehr Daten, desto wahrscheinlicher sind Kollisionen (desto langsamer wird der Importprozess).

Ein paar Tipps für große Imports:
https://support.tigertech.net/mysql-large-inserts

Danke, werde ich gleich mal versuchen.

[erc]

@lstegelitz bei InnoDB kannst du die Indizes nicht deaktivieren. Du kannst sie nur löschen und danach neu erstellen.

@Gabbahead wenn der Server nur für die DB ist kannst du ruhig noch mehr auf den Buffer Pool geben. Wenn da noch andere Dienste drauf laufen musst du drauf achten das die sich nicht den Speicher gegenseitig klauen. Bei 20GB sollte das aber nicht zum Problem werden. innodb_additional_mem_pool_size kannst du auf 64MB setzen, da bist du schon sehr großzügig.

[Gabbahead]

In kürze wird darauf ein Shop (JTL) laufen, aber aktuell nutze ich ihn nur für die Aufbereitung der Datenbank. Wenn ich mir aber die aktuelle Datenbank so anschaue, wird die DB so oder so einen eigenen Server bekommen. ^^
Die Werte der DB werde ich nach der Aufbereitung wieder entsprechend anpassen.

Die Änderung der Werte haben aktuell leider nicht wirklich viel gebracht, bis auf das sich der Arbeitspeicher auffällig ruhig verhält, aber Virtuell dort nun mehr passiert. ^^

[Gabbahead]

Hat sich meine Frage bezüglich CSV Dateien und MySQL Schleife eigentlich erledigt ? Denn Local per CSV Import mit LOAD DATA INFILE geht der Import um ein vielfaches schneller. Nur ist es halt mühsam, das ganze mit über 500 Dateien händisch zu machen.

Vielleicht hat da ja jemand ne fertige Microsoft CMD Schleife für mich, oder eben eine MySQL Schleife, denn per CMD hab ich es irgendwie nicht so ganz hinbekommen. Es kam keine Fehlermeldung, sondern es passiert einfach nichts. ^^