Analyser l’existant

Vous pouvez optimiser la structure de vos tables existantes en analysant leur contenu, après bien sûr un certain temps d’exploitation. Pour cela, il suffit de faire appel à la procédure ANALYSE fournie par MySQL :

SELECT * FROM employee PROCEDURE ANALYSE(nb_de_colonnes);

L’analyse se fera sur les nb_de_colonnes premières colonnes de la table. Vous aurez alors tout un rapport sur le contenu des données par champ : la valeur min, max, la structure conseillée correspondante.

• Choisir CHAR ou VARCHAR ?

Le CHAR occupe plus d’espace disque, mais son traitement est beaucoup plus rapide. Le VARCHAR fragmente les données, donc les rend en partie moins stables, mais permet d’économiser beaucoup de place.

• Stocker les fichiers dans la table (TEXT/BLOB), ou sur un FTP ?

Le fait de stocker dans la table alourdit la base, et si le fichier est trop volumineux, celui-ci peut être tronqué sans qu’on ne soit prévenu. De plus, ils ont plus de chance d’être fragmentés. Mais cela peut aussi apporter des avantages. Comme par exemple bénéficier de la réplication MySQL entre les serveurs, de l’indexation fulltext, de tout centraliser dans un seul et même backup. A vous de voir maintenant !

• Conseils sur les types de nombres

Le choix entre un DECIMAL, FLOAT, ou DOUBLE importe peu, la différence n’est pas trop grande. Par contre le choix entre un TINYINT, SMALLINT,MEDIUMINT, INT, ou BIGINT permet d’économiser de nombreux et précieux octets.

Pour spécifier l’âge d’une personne , par exemple, on pourra choisir de le typer UNSIGNED TINYINT ce qui nous donne une palette de 0 à 255, largement suffisant. UNSIGNED permet de récupérer le bit qui détermine le signe pour le nombre, on gagne donc une puissance de 2 (de 0 à 255 au lieu de -127 à +127). Pour le détail sur l’occupation mémoire des types de nombres, je vous renvoie à la doc MySQL.

• Utilisation des ENUM

Comment feriez vous pour préciser la fonction d’un employé ? Disons qu’il existe : ‘CADRE’, ‘NON CADRE’, et ‘NON DEFINI’. Deux possibilités s’offrent à nous. On peut créer une table de fonction contenant 3 lignes, et qu’on va joindre à la table employe pour en définir la fonction. C’est ce qu’on trouve dans une base de données relationnelle pure. Et c’est la meilleure solution à adopter dans le cas ou la table fonction doit être utilisée pour d’autres relations. Du point de vue des performances, cela oblige à alourdir la requête d’une jointure pour simplement connaître la spécialisation d’un objet. Il est alors permis d’envisager d’utiliser un type ENUM qui va définir pour chaque ligne, directement, la fonction de l’employé.

• Utilisation des SET

On n’y pense pas forcément, mais il existe également un type SET, ressemblant au type ENUM, mais qui permet de définir non pas 1, mais plusieurs valeurs possibles pour un champ. C’est une technique qui n’est pas forcément facile à expliquer, mais pour ceux qui connaissent un peu la commande chmod d’unix ,c’est le même principe. Chaque valeur du SET est en fait une valeur puissance de 2, qui s’écrit en binaire avec un 1 et de 0 à n 0. La superposition de ces valeurs permet de déterminer l’ensemble des valeurs pour ce champ. Exemple, le champ vaut 11. En binaire, cela donne 1011. La champ prend les valeurs 1, 3 et 4 des valeurs définies par le SET. Petit lien vers la doc MySQL.

Comment les vues peuvent-elles permettre d’optimiser son application ? C’est ce que nous allons voir…
Continue reading →

Le suivi des interventions

Afin de centraliser les changement effectués sur la base de données, et pour que vos collègues puissent s’y retrouver, pensez à enregistrer (pourquoi pas dans une base de données d’ailleurs) toutes les interventions d’optimisation que vous faîtes, ou qui sont à faire, et les commentaires qui s’y rattachent. Cela permet de se souvenir du but de l’intervention, et en cas de dommage collatéraux sur cette modif, les commentaires pourront être pris en compte pour corriger la faille.

On peut par exemple enregistrer cela sous la forme d’un historique : la date de début, voire de fin, l’auteur, l’identification du problème, les relations possibles avec les autres interventions, une description du problème, son résultat, etc.

L’analyse des résultats

Lorsqu’intervient un problème, plusieurs solutions peuvent être envisageables. Par exemple, créer un index, améliorer la requête, mettre à jour des variables du serveur, etc. Le fait d’en modifier plusieurs en même temps ne permet pas d’identifier exactement la cause du problème initial, et peut même provoquer de nouvelles pannes dans d’autres cas d’utilisations. Le fait de ne provoquer qu’un changement à la fois permet de s’assurer de la cause du problème, et d’éviter, ou le cas échéant identifier les causes des problèmes collatéraux.

Les tests périodiques

Afin de s’assurer des performances globales de sa base de données, et de vérifier que le système général ne se fatigue pas, on peut par exemple tous les 6 mois, tous les ans, suivre le résultat d’exécution d’une même requête. MySQL fournit un outil permettant cela. La fonction BENCHMARK Permet d’exécuter n fois une même requête. Son résultat retourne toujours 0, mais ce qu’il est intéressant de noter, c’est le temps d’exécution de la requête.

Cet outil permet aussi de comparer la puissance de calcul de vos serveurs.

SELECT BENCHMARK(5000000, MD5(rand()));

Ici, on exécute 5 millions de fois le calcul du hashage d’un nombre aléatoire. Effectuer plusieurs fois la requête à des intervalles de temps plus ou moins espacés et noter la moyenne des temps de calculs obtenus. Revenez dans 6 mois, et recommencez, vous saurez si le système ne fatigue pas trop. Ou exécutez ces mêmes calculs sur un autre serveur pour comparer leur puissance.

Suivre les logs

Vous pouvez configurer MySQL pour loguer les requêtes lentes (slow-queries), et les requêtes qui ne font pas appel aux index car ceux-ci sont inexistants :

--log_slow_queries --log_queries_not_using_indexes

Voici la marche à suivre pour copier à chaud une base de données d’un serveur (de production généralement), à un autre (de réplication, de développement, …) directement, sans passer par des fichiers intermédiaires.

Nous allons créer un fichier batch pour cela. C’est rapide et ça dépanne. Vous pourrez par la suite vous créer un script un peu plus élaboré. Les besoins du moment m’ont demandé de le faire pour Windows, mais il sera très aisé de transcrire cela en script unix.

Continue reading →