Paramètres de serveur dans Azure Database pour MySQL

S’APPLIQUE À : Azure Database pour MySQL - Serveur unique

Cet article fournit des considérations et des instructions pour la configuration des paramètres de serveur dans Azure Database pour MySQL.

Que sont les paramètres du serveur ?

Le moteur MySQL fournit de nombreux paramètres et variables de serveur différents que vous pouvez utiliser pour configurer et régler le comportement du moteur. Certains paramètres peuvent être définis dynamiquement pendant le runtime. D’autres sont statiques et nécessitent un redémarrage du serveur pour être appliqués.

Azure Database pour MySQL expose la capacité à modifier la valeur de différents paramètres du serveur MySQL en utilisant le portail Azure, Azure CLI et PowerShell pour répondre aux besoins de votre charge de travail.

Paramètres de serveur configurables

La liste des paramètres de serveur pris en charge s’allonge en permanence. Dans le portail Azure, utilisez l’onglet Paramètres du serveur pour afficher la liste complète des paramètres du serveur et configurer leurs valeurs.

Reportez-vous aux sections suivantes pour en savoir plus sur les limites de plusieurs paramètres de serveur couramment mis à jour. Les limites sont déterminées par le niveau tarifaire et vCores du serveur.

Pools de threads

MySQL affecte traditionnellement un thread pour chaque connexion client. À mesure que le nombre d’utilisateurs simultanés augmente, une certaine baisse des performances se fait sentir. De nombreux threads actifs peuvent fortement impacter les performances en raison de l’augmentation du basculement de contexte, de la contention de thread et de la localité incorrecte pour les caches de processeur.

Les pools de threads, une fonctionnalité côté serveur et distincte du regroupement de connexions, optimisent les performances en introduisant un pool dynamique de threads de travail. Cette fonctionnalité vous permet de limiter le nombre de threads actifs qui s’exécutent sur le serveur et de réduire l’activité des threads. Cela permet de vous assurer qu’une rafale de connexions n’entraînera pas un manque de ressources ou de mémoire sur le serveur. Les pools de threads sont plus efficaces pour les requêtes courtes et les charges de travail gourmandes en ressources processeur, comme les charges de travail OLTP.

Pour plus d’informations, consultez Présentation des pools de threads dans Azure Database pour MySQL.

Configurer le pool de threads

Pour activer un pool de threads, changez le paramètre de serveur thread_handling sur pool-of-threads. Par défaut, ce paramètre est défini sur one-thread-per-connection, ce qui signifie que MySQL crée un thread pour chaque nouvelle connexion. Il s’agit d’un paramètre statique qui nécessite un redémarrage du serveur pour s’appliquer.

Vous pouvez également configurer les nombres maximal et minimal de threads dans le pool en définissant les paramètres de serveur suivants :

• thread_pool_max_threads : cette valeur limite le nombre de threads dans le pool.
• thread_pool_min_threads : Cette valeur définit le nombre de threads qui sont réservés même après la fermeture des connexions.

Pour améliorer les problèmes de performances des requêtes courtes sur le pool de threads, vous pouvez activer l’exécution par lot. Au lieu de revenir au pool de threads immédiatement après l’exécution d’une requête, les threads restent actifs pendant un bref laps de temps pour attendre la requête suivante via cette connexion. Le thread exécute ensuite la requête rapidement et, quand il a terminé cette opération, il attend la prochaine requête. Ce processus continue jusqu’à ce que le temps total passé dépasse un seuil.

Vous déterminez le comportement de l’exécution par lot en utilisant les paramètres de serveur suivants :

• thread_pool_batch_wait_timeout: Cette valeur spécifie la durée pendant laquelle un thread attend qu’une autre requête soit en cours de traitement.
• thread_pool_batch_max_time : cette valeur détermine la durée maximale pendant laquelle un thread répète le cycle d’exécution de la requête et attend la requête suivante.

log_bin_trust_function_creators

Dans Azure Database pour MySQL, les journaux binaires sont toujours activés (le paramètre log_bin est défini sur ON). Si vous souhaitez utiliser des déclencheurs, vous obtenez une erreur similaire à celle-ci : Vous n’avez pas le SUPER privilège et la journalisation binaire est activée (vous pouvez utiliser la variable log_bin_trust_function_creators moins sécurisée).

Le format de journalisation binaire est toujours ROW, et toutes les connexions au serveur utilisent toujours la journalisation binaire basée sur les lignes. La journalisation binaire basée sur les lignes vous aide à maintenir la sécurité et la journalisation binaire ne peut pas s’arrêter, ce qui vous permet, en toute sécurité, de définir log_bin_trust_function_creators sur TRUE.

innodb_buffer_pool_size

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

Serveurs sur Stockage v1 universel (prenant en charge jusqu’à 4 To)

Serveurs sur Stockage v2 universel (prenant en charge jusqu’à 16 To)

innodb_file_per_table

MySQL stocke la table InnoDB dans différents espaces de stockage en fonction de la configuration que vous fournissez lors de la création de la table. L’espace disque logique du système est la zone de stockage pour le dictionnaire de données InnoDB. Un espace disque logique de fichier par table contient des données et des index pour une table InnoDB unique, et est stocké dans son propre fichier de données au sein du système de fichiers.

Vous contrôlez ce comportement à l’aide du paramètre de serveur innodb_file_per_table. La définition de innodb_file_per_table sur OFF amène InnoDB à créer des tables dans l’espace disque logique du système. Autrement, InnoDB crée des tables dans des espaces disques logiques de fichier par table.

Azure Database pour MySQL prend en charge jusqu’à 4 To maximum dans un même fichier de données sur le stockage v2 universel. Si la taille de votre base de données est supérieure à 4 To, vous devez créer la table dans l’espace disque logique innodb_file_per_table. Si la table a une taille supérieure à 4 To, vous devez utiliser la table de partition.

join_buffer_size

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

max_connections

Lorsque le nombre de connexions dépasse la limite, vous pouvez recevoir une erreur.

max_heap_table_size

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

query_cache_size

Le cache des requêtes est désactivé par défaut. Pour activer le cache des requêtes, configurez le paramètre query_cache_type.

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

lower_case_table_names

Le paramètre lower_case_table_name a la valeur 1 par défaut, et vous pouvez le modifier dans MySQL 5.6 et MySQL 5.7.

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

innodb_strict_mode

Si vous recevez une erreur similaire à Row size too large (> 8126), désactivez le paramètre innodb_strict_mode. Vous ne pouvez pas modifier innodb_strict_mode globalement au niveau du serveur. Si la taille des données de ligne est supérieure à 8 Ko, les données sont tronquées, sans notification d’erreur, et cela entraîne une perte de données potentielle. Il est conseillé de modifier le schéma pour qu’il corresponde à la limite de taille de page.

Vous pouvez définir ce paramètre au niveau de la session, en utilisant init_connect. Pour définir innodb_strict_mode au niveau de la session, consultez Définition des paramètres non listés.

sort_buffer_size

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

tmp_table_size

Consultez la documentation MySQL pour en savoir plus sur ce paramètre.

Warmup du pool de tampons InnoDB

Après le redémarrage d’Azure Database pour MySQL, les pages de données qui résident sur le disque sont chargées à mesure que les tables sont interrogées. Cela augmente la latence et ralentit les performances lors de la première exécution des requêtes. Pour les charges de travail sensibles à la latence, il est possible d’observer une baisse des performances inacceptable.

Vous pouvez utiliser le warmup du pool de tampons InnoDB pour raccourcir la période de warmup. Ce processus recharge les pages de disque qui se trouvaient dans le pool de tampons avant le redémarrage, plutôt que d’attendre les opérations DML ou SELECT pour accéder aux lignes correspondantes. Pour plus d’informations, consultez Paramètres du serveur pour le pool de tampons InnoDB.

Toutefois, l'amélioration des performances se fait au détriment d'un temps de démarrage plus long pour le serveur. Lorsque vous activez ce paramètre, les délais de démarrage et de redémarrage du serveur doivent augmenter en fonction des IOPS provisionnées sur le serveur. Il est conseillé de tester et de superviser le temps de redémarrage pour vous assurer que les performances de démarrage ou redémarrage sont acceptables, car le serveur n’est pas disponible pendant ce temps. N’utilisez pas ce paramètre lorsque le nombre d’IOPS provisionnées sont inférieures à 1000 (en d’autres termes, lorsque le stockage provisionné est inférieur à 335 Go).

Pour enregistrer l'état du pool de tampons au moment de l'arrêt du serveur, définissez le paramètre de serveur innodb_buffer_pool_dump_at_shutdown sur ON. De même, définissez le paramètre de serveur innodb_buffer_pool_load_at_startup sur ON pour restaurer l'état du pool de tampons au moment du démarrage du serveur. Vous pouvez contrôler l'impact sur le démarrage ou le redémarrage en réduisant et en ajustant la valeur du paramètre de serveur innodb_buffer_pool_dump_pct. Par défaut, ce paramètre a la valeur 25.

time_zone

Lors du déploiement initial, un serveur exécutant Azure Database pour MySQL inclut des tables système pour les informations de fuseau horaire, mais ces tables ne sont pas remplies. Vous pouvez remplir les tables en appelant la procédure stockée mysql.az_load_timezone à partir d’outils tels que la ligne de commande MySQL ou MySQL Workbench. Pour plus d’informations sur la façon d’appeler les procédures stockées et de définir les fuseaux horaires au niveau global ou au niveau d’une session, consultez Utilisation avec le paramètre de fuseau horaire (portail Azure) ou Utilisation avec le paramètre de fuseau horaire (Azure CLI).

binlog_expire_logs_seconds

Dans Azure Database pour MySQL, ce paramètre spécifie le nombre de secondes pendant lesquelles le service attend avant de vider le fichier journal binaire.

Le journal binaire contient des événements qui décrivent des modifications de base de données, telles que des opérations de création de table ou de modification de données de table. Il contient également des événements pour les instructions qui peuvent potentiellement entraîner des modifications. Le journal binaire est principalement utilisé pour les opérations de réplication et les opérations de récupération de données.

En règle générale, les journaux binaires sont vidés dès que le descripteur est libéré du service, de la sauvegarde ou du jeu de réplicas. En présence de plusieurs réplicas, les journaux binaires ne sont vidés qu’après que le réplica le plus lent a lu les modifications. Si vous souhaitez que les journaux binaires soient conservés plus longtemps, vous pouvez configurer le paramètre binlog_expire_logs_seconds. Si vous définissez binlog_expire_logs_seconds sur 0, qui est la valeur par défaut, le journal binaire est vidé dès que son descripteur est libéré. Si vous définissez binlog_expire_logs_seconds sur une valeur supérieure à 0, le journal binaire est vidé uniquement après cette période.

Dans Azure Database pour MySQL, les fonctionnalités managées telles que la sauvegarde et la purge de réplica en lecture des fichiers binaires sont gérées en interne. Lorsque vous répliquez les données à partir du service Azure Database pour MySQL, vous devez définir ce paramètre dans le réplica principal pour éviter la purge des journaux binaires avant que le réplica n’ait lu les modifications du réplica principal. Si vous définissez binlog_expire_logs_seconds sur une valeur plus élevée, les journaux binaires ne sont pas vidés assez tôt. Cela peut entraîner une facturation du stockage supplémentaire.

event_scheduler

Dans Azure Database pour MySQL, le paramètre de serveur event_schedule gère la création, la planification et l’exécution d’événements, c’est-à-dire des tâches qui s’exécutent selon une planification, et qui sont exécutées par un thread de programmateur d’évènements spécial. Lorsque le paramètre event_scheduler est défini sur ACTIVÉ, le thread du programmateur d’évènements est répertorié en tant que processus démon dans la sortie de SHOW PROCESSLIST. Vous pouvez créer et planifier des événements à l’aide de la syntaxe SQL suivante :

CREATE EVENT <event name>
ON SCHEDULE EVERY _ MINUTE / HOUR / DAY
STARTS TIMESTAMP / CURRENT_TIMESTAMP
ENDS TIMESTAMP / CURRENT_TIMESTAMP + INTERVAL 1 MINUTE / HOUR / DAY
COMMENT ‘<comment>’
DO
<your statement>;

Configuration du paramètre du serveur event_scheduler

Le scénario suivant illustre une façon d’utiliser le paramètre event_scheduler dans Azure Database pour MySQL. Pour illustrer ce scénario, considérez l’exemple suivant, un tableau simple :

mysql> describe tab1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| CreatedAt | timestamp   | YES  |     | NULL    |                |
| CreatedBy | varchar(16) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.23 sec)

Pour configurer le paramètre du serveur event_scheduler dans Azure Database pour MySQL, procédez comme suit :

1. Dans le portail Azure, accédez à votre serveur, puis sous Paramètres, sélectionnez Paramètres de serveur.

2. Dans le panneau Paramètres de serveur, recherchez event_scheduler, dans la liste déroulante VALUE, sélectionnez ACTIVÉ, puis Enregistrer.

   Notes

   La modification de la configuration du paramètre de serveur dynamique sera déployée sans redémarrage.

3. Ensuite, pour créer un événement, connectez-vous au serveur MySQL et exécutez la commande SQL suivante :

   CREATE EVENT test_event_01
   ON SCHEDULE EVERY 1 MINUTE
   STARTS CURRENT_TIMESTAMP
   ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
   COMMENT ‘Inserting record into the table tab1 with current timestamp’
   DO
   INSERT INTO tab1(id,createdAt,createdBy)
   VALUES('',NOW(),CURRENT_USER());
   

4. Pour afficher les détails du programmateur d'évènements (Event Scheduler), exécutez l’instruction SQL suivante :

   SHOW EVENTS;
   

   Vous obtenez la sortie suivante :

   mysql> show events;
   +-----+---------------+-------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
   | Db  | Name          | Definer     | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends                | Status  | Originator | character_set_client | collation_connection | Database Collation |
   +-----+---------------+-------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
   | db1 | test_event_01 | azureuser@% | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2023-04-05 14:47:04 | 2023-04-05 15:47:04 | ENABLED | 3221153808 | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
   +-----+---------------+-------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
   1 row in set (0.23 sec)
   

5. Après quelques minutes, interrogez les lignes du tableau pour commencer à afficher les lignes insérées toutes les minutes en fonction du paramètre event_scheduler que vous avez configuré :

   mysql> select * from tab1;
   +----+---------------------+-------------+
   | id | CreatedAt           | CreatedBy   |
   +----+---------------------+-------------+
   |  1 | 2023-04-05 14:47:04 | azureuser@% |
   |  2 | 2023-04-05 14:48:04 | azureuser@% |
   |  3 | 2023-04-05 14:49:04 | azureuser@% |
   |  4 | 2023-04-05 14:50:04 | azureuser@% |
   +----+---------------------+-------------+
   4 rows in set (0.23 sec)
   

6. Après une heure, exécutez une instruction Select sur le tableau pour afficher le résultat complet des valeurs insérées dans le tableau toutes les minutes pendant une heure, car event_scheduler est configuré dans notre cas.

   mysql> select * from tab1;
   +----+---------------------+-------------+
   | id | CreatedAt           | CreatedBy   |
   +----+---------------------+-------------+
   |  1 | 2023-04-05 14:47:04 | azureuser@% |
   |  2 | 2023-04-05 14:48:04 | azureuser@% |
   |  3 | 2023-04-05 14:49:04 | azureuser@% |
   |  4 | 2023-04-05 14:50:04 | azureuser@% |
   |  5 | 2023-04-05 14:51:04 | azureuser@% |
   |  6 | 2023-04-05 14:52:04 | azureuser@% |
   ..< 50 lines trimmed to compact output >..
   | 56 | 2023-04-05 15:42:04 | azureuser@% |
   | 57 | 2023-04-05 15:43:04 | azureuser@% |
   | 58 | 2023-04-05 15:44:04 | azureuser@% |
   | 59 | 2023-04-05 15:45:04 | azureuser@% |
   | 60 | 2023-04-05 15:46:04 | azureuser@% |
   | 61 | 2023-04-05 15:47:04 | azureuser@% |
   +----+---------------------+-------------+
   61 rows in set (0.23 sec)
   

Autres scénarios

Vous pouvez configurer un événement en fonction des exigences de votre scénario spécifique. Voici quelques exemples similaires de programmation d’instructions SQL à exécuter à des intervalles de temps différents.

Exécuter une instruction SQL maintenant et répéter une fois par jour sans fin

CREATE EVENT <event name>
ON SCHEDULE
EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
COMMENT 'Comment'
DO
<your statement>;

Exécuter une instruction SQL toutes les heures sans fin

CREATE EVENT <event name>
ON SCHEDULE
EVERY 1 HOUR
COMMENT 'Comment'
DO
<your statement>;

Exécuter une instruction SQL tous les jours sans fin

CREATE EVENT <event name>
ON SCHEDULE 
EVERY 1 DAY
STARTS str_to_date( date_format(now(), '%Y%m%d 0200'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
COMMENT 'Comment'
DO
<your statement>;

Paramètres de serveur non configurables

Les paramètres de serveur suivants ne sont pas configurables dans le service :

Les autres variables non listées ici sont définies sur les valeurs MySQL par défaut. Reportez-vous à la documentation MySQL pour les versions 8.0, 5.7 et 5.6.

Étapes suivantes

• Guide pratique pour configurer des paramètres serveur en utilisant le portail Azure
• Guide pratique pour configurer des paramètres serveur en utilisant Azure CLI
• Guide pratique pour configurer des paramètres serveur en utilisant PowerShell