Meilleures pratiques pour l'optimisation des bases de données MySQL

Question

Quelles sont les meilleures pratiques pour optimiser une installation MySQL afin d'obtenir les meilleures performances lors de la gestion de tables un peu plus grandes (> 50k enregistrements avec un total d'environ 100MB par table) ? Nous cherchons actuellement à réécrire DelphiFeeds.com (un site de nouvelles pour la communauté des programmeurs Delphi) et nous avons remarqué que de simples instructions de mise à jour peuvent prendre jusqu'à 50 ms. Cela semble beaucoup. Existe-t-il des paramètres de configuration recommandés que nous devrions activer/paramétrer et qui sont généralement désactivés sur une installation MySQL standard (par exemple, pour profiter de plus de RAM pour mettre en cache les requêtes et les données, etc.)

Par ailleurs, quelles sont les implications en termes de performances du choix des moteurs de stockage ? Nous prévoyons d'utiliser InnoDB, mais si MyISAM est recommandé pour des raisons de performances, nous pourrions l'utiliser.

Answer 1

La "meilleure pratique" est la suivante :

1. Mesurez les performances, en isolant le sous-système concerné aussi bien que possible.
2. Identifier la cause première du goulot d'étranglement. Êtes-vous limité en termes d'E/S ? Limité par l'unité centrale ? De la mémoire ? Vous attendez des verrous ?
3. Apporter des changements pour atténuer la cause fondamentale que vous avez découverte.
4. Mesurez à nouveau, pour démontrer que vous avez résolu le goulot d'étranglement et que vous êtes en mesure d'assurer la sécurité. de combien .
5. Passez à l'étape 2 et répétez l'opération si nécessaire jusqu'à ce que le système fonctionne suffisamment rapidement.

S'abonner au flux RSS à l'adresse suivante http://www.mysqlperformanceblog.com et lisez également ses articles historiques. C'est une ressource extrêmement utile pour les questions de performance. Par exemple, vous avez posé une question sur InnoDB par rapport à MyISAM. Leur conclusion : InnoDB est en moyenne 30 % plus performant que MyISAM. Il existe toutefois quelques scénarios d'utilisation dans lesquels MyISAM est plus performant qu'InnoDB.

• InnoDB vs. MyISAM vs. Falcon benchmarks - part 1

Les auteurs de ce blog sont également co-auteurs de "High Performance MySQL", le livre mentionné par @Andrew Barnett.

---

Re comment from @ʞɔıu : La façon de savoir si l'on est lié aux E/S, au CPU ou à la mémoire dépend de la plateforme. Le système d'exploitation peut proposer des outils tels que ps, iostat, vmstat ou top. Il se peut aussi que vous deviez vous procurer un outil tiers si votre système d'exploitation n'en fournit pas.

Fondamentalement, la ressource qui est utilisée à 100 %/saturation est susceptible d'être votre goulot d'étranglement. Si la charge du processeur est faible mais que la charge des E/S est à son maximum pour votre matériel, alors vous êtes limité par les E/S.

Il ne s'agit toutefois que d'un seul point de données. Le remède peut également dépendre d'autres facteurs. Par exemple, une requête SQL complexe peut effectuer un tri de fichiers, ce qui occupe les entrées/sorties. Faut-il augmenter le matériel ou le rendre plus rapide, ou faut-il revoir la conception de la requête pour éviter le tri de fichiers ?

Les facteurs sont trop nombreux pour être résumés dans un post StackOverflow, et le fait qu'il existe de nombreux livres sur le sujet le confirme. Le fonctionnement efficace des bases de données et l'utilisation optimale des ressources est un travail à temps plein qui nécessite des compétences spécialisées et une étude constante.

---

Jeff Atwood vient d'écrire un article de blog intéressant sur la détection des goulets d'étranglement dans un système :

• Le jeu de la performance informatique

Answer 2

Achetez "High Performance MySQL" chez O'Reilly. Il y a presque 700 pages sur le sujet, donc je doute que vous trouviez une réponse succincte sur SO.

Answer 3

Il est difficile de brosser les choses à grands traits, mais il est possible d'avoir une vue d'ensemble modérée.

• Vous devez évaluer les ratios lecture/écriture. Pour les tables dont le ratio est inférieur à environ 5:1, vous bénéficierez probablement d'InnoDB car les insertions ne bloqueront pas les sélections. Mais si vous n'utilisez pas de transactions, vous devriez modifier innodb_flush_log_at_trx_commit à 1 pour retrouver les performances de MyISAM.
• Examinez les paramètres de la mémoire. Les paramètres par défaut de MySQL sont très conservateurs et certaines limites de mémoire peuvent être augmentées d'un facteur 10 ou plus sur du matériel ordinaire. Cela profitera à vos SELECTs plutôt qu'à vos INSERTs.
• MySQL peut enregistrer des éléments tels que les requêtes qui n'utilisent pas d'index, ainsi que les requêtes qui prennent trop de temps (à définir par l'utilisateur).
• Le cache des requêtes peut être utile, mais vous devez l'instrumenter (c'est-à-dire voir combien il est utilisé). Cacti peut le faire, tout comme Munin.
• La conception de l'application est également importante :
  • La mise en cache légère d'ensembles de données fréquemment consultés mais de petite taille fera une grande différence (durée de vie du cache de quelques secondes).
  • Ne récupérez pas des données que vous avez déjà sous la main.
  • Le stockage en plusieurs étapes peut s'avérer utile en cas de volume élevé d'insertions dans des tables qui sont également lues de manière intensive. L'idée de base est que vous pouvez avoir une table pour les insertions ad hoc ( INSERT DELAYED peut également être utile), mais un processus par lots pour déplacer les mises à jour dans MySQL de là vers l'endroit où toutes les lectures ont lieu. Il existe des variantes de cette méthode.
• N'oubliez pas que la perspective et le contexte sont également importants. UPDATE peut en fait être tout à fait triviale si cette "longue" mise à jour n'a lieu qu'une fois par jour.

Answer 4

Il existe des tonnes de bonnes pratiques qui ont été discutées précédemment, il n'y a donc aucune raison de les répéter. Pour des conseils concrets sur ce qu'il faut faire, j'essaierais de lancer Accordeur MySQL . Il s'agit d'un script perl script que vous pouvez télécharger et exécuter sur votre serveur de base de données. Il vous donnera un grand nombre de statistiques sur les performances de votre base de données (par exemple, les hits de cache) ainsi que des recommandations concrètes sur les problèmes ou les paramètres de configuration qui doivent être ajustés pour améliorer les performances.

Bien que ces statistiques soient toutes disponibles dans MySQL lui-même, je trouve que cet outil les fournit d'une manière beaucoup plus facile à comprendre. Bien qu'il soit important de noter que les recommandations sont à prendre au cas par cas, j'ai trouvé qu'elles étaient généralement assez précises. Assurez-vous simplement que vous avez bien exercé la base de données au préalable avec un trafic réaliste.