21 conseils pour optimiser MySQL

Aujourd'hui, un ami m'a demandé comment optimiser MySQL Je l'ai trié selon ma réflexion, et cela peut être grossièrement divisé en 21 directions. Il y a aussi quelques détails (table cache, conception de la table, index conception, terminal cache etc.) ne sera pas répertorié maintenant. Pour un système, c'est également un bon système de pouvoir effectuer ce qui suit à un stade précoce.

1. Assurez-vous qu'il y a suffisamment de mémoire

Le facteur le plus critique pour que la base de données fonctionne efficacement est une mémoire suffisante. il est volumineux, les données peuvent être mises en cache et les mises à jour peuvent d'abord être effectuées en mémoire. Cependant, différentes entreprises ont des besoins différents en matière de mémoire. Il est recommandé que la mémoire représente 15 à 25 % des données. Pour les données particulièrement chaudes, la mémoire doit en principe atteindre 80 % de la taille de la base de données.

2. Besoin de processeurs plus nombreux et plus rapides

MySQL 5.6 peut utiliser 64 cœurs, mais chaque requête MySQL ne peut s'exécuter que sur un seul processeur, elle nécessite donc plus de processeurs. Un processeur plus rapide serait préférable. concurrence.

3. Choisissez un système d'exploitation approprié

Selon les recommandations officielles, Solaris est le plus recommandé De la production actuelle, CentOS et REHL sont tous deux de bons choix. Pour ceux après 6, bien sûr, Oracle Linux est également un bon choix. Bien que Windows ait été optimisé depuis MySQL 5.5, il n'est pas recommandé d'utiliser Windows dans un environnement à haute concurrence

4. Optimisez correctement les paramètres système

Modifiez le descripteur de fichier ulimit -n default 1024. est trop petit

Limite du nombre de processus ulimit -u Différentes versions sont différentes

Désactiver NUMA numctl -interleave=all

5. Choisissez l'algorithme d'allocation de mémoire approprié

L'allocation de mémoire par défaut est le malloc de c. Il existe désormais de nombreux algorithmes d'allocation de mémoire optimisés :

jemalloc et tcmalloc

À partir de MySQL 5.5, la méthode de stockage interne déclarée est prise en charge.

　[mysqld_safe]

malloc-lib = tcmalloc

Ou pointez directement sur le fichier so

[mysqld_safe]

malloc- lib=/usr/local/lib/libtcmalloc_minimal.so

6. Utilisez un périphérique de stockage SSD ou une carte SSD plus rapide

Le support de stockage affecte grandement la vitesse de lecture et d'écriture aléatoire des mises à jour de MySQL. L'émergence d'une nouvelle génération de périphériques de stockage, de SSD et de cartes SSD, a également fait briller MySQL, et Taobao a mené un bon combat au sein de l'IOE.

7. Choisissez un bon système de fichiers

Nous recommandons XFS et Ext4. Si vous utilisez toujours ext2 ou ext3, veuillez mettre à niveau dès que possible. XFS est recommandé, c'est également un système de fichiers que Linux prendra en charge à l'avenir.

Système de fichiers hautement recommandé : , nobarrier)

Paramètres de montage ext4 :

ext4 (rw, noatime, nodiratime, nobarrier, data=ordered)

Si vous utilisez un SSD ou un disque SSD, vous devez prendre en compte :

　• innodb_page_size = 4K

　• Innodb_flush_neighbors = 0

　9. Sélectionnez le calendrier d'E/S approprié

Si c'est normal, veuillez utiliser la date limite. La valeur par défaut est noop

echo deline >/sys/block/{DEV-NAME}/queue/scheduler

10. Choisissez le Raid approprié. Stratégie de cache de carte

Veuillez utiliser un Raid alimenté et l'activer. WriteBack est bon pour accélérer le journal redo, le journal binaire et le fichier de données.

11. Désactiver le cache de requêtes

Le cache de requêtes est un peu inutile dans Innodb. Les données Innodb elles-mêmes peuvent être mises en cache dans le pool de tampons d'Innodb. Il s'agit d'un cache de jeu de résultats. activé, mis à jour et écrit. La vérification du cache de requêtes augmentera la surcharge d'écriture.

Dans MySQL 5.6, le cache des requêtes est désactivé.

12. Utiliser le pool de threads

Désormais, une donnée correspond à plus de 5 scénarios d'application, mais MySQL a une fonctionnalité selon laquelle les performances diminueront à mesure que le nombre de connexions augmente, donc pour ceux qui en ont plus plus de 200 connexions. Veuillez envisager d'utiliser un pool de threads dans les scénarios futurs. C'est une excellente invention.

13. Ajuster correctement la mémoire

13.1 Réduire l'allocation de mémoire pour les connexions

Les connexions peuvent être mises en cache à l'aide de thread_cache_size, qui n'est pas aussi puissant que le pool de threads. La mémoire allouée par la base de données sur la connexion est la suivante :

max_used_connections * (

read_buffer_size +

read_rnd_buffer_size +

join_buffer_size +

sort_buffer_size +

binlog_cache_size +

thread_stack +

2 * net_buffer_length …

)

　13.2 Créer un pool de tampons plus grand

Allouez 60 à 80 % de la mémoire à innodb_buffer_pool_size Cela ne doit pas dépasser la taille des données et n'en allouez pas plus de 80 %, sinon le swap sera utilisé.

　14. Choisissez raisonnablement le mécanisme de rafraîchissement du LOG

　Redo Logs :

　- innodb_flush_log_at_trx_commit = 1 // Le plus sûr

　- innodb_flush_log_at_trx_commit = 2 // Meilleures performances

- innodb_flush_log_at_trx_commit = 0 // Meilleures performances

Binlog :

binlog_sync = 1 Nécessite la prise en charge des validations de groupe. Si vous n'avez pas cette fonction, vous pouvez envisager binlog_sync=. 0 pour obtenir de meilleurs résultats.

Fichier de données :

innodb_flush_method = O_DIRECT

15. Veuillez utiliser la table Innodb

Plus de ressources peuvent être utilisées et l'opération de modification en ligne a été améliorée . Actuellement, le texte intégral non chinois est également pris en charge, ainsi que l'accès à l'API Memcache. C'est actuellement le meilleur moteur pour MySQL.

Si vous êtes toujours dans MyISAM, pensez à changer rapidement.

16. Configurer un journal de rétablissement plus grand

Dans le passé, lorsque Percona 5.5 et MySQL 5.5 officiel rivalisaient pour les performances, le conseil gagnant était d'allouer plus de 4 Go de journal de rétablissement, et la restauration officielle MySQL5.5 Le journal ne peut pas dépasser 4G À partir de MySQL 5.6, il peut dépasser 4G. Habituellement, la taille totale du journal de restauration dépasse 500 Mo. Vous pouvez observer la quantité de journalisation générée et allouer la quantité de journalisation supérieure à une heure.

17. Optimiser les E/S du disque

Innodb_io_capactiy Configurez simplement 800 sous sas 15000 tr/min, et configurez plus de 2000 sous ssd.

Dans MySQL 5.6 :

innodb_lru_scan_degree = innodb_io_capacity / innodb_buffer_pool_instances

innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)

　18. Utiliser un espace table indépendant

À l'heure actuelle, les nouvelles fonctionnalités sont la prise en charge indépendante de l'espace table :

Tronquer le recyclage de l'espace table

Transfert d'espace table

Mieux vaut optimiser la fragmentation lors de la gestion les performances augmentent,

Globalement, il est inutile d'utiliser des espaces table indépendants.

19. Configurer une concurrence raisonnable

innodb_thread_concurrency = La concurrence est le paramètre le plus fréquemment modifié dans Innodb. Différentes versions peuvent présenter des modifications dans différentes versions mineures. Recommandation générale :

Lors de l'utilisation d'un pool de threads :

innodb_thread_concurrency = 0 convient.

S'il n'y a pas de pool de threads :

5.5 Recommandation : innodb_thread_concurrency =16 – 32

5.6 Recommander innodb_thread_concurrency = 36

20. Optimiser les transactions Niveau d'isolement

La valeur par défaut est Lecture répétable

Il est recommandé d'utiliser le format Binlog validé en lecture qui utilise un mélange ou une ligne

Niveau d'isolement inférieur = meilleures performances

21. Faites attention à la surveillance

Aucun environnement ne peut être séparé de la surveillance. S'il n'y a pas de surveillance, cela peut être comme un aveugle et un éléphant. Il est recommandé de créer une surveillance avec zabbix+mpm.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!