dbnewz

Aujourd’hui j’ai reçu un message d’un collègue qui était surpris de voir que son “datafile” InnoDB gardait la même taille aprés avoir effacé des tables ou des éléments de ses tables.
C’est tout à fait normal! Toutes les bases de données marchent de la même facon. Un tablespace s’agrandira tout seul si vous le créez en mode autoextend et ce sous MySQL mais aussi Oracle, IBM DB2,… Si vous n’activez pas la fonction autoextend quand la base voudra allouer de la place, vous verrez une belle erreur.

La question est pourquoi? Performances!

Pour gagner en performances, vous voulez que votre base pré alloue de l’espace disque, des blocks de data pour vous. Nous parlons alors d’extend. Vu ces conditions veut on vraiment désalloué des entends pour les réallouer ensuite? La réponse est non!

Comment alors récupérer de l’espace? La seule solution est d’exporter les data, d’effacer les files et importer le tout.

Que peut on faire pour optimiser ça?

Effacer un maximum avant d’ajouter de l’information et ainsi contrôler du mieux que l’on peut la taille de votre tablespace.

Et pour MySQL?

Après avoir discuté avec Peter Zaitsev et Heikki Tuuri, il semblerait que “innodb_file_per_table” qui est apparu avec MySQL 4.1.3, pourrait contourner le problème. Cela semblerai être plus performant qu’un seul tablespace.

“innodb_file_per_table” est un paramètre de configuration dans le my.cnf, et est pris en compte seulement lors de la création d’une table. Donc pour migrer d’un seul tablespace vers plusieurs fichiers vous devrez encore une fois faire un export/import.

Une fois ceci fait, si vous effacez vos entrées et faite un “OPTIMIZE TABLE” l’OS libérera l’espace. Chaque table a son propre fichier .ibd et la commande recrée un fichier, efface l’ancien et renomme le nouveau.

Ce qui veut dire qu’a un instant t vous avez 2 fois les data, prévoyez donc une place suffisante sur votre disque et n’attendez pas le dernier moment.

Pour conclure, autoextent c’est bien mais vous devez néanmoins surveillez ça de prés.

Si vous jouez un peu avec MySQL vous devez déjà connaitre la commande SHOW STATUS. Elle vous donne un snapshot de tous les compteurs interne de MySQL. Ces compteurs traquent des événements précis, par exemple:

mysql> show status like ‘Com_select’;
+—————+————+
| Variable_name | Value |
+—————+————+
| Com_select | 2293615720 |
+—————+————+
1 row in set (0.00 sec)

Voila le nombre de select qu’il y a eu sur ce server, 2.3 milliards… oups…

mysql> show status like ‘Questions’;
+—————+———–+
| Variable_name | Value |
+—————+———–+
| Questions | 533719732 |
+—————+———–+
1 row in set (0.00 sec)

‘Questions’ est le nombre de requêtes et commandes envoyées au serveur et devrait donc être logiquement la somme de toutes les “Com_*”.
Est ce un bug? 533 millions alors que rien qu’en select je suis déjà à 2.3 milliard?

En fait tous ces compteurs sont des entiers non signés. Donc sur des plateformes 32bit nous atteignons un maximum de 4.2 milliards avant que cela revienne à zéro. Donc tout est bien normal ici.

Maintenant la valeur qui est bizarre pour moi est ‘Com_admin_commands’.

mysql> show status like ‘Com_admin_commands’;
+——————–+————+
| Variable_name | Value |
+——————–+————+
| Com_admin_commands | 2731594764 |
+——————–+————+
1 row in set (0.00 sec)

2,7 milliards, ok mais à quoi correspond une commande admin? Je n’ai pas trouvé une seule ligne de documentation la dessus. Donc la seule solution est de regarder le code source (J’adore l’Open Source). C’est ainsi que l’on découvre que cela correspond à

• COM_TABLE_DUMP: demande au serveur d’envoyer la définition des tables et les data au format RAW. C’est utilisé par la réplication si vous utilisez LOAD DATA FROM MASTER. C’est commande est obsolète, NE PLUS UTILISER.
• COM_CHANGE_USER: dit au serveur que le client veut changer le user associé à la session - mysql_change_user( ).
• COM_BINLOG_DUMP: demande au serveur d’envoyer un flux constent des binlogs à partir d’une certaine valeur - mysqlbinlog
• COM_SHUTDOWN: commande l’arrêt du serveur - mysql_shutdown( )
• COM_PING: ping pour MySQL - mysql_ping( )
• COM_DEBUG: force un dump dans le log d’erreurs - mysql_dump_debug_info( )

Pour plus de détails la dessus, je vous conseille le nouveau livre de Sasha Pachev aux éditions O’Reilly, Understanding MySQL Internals qui est sorti en Avril 2007 et a été présenté pendant la conférence MySQL.

Donc revenons à cette base, nous avons 2.3 milliards de commandes admin, et cela semble être le plus vraisemblablement à cause de la fonction ping. Je me demande vraiment à quoi cela peut servir si ce n’est à garder des connections ouvertes pour rien.

• à t, je ping tout est ok
• à t+1, je perd ma connection
• à t+2, j’execute ma commande et j’ai pas de connection

C’est une fonction faite pour les clients qui gardent les connections en attente pendant longtemps…ce qui est INUTILE avec MySQL. Au lieu de garder des connections dans le vide… FERMEZ les!

Dans une architecture MySQL nous retrouvons un maître (M) pour les écritures et plusieurs esclaves (S) pour la lecture. MySQL ne permet pas l’extensibilité en écriture mais en lecture, d’ou la notion de “Shards”, un ensemble de plusieurs blocks (M-S). L’idée est de partitionner vos données. Mais ceci est une autre histoire. Pour accéder à vos esclaves, nous utilisons une VIP/Load Balancer en “round robin”. Vos applications étant ainsi re-dirigées vers un esclave de façon aléatoire.
La question est maintenant: Comment puis je maintenant enlever un esclave de la rotation?
Tout va dépendre de ce qui vous utilisez comme HW / SW.

En tout cas, ce que nous voulons:

• Ne pas arrêter l’esclave: pour pouvoir travailler dessus sans pour autant le laisser en rotation
• Ne pas utiliser skip-networking: pour ne pas avoir à redémarrer MySQL

Donc nous allons devoir couper la vérification en 2 parties:

• 1. Server OK
• 2. MySQL OK

Votre VIP devra sortir le serveur MySQL de la rotation si (1) ou si (1) ET (2). Un simple stratagème est de mettre un serveur http avec un fichier X.html. Si le fichier X.html est présent alors le serveur est ok.
Ensuite vous pouvez vérifier la connectivité MySQL en elle même.