Quel est le meilleur outil de performance, le plus convivial, pour MySQL ? J'aimerais qu'on m'aide à identifier le goulot d'étranglement de ma configuration. Le problème se situe-t-il dans les instructions SQL, les variables de configuration ou autre chose ?
La mauvaise nouvelle : il existe des outils d'interface graphique pour vous aider, mais c'est un travail qualifié et très varié. Ils ne couvrent donc pas tout, et il est probable que vous devrez utiliser des outils en ligne de commande, des instructions SQL, etc. pour vous aider. Je n'ai vraiment utilisé que les outils en ligne de commande. Je vais donner un aperçu des choses que je connais/ai utilisées :
Tout d'abord, vous avez besoin d'une bonne conception de la base de données. Si la conception est mauvaise, vous ne pourrez pas aller bien loin. Cela inclut la normalisation, ainsi que l'utilisation de types appropriés pour les champs. Je vais laisser ce point ici, car je pense que c'est un peu une parenthèse, et pas ce que vous recherchez.
Assurez-vous que le cache de requêtes MySQL est configuré et fonctionne et donnez-lui un peu plus de RAM si vous le pouvez, et assurez-vous que vos requêtes importantes ne font rien qui empêche mysql de les mettre en cache. Par exemple, l'utilisation de la fonction NOW() dans les requêtes fait cela - pour des raisons évidentes - NOW change toutes les secondes ! Vous pouvez à la place mettre un timestamp dans le sql, et utiliser le temps à la minute/heure/jour près (la plus grande période avec laquelle vous pouvez vous en sortir) pour permettre à mysql de bénéficier d'un avantage de cache.
Pour commencer à optimiser les choses : Mettre "EXPLAIN" devant select est LE moyen de voir comment une requête est exécutée et de déterminer comment l'améliorer. Apprendre à interpréter la sortie : http://dev.mysql.com/doc/refman/5.0/en/using-explain.html Vous pourrez souvent ajouter de nouveaux index ou ajouter des colonnes aux index existants pour améliorer les choses. Mais vous rencontrerez aussi des cas où les requêtes devront être restructurées.
Pour commencer à améliorer les performances avec MySQL (en supposant que vous ne sachiez pas déjà quelle est la requête problématique), il faut vérifier le journal des requêtes lentes - il enregistre dans un fichier toutes les requêtes qui prennent plus de x secondes.
La vue d'ensemble, y compris la configuration si elle n'est pas déjà enregistrée, est ici : http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html - J'ai également constaté que la mise à 0 de long_query_time pendant un jour ou deux, de sorte que toutes les requêtes sont enregistrées ici avec le temps pris, est un moyen utile pour avoir une idée de l'évolution exacte des performances. Mais je ne m'y risquerais pas immédiatement ! Et ne le laissez pas actif, les journaux peuvent devenir énormes.
Une fois que vous avez obtenu quelques jours d'enregistrement, j'ai trouvé mysqlsla (mysql slow log analyser) ici : http://hackmysql.com/mysqlsla est un bon outil.
Il peut faire plus qu'une simple analyse du journal des requêtes lentes - lisez le manuel. Mais pour expliquer ce qu'il fait pour les journaux de requêtes lentes : le journal des requêtes lentes peut contenir beaucoup de données, il peut donc être difficile de déterminer quelles requêtes sont les plus coûteuses dans l'ensemble - par exemple : prendre en compte le nombre de fois qu'elles sont exécutées et quand deux requêtes sont en fait les mêmes avec un identifiant différent dans une clause where.
MySQL sla fait tout cela pour vous. Il parcourt le journal et peut regrouper les requêtes qui sont identiques ou qui ont des valeurs différentes dans les clauses where. Il vous présente ensuite (par défaut) les 10 premières requêtes en termes de temps d'exécution total - ce qui réserve souvent des surprises, mais constitue généralement le point de départ le plus productif - prenez la requête la plus coûteuse et utilisez EXPLAIN dessus pour voir si vous pouvez l'améliorer.
Certaines requêtes prennent beaucoup de temps et ne peuvent pas être améliorées facilement. Dans ce cas, pouvez-vous obtenir les données d'une autre manière ou au moins les mettre en cache à la place ? Vous pouvez même constater qu'il est nécessaire de modifier le schéma de la base de données. De même, certaines requêtes peuvent se trouver en haut de la liste de mysqlsla parce que vous les exécutez souvent (surtout si long_query_time est défini à 0), même si elles s'exécutent assez rapidement. Peut-être est-il temps d'ajouter un peu de cache à votre application ?
http://www.maatkit.org/ semble également prometteur - je ne l'ai jamais utilisé, mais l'outil mk-query-profiler devrait être utile pour examiner plus en profondeur les raisons de la lenteur des requêtes.
Une chose complètement distincte à regarder également : la page "status" dans PHPMYADMIN (ou vous pouvez exécuter toutes les requêtes pour générer cette information ....) - elle met en évidence les choses qu'elle pense être mauvaises en rouge, et peut vous aider à voir où vous pourriez bénéficier de l'allocation des ressources système. Je n'en sais pas beaucoup sur ce sujet - mon approche a toujours été que si quelque chose est en rouge et semble mauvais, il faut aller se documenter sur le sujet et décider si c'est important et si je dois faire quelque chose (ce qui signifie généralement allouer plus de ressources à MySQL en changeant la configuration).
Récemment, j'ai découvert que l'exécution de SHOW PROCESSLIST peut également être utile sur un serveur qui souffre. Bien qu'il ne vous donne que des informations en direct (enfin, un instantané en direct), il peut vous aider à avoir une idée de ce qui se passe à un moment donné, surtout si vous rafraîchissez plusieurs fois et observez les changements. J'ai récemment remarqué qu'un serveur utilisait toutes les connexions mysql disponibles pour exécuter une requête identique en utilisant cette méthode. Bien sûr, cela aurait été dans le journal des requêtes lentes, mais c'était un moyen vraiment rapide et évident de voir ce qui se passait.
Si je comprends bien, mysqlsla et maatkit ont fusionné dans percona toolkit. Néanmoins, un bon visualisateur de journaux de requêtes lentes mysql est basé sur le web. github.com/benkaiser/mysql-slow-query-log-visualizer
EXPLIQUER est votre ami. Sinon, vous devez vous fier à un mélange d'outils ad hoc, de tests et d'outils du système d'exploitation pour savoir exactement ce qui se passe.
Beaucoup de choses peuvent être vues efficacement soit à partir des outils du système d'exploitation (bon pour déterminer si une situation est liée au CPU ou à l'IO) ou les diverses variables qui peuvent être vues dans MySQL lui-même (montrer les variables globales, montrer l'état du moteur innodb, etc).
Je pense qu'il est vraiment important d'avoir un ENVIRONNEMENT DE TEST DE PERFORMANCE. Obtenez une base de données de taille de production avec une charge similaire à celle de la production (en utilisant la simulation de charge) fonctionnant sur un matériel de qualité de production dans votre laboratoire (coûteux mais essentiel).
Une fois que vous avez fait cela, vous pouvez modifier tous les paramètres que vous voulez en toute sécurité, en sachant que même si le serveur entier fond, vous pouvez le reconstruire sans que rien ne soit affecté.
Ne faites pas les choses spécifiques que les gens suggèrent à moins de les avoir testées dans votre environnement de test de performance et d'avoir constaté qu'elles sont manifestement bénéfiques.
Pour l'essentiel, l'optimisation des performances porte généralement sur trois domaines, dans cet ordre :
Les deux premiers sont souvent négligés au profit du dernier, qui peut aider mais seulement après que les deux premiers aient été réalisés.
Ne sous-estimez pas l'importance de réduire la quantité de travail que le serveur doit effectuer - il est toujours préférable d'utiliser des champs plus petits, moins de lignes, moins de colonnes, etc.
J'ai trouvé MONyog pour être d'une GRANDE aide lorsque j'avais un serveur mysql en souffrance.
Il s'agit d'un système basé sur le Web qui analyse votre système 24 heures sur 24, 7 jours sur 7, et vous donne des suggestions judicieuses concernant les meilleures valeurs des variables système, la taille des caches, etc...
Il dispose également d'une fonction intégrée d'analyse des requêtes lentes de type mysqlsla.
Dommage que ce ne soit pas gratuit. 
MONyog - Moniteur et conseiller MySQL a une nouvelle interface utilisateur que j'adore, j'aime surtout ces fonctionnalités :
Trouver le SQL problématique - en utilisant le slow log, le general log, PROCESSLIST et à travers PROXY
Alerte sur les conditions critiques telles que - le serveur est en panne, trop de connexions, etc.
Contrôlez périodiquement le résultat de votre propre requête
Surveillance de la réplication
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
