Optimiser en profondeur la base de données MySQL sous charge élevée sous Linux

Dans le même temps, le nombre de visites en ligne continue d'augmenter. Lorsque le serveur doté de 1 Go de mémoire est évidemment très sollicité, il plante même tous les jours ou le serveur se bloque de temps en temps. Ce problème me dérange depuis plus de la moitié. par mois. MySQL utilise un algorithme très évolutif, vous pouvez donc généralement l'exécuter avec moins de mémoire ou donner à MySQL plus de mémoire pour obtenir de meilleures performances.

Après avoir installé MySQL, les fichiers de configuration doivent se trouver dans le répertoire /usr/local/mysql/share/mysql. Il existe plusieurs fichiers de configuration, notamment my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf. , Les sites Web avec des flux de trafic différents et des environnements de serveur avec des configurations différentes nécessiteront bien entendu des fichiers de configuration différents.

Dans des circonstances normales, le fichier de configuration my-medium.cnf peut répondre à la plupart de nos besoins ; généralement nous copierons le fichier de configuration dans /etc/my.cnf et n'aurons qu'à modifier ce fichier de configuration, en utilisant les variables mysqladmin extend-status –u root –p peut voir les paramètres actuels. Il existe trois paramètres de configuration qui sont les plus importants, à savoir key_buffer_size, query_cache_size, table_cache .

key_buffer_size ne fonctionne que pour les tables MyISAM,

key_buffer_size spécifie la taille du tampon d'index, qui détermine la vitesse de traitement de l'index, en particulier la vitesse de lecture de l'index. Généralement, nous le fixons à 16M. En fait, ce nombre est loin d'être suffisant pour les sites légèrement plus grands. En vérifiant les valeurs d'état Key_read_requests et Key_reads, vous pouvez savoir si le paramètre key_buffer_size est raisonnable. Le rapport key_reads / key_read_requests doit être aussi bas que possible, au moins 1:100, 1:1000 est préférable (la valeur d'état ci-dessus peut être obtenue en utilisant SHOW STATUS LIKE 'key_read%'). Ou si vous avez installé phpmyadmin, vous pouvez le voir via l'état d'exécution du serveur. L'auteur recommande d'utiliser phpmyadmin pour gérer mysql. Les valeurs d'état suivantes sont l'exemple d'analyse que j'ai obtenu via phpmyadmin :

Ce serveur fonctionne depuis 20 jours
key_buffer_size – 128 M
key_read_requests – 650759289
key_reads - 79112
Le ratio est proche de 1 : 8 000 et l’état de santé est très bon.

Une autre façon d'estimer key_buffer_size consiste à additionner l'espace occupé par les index de chaque table dans la base de données de votre site Web. Prenons ce serveur comme exemple : les index de plusieurs tables plus grandes totalisent environ 125 M. Ce nombre augmentera à mesure que la table deviendra. plus grand.

À partir de la version 4.0.1, MySQL fournit un mécanisme de mise en mémoire tampon des requêtes. En utilisant la mise en mémoire tampon des requêtes, MySQL stocke l'instruction SELECT et les résultats de la requête dans le tampon. À l'avenir, pour la même instruction SELECT (sensible à la casse), les résultats seront lus directement à partir du tampon. Selon le manuel d'utilisation de MySQL, l'utilisation de la mise en mémoire tampon des requêtes peut atteindre une efficacité allant jusqu'à 238 %.

En ajustant les paramètres suivants, vous pouvez savoir si le paramètre query_cache_size est raisonnable.
Insertions Qcache
Accès à Qcache
Pruneaux Qcache lowmem
Blocs gratuits Qcache
Blocs totaux de Qcache

Si la valeur de Qcache_lowmem_prunes est très grande, cela indique que la mise en mémoire tampon est souvent insuffisante. En même temps, si la valeur de Qcache_hits est très grande, cela indique que le tampon de requête est utilisé très fréquemment. Dans ce cas, la taille du tampon. doit être augmenté. Si la valeur de Qcache_hits est faible, cela indique que le taux de répétition de vos requêtes est très faible. Dans ce cas, l'utilisation de la mise en mémoire tampon des requêtes affectera l'efficacité, vous pouvez donc envisager de ne pas utiliser la mise en mémoire tampon des requêtes. De plus, l'ajout de SQL_NO_CACHE à l'instruction SELECT peut clairement indiquer que le tampon de requête n'est pas utilisé.
Qcache_free_blocks, si la valeur est très grande, cela indique qu'il y a de nombreux fragments dans le tampon query_cache_type spécifie s'il faut utiliser la mise en mémoire tampon des requêtes

J'ai mis :
query_cache_size = 32 Mo
query_cache_type = 1

Obtenez la valeur d'état suivante :
Les requêtes Qcache dans le cache 12737 indiquent le nombre d'éléments actuellement mis en cache
Qcache insère 20649006
Qcache atteint 79060095. Il semble que le taux de requêtes répétées soit assez élevé.
Qcache lowmem prunes 617913 Il arrive très souvent que le cache soit trop faible.
Qcache non mis en cache 189896
Mémoire libre Qcache 18573912 Espace de cache restant actuel
Blocs gratuits Qcache 5328 Ce nombre semble un peu grand et fragmenté
Blocs totaux de Qcache 30953
Si la mémoire autorise 32 Mo, vous devez en ajouter davantage.

table_cache spécifie la taille du cache de table. Chaque fois que MySQL accède à une table, s'il y a de l'espace dans le tampon de la table, la table est ouverte et placée dedans, permettant un accès plus rapide au contenu de la table. En vérifiant les valeurs d'état Open_tables et Opened_tables aux heures de pointe, vous pouvez décider si vous devez augmenter la valeur de table_cache. Si vous constatez que open_tables est égal à table_cache et que open_tables augmente, vous devez alors augmenter la valeur de table_cache (la valeur d'état ci-dessus peut être obtenue en utilisant SHOW STATUS LIKE 'Open%tables'). Notez que table_cache ne peut pas être défini aveuglément sur une grande valeur. Si la valeur est trop élevée, cela peut entraîner des descripteurs de fichiers insuffisants, entraînant des performances instables ou un échec de connexion.

Pour les machines dotées de 1 Go de mémoire, la valeur recommandée est 128-256.

Paramètres de l'auteur
table_cache = 256

Obtenez le statut suivant :
Tables ouvertes 256
Tables ouvertes 9046
Bien que open_tables soit déjà égal à table_cache, par rapport à la durée d'exécution du serveur, il fonctionne depuis 20 jours et la valeur de open_tables est également très faible. Par conséquent, augmenter la valeur de table_cache ne devrait pas être d’une grande utilité. Si la valeur ci-dessus apparaît après 6 heures d'exécution, vous devriez alors envisager d'augmenter table_cache.

Si vous n'avez pas besoin d'enregistrer les journaux binaires, désactivez cette fonction. Notez qu'après l'avoir désactivée, vous ne pourrez pas restaurer les données avant le problème. Vous devez sauvegarder manuellement toutes les instructions qui mettent à jour les données, ainsi que celles-ci. le but est de restaurer la base de données. Utilisez-la pour restaurer les données à leur état final autant que possible. De plus, si vous effectuez une réplication synchrone (Réplication), vous devez également utiliser le journal binaire pour transférer les modifications.

log_bin spécifie le fichier journal Si aucun nom de fichier n'est fourni, MySQL générera lui-même le nom de fichier par défaut. MySQL ajoutera automatiquement un index numérique après le nom du fichier et régénérera un nouveau fichier binaire à chaque démarrage du service. De plus, utilisez log-bin-index pour spécifier le fichier d'index ; utilisez binlog-do-db pour spécifier la base de données à enregistrer ; utilisez binlog-ignore-db pour spécifier la base de données à ne pas enregistrer. Remarque : binlog-do-db et binlog-ignore-db ne spécifient qu'une seule base de données à la fois. La spécification de plusieurs bases de données nécessite plusieurs instructions. De plus, MySQL changera tous les noms de bases de données en minuscules. Vous devez utiliser tous les noms en minuscules lors de la spécification de la base de données, sinon cela ne fonctionnera pas.

Pour désactiver cette fonction, ajoutez simplement le signe # devant.
#log-bin

Activer le journal des requêtes lentes (journal des requêtes lentes)

Le journal des requêtes lentes est utile pour traquer les requêtes problématiques. Il enregistre toutes les requêtes qui ont vérifié long_query_time et, si nécessaire, enregistre sans utiliser d'index. Voici un exemple de journal de requêtes lentes :

Pour activer les journaux de requêtes lentes, vous devez définir les paramètres log_slow_queries, long_query_times et log-queries-not-using-indexes.

log_slow_queries spécifie les fichiers journaux. Si aucun nom de fichier n'est fourni, MySQL générera lui-même un nom de fichier par défaut. long_query_times spécifie le seuil de requête lente, la valeur par défaut est de 10 secondes. log-queries-not-using-indexes est un paramètre introduit après la version 4.1.0, qui indique que les requêtes qui n'utilisent pas d'index sont enregistrées. L'auteur définit long_query_time=10

Paramètres de l'auteur :

sort_buffer_size = 1M
max_connections=120
attente_timeout = 120
back_log=100
read_buffer_size = 1M
fil_cache=32
interactif_timeout=120
thread_concurrency = 4

Description du paramètre :

arriéré
Le nombre de connexions que MySQL doit avoir. Cela fonctionne lorsque le thread principal MySQL reçoit de nombreuses demandes de connexion sur une courte période de temps, puis que le thread principal prend un certain temps (bien que brièvement) pour vérifier les connexions et démarrer un nouveau thread. La valeur back_log indique combien de requêtes peuvent être stockées dans la pile sur une courte période de temps avant que MySQL ne cesse temporairement de répondre aux nouvelles requêtes. Ce n'est que si vous prévoyez de nombreuses connexions sur une courte période que vous devez l'augmenter. En d'autres termes, cette valeur correspond à la taille de la file d'attente d'écoute pour les connexions TCP/IP entrantes. Votre système d'exploitation a sa propre limite sur cette taille de file d'attente. La page de manuel de l'appel système Unix Listen(2) devrait contenir plus de détails. Consultez la documentation de votre système d'exploitation pour connaître la valeur maximale de cette variable. Tenter de définir back_log une valeur supérieure à la limite de votre système d'exploitation n'aura aucun effet.
max_connections
Le nombre maximum de connexions simultanées est de 120. S'il dépasse cette valeur, il sera automatiquement récupéré et les problèmes seront automatiquement résolus.

fil_cache
Je n'ai trouvé aucune instruction spécifique, mais après l'avoir défini sur 32, plus de 400 threads ont été créés en 20 jours, alors que des milliers de threads ont été créés en un jour auparavant, c'est donc toujours utile.

thread_concurrency
#Définissez le numéro de votre processeur x2, par exemple, s'il n'y a qu'un seul processeur, alors thread_concurrency=2
#Il y a 2 processeurs, alors thread_concurrency=4

sauter-innodb
#Supprimer le support d'InnoDB