Feb
15
2009
Liens symboliques et fichiers temporaires sous MyISAM
Posted by DBNewz on Sun 15 Feb 2009 16:44 UTC
Tags:

Récemment confronté à des problèmes ponctuels d’espace disque sur un serveur gros consommateur de ce type de ressources (datawarehouse), j’ai dû temporairement jongler entre différentes partitions afin de permettre au serveur MySQL de continuer à fonctionner.

Le problème :
La partition accueillant le répertoire d’installation standard de notre MySQL arrivant à saturation, il devenait impossible de passer certaines commandes dont le ALTER TABLE. Celle-ci nécessite en effet la plupart du temps (pour MyISAM) la création de fichiers temporaires (.MYD, .MYI, .frm) dont les tailles sont semblables à celles des fichiers initiaux (aux modifications de structure près). Le hic : la taille restante sur cette partition ne permettait pas la création de tels fichiers.

Sur une table de plusieurs centaines de millions de lignes, pesant quelques dizaines de Go, un ALTER TABLE prend souvent plusieurs heures… Je vous conseille donc de vérifier dès le départ si vous disposez sur votre partition d’un espace au moins équivalent à la somme des .MYD + .MYI de votre table si vous souhaitez éviter de saturer totalement l’espace disque.

Si malgré tout vous poussez le vice jusqu’à défier toutes vos sondes de monitoring et obtenez un “Use 100%”  sur un “df -h”, sachez que le .TMD susceptible de faire son apparition est là pour tenter de réparer les fichiers temporaires (au moins le .MYD) crées lors du ALTER TABLE. Cette étape supplémentaire rallonge fortement l’opération initiale à tel point qu’il est parfois plus judicieux de l’interrompre, supprimer les fichiers temporaires initialement crées par le ALTER, ainsi que le .TMD, et de tout reprendre à zéro avec cette fois un espace disque suffisant.

Pour éviter d’en arriver là, et si l’espace disque vient à manquer sur la partition de votre MySQL, les liens symboliques peuvent vous sauver la mise. Ils permettent de déplacer bases et tables MyISAM sur différentes partitions / disques, soit pour des raisons de performance (on peut alors allouer une base/table à un disque) soit comme ici surtout pour des raisons de place.

Le jonglage à base de liens symboliques permet-il d’éviter tous les problèmes ? Quel rôle joue la variable TMPDIR ?

Autant être clair dès maintenant, la variable TMPDIR n’intervient pas dans le cadre d’un ALTER TABLE. Si celle-ci désigne bien l’emplacement où MySQL stocke ses fichiers temporaires, un ALTER TABLE crée une table temporaire dans le même répertoire que la table originale.

En effet :

mysql> show variables like ‘tmpdir’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| tmpdir        | /tmp  |
+—————+——-+
1 row in set (0.00 sec)

debian:~/sandboxes/msb_5_1_28/data/test# ls -l

-rw-rw—- 1 root root     8644 2009-02-08 02:52 t.frm
-rw-rw—- 1 root root 20000000 2009-02-08 02:52 t.MYD
-rw-rw—- 1 root root 17444864 2009-02-08 02:52 t.MYI

Pendant l’exécution du ALTER TABLE on obtient :

debian:~/sandboxes/msb_5_1_28/data/test# ls -l

-rw-rw—- 1 root root     8616 2009-02-08 08:00 #sql-e1c_1.frm
-rw-rw—- 1 root root  1966080 2009-02-08 08:00 #sql-e1c_1.MYD
-rw-rw—- 1 root root     1024 2009-02-08 08:00 #sql-e1c_1.MYI
-rw-rw—- 1 root root     8644 2009-02-08 02:52 t.frm
-rw-rw—- 1 root root 20000000 2009-02-08 02:52 t.MYD
-rw-rw—- 1 root root 17444864 2009-02-08 02:52 t.MYI

Après l’opération :

debian:~/sandboxes/msb_5_1_28/data/test# ls -l

-rw-rw—- 1 root root     8616 2009-02-08 08:00 t.frm
-rw-rw—- 1 root root  9000000 2009-02-08 08:00 t.MYD
-rw-rw—- 1 root root 17422336 2009-02-08 08:01 t.MYI

Les fichiers temporaires liés à la reconstruction de la nouvelle table (#…) sont bien situés dans le répertoire de la table d’origine, et non pas dans /tmp.
Dans le cadre d’un GROUP BY ou ORDER BY, MySQL peut avoir besoin de créer des fichiers temporaires dont l’emplacement sera cette fois défini par la variable TMPDIR.

La création des fichiers temporaires au sein même du répertoire d’origine de la table n’est pas vraiment souhaitable si l’on manque d’espace disque, comment régler le problème ?

Tentons de déplacer la table incriminée par des liens symboliques.

Deux remarques importantes :

  1. - le .frm doit rester dans le répertoire initial, il ne peut pas être transformé en lien symbolique, en revanche le .MYD et le .MYI peuvent être déplacés où bon vous semble, il est notamment possible de les stocker chacun dans un répertoire différent.
  2. - La documentation stipule que la manipulation d’une table MyISAM par liens symboliques doit s’effectuer serveur MySQL éteint. La “procédure” fonctionne serveur allumé mais vous vous exposez à différents problèmes : accès aux tables concernées pendant la procédure de déplacement de vos tables et résultats aléatoires.

J’ai effectué de nombreux tests avec un serveur MySQL non coupé, les résultats varient selon les exécutions, les versions de MySQL… Difficile d’établir une conclusion concernant un comportement de MySQL lorsque le serveur fonctionne et qu’on utilise les liens symboliques pour déplacer une table, voici différents comportements que j’ai observé :

  • Tout se passe bien : vos tables pointent vers un autre répertoire, vous appliquez le ALTER TABLE et les fichiers temporaires suivent les liens symboliques pour être finalement crées sur le répertoire de destination, tout est ok.
  • MySQL est susceptible de supprimer vos liens symboliques en fin d’ALTER TABLE, alors que vos .MYD et .MYI du répertoire cible sont supprimés pour être finalement recrées dans le répertoire initial.
  • MySQL semble avoir du mal avec les liens symboliques détenus par root… J’ai remarqué qu’avec un chown -h mysql:root par exemple, le serveur MySQL s’en sortait mieux. A noter que la 5.1.30 semble faire effectuer ce “changement de propriétaire” d’elle même : en fin d’ALTER TABLE mes liens symboliques sont passés de root:root à mysql:mysql.

Bref, d’une façon générale avant de vous lancer dans le symlinking d’une très lourde table, testez d’abord la manipulation sur une table plus modeste afin de vérifier comment se comporte votre serveur MySQL, ceci tout particulièrement si vous comptez effectuer l’opération serveur allumé (encore une fois non recommandé, attention aux accès potentiels sur vos tables…).

La procédure à suivre pour vos tests est simple, voici la mienne :

J’ai repris la structure de la table d’un billet précédent (générer un jeu de données), ainsi que la procédure stockée nécessaire pour la renseigner, souvent 1M de lignes pour mes tests (à varier selon votre configuration, il faut que les ALTER TABLE que vous passerez soient suffisamment longs pour vous laisser le temps d’effectuer au moins un “ls -l”

J’ai initialement mes 3 fichiers de ma table MyISAM “t” :

debian:~/sandboxes/msb_5_1_28/data/test# ls -l
-rw-rw—- 1 root root 8616 2009-02-08 13:34 t.frm
-rw-rw—- 1 root root 4500000 2009-02-08 13:34 t.MYD
-rw-rw—- 1 root root 8177664 2009-02-08 13:34 t.MYI

On déplace les fichiers .MYD et .MYI vers un répertoire cible :
mv t.M* /symlink_test

On ajoute les liens symboliques :

ln -s /symlink_test/t.MYD t.MYD
ln -s /symlink_test/t.MYI t.MYI

debian:~/sandboxes/msb_5_1_28/data/test# ls -l
-rw-rw—- 1 root root … 13:34 t.frm
lrwxrwxrwx 1 root root  … 13:49 t.MYD -> /symlink_test/t.MYD
lrwxrwxrwx 1 root root  … 13:49 t.MYI -> /symlink_test/t.MYI

Les fichiers .MYD et .MYI sont situés dans /symlink_test et on applique un ALTER TABLE à notre table.
Voici quelques résultats obtenus pendant le ALTER TABLE…

Cas 1 : MySQL ne suit pas les liens symboliques :

/var/database/mysql50/tmp# ls -l

-rw-rw—- 1 mysql mysql   … 23:34 #sql-fe6_6e715a.frm
-rw-rw—- 1 mysql mysql … 23:34 #sql-fe6_6e715a.MYD
-rw-rw—- 1 mysql mysql   … 23:34 #sql-fe6_6e715a.MYI
-rw-rw—- 1 mysql mysql   … 23:31 t.frm
lrwxrwxrwx 1 root  root      … 23:33 t.MYD -> /sym/t.MYD
lrwxrwxrwx 1 root  root      … 23:33 t.MYI -> /sym/t.MYI
… et finalement les supprime en fin d’ALTER TABLE :

/var/database/mysql50/tmp# ls -l

-rw-rw—- 1 mysql mysql  … 23:34 t.frm
-rw-rw—- 1 mysql mysql … 23:34 t.MYD
-rw-rw—- 1 mysql mysql … 23:34 t.MYI

A tout hasard vérifiez la variable ‘have_symlink’ dans votre configuration, elle est normalement activée par défaut :
mysql> show variables like ‘have_symlink’;

+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_symlink  | YES   |

Cas 2 : MySQL suit les liens symboliques :

-rw-rw—- 1 root root 8644 2009-02-08 14:01 #sql-e1c_1.frm
lrwxrwxrwx 1 root root  … #sql-e1c_1.MYD -> /symlink_test/test/#sql-e1c_1.MYD
lrwxrwxrwx 1 root root  … #sql-e1c_1.MYI -> /symlink_test/test/#sql-e1c_1.MYI
-rw-rw—- 1 root root … t.frm
lrwxrwxrwx 1 root root   … t.MYD -> /symlink_test/test/t.MYD
lrwxrwxrwx 1 root root   … t.MYI -> /symlink_test/test/t.MYI

… sur le répertoire cible on constate la présence des fichiers temporaires :
debian:/symlink_test/test# ls -l
-rw-rw—- 1 root root 20000000 2009-02-08 14:01 #sql-e1c_1.MYD
-rw-rw—- 1 root root  9265152 2009-02-08 14:01 #sql-e1c_1.MYI
-rw-rw—- 1 root root 10000000 2009-02-08 13:58 t.MYD
-rw-rw—- 1 root root 18443264 2009-02-08 13:59 t.MYI

Si vous en avez la possibilité, suivez les préconisations de la doc MySQL et établissez un lien symbolique sur la base entière plutôt que sur une table en particulier.
Ex :

debian:~/sandboxes/msb_5_1_28/data/test# cd ..
debian:~/sandboxes/msb_5_1_28/data# mv test /symlink_test/
debian:~/sandboxes/msb_5_1_28/data# ln -s /symlink_test/test test
debian:~/sandboxes/msb_5_1_28/data# ls -l
lrwxrwxrwx 1 root root … 16:08 test -> /symlink_test/test

Et dans le répertoire cible, pendant l’exécution du ALTER TABLE :

debian:/symlink_test/test# ls -l
-rw-rw—- 1 root root     … 16:08 #sql-1efe_1.frm
-rw-rw—- 1 root root  … 16:08 #sql-1efe_1.MYD
-rw-rw—- 1 root root  … 16:08 #sql-1efe_1.MYI
-rw-rw—- 1 root root     … 15:58 t.frm
-rw-rw—- 1 root root … 15:58 t.MYD
-rw-rw—- 1 root root … 15:58 t.MYI

… les fichiers temporaires ont bien suivi le lien symbolique, c’est la méthode recommandée par MySQL : le déplacement d’une base entière.

A noter qu’il est également possible d’établir de faire pointer un .MYD et un .MYI vers un répertoire précis via respectivement DATA DIRECTORY et INDEX DIRECTORY, deux options disponibles pour un CREATE TABLE. Malheureusement un ALTER TABLE ne peut pas modifier ces valeurs.

Les liens relatifs à notre thème du jour sur la doc MySQL :
http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir
http://dev.mysql.com/doc/refman/5.1/en/symbolic-links-to-tables.html