mysqldump massiv beschleunigen. Bitte.

[registrierter Gast]

Hallo.

Als Entwickler hole ich regelmäßig einen Dump der MySQL Datenbank des Livesystems und spiele diesen lokal ein.

Leider ist die Größe der Datenbank in den letzten Tagen massiv gestiegen, weshalb der Dump momentan 40min dauert. Für unterbrechungsfreies Arbeiten ist das natürlich hinderlich.

Damals konnte ich das Einspielen des Dumps noch durch Nutzung einer Ramdisk (http://icephoenix.us/stuff/how-to-move-mysql-storage-to-ramfs-or-tmpfs-partition/) beschleunigen. Damit war das Einspielen des Dumps immer zügig durch. Aber die Datenbank ist mittlerweile über 15GB groß und passt nicht mehr in den RAM.

Jetzt ist die Frage, wie ich das Dumpen und Einspielen beschleunigen könnte.

Behelfsmäßig habe ich eine weitere SSD mit ext2 (ohne Journaling) eingebaut und lagere dort die Datenbankdaten. Damit ist der Vorgang nun in 25-30 Minuten durch.
Das ist noch immer relativ lang.

Die Frage ist nun, wie ich den kompletten Vorgang weiter beschleunigen kann.

Ich dachte an eine schnellere SSD für die Datenbankdaten. Momentan ist es eine 128GB Crucial M4 (http://geizhals.de/crucial-m4-128gb-ct128m4ssd2-a626829.html). Eine Samsung SSD 850 PRO 512GB (http://geizhals.de/samsung-ssd-850-pro-512gb-mz-7ke512bw-a1136104.html) hätte z.B. die dreifachen 4K IOPS.
Eine SSC (http://geizhals.de/comay-bladedrive-g24-240gb-a1126529.html) (!!) wäre nochmals dramatisch schneller. Fraglich ist, ob diese unter Linux funktioniert.

Gibt es noch andere Wege, um das Einspielen des Dumps zu beschleunigen? :uponder:

Viele Grüße
gereggter Gast

[nalye]

Was ist mit Xtrabackup/Percona? Läuft recht schnell meiner Meinung nach

[Sumpfmolch]

Was ist der limitierende Faktor? Datendurchsatz hast du anscheinend nur 6-12 MB/s.

Wird das Backup komprimiert? Wie sieht die CPU Auslastung aus?

[Birdman]

I.d.R. hängt der Speed von mysql_dumps heutzutage alleine an der CPU, da nur ein CPU core verwendet wird und das ganze halt sehr sequenzell und dank standard sql statements nie wirklich schnell sein wird.
Wenn man die Relationen im Griff hat, kann man aber unterschiedliche Tables derDB von jeweils andern dump prozessen rausschreiben lassen und so eine manuelle parallellität hinbekommen.

Bei MyISAM könnte man auch mysql_hotcopy verwenden.

Ansonsten diese MySQL DB auf eine eigene LVM Partition legen und dann macht man halt einen Snapshot von der Disk erstellen.
Dazu müsste der MySQL Server aber wohl jeweils kurz gestoppt werden, oder man bastelt sich was mit flush & lock tables, bevor/während der Snapshot läuft.

[registrierter Gast]

Was ist mit Xtrabackup/Percona? Läuft recht schnell meiner Meinung nach
Damit gänge das Einspielen schneller, aber der Download der Datenbank dauerte länger, weil diese selbst komprimiert noch mehrere GB groß ist.
Aktuell dauert der Download des komprimierten Dumps (ca. 400MB) ca. 4 Minuten. Mehrere Gigabyte dauern entsprechend länger.

Was ist der limitierende Faktor? Datendurchsatz hast du anscheinend nur 6-12 MB/s.

Wird das Backup komprimiert? Wie sieht die CPU Auslastung aus?
Der ganze Vorgang läuft komplett gepiped. Vom dumpen, gzippen, übertragen per SSH, gunzippen, einspielen – alles ein Rutsch. Die CPU Auslastung liegt aktuell bei ca. 70%.
Als ich neulich noch die Ramdisk hatte, war die Auslastung bei fast 100%. Da limitiert die SSD.

I.d.R. hängt der Speed von mysql_dumps heutzutage alleine an der CPU, da nur ein CPU core verwendet wird und das ganze halt sehr sequenzell und dank standard sql statements nie wirklich schnell sein wird.
Wenn man die Relationen im Griff hat, kann man aber unterschiedliche Tables derDB von jeweils andern dump prozessen rausschreiben lassen und so eine manuelle parallellität hinbekommen.
Beim parallelen Einspielen verliert man ggf. die referentielle Integrität der Daten.

Ansonsten diese MySQL DB auf eine eigene LVM Partition legen und dann macht man halt einen Snapshot von der Disk erstellen.
Dazu müsste der MySQL Server aber wohl jeweils kurz gestoppt werden, oder man bastelt sich was mit flush & lock tables, bevor/während der Snapshot läuft.
Da würde wieder die Internetanbindung limitieren.


Ich glaube, der limitierende Faktor ist tatsächlich die immense Größe der Datenbank und ich muss mich damit abfinden. :(

[Sumpfmolch]

Der ganze Vorgang läuft komplett gepiped. Vom dumpen, gzippen, übertragen per SSH, gunzippen, einspielen – alles ein Rutsch. Die CPU Auslastung liegt aktuell bei ca. 70%.
Als ich neulich noch die Ramdisk hatte, war die Auslastung bei fast 100%. Da limitiert die SSD.


Dennoch kommst du insgesamt nur auf 10MB/s Datendurchsatz, wenn man nach deinen 15GB in 25min geht.

Hast du schon mal die Schritte einzeln laufen lassen und geschaut, was jeweils wieviel Zeit benötigt?
Sichergestellt, dass unnötige doppelte Komprimierung vermieden wird (mysqldump -C & gzip pipe oder ssh compression)?
Wieviele CPU Kerne haben 70% Auslastung?

[Gast]

Kann man nicht einfach tmpfs für sowas verwenden? Ist im Grunde genommen ähnlich wie ramdisk, nur ohne die Limitierung. Um die Wartezeit zu verringern könnte man einen Zeitplan auf dem Server anlegen wenn der freie Kapazitäten hat.
Ich glaube dass mysql eine Lösung für diese Aufgabenstellung hat, z.B. um das alles im Speicher zu verwalten oder um den Datendurchsatz zu optimieren, indem nur bestimmte Teile verwendet werden.

[Gast]

Benutze zum Dumpen den Parameter --single-transaction

[Marscel]

Lief mir gerade durch den Ticker, probier doch mal aus, ob das (inkrementell?) praktikabel ist: https://github.com/fastmonkeys/stellar

[Sumpfmolch]

Ich hatte schon überlegt, ob man nicht inkrementelle backups machen könnte und nur diese überträgt und einspielt...

[arcanum]

Lief mir gerade durch den Ticker, probier doch mal aus, ob das (inkrementell?) praktikabel ist: https://github.com/fastmonkeys/stellar

"How it works

Stellar works by storing copies of the database in the RDBMS (named as stellar_xxx_master and stellar_xxxx_slave). When restoring the database, Stellar simply renames the database making it lot faster than the usual SQL dump...

Warning: Please don't use Stellar if you can't afford data loss. It's great for developing but not meant for production."

wohl kaum geeignet für seine zwecke.

[(del676)]

Ka ob das funzt, aber nen cluster machen?
Dann den einen Clusternode rausnehmen und dort die DB dumpen. Derweilen laeuft das Production System weiter. Dann den Clusternode wieder dazustecken, syncen und weiterlaufen lassen.

[registrierter Gast]

Dennoch kommst du insgesamt nur auf 10MB/s Datendurchsatz, wenn man nach deinen 15GB in 25min geht.

Hast du schon mal die Schritte einzeln laufen lassen und geschaut, was jeweils wieviel Zeit benötigt?
Sichergestellt, dass unnötige doppelte Komprimierung vermieden wird (mysqldump -C & gzip pipe oder ssh compression)?
Wieviele CPU Kerne haben 70% Auslastung?
Das kommt schon hin. Ein Dump einzuspielen ist nicht einfach nur eine Datei zu kopieren. Die Textdaten werden in mysqls Binärformat umgewandelt und gleichzeitig der Index für die Daten aufgebaut.

Nein, keine doppelte Komprimierung. Es wird ein Kern mit 70% ausgelastet. Weiter oben wurde bereits erwähnt, dass ein Dump nur singlethreaded läuft. :(

Es läuft so (aus dem Kopf heraus), um den Overhead kleinstmöglich zu halten:

ssh user@server.de:"mysqldump --single-transaction -u user --password=pass database | gzip -c" | gzip -d > mysql -u root locale_database



"How it works

Stellar works by storing copies of the database in the RDBMS (named as stellar_xxx_master and stellar_xxxx_slave). When restoring the database, Stellar simply renames the database making it lot faster than the usual SQL dump...

Warning: Please don't use Stellar if you can't afford data loss. It's great for developing but not meant for production."

wohl kaum geeignet für seine zwecke.
That's right. ;)


Ka ob das funzt, aber nen cluster machen?
Dann den einen Clusternode rausnehmen und dort die DB dumpen. Derweilen laeuft das Production System weiter. Dann den Clusternode wieder dazustecken, syncen und weiterlaufen lassen.
Das Bottleneck wäre dann weiterhin das Einspielen des riesigen Dumps. (Oder verstand ich die Idee falsch?)

Edit:
Ach du meinst wahrscheinlich, dass man beim inaktiven Cluster parallele Dumps laufen lassen kann, weil die referentielle Integrität nicht verloren geht. Schließlich ist der Cluster inaktiv und die Datenbank ändert sich nicht. :uponder:

[Marscel]

wohl kaum geeignet für seine zwecke.

Stand gestern um die Uhrzeit noch nicht da (https://github.com/fastmonkeys/stellar/commit/b9575a683b0c3d00739c2f5ff3388a4233954ab2#diff-04c6e90faac2675aa89e2176d2eec7d8). :tongue:

[PatkIllA]

Brauchst du eigentlich immer wieder den neusten Stand?
Ich kann für meine Datenbankprogrammierung meistens einfach einen alten Einspielen.
Das spart schon mal das rüberziehen und beim ZUrücksetzen sollten sich auch ein paar Möglichkeiten ergeben. Notfalls übers Dateisystem.

[littlejam]

Da xtrabackup schon ausgeschieden ist :-(

1. Parallelisieren, mehrere Tabellen simultan
2. Indexe deaktivieren
3. Foreign-key-checks deaktivieren

Siehe hier (http://support.tigertech.net/mysql-large-inserts)

Ansonsten sinds halt 15GB, dumps sind nur bis zu einer bestimmten Größe praktikabel.

Grüße

[sei laut]

Einen lokalen Slave laufen lassen? Dann dümpeln die Daten nach und nach rein und nicht schlagweise. Sofern das praktikabel ist.

Aber: Ab einer bestimmten Größe sollte man sich fragen, ob man wirklich den ganzen! dump braucht - der große Batzen sind oft Text/Blob-Datenfelder, die man beim entwickeln und testen nicht braucht.

[Berni]

Was spricht denn eigtl. konkret gegen das schon angesprochene mysql_hotcopy?

Abgesehen davon halte ich so nen Komplettabzug auch für bissel fragwürdig denn auch das Erstellen der Abzüge dauert - egal wie mans macht - da doch ne ganze Weile und während der Zeit ist die produktive Datenbank dann ja nicht verfügbar...vielleicht wäre es ja auch organisatorisch möglich, das zu lösen (z.B. indem das irgendwann während der Wartungszeit/Nachts läuft, dann ists auch egal obs 10 oder 40 Minuten dauert).

[Ben Carter]

Wenn möglich: den Server für Wartungsarbeiten stoppen und auf Dateisystemebene kopieren. Das sollte in wenigen Sekunden machbar sein.

lg Ben

[littlejam]

Wenn möglich: den Server für Wartungsarbeiten stoppen und auf Dateisystemebene kopieren. Das sollte in wenigen Sekunden machbar sein.

lg Ben
xtrabackup (http://www.percona.com/doc/percona-xtrabackup/2.1/) wurde schon angesprochen, ist keine Lösung weil dann 15GB durchs Internet müssen statt 400MB komprimiertes Textfile.
Damit ist ein Stoppen unnötig.

Edit
Eine SSC (http://geizhals.de/comay-bladedrive-g24-240gb-a1126529.html) (!!) wäre nochmals dramatisch schneller. Fraglich ist, ob diese unter Linux funktioniert.

Läuft unter Linux, dieses spezielle weiß ich nicht, aber ein OCZ Revodrive läuft.

Grüße

[registrierter Gast]

Wir werden das Problem nun dahingehend lösen, dass wir im nächsten Sprint das Gros der Daten aus der Datenbank rausschmeißen und die aktuelle Datenhaltung / -filterung durch etwas anderes ersetzen. :uclap:

[littlejam]

Wir werden das Problem nun dahingehend lösen, dass wir im nächsten Sprint das Gros der Daten aus der Datenbank rausschmeißen und die aktuelle Datenhaltung / -filterung durch etwas anderes ersetzen. :uclap:
http://media.giphy.com/media/xLJE238iO1Cta/giphy.gif

Gruß

[littlejam]

Bin selbst vor ein paar Tagen drüber gestolpert...

Dumpen und wieder einspielen via SELECT INTO OUTFILE und LOAD DATA INFILE geht deutlich schneller als mysqldump.
Bei mir ists von 4h auf 40 Min. runter gegangen.
Entweder manuell Tabelle für Tabelle per for-Schleife machen oder - trommelwirbel - mit mysqldump :-\
Die Option heißt --tab.

Gibt ein paar Stolpersteine, aber normalerweise sollte das gehen.
Stolpersteine sind encoding und Filezugriffsrechte.

Edit:
--use-threads=xxx ist auch noch eine Option.

Grüße