Comment Optimiser mySQLComme nous l’expliquons sur un article posté
sur le site, la mauvaise optimisation mySQL condamne le projet internet et pose des barrières infranchissables à son développement. Aucun site ne peut devenir populaire si son mySQL n’est pas optimal.
Avant de lancer un projet, passez du temps à choisir la bonne application de tout le choix que vous avez en ligne. Vérifiez qu’il y a des sites à fort trafic qui utilisent la même application avec succès. Si vous faites develloper vos propres programmes, insistez particulierement sur les tests de charge. Ce sont les plus importants pour l’avenir de votre projet.
Pour les applications existantes, connectez-vous en root mySQL sur le phpmyadmin de votre serveur ou VPS. Si vous ne connaissez pas le code d’accès, contactez notre Staff pour l’obtenir. Une fois connecté, il faut cliquer en haut sur le tab « Status ». Le serveur va prendre quelques instants à vous afficher une page qui s’appelle « Runtime Information ». Elle vous présentera des statistiques techniques sur le fonctionnement de la base de données et sa relation aux requêtes qu’elle reçoit. Tout ce qui est affiché en route nécessite votre attention. Ce sont des valeurs anormales qui ont un impact négatif sur la vitesse et la performance de votre site. Plus vous allez avoir de visiteurs, plus ces valeurs vont jouer un rôle important. Voici un exemple issu d’un cas concret :
Ici, la valeur en rouge concerne le paramètre
Innodb_buffer_pool_reads. Le client utilise ici InnoDB comme moteur de la base mySQL. Plusieurs moteurs sont disponibles et choisis en fonction de l’utilisation de la base. InnoDB est un choix très courant. Quand mySQL est lancé, InnoDB crée un espace dans la mémoire du serveur. Cet espace s’appelle le Buffer Pool. Les données les plus demandées, sont stockées dans la mémoire par InnoDB. Un algorithme complexe lui permet progressivement de sélectionner les données à garder dans la RAM du serveur. Quand celles-ci subissent des modifications, elles sont faites dans la copie qui est dans la RAM. Plus tard, les opérations sont groupées et écrites sur le disque. Ceci augmente la performance parce que la RAM est plus rapide qu’un disque. Plus le Buffer Pool de InnoDB est grand, plus il va utiliser de mémoire comme cache de lecture/écriture.
Quand un client lance une requête (par exemple lit un post sur un forum), le bloc de données concerné par la requête est injectée au milieu du Buffer Pool. S’il est redemandé encore, il monte dans la liste et peut même finir dans une liste spéciale des requêtes les plus demandées. Si tout à coup il est de moins en moins demandé, il « vieillit » et tombe en bas de la liste. Avec le temps, il devient un candidat à la suppression et InnoDB l’éjecte du Buffer Pool.
Quand une requête est faite et qu’elle ne comporte pas la clause WHERE, elle va importer une énorme quantité de données dans le Buffer Pool et détruire toute la liste optimisée par InnoDB.
Chaque fois que ceci survient, InnoDB recommence à lire les données depuis le disque. Vous avez une baisse temporaire de performance et le compteur Innodb_buffer_pool_reads est incrémenté. La valeur parfaite c’est zero mais elle est impossible aj obtenir. L’important avec ce paramètre, ce n’est pas sa valeur à un moment donné, mais son taux d’augmentation.
Pour éviter des soucis de cette nature, il faut veiller à ce que vos requêtes PHP soient programmées de manière à ne pas sélectionner toute la base inutilement. La clause WHERE doit être tout le temps utilisée pour chercher spécifiquement les enregistrements nécessaires. Imaginez un forum où la totalité des profils des membres sont importés dans la mémoire chaque fois qu’un seul membre modifie son adresse email son avatar. Ca marchera tant que vous n’avez que 10 membres, mais avec 5000 membres, ça cause des problèmes à tous les coups.
A suivre…
