Hallo Leute,
Auf meinem NAS (DS220+ 12GB RAM) habe ich MariaDB 10.3.37 installiert.
Ein Update der Version dürfen wir aufgrund einer Inkompatiblität mit
einem anderen Programm nicht machen.
Das Arbeiten ist flott und auch eine Sicherung ist schnell erstellt.
AAABER:
Will man die DB zurücksichern, dauert es gefühlt ewig.
(DB Sicherungsdatei ungefähr 45 MB, Dauer ungefähr 1 Stunde)
Zurückgespielt wird mit folgendem Script:
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.
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
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:
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.
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.
Ate E. schrieb:> Und wie kann ich den MySQL Dump am schnellsten wieder zurücklesen?
Am schnellsten wäre, die Binärdateien im data Verzeichnis zu sichern und
zurück zu spielen. Das dauert nur wenige Sekunden.
Aber du riskiert damit inkonsistente Daten, wenn die DB während dieser
Zeit auf die Dateien zugreift. Unter Umständen lassen sie diese
Inkonsistenzen nicht reparieren.
Außerdem musst du damit rechnen, dass solche binären Backups nicht auf
Rechner mit anderer Architektur übertragbar sind - selbst wenn die
Versionsnummer von MySQL dort die gleiche ist.
Falls du das trotzdem machen willst, stoppe den DB server Dienst vor dem
backup/restore.
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.
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 ...
Thread beobachten
Seitenaufteilung abschalten