Publicité
Mysql Innodb espace disque

Déjà en 2005, je gueulais à qui voulait l'entendre que InnoDB à un bug sérieux sur la taille de son fichier principal. En 2014, ce bug ne semble toujours pas corrigé. Pourtant, il peut bloquer des systèmes entiers. L'explication est simple, le fichier qui stocke vos bases de données relationnelles, ne fait que grossir. Grossir et grossir encore sans jamais redonner l’espace disque alloué dont il ne sert plus. Si vous effacez une database (innodb), la place prise sur le disque par le fichier ibdata n'est pas redonnée. Pire cette place n'est même pas réutilisée par les autres database. D'origine ce fichier est dans le répertoire /var/lib/mysql. Dans la plupart des configurations Linux, ce répertoire est sur la partition principale. Dès lors, vous risquez l'asphyxie de la partition et le blocage de votre linux (surtout ne pas redémarrer).

 

Il y a tout de même des choses positives. Après test, lorsque je supprime toutes les tables d’une base (oui soyons fou), certes l’espace disque n’est pas redonné, mais lorsque je remonte le dump, l’ancienne place prise par les bases est reprise.

 

Bref, si un jour vous avez une erreur de ce type  SQLSTATE[42S02]: Base table or view not found
Entrez vite un df -h dans un terminal

jbaptiste@Ubuntu-v2:~/www/drupal/master.longchamp.com/drupal/sites/master$ df

/dev/sda1         18415356 17456840          4 100%
/none                     4        0          4   0% /sys/fs/cgroup
udev               2013816       12    2013804   1% /dev
tmpfs               404796      932     403864   1% /run
none                  5120        0       5120   0% /run/lock
none               2023976     1764    2022212   1% /run/shm
none                102400       28     102372   1% /run/user
/dev/sdc1         15729120  7289524    7617556  49% /mnt/mandriva
/dev/sdb1         30830592 24991028    4250420  86% /space2
/dev/sda5        102272436 72630176   24424224  75% /home
/dev/sdc6         21263412 18904032    2342996  89% /mnt/jbaptiste

 

Si à l'issue de cette commande vous avez un 100% sur /dev/sda1 là c'est cuit. Il y a de fortes chances que votre partition principale (celle qui contient le noyau et plein de trucs rigolos) est pleine. Il va falloir agir

Cette méthode a été testée sur une Ubuntu 13.10

Se donner un peu d’espace pour travailler

 

Attention la procédure est à appliquer à la lettre, une erreur de copie et ça peut vite être le drame. Pas de quoi stresser non plus, on prend des précautions.

Pour faire un peu d'espace sur le disque, j'ai effacé les log avec

sudo rm /var/log/*.gz

Mais cela n’a pas été suffisant, j’ai donc éffacer ce qu’il y avait dans /tmp

Mais cela n’a pas été suffisant, alors j’ai effacé avec ubuntu-tweak que j’avais sur ma machine les vieux noyau du système. Dans le répertoire /opt il y a aussi des trucs à faire. Cela m’a permis de récupérer 200 Mo et de travailler sereinement.

Arrêter le mysql

sudo service mysql stop

Passer en root avec sudo su ou sudo -s (Attention lorsqu’on passe dans ce mode on fait vite des bêtises, du style faire un chmod 600 sur son home …)

Sinon ajoutez des sudo devant les commandes 

Créer un répertoire mysql (sur une partition pas trop pleine (pour moi la /home)

mkdir /home/mysql

la donner au user Mysql

chown mysql:mysql /home/mysql

copier tout le contenu du répertoire /var/lib/mysql dans /home/mysql avec la commande suivante :

cp -pr /var/lib/mysql/* /home/mysql

ou

rsync -rp /var/lib/mysql/* /home/mysql

Le -p est très important car il préserve les droits et les permissions pour chaque fichier. Cela vous évitera de le faire à la main.

Mais tout n’est pas fini, loin de là. Maintenant, il faut créer un petit lien symbolique pour ne pas perturber la configuration d’Ubuntu.

On va d’abord bouger le répertoire d’origine, histoire de ne pas perdre les bases (on ne sait jamais)

mv /var/lib/mysql/ /var/lib/mysqls

Création du lien et modification du propriétaire

ln -s /home/mysql/ /var/lib/mysql
chown -h mysql:mysql /var/lib/mysql

 

Mais ce n’est pas tout, si votre version d’Ubuntu utilise apparmor qui sert à contrôler les droits sur les applications, il va falloir modifier sa configuration.

Le fichier ce trouve ici (pour 13.10) /etc/apparmor.d/usr.sbin.mysqld

Remplacer les lignes (le # indique un commentaire)

#  /var/lib/mysql/ r,
#  /var/lib/mysql/** rwk,

par

  /home/mysql/ r,
  /home/mysql/** rwk,

 

Modifier le fichier de configuration de mysql /etc/mysql/my.cnf (faire une copie avant)

cd /etc/mysql/
cp my.cnf my.cnf.jba
vi my.cnf

Ajouter la ligne suivante dans la section [mysqld] :
#datadir                = /var/lib/mysql
datadir                = /home/mysql

 

Recharger la configuration apparmor

service apparmor reload

Enfin redémarrer mysql

 service mysql start

blague possible

start: Job failed to start

Repassez tous les étapes ci-dessus. Si vraiment cela ne fonctionne pas. Tout refaire en arrière.

 

Récupérer de l’espace

Maintenant que l'on a sécurisé notre Linux occupons-nous de cette saloperie d'InnoDB. Je dis saloperie, car je ne l'aime pas et je ne l'ai jamais aimé. Cela n'appartient qu'à moi et InnoDB doit bien avoir des qualités sinon il aurait été supprimé depuis longtemps. Il est temps de récupérer de l'espace. Le paradoxe est qu'il va falloir en perdre encore un peu pour pouvoir en récupérer

 

Voici la méthode que l’on trouve en général

  1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
  2. Drop all databases except the above 2 databases
  3. Stop mysql
  4. Delete ibdata1 and ib_log files
  5. Start mysql
  6. Restore from dump

En français ça donne quoi

Se connecteur au serveur mysql

Lister les tables utilisées par innodb

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

1. utiliser le script suivant (inspiré ce celui-ci  ici ) le placer dans un fichier mysqldump.sh (par exemple)

#!/bin/bash
MYUSERNAME=root
MYPASSWORD=
MYHOST=127.0.0.1

MYSQL=$(mysql -N -u root -pEnversx2 -h127.0.0.1 <<<"SELECT DISTINCT (CONCAT( table_schema,  ' ' )) FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" )

mysqldump -v -u${MYUSERNAME} -p${MYPASSWORD} -h${MYHOST} --databases  ${MYSQL} > DB-DUMP.sql

Ensuite taper bash mysqldump.sh

 

Ensuite, je vous conseille de faire

mysql -N -u root -pmotdepasse -h 127.0.0.1 <<< "SELECT DISTINCT(CONCAT(table_schema,' ')) FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';"

Pour voir quelles sont les tables que vous devez effacer (NE JAMAIS EFFACER LES DATABASES mysql et performance_schema databases, Sinon là c’est mort !!!

 

Dans mon cas et pour information le dump non compressé pèse 1,2Go alors que le fichiers InnoDB pèse 5 Go, en clair il y a 3.8 Go utilisé pour le “relationnelle” de base relationnelle et des index !!!

2)  Effacer les tables avec DROP DATABASE database, où vous remplacer “database” la les noms listés avec la commande précédente (on pourrait faire un script, mais là je suis naze)

3) sudo service mysql stop

4) sudo rm /home/mysql/ibdata1

sudo rm /home/mysql/ib_log*

5) sudo service mysql start

6) mysql -uroot -pEnversx2 -h127.0.0.1 < DB-DUMP.sql

 

Lors de la première insertion, j'ai gagné 100 Mo, c'est-à-dire rien du tout. Il est donc conseillé de faire le ménage avant de faire le DUMP.

 

Je vous conseille d’ajouter aussi les lignes suivantes pour optimiser la place dans la future configuration. Cela aura pour effet de faire un fichier par table (ce qui n’est pas idéal, notamment avec Drupal 7)

innodb_file_per_table
innodb_flush_method
=O_DIRECT
innodb_log_file_size
=1G
innodb_buffer_pool_size
=4G

 

Conclusion

On peut se demander si c'est un bug, ou un oubli volontaire. Il semble moins gênant qu'en 2005. Cela dit, son effet est toujours aussi dévastateur. Il ne faut donc pas perdre de vue qu'avec les bases de données relationnelles la place prise sur le disque est 3 fois plus importante que son dump (C'est une estimation à la grosse louche ;) ). Je n'aime toujours pas InnoDB, car je ne contrôle pas vraiment l'espace disque utilisé. Les outils donnés pour réduire la taille ne donnent pas entièrement satisfaction. Bref, il faut vraiment se poser la question ai-je besoin d'une base de données relationnelle avant d'utiliser InnoDB.

 

 

 

 

 


Vous avez aimé cet article ? Alors partagez-le avec vos amis en cliquant sur les boutons ci-dessous :


Commentaires

  • Aucun commentaire trouvé

Poster un commentaire en tant qu'invité

0

La pub ça rapporte pas grand chose, en plus ça vous laisse d'horribles cookies non comestibles, autrement appelé trackers. Ce cookie est utilisé par DoubleClick (Google Adsense). Vous avez tout le droit de ne pas en vouloir et donc quitter cet excellent blog. Dans le cas contraire, vous l'acceptez et vous poursuivez le surf en toute connaissance de cause (par ailleurs j'utilise piwik).