Décrire la concurrence des threads
Chaque connexion client a son propre thread, et les requêtes sont exécutées dans ce thread. Chaque thread s’exécute sur un seul cœur. La concurrence est le nombre de threads qui peuvent s’exécuter en même temps. Par défaut, le moteur de stockage InnoDB exécute simultanément autant de threads que nécessaire.
InnoDB utilise plusieurs paramètres de serveur pour gérer la concurrence des threads. Deux paramètres régissent le nombre de threads concurrents :
- innodb_thread_concurrency
- innodb_thread_sleep_delay
La valeur par défaut de innodb_thread_concurrency est zéro, ce qui signifie qu’InnoDB ne limite pas le nombre de threads exécutés en même temps. En règle générale, il est conseillé de ne pas modifier ce paramètre à moins que vous n’ayez besoin de limiter le nombre de threads concurrents pouvant s’exécuter à tout moment.
Pour les bases de données avec une charge de travail particulièrement élevée, ou si vous pensez que la concurrence est à l’origine de performances médiocres, vous pouvez limiter le nombre de threads exécutés en même temps. Comme point de départ, définissez innodb_thread_concurrency sur le même nombre de cœurs disponibles ou le double. Chaque requête ne peut s’exécuter que sur un seul cœur, mais le temps d’exécution est extrêmement rapide.
Si un thread ne peut pas s’exécuter car innodb_thread_concurrency a atteint sa limite, innodb_thread_sleep_delay définit la période d’attente en microsecondes avant que le thread ne tente de s’exécuter à nouveau. La valeur par défaut est de 10 000 microsecondes. Si le thread ne peut pas s’exécuter à la deuxième tentative, il est placé dans une file d’attente de threads. La valeur innodb_thread_sleep_delay est correcte pour la plupart des charges de travail. Par contre, si vous exécutez un nombre extrêmement important de très petites requêtes, cette valeur peut être trop élevée et provoquer une latence. Avant de modifier la valeur, envisagez de définir innodb_adaptive_max_sleep_delay.
innodb_adaptive_max_sleep_delay permet à InnoDB d’ajuster la valeur de innodb_thread_sleep_delay en fonction de la charge de travail actuelle. La valeur par défaut de innodb_adaptive_max_sleep_delay est de 15 000 microsecondes. Le délai de veille est ajusté vers le haut ou vers le bas, jusqu’à cette valeur maximale.
Conseil
Si vous utilisez du matériel adéquat et MySQL version 8.0 ou ultérieure, vous n’aurez probablement pas besoin de modifier les paramètres de concurrence. Si vous n’exécutez pas la dernière version de MySQL, effectuez une mise à niveau vers la dernière version avant de régler les paramètres de concurrence.
Notes
Si vous utilisez du matériel local, il est difficile d’acheter du nouveau matériel pour résoudre les goulots d’étranglement liés à la concurrence. Dans ces scénarios, la meilleure solution peut être de partitionner vos données.
Avec Azure Database pour MySQL, vous pouvez mettre à niveau le niveau de calcul facilement et à moindre coût.
Pour les requêtes de longue durée, le paramètre innodb_concurrency_tickets définit le nombre de « tickets » ou la quantité de travail pouvant être effectuée sans aucune vérification de concurrence supplémentaire. Si vous avez beaucoup de requêtes très longues, vous pouvez ajuster cette valeur. Toutefois, pour la plupart des charges de travail, cette valeur n’a pas besoin d’être modifiée.
Enfin, le paramètre innodb_commit_concurrency définit le nombre de threads pouvant être validées en même temps. Si vous définissez la valeur de innodb_thread_concurrency pour limiter le nombre de threads pouvant s’exécuter en même temps, vous pouvez également définir innodb_commit_concurrency avec une valeur faible pour obtenir de meilleurs résultats. La valeur par défaut est 0, ce qui permet de valider simultanément n’importe quel nombre de threads.
Tous ces paramètres peuvent être affichés et modifiés dans le Portail Azure, dans Paramètres du serveur ou avec Azure CLI.
Dans MySQL Workbench, vous pouvez voir le nombre de threads qui ont été créés, le nombre de threads connectés et le nombre de threads en cours d’exécution.
Pour voir les connexions clientes dans MySQL Workbench :
- Connectez-vous à votre serveur MySQL en utilisant une connexion nommée existante ou en créant une connexion.
- MySQL Workbench se connecte au serveur et ouvre un onglet Query.
- Sélectionnez l’onglet Administration.
- Sous Management, sélectionnez Client Connections. Les métriques suivantes sont fournies :
- Threads connectés
- Threads en cours d’exécution
- Threads créés
- Threads mis en cache
- Nombre total de connexions
- Limite de connexions
- Clients abandonnés
- Connexions abandonnées
Limitation des connexions clientes
MySQL traite normalement autant de connexions client que nécessaire. Toutefois, si un trop grand nombre de connexions sont établies, chacune exécutant des requêtes, l’exécution peut ralentir jusqu’à ce que toutes les requêtes se terminent après un délai important. Il s’agit du problème de « thundering herd » dans lequel de nouvelles connexions sont ajoutées alors que des connexions existantes ne peuvent pas s’exécuter dans un délai raisonnable.
Azure Database pour MySQL vous permet de limiter le nombre de connexions au serveur. Dans MySQL Workbench, ce nombre est indiqué par Connection Limit. Dans Azure Database pour MySQL, il s’agit d’un paramètre appelé max_connections sous Paramètres du serveur. Lorsque le nombre de connexions atteint cette limite, le serveur attend que ce nombre diminue avant de connecter plus d’utilisateurs. Il s’agit d’une protection contre la surcharge du serveur et l’exécution de requêtes avec des délais d’attente inacceptables.
Pour modifier la valeur de max_connections, accédez au portail Azure, naviguez jusqu’à votre serveur Azure Database pour MySQL, puis sélectionnez Paramètres du serveur. Dans la barre de recherche, entrez max_connections pour afficher ou modifier la valeur max_connections.
Définissez max_connections sur 1x, 2x ou 4x le nombre de cœurs de processeur disponibles. Doublez le nombre de max_connections jusqu’à ce que le nombre de transactions par seconde n’augmente plus. Une fois que la latence commence à augmenter, vous savez que le nombre de connexions ne limite pas les performances.