Ce second article sera sur la configuration automatique de variables grâce à innodb_dedicated_server pour un serveur MySQL en 8.0.14
MySQL 8.0 permet d’activer la configuration automatique pour un serveur MySQL dédié. Par défaut, cette variable est positionnée à OFF. Cette fonctionnalité a été introduite en version 8.0.3
Lorsque innodb_dedicated_server est activé, InnoDB configure automatiquement les variables suivantes en fonction de la mémoire détectée sur le serveur :
– innodb_buffer_pool_size
– innodb_log_file_size
– innodb_log_files_in_group (à partir de MySQL 8.0.14)
– innodb_flush_method
Cependant, il faut activer innodb_dedicated_server que si l’instance MySQL est sur un serveur dédié ou elle peut utiliser toutes les ressources système disponibles.
Par exemple, on peut l’activer sur un serveur MySQL dans un conteneur Docker sur le Cloud ou sur une machine virtuelle dédiée. Par contre, l’activation n’est pas recommandée si l’instance MySQL partage des ressources système avec d’autres applications.
Voici les informations issues de la documentation officielle (https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html) et qui décrivent la configuration automatique de chaque variable une fois l’activation de innodb_dedicated_server :
innodb_buffer_pool_size
La taille du buffer pool est configurée en fonction de la quantité de mémoire détectée sur le serveur :
innodb_log_file_size
Depuis MySQL 8.0.14, la taille du fichier journal est configurée en fonction de la taille du pool de mémoire tampon configurée automatiquement.
Avant MySQL 8.0.14, la variable innodb_log_file_size était automatiquement configurée en fonction de la quantité de mémoire détectée sur le serveur :
innodb_log_files_in_group
Le nombre de fichiers de journaux est configuré en fonction de la taille du buffer pool configurée automatiquement (en gigaoctets). La configuration automatique de la variable innodb_log_files_in_group a été ajoutée dans MySQL 8.0.14.
innodb_flush_method
La méthode flush est positionnée sur O_DIRECT_NO_FSYNC lorsque innodb_dedicated_server est activé. Si le paramètre O_DIRECT_NO_FSYNC n’est pas disponible, le paramètre par défaut innodb_flush_method est utilisé.
Avant MySQL 8.0.14, le paramètre O_DIRECT_NO_FSYNC n’était pas recommandé pour une utilisation sur les systèmes Linux. Le système pouvait se bloquer en raison de la désynchronisation des métadonnées du système de fichiers. Depuis MySQL 8.0.14, InnoDB appelle fsync() après la création d’un nouveau fichier, l’augmentation de la taille du fichier et la fermeture d’un fichier, ce qui permet d’utiliser le mode O_DIRECT_NO_FSYNC en toute sécurité sur les systèmes de fichiers XFS et EXT4. L’appel système fsync () est toujours ignoré après chaque opération d’écriture.
Par contre, si une option est configurée explicitement dans un fichier de configuration comme my.ini ou my.cnf, le paramètre spécifié est utilisé en priorité et un warning au démarrage est affiché :
[Avertissement] [000000] InnoDB: L'option innodb_dedicated_server est ignorée pour innodb_buffer_pool_size car innodb_buffer_pool_size = 134217728 est spécifiée explicitement.
La configuration explicite d’une option n’empêche pas la configuration automatique d’autres options. Par exemple, si innodb_dedicated_server est activé et innodb_buffer_pool_size est configuré explicitement dans un fichier de configuration, innodb_log_file_size et innodb_log_files_in_group sont automatiquement configurés en fonction de la taille du buffer pool implicite calculée sur la quantité de mémoire détectée sur le serveur. Les paramètres configurés automatiquement sont évalués et reconfigurés si nécessaire à chaque fois que le serveur MySQL est démarré.
Voici un exemple montrant les changements des paramètres en utilisant la fonctionnalité.
Avant modification :
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.14 | +-----------+ 1 row in set (0.00 sec) mysql> select @@global.innodb_dedicated_server; +----------------------------------+ | @@global.innodb_dedicated_server | +----------------------------------+ | 0 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from performance_schema.variables_info where variable_name like 'innodb_dedicated%'; +-------------------------+-----------------+---------------+-----------+-----------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +-------------------------+-----------------+---------------+-----------+-----------+----------+----------+----------+ | innodb_dedicated_server | COMPILED | | 0 | 0 | NULL | NULL | NULL | +-------------------------+-----------------+---------------+-----------+-----------+----------+----------+----------+ 1 row in set (0.01 sec) mysql> select @@global.innodb_buffer_pool_size/1024/1024, @@global.innodb_log_file_size/1024/1024, @@global.innodb_log_files_in_group, @@global.innodb_flush_method; +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ | @@global.innodb_buffer_pool_size/1024/1024 | @@global.innodb_log_file_size/1024/1024 | @@global.innodb_log_files_in_group | @@global.innodb_flush_method | +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ | 128.00000000 | 48.00000000 | 2 | unbuffered | +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ 1 row in set (0.00 sec) mysql> select @@innodb_dedicated_server `Innodb Dedicated Server`, concat(round((@@innodb_buffer_pool_size / 1024 / 1024 ),2),' GB') `Innodb Buffer Pool Size`,concat (round((@@innodb_log_file_size / 1024 / 1024),2),' MB') `Innodb Log file size`,@@innodb_flush_method `Innodb Flush Method`\G *************************** 1. row *************************** Innodb Dedicated Server: 0 Innodb Buffer Pool Size: 128.00 GB Innodb Log file size: 48.00 MB Innodb Flush Method: unbuffered 1 row in set (0.11 sec)
On constate qu’en configuration par défaut, quand la variable innodb_dedicated_server est désactivée, la variable innodb_buffer_pool_size est positionné à 128M, la variable innodb_log_file_size à 48M et innodb_log_files_in_group (ajouté à partir de MySQL 8.0.14) à 2.
Cependant, la variable innodb_log_file_size a été positionné explicitement par défaut à 48M dans le fichier de conf my.ini, ce qui signifie que cette variable ne sera pas modifié automatiquement après l’activation de innodb_dedicated_server.
J’ai un serveur MySQL non dédié sur un PC pro Windows et j’ai 8G de RAM dont 7,7G utilisable. La consommation du processus mysqld consomme un peu plus de 350M et j’utilise MySQL 8.0 Command Line Interface. J’ai 5,6G de mémoire disponible sur mon PC.
Nous allons donc activer la variable innodb_dedicated et voir quelles sont les valeurs des variables positionnées automatiquement en fonction de la mémoire sur mon PC.
mysql> set persist innodb_dedicated_server=1; ERROR 1238 (HY000): Variable 'innodb_dedicated_server' is a read only variable mysql> set global innodb_dedicated_server=1; ERROR 1238 (HY000): Variable 'innodb_dedicated_server' is a read only variable
La variable innodb_dedicated_server étant en lecture seule, nous devons l’ajouter dans le fichier de configuration my.ini et redémarrer le serveur ou service MySQL :
mysql> restart; Query OK, 0 rows affected (0.00 sec)
Après modification :
mysql> select @@global.innodb_dedicated_server; No connection. Trying to reconnect... Connection id: 7 Current database: *** NONE *** +----------------------------------+ | @@global.innodb_dedicated_server | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from performance_schema.variables_info where variable_name like 'innodb_dedicated%'; +-------------------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +-------------------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+ | innodb_dedicated_server | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL | +-------------------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+ 1 row in set (0.01 sec) mysql> select @@global.innodb_buffer_pool_size/1024/1024, @@global.innodb_log_file_size/1024/1024, @@global.innodb_log_files_in_group, @@global.innodb_flush_method; +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ | @@global.innodb_buffer_pool_size/1024/1024 | @@global.innodb_log_file_size/1024/1024 | @@global.innodb_log_files_in_group | @@global.innodb_flush_method | +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ | 6144.00000000 | 48.00000000 | 6 | unbuffered | +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ 1 row in set (0.00 sec) mysql> select * from performance_schema.variables_info where variable_name like 'innodb_log_file%'; +---------------------------+-----------------+----------------------------------------------+-----------+----------------------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +---------------------------+-----------------+----------------------------------------------+-----------+----------------------+----------+----------+----------+ | innodb_log_file_size | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 4194304 | 18446744073709551615 | NULL | NULL | NULL | | innodb_log_files_in_group | COMPILED | | 2 | 100 | NULL | NULL | NULL | +---------------------------+-----------------+----------------------------------------------+-----------+----------------------+----------+----------+----------+ 2 rows in set (0.01 sec)
Une fois l’activation de innodb_dedicated_server, on constate que les variables ont changé en fonction de la mémoire détecté (8G dont 7,7G utilisable) sur mon ordinateur.
En effet, la variable innodb_buffer_pool_size est passée de 128M à 6144M et la variable innodb_log_files_in_group est passée de 2 à 6 fichiers à cause du changement de innodb_buffer_pool_size.
Pour autant, la variable innodb_log_file_size n’a pas été modifiée car elle avait été mise explicitement par défaut dans le fichier de configuration my.ini :
Cela se constate en regardant les informations du fichier error log informant la non prise en compte de innodb_log_file_size avec innodb_dedicated_server :
Quant à la variable innodb_flush_method, celle-ci a gardé sa valeur par défaut sur Windows. En effet, il y a seulement 2 valeurs possibles sur Windows pour cette variable qui sont unbuffered ou normal. Sur Linux, il y a plus de valeurs pour innodb_flush_method et avant la 8.0.14, la valeur O_DIRECT_NO_FSYNC n’était pas recommandé à cause de soucis rencontrés sur le système.
On va maintenant commenter la variable innodb_log_file_size positionnée explicitement à 48M dans my.ini
Une fois cela fait, redémarrer le serveur ou service MySQL et vérifier la nouvelle valeur de innodb_log_file_size.
mysql> restart; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.innodb_buffer_pool_size/1024/1024, @@global.innodb_log_file_size/1024/1024, @@global.innodb_log_files_in_group, @@global.innodb_flush_method; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 8 Current database: *** NONE *** +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ | @@global.innodb_buffer_pool_size/1024/1024 | @@global.innodb_log_file_size/1024/1024 | @@global.innodb_log_files_in_group | @@global.innodb_flush_method | +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ | 6144.00000000 | 512.00000000 | 6 | unbuffered | +--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+ 1 row in set (0.16 sec) mysql> select @@global.innodb_dedicated_server; +----------------------------------+ | @@global.innodb_dedicated_server | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from performance_schema.variables_info where variable_name like 'innodb_log_file%'; +---------------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +---------------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ | innodb_log_file_size | COMPILED | | 4194304 | 18446744073709551615 | NULL | NULL | NULL | | innodb_log_files_in_group | COMPILED | | 2 | 100 | NULL | NULL | NULL | +---------------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ 2 rows in set (0.10 sec)
Quand la variable innodb_log_file_size est commentée dans my.ini, on constate que sa valeur de 512M est celle définie en fonction de la taille du buffer pool InnoDB. Avant la version 8.0.14, la valeur de la variable innodb_log_file_size était configurée différement avec plus de niveau liée à la mémoire détectée sur le serveur.
On constate également une augmentation logique mémoire du process mysqld après l’activation :
Puisque le test de cette nouveauté s’est faite sur un ordinateur Windows non dédié, il serait intéressant de voir la gestion de la mémoire sur un serveur Linux dédié dans le Cloud avec innodb_dedicated_server et également pour voir les différents choix possible de innodb_flush_method sur Linux (par exemple des droits restraits sur certains fichiers).
Pour autant, même si cette option est très intéressante sur le papier, certains environnements précis auront besoin de configuration plus précise et ajustée à la charge du travail effectuée sur le serveur MySQL en spécifiant explicitement certaines variables au lieu d’utiliser le calcul automatique de innodb_dedicated_server.
En effet, les valeurs des variables calculées automatiquement par rapport à la mémoire du serveur sont quand même bien hautes et ne seront pas forcément nécessaire pour certains serveurs.
Par exemple, un serveur qui est beaucoup chargé en écriture sur les tables InnoDB pourrait être intéressé car si la charge augmente au fil du temps sur le serveur MySQL, les valeurs s’ajusteront en fonction de la mémoire ajoutée au prochaine reboot. Mais dans un autre cas, si un serveur de type Blog WordPress avec 90% de charge de lecture utilise innodb_dedicated_server, avoir des valeurs assez hautes sur ces variables ne lui seraient pas très utiles au vue de son activité.
Le fait d’avoir une priorité sur une valeur mise explicitement dans le fichier de configuration my.ini par rapport à innodb_dedicated_server, permet aux DBAs de garder et d’avoir la main pour spécifier des valeurs plus précises pour les variables vis-à-vis de l’activité.
Donc, il ne faut pas utiliser innodb_dedicated_server si ce n’est pas un serveur dédié et éviter d’avoir les variables dépendantes configurées explicitement si innodb_dedicated_server est activé.
Vous en savez donc plus sur cette nouveauté innodb_dedicated_server de MySQL 8.0 qui fait beaucoup penser à la gestion automatique de la mémoire (sga et pga) sur une base Oracle avec memory_target.
A bientôt pour de nouveaux articles sur MySQL !
Erwan Ollitrault
Continuez votre lecture sur le blog :
- Nouveautés MySQL 8.0 : Variables persistés (Capdata team) [MySQL]
- Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
- XtraDB sauvegarde votre cache, et ça marche ! (Benjamin VESAN) [MySQL]
- MySQL et les tables temporaires internes (Benjamin VESAN) [MySQL]
- Nouveautés MySQL 8.0 : Les indexes invisibles (Capdata team) [MySQL]