MySqlTmpDir

wall < "MySQL tmpdir auf tmpfs auslagern"

Broadcast message from spillerm@unixe.de (pts/1) (Mo Jul 27 10:03:24 2015):
4
Diesen Beitrag schrieb ich vor 3 Jahren. Behalte das beim Lesen bitte im Hinterkopf.

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 gross 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?

mysql tmpdirIn einigermassen regelmässigen Abständen lasse ich auf meinem Server mysqltuner.pl laufen — hiermit lassen sich grundästzlich 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 mysql-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:

mkdir /var/lib/mysqltmp
chown mysql:mysql /var/lib/mysqltmp/
id mysql
uid=1002(mysql) gid=1002(mysql) groups=1002(mysql)

Anschliessend musst du das Verzeichnis in die /etc/fstab einpflegen.

## /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

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 anschliessend durch (service mysql restart).

## /etc/mysql/my.cnf
...
[mysqld]
...
tmpdir			= /var/lib/mysqltmp
...

Überprüfe anschliessend, 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)

Und es läuft…

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.

Unterschrift 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…

4