MySQL tmpdir auf tmpfs auslagern
Diesen Beitrag schrieb ich 9 Jahre und 3 Monate zuvor; die nachfolgenden Ausführungen müssen heute weder genau so nach wie vor funktionieren, noch meiner heutigen Meinung entsprechen. Behalte das beim Lesen (und vor allem: beim Nachmachen!) bitte stets im Hinterkopf.
- Temporary tables created on disk?
- tmpdir erstellen und mounten
- mysql soll das neue tmpdir nutzen
- Fazit
Es gibt ziemlich viele Gründe, weshalb mit temporären Tabellen gearbeitet wird oder werden muss: BLOB oder TEXT-Spalten in der Datenbank, UNION-Abfragen, Tabellen, die zu groß werden, als dass sie im Speicher gehalten werden können – die Liste umfasst so einiges. Doch wenn du es schon nicht vermeiden kannst – wie kannst du die Verarbeitung zumindest beschleunigen?
Temporary tables created on disk?
In einigermaßen regelmäßigen Abständen lasse ich auf meinem Server mysqltuner.pl laufen – hiermit lassen sich grundsätzlich ganz gut Anhaltspunkte finden, um gegebenenfalls die Tuning-Schrauben noch etwas anzuziehen. Die folgende Zeile sprang mir hierbei ins Auge:
[!!] Temporary tables created on disk: 46% (.../...)
Ganze 46% – das ist schon relativ viel, bei Nutzung eines WordPress beispielsweise allerdings auch nicht ganz unerwartet: allein innerhalb eines Artikels sind in wp_posts der post_title vom Typ TEXT, genau wie post_excerpt, to_ping, pinged. Wie kannst du nun herausfinden, wo diese temporären Tabellen hingeschrieben werden? Es ist eine einfache SQL-Abfrage:
mysql> show variables like '%tmpdir%';
+-------------------+-------------------+
| Variable_name | Value |
+-------------------+-------------------+
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-------------------+-------------------+
2 rows in set (0.00 sec)
Das entspricht der Standardkonfiguration: tmpdir ist /tmp, was ja irgendwie auch naheliegend ist, nicht selten aber im Filesystem liegt – und alles, was Festplattenzugriffe generiert, bremst das System aus. Die Idee ist, die tmpdir auf ein tmpfs, also in den Arbeitsspeicher, zu packen und somit die Verarbeitungsgeschwindigkeit deutlich zu erhöhen. Hierzu legst du einen Ordner an, setzt die Zugriffsrechte und überprüfst, welche UID und GID dein User mysql hat:
tmpdir erstellen und mounten
$ mkdir /var/lib/mysqltmp
$ chown mysql:mysql /var/lib/mysqltmp/
$ id mysql
uid=1002(mysql) gid=1002(mysql) groups=1002(mysql)
Anschließend musst du das Verzeichnis in die /etc/fstab einpflegen.
## file: "/etc/fstab"
tmpfs /var/lib/mysqltmp tmpfs rw,gid=1002,uid=1002,size=256M,nr_inodes=10k,mode=0700 0 0
...
Mounte dein Verzeichnis und und überprüfe, ob alles fehlerfrei funktioniert:
$ mount -a
$ mount | grep mysqltmp
tmpfs on /var/lib/mysqltmp type tmpfs (rw,gid=1002,uid=1002,size=1024M,nr_inodes=40k,mode=0700)
$ df -h|grep mysqltmp
tmpfs 256M 0 256M 0% /var/lib/mysqltmp
mysql soll das neue tmpdir nutzen
Jetzt, da das Verzeichnis verfügbar ist, musst du mysqld noch sagen, dass er es fortan als tmpdir nutzen soll. Dazu bearbeitest du die Konfigurationsdatei und startest den Dienst anschließend durch (service mysql restart).
## file: "/etc/mysql/my.cnf"
...
[mysqld]
...
tmpdir = /var/lib/mysqltmp
...
Überprüfe anschließend, ob er auch brav das genannte Verzeichnis benutzt; es ist die gleiche mysql-Abfrage wie zu Anfang:
mysql> show variables like '%tmpdir%';
+-------------------+-------------------+
| Variable_name | Value |
+-------------------+-------------------+
| slave_load_tmpdir | /var/lib/mysqltmp |
| tmpdir | /var/lib/mysqltmp |
+-------------------+-------------------+
2 rows in set (0.00 sec)
Fazit
Im laufenden Betrieb kannst du nun beobachten, wie dein neues Verzeichnis genutzt wird; ich lasse beispielsweise munin Graphen malen, und hier ist für mich zweierlei ersichtlich: zum einen wird das Verzeichnis bei disk usage bzw. inode usage mit eingezeichnet, und zum anderen sind die iowait-Werte bei cpu usage deutlich gesunken. Signifikante Auswirkungen hat diese Änderungen selbstredend auf Hosts, die viele Datenbanken bedienen. Und hier ist dann auch Vorsicht geboten: nämlich an dem Punkt, an dem das tmpfs-Verzeichnis voll wird. Dann beginnt das System nämlich, den swap-Bereich zu nutzen – was wiederum bedeutet, dass auf der Festplatte herumgekratzt wird. Ich habe mir daher einen Check in Icinga aufgenommen, der mich warnt, falls das Verzeichnis voll läuft – und sollte es passieren, muss die Konfig halt vorsichtig weiter aufgemotzt werden…
Hintergrundbild: Nahaufnahme Gartenblumen, 2018, 1500x 690px, Bild genauer anschauen – © Marianne Spiller – Alle Rechte vorbehalten
