Warum dauert die MariaDB Rücksicherung so lange

Ohne jetzt jeden Schritt gelesen zu haben, kann ich Dir versichern, dass DB sichern und restore mühsam sein können, wenn man jeden Datensatz einzeln bearbeiten muß.

Ate E. schrieb:

Will man die DB zurücksichern, dauert es gefühlt ewig. (DB Sicherungsdatei ungefähr 45 MB, Dauer ungefähr 1 Stunde)

Das kann auch 3 Tage dauern, wenn die DB etwas grösser ist, trotz SSDs als Platz für die Tablespaces. Und bei HDDs ist es naturgemäss weitaus langsamer als bei SSDs.

Dieser Backup ist allerdings ein SQL Dump, weshalb der Inhalt als SQL Kommandos eingelesen wird. Das ist der besste Weg, um Inkonsistenzen aus dem Weg zu gehen, aber mitnichten der schnellste Weg, Backups einzulesen.

Und wie kann ich den MySQL Dump am schnellsten wieder zurücklesen?

Mit SSD als Ziel. SQL auszuführen braucht halt Zeit und wenn du nur SQL als Backup hast, wirds nicht schneller.

Wenn du diese Situation vermeiden willst, solltest du eine Form des Backups verwenden, die nicht auf mysqldump basiert, sondern die Tablespaces kopiert. Das ist allerdings etwas von der Version abhängig, und ob MySQL oder MariaDB.

Lu schrieb:

wenn man jeden Datensatz einzeln bearbeiten muß.

Das ist hier genau die Frage, wie sehen deine gesicherten Daten aus? Jedes SQL-Kommando einzeln und nicht im Blockmodus? Wird ein etwaiger index zuvor ausgeschaltet und erst wenn alle Daten vollständig sind neu erzeugt? Von wievielten Datensätzen wir hier überhaupt geredet?

Ich kenn es eher von mysqldump, da gibt es zig Optionen, die zum Teil erhebliche Auswirkung auf die Dauer haben können:

• https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html

aus deiner my.cnf:

1

innodb_buffer_pool_size = 16M

2

innodb_log_file_size = 2M

3

innodb_log_buffer_size = 1024M

das ist ja wohl vollkommen für den Arsch. Welcher Hirntote hat das konfiguriert?

Der InnoDB Buffer Pool ist der in-Memory Buffer für jegliche InnoDB Tabellen. Immer wenn etwas von einer InnoDB Tabelle gelesen wird, wird die entsprechende Page von der Platte in diesen Buffer gelesen und bleibt dort bis der Platz für eine andere Page gebraucht wird. Dito beim Schreiben: die Page wird von der Festplatte in den Buffer geladen und dort modifiziert. Dann bleibt sie solange wie möglich im Buffer, damit nicht ein Update von ein paar Bytes die ganze Page (16KB!) wegschreiben muß (das Phänomen nennt sich write-amplification). In deinen InnoDB Buffer Pool passen gerade mal 1000 Pages. Das ist lächerlich wenig wenn du 12G RAM hast.

Das InnoDB Logfile (redo Log, write ahead log) wird bei jedem Page-Update geschrieben. Allerdings ohne write-amplification. Durch die begrenzte Größe wird aber irgendwann (mit deiner Konfiguration nach 2x 2MB an Schreibzugriffen) die älteste Transaktion im Log überschrieben. Und spätestens dann muß die Page, auf der diese Transaktion stattgefunden hat, auf die Platte geschrieben worden sein. Durch die lächerlich kleine Log-Größe zwingst du InnoDB dazu laufend Pages rauszuschreiben.

Schließlich der Log Buffer. Der puffert Schreibzugriffe ins Log. Bei innodb_flush_log_at_trx_commit = 1 erzwingt jedoch jedes COMMIT (und bei auto-commit sogar jedes schreibende SQL-Statement) daß das Log geflushed wird. Den Log Buffer größer zu machen als das Log selber ist jedoch in jedem Fall absoluter Unsinn.

Vorschlag:

1

innodb_buffer_pool_size = 1G

2

innodb_log_file_size = 256M

3

innodb_log_buffer_size = 8M

Das ist zwar immer noch lächerlich wenig. Aber auch deine Datenbank ist mit gerade mal 45M (für den SQL-Dump!) lächerlich klein.

Die 45MB Dump sollten dann in wenigen 10s restored sein.

Ate E. schrieb:

Und wie kann ich den MySQL Dump am schnellsten wieder zurücklesen?

Indem du ihn nicht mit mysqldump erzeugst, sondern z.B. mit mydumper. Das erzeugt mehrere parallele Streams die dann parallel restored werden können (mit myloader). Brauchst du aber bei deiner winzigen Datenbank nicht.

(prx) A. K. schrieb:

SQL auszuführen braucht halt Zeit und wenn du nur SQL als Backup hast, wirds nicht schneller.

45MB an SQL-Statements sind nicht die Welt. Das sollte auch ein schwächlicher Server in höchstens einer Minute schaffen.

Irgend W. schrieb:

Das ist hier genau die Frage, wie sehen deine gesicherten Daten aus? Jedes SQL-Kommando einzeln und nicht im Blockmodus? Wird ein etwaiger index zuvor ausgeschaltet und erst wenn alle Daten vollständig sind neu erzeugt?

mysqldump (sowohl von MariaDB als auch von MySQL) erzeugt standardmäßig multi-row INSERTs und die Indizes werden erst erzeugt, wenn die Daten gelesen sind. Ich weiß aber nicht, ob der Dump von mysqldump erzeugt wird und nicht etwa von phpMyAdmin.

Axel S. schrieb:

innodb_buffer_pool_size = 1G innodb_log_file_size = 256M innodb_log_buffer_size = 8M

Werde ich probieren!

Die meisten Tabellen sind aber MyISAM! (Siehe Anhang)

Hier nochmals das Script für das Backup:

1

#!/bin/bash

2

KEEP=56

3

BACKUPS=`find /volume1/Backups/hour -name "MariaDBDump_*.gz" | wc -l | sed 's/\ //g'`

4

while [ $BACKUPS -ge $KEEP ]

5

do

6

ls -tr1 /volume1/Backups/hour/MariaDBDump_*.gz | head -n 1 | xargs rm -f 

7

BACKUPS=`expr $BACKUPS - 1` 

8

done

9

DATE=`date +%Y_%m_%d__%H_%M`

10

rm -f /volume1/Backups/hour/.MariaDBDump_${DATE}.gz_INPROGRESS

11

/volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysqldump --opt -uroot -p'KurzPfurtz017OPk!' --all-databases | gzip -c -9 > /volume1/Backups/hour/.MariaDBDump_${DATE}.gz_INPROGRESS

12

mv -f /volume1/Backups/hour/.MariaDBDump_${DATE}.gz_INPROGRESS /volume1/Backups/hour/MariaDBDump_${DATE}.gz

13

exit 0

Axel S. schrieb:

Indem du ihn nicht mit mysqldump erzeugst, sondern z.B. mit mydumper. Das erzeugt mehrere parallele Streams die dann parallel restored werden können (mit myloader). Brauchst du aber bei deiner winzigen Datenbank nicht.

Sehe ich mir näher an.

Ate E. schrieb:

Hier nochmals das Script für das Backup:

Wer hat das geschrieben? das PW im Klartext soll im Skript stehen?

Alexander schrieb:

Wer hat das geschrieben? das PW im Klartext soll im Skript stehen?

Das Script läuft auf meinem NAS!

Axel S. schrieb:

Indem du ihn nicht mit mysqldump erzeugst, sondern z.B. mit mydumper. Das erzeugt mehrere parallele Streams die dann parallel restored werden können (mit myloader). Brauchst du aber bei deiner winzigen Datenbank nicht.

mydumper ist leider für meinen NAS nicht verfügbar.

Axel S. schrieb:

45MB an SQL-Statements sind nicht die Welt.

An sich nicht. Aber 45 MB komprimiert kann je nach Inhalt der DB auf sehr viel mehr SQL raus laufen und seine arme NAS ist ziemlich low-end. Der Fall mit den mehreren Tagen waren allerdings 100 GB gezippt und 1,5 TB ibdata1.

Ate E. schrieb:

mydumper ist leider für meinen NAS nicht verfügbar.

Aber vielleicht mysqlhotcopy. Das produziert keinen Dump, sondern eine binäre Kopie der Tablespaces.

Monk schrieb:

Aber du riskiert damit inkonsistente Daten, wenn die DB während dieser Zeit auf die Dateien zugreift.

Dafür steht das "hot" in mysqlhotcopy. Geht aber nur bei MyISAM Tablespaces, nicht bei InnoDB.

MariaDB wiederum hat "mariabackup".

(prx) A. K. schrieb:

MariaDB wiederum hat "mariabackup".

Kannst mir eine Quelle für den Synology DS220+ nennen?

Sorry, Synology ist nicht meine Welt.

Ate E. schrieb:

Axel S. schrieb:

innodb_buffer_pool_size = 1G innodb_log_file_size = 256M innodb_log_buffer_size = 8M

Werde ich probieren!

Die meisten Tabellen sind aber MyISAM!

Dann mußt du die key_buffer_size hochdrehen. Die ist mit 1M auch arg knapp bemessen. Ist ok für eine InnoDB-mostly Instanz, da sind nur die Privilege-Tabellen MyISAM. Ich würde mittelfristig aber eine Migration zu InnoDB empfehlen. Das hat besseres Caching (eben den Buffer Pool) und nicht wie MyISAM nur für Indizes (Key Buffer).

Summiere die Größe aller MYI Files. Dann hast du einen Anhaltspunkt wie groß du den Key Buffer machen solltest.

Wenn du (fast) keine InnoDB Tabellen hast, dann laß die Einstellungen auf den Defaults.

(prx) A. K. schrieb:

Aber vielleicht mysqlhotcopy. Das produziert keinen Dump, sondern eine binäre Kopie der Tablespaces.

Das wäre für MyISAM Tabellen die erste Wahl. Aber nur für diese.

Allerdings würde ich dann zweigleisig fahren. Backup per mysqlhotcopy und mysqldump. Restore präferiert von den hot copies und nur falls das fehl schlägt vom Dump.

Ate E. schrieb:

(prx) A. K. schrieb:

MariaDB wiederum hat "mariabackup".

Kannst mir eine Quelle für den Synology DS220+ nennen?

Google...

Was dir dann verrät, das es mariabackup als docker container gibt, wie man einen beliebigen Container von docker hub auf der Synology installiert, und wie man dann das alle konfigurieren muss.

Google ist schon ein wirklich tolles Tool. Das solltest du wirklich mal ausprobieren.

Oliver

Nachtrag:

ich habe mir deine my.cnf nochmal genauer angesehen und da sind mehrere Dinge fraglich:

table_open_cache = 2000 table_definition_cache = 2000

Hast du wirklich derartig viele Tabellen? Eigentlich braucht man das nicht anzufassen, sondern kann sich auf die Automatik (abhängig von max_connections) verlassen.

max_allowed_packet = 100M

Das ist sehr viel. Und es fällt pro Connection an. Gibt es da einen Grund dafür?

read_buffer_size = 256K read_rnd_buffer_size = 256K

Muß man eingentlich nie setzten.

net_buffer_length = 100K

Dito. Und sowieso setzt man das besser auf Systemebene (net.core.rmem_max, net.core.wmem_max, mit sysctl). Ist aber nicht kriegsentscheidend.

thread_stack = 240K

Von dieser Einstellung läßt man immer die Finger.

tmp_table_size = 1G max_heap_table_size = 1G

Das ist ebenfallst sehr viel wenn man bedenkt, daß das Synology nur 12G RAM hat. Denn das Limit gilt pro Tabelle. Und für die Anzahl der Tabellen gibt es kein Limit.

performance_schema=ON

Wenn du das nicht nutzt, schalte es aus.

Schlußfolgerung: ich würde das alles auf Default lassen. Mit folgenden Ausnahmen:

(innodb_buffer_pool_size) key_buffer_size tmp_table_size max_heap_table_size

und die an den tatsächlichen Bedarf anpassen.

Beim Einlesen werden alle notwendigen Kommandos nacheinander ausgeführt, wie "create table ..." und dann fleißig "insert into ...".

Wenn die Datenbank Indices enthielt (werden gleich nach dem Ausführen von "create table" mit "create index ..." wieder angelegt), wird nach jedem "insert" eines Datensatzes der Index ergänzt. Index schreiben dauert immer lange ...