MySQL InnoDB Datenbank (MariaDB) auf ZFS Dataset umziehen und betreiben

grau

Member
Hallo zusammen.
Bevor ich mir da irgendwas kaputt mache, frage ich lieber die Profis (ja, ich unterstelle den Mitlesenden eine gewisse Professionalität).

Ich möchte bei einem bestehenden MySQL (MariaDB) Jail (mit iocage erstellt) die Datenbank (InnoDB) auf ein eigenes ZFS Datasets auslagern, um diese unabhängig von der Jail zu machen. Nach ersten Recherchen ist dies aber gar nicht so einfach, da wohl einige Parameter sowohl in der my.cnf als auch beim Anlegen des Datasets zu beachten sind.

Aktuell sieht das also so aus: die Datenbank selbst im Jail ist unter /zroot/iocage/jails/mysql/root/var/db/mysql (vom host gesehen) zu finden.
Code:
zroot/iocage/jails/mysql                 1.58G   396G    92K  /zroot/iocage/jails/mysql
zroot/iocage/jails/mysql/root            1.58G   396G  2.37G  /zroot/iocage/jails/mysql/root

Wie mache ich das also "richtig", die bestehenden Datenbank-Inhalte aus dem klassischen Jail ZFS Dataset auf ein eigenes Dataset umzuziehen - bestmöglichst ohne Datenverlust. Welche Schalterchen und Parameter müssen hier beachtet werden. Freue mich auf Euren Input.
 
Da die Datenbank nicht allzu groß zu sein scheint, würde ich es richtig machen. Die Datenbank mit mysqldump einmal komplett dumpen. Anschließend in der my.cnf die neuen Pfade setzen, die Datenbank komplett neu initialisieren und zum Schluss den Dump zurückspielen. Das ist der sicherste Weg, deutlich sicherer als an 20 Stellen in Dateien, die nicht zum Editieren gedacht sind, die Pfade zu verdrehen. Und es hat den Vorteil, dass du einmal ohne 'OPTIMIZE TABLE' Orgie aufgeräumt hast.
 
Hallo @Yamagi,

vielen Dank für Deine schnelle Antwort. Ich denke, das werde ich auch so machen. Allerdings scheint es doch einige Dinge zu geben, die man "einstellen" sollte (sowohl auf zfs-Seite als auch in my.cnf), damit die Datenbank und ZFS richtig zusammen funktionieren.
 
Du brauchst 2 datasets, eins für die logs, eins für die innodb files. Die genauen Settings kann ich dir am Abend posten, oder du Googlest nochmal, die record size ist hier wichtig.
 
Also, ich habe (leider Gottes immer noch) buchstäblich Terabytes in MySQL. Die Erkenntnis nach Jahren des Ärgers damit ist, dass man dort nicht allzu viel einstellen muss und sollte.

Die Record Size hat @medV2 gerade erwähnt, auf dem Dataset für InnoDB sollte sie auf 16 Kilobyte gesetzt sein. Auf dem Dataset für die Bin- und Relaylogs auf 128 Kilobyte. Damit entsprich eine InnoDB-Page einem ZFS-Block, was deutlich höhere Zugriffsgeschwindigkeiten bringt.

Wichtig oder zumindest hilfreich ist es auch sicherzustellen, dass Daten nur einmal gecached werden. Also entweder in ZFS ARC oder in InnoDBs Cache, aber nicht in beiden. Das verschwendet nur sinnlos RAM. In setze daher den ARC auf einen sehr niedrigen Wert von 2GB und dafür innodb_buffer_pool_size auf die Ausgabe von SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;. Das ist allerdings das rechnerische Optimum, nicht unbedingt das praktische Optimum. Für kleine Datenbanken mit wenig Load ist der Wert eventuell zu groß, für große Datenbank so hoch, dass die Mengen RAM einfach nicht wirtschaftlich sind.

Ich würde das Dataset auch nicht komprimieren, die Kompression haut bei MySQL wirklich sehr auf die Performance. Das Gleiche gilt auch für die ARC-Kompression. Selbst bei kleinen ARCs kann die weh tun, da die Dekompression synchron im Kontext des jeweiligen MySQL-Threads ausgeführt wird, also Latenz einfügt. Das wird sich mit dem Merge des neuen OpenZFS (früher ZFS on Linux) lösen. Bis dahin hilft vfs.zfs.compressed_arc_enabled="0" in der loader.conf. Ähnlich ist es bei Scattered Allocations des ARC. InnoDB ist einer der wenigen Workloads, wo sie tendenziell negativen Performanceinfluss haben. Ein vfs.zfs.abd_scatter_enabled="0" in der loader.conf schaltet sie ab. Aber gleich die Warnung hinterher: Wenn die Box noch andere Dinge als nur MySQL macht, wird der Verlust bei anderen Workloads wahrscheinlich höher als der Gewinn bei MySQL sein! Also die beiden Optionen nicht blind setzen.

Und last but not least ist innodb_file_per_table sehr zu empfehlen. Nicht unbedingt aus Performance-Sicht, sondern da es beim Purge (dem Freigeben nicht mehr referenzierter Daten) hilft. Außerdem ist im Fall von Datenkorruption nur eine Tabelle zerstört und nicht die ganze Datenbank. Ich weiß, Backups. Aber selektive Reparatur ist oft schneller und einfacher als eine Totalwiederherstellung.
 
Die Recordsize hat @Yamagi ja schon nachgereicht, ich hab auf dem InnoDB - Dataset auch noch primarycache=metadata gesetzt. Ob Kompression was bring oder nicht hängt immer stark von den Daten ab, aber die neuen MariaDBs können das auch schon selbst und sollte man auch auf jedenfall bevorzugen.

Die Option innodb_file_per_table kann möglicherweiße zu schlechterer Performance bei gewissen komplexen Querys führen. Ob das noch so ist kann ich allerdings nicht sagen, da wir fast keine MySQL mehr in der Prod. verwenden. Die Info ist von einem Sun-MySQL Guru der mit uns mal unsere Konfigurationen durchgegangen ist (ja, damals gabs Sun noch, vielleicht ist das also schon ganz anders).
 
So, ich habe jetzt das hier gefunden: https://wiki.freebsd.org/ZFSTuningGuide#MySQL

Tweaks for ZFS

  • zfs set primarycache=metadata tank/db
  • zfs set atime=off tank/db
  • zfs set recordsize=16k tank/db/innodb
  • zfs set recordsize=128k tank/db/logs
  • zfs set zfs:zfs_nocacheflush = 1
  • zfs set sync=disabled tank/db
Note: MySQL 5.6.6 and newer (and related MariaDB / Percona forks) has innodb_file_per_table = on as default, so IBD files are not created under tank/db/innodb (defined by innodb_data_home_dir in your my.cnf), they are created under tank/db/<db_name>/ and you should use recordsize=16k on this dataset too or switch back to innodb_file_per_table = off

Also mal kurz zum Verständnis: ich habe eine Datenbank "meineDaten", die gefüllt ist mit InnoDB Tables. Wenn ich mir das aktuelle Verzeichnis /var/db/mysql mal anschaue, dann habe ich da also einerseits Verzeichnisse (meineDaten, mysql, performance_schema, sys, ...), die ib_logfiles, der ib_buffer_pool, mysql-slow.log, pid & err

Wird für jede Datenbank dann ein eigenes tank/db/innodb/meineDaten, tank/db/innodb/performance_schema, tank/db/innodb/sys ... angelegt mit recordsize 16k? Was passiert eigentlich, wenn "aus Gründen" in der Datenbank eine MyISAM Tabelle angelegt wird? Was mache ich mit den ib_logfiles und den mysql.err / mysql.pid?
 
Ich kenn die Defaulteinstellungen nicht, da ich meine vor ewigkeiten geändert habe. Ich würde dir das aber auch empfehlen.

Und zwer folgendes:

datadir=/data/mysql/data
innodb_log_group_home_dir = /data/mysql/log

Damit hast du Log und Daten getrennt. Egal ob du nun per innodb_file_per_table die Daten auf einzelne Files per Table verteilst oder ob du alles in große ibdata Files legst - die Datenfiles sind immer auf dem datadir - Pfad, die Logs (Writeahead und Binarylogs unter dem log_group Pfad). Pfade natürlich entsprechend anpassen.

Achtung du musst dann womöglich alles löschen damit Mysql das beim Start neu anlegt. Hierfür muss auch noch in der rc.conf zusätzlich den Datapfad eingetragen:

mysql_dbdir="/data/mysql/data"

Vielleicht braucht man das aber nichtmehr, das wurde alles vor längerer Zeit gemacht, schadet allerdings nicht.


Dann brauchst du 2 Datasets mit den oben beschriebenen Settings. Die .err und .pid sind egal, die ib_logfiles kommen mit den beschriebenen Settings ins log_group Dir.

Wenn du aus welchen Gründen auch immer ne MyISAM anlegst würde ich dir nen Besuch beim Arzt raten :D


Nachtrag: Wenn du Binarylogs oder Relaying verwendest brauchst du in der my.cnf noch:

log_bin = /data/mysql/log/binlog
relay_log=/data/mysql/log/relay-bin
 
Die Option innodb_file_per_table kann möglicherweiße zu schlechterer Performance bei gewissen komplexen Querys führen.
Es ist theoretisch langsamer, wenn ein Query über mehrere Tabellen läuft. Da er dann mehrere InnoDB-Dateien durchhampeln muss. Aber meiner Erfahrung nach ist das wirklich minimal und den potentiellen Ärger nicht wert. Ich würde sogar soweit gehen und sagen: Wenn man in Bereiche kommt, wo solche Optimierungen relevant werden, ist MySQL eh das falsche Werkzeug. Wenn man nicht drum herum kommt MySQL einzusetzen (willkommen im Club) ist es besser das Problem mit Hardware totzuschlagen.
 
Zurück
Oben