![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>MySQL 8.0 permet d’activer la configuration automatique pour un serveur MySQL d\u00e9di\u00e9. Par d\u00e9faut, cette variable est positionn\u00e9e \u00e0 OFF. Cette fonctionnalit\u00e9 a \u00e9t\u00e9 introduite en version 8.0.3<\/p>\n
Lorsque innodb_dedicated_server est activ\u00e9, InnoDB configure automatiquement les variables suivantes en fonction de la m\u00e9moire d\u00e9tect\u00e9e sur le serveur :<\/p>\n
– innodb_buffer_pool_size
\n– innodb_log_file_size
\n– innodb_log_files_in_group (\u00e0 partir de MySQL 8.0.14)
\n– innodb_flush_method<\/p>\n
Cependant, il faut activer innodb_dedicated_server que si l’instance MySQL est sur un serveur d\u00e9di\u00e9 ou elle peut utiliser toutes les ressources syst\u00e8me disponibles.<\/p>\n
Par exemple, on peut l’activer sur un serveur MySQL dans un conteneur Docker sur le Cloud ou sur une machine virtuelle d\u00e9di\u00e9e. Par contre, l’activation n’est pas recommand\u00e9e si l’instance MySQL partage des ressources syst\u00e8me avec d’autres applications.<\/p>\n
Voici les informations issues de la documentation officielle (https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-dedicated-server.html<\/a>) et qui d\u00e9crivent la configuration automatique de chaque variable une fois l’activation de innodb_dedicated_server :<\/p>\n La taille du buffer pool est configur\u00e9e en fonction de la quantit\u00e9 de m\u00e9moire d\u00e9tect\u00e9e sur le serveur :<\/p>\n Depuis MySQL 8.0.14, la taille du fichier journal est configur\u00e9e en fonction de la taille du pool de m\u00e9moire tampon configur\u00e9e automatiquement.<\/p>\n Avant MySQL 8.0.14, la variable innodb_log_file_size \u00e9tait automatiquement configur\u00e9e en fonction de la quantit\u00e9 de m\u00e9moire d\u00e9tect\u00e9e sur le serveur :<\/p>\n Le nombre de fichiers de journaux est configur\u00e9 en fonction de la taille du buffer pool configur\u00e9e automatiquement (en gigaoctets). La configuration automatique de la variable innodb_log_files_in_group a \u00e9t\u00e9 ajout\u00e9e dans MySQL 8.0.14.<\/p>\n La m\u00e9thode flush est positionn\u00e9e sur O_DIRECT_NO_FSYNC lorsque innodb_dedicated_server est activ\u00e9. Si le param\u00e8tre O_DIRECT_NO_FSYNC n’est pas disponible, le param\u00e8tre par d\u00e9faut innodb_flush_method est utilis\u00e9.<\/p>\n Avant MySQL 8.0.14, le param\u00e8tre O_DIRECT_NO_FSYNC n’\u00e9tait pas recommand\u00e9 pour une utilisation sur les syst\u00e8mes Linux. Le syst\u00e8me pouvait se bloquer en raison de la d\u00e9synchronisation des m\u00e9tadonn\u00e9es du syst\u00e8me de fichiers. Depuis MySQL 8.0.14, InnoDB appelle fsync() apr\u00e8s la cr\u00e9ation d\u2019un nouveau fichier, l\u2019augmentation de la taille du fichier et la fermeture d\u2019un fichier, ce qui permet d\u2019utiliser le mode O_DIRECT_NO_FSYNC en toute s\u00e9curit\u00e9 sur les syst\u00e8mes de fichiers XFS et EXT4. L’appel syst\u00e8me fsync () est toujours ignor\u00e9 apr\u00e8s chaque op\u00e9ration d’\u00e9criture.<\/p>\n Par contre, si une option est configur\u00e9e explicitement dans un fichier de configuration comme my.ini ou my.cnf, le param\u00e8tre sp\u00e9cifi\u00e9 est utilis\u00e9 en priorit\u00e9 et un warning au d\u00e9marrage est affich\u00e9 :<\/p>\n La configuration explicite d’une option n’emp\u00eache pas la configuration automatique d’autres options. Par exemple, si innodb_dedicated_server est activ\u00e9 et innodb_buffer_pool_size est configur\u00e9 explicitement dans un fichier de configuration, innodb_log_file_size et innodb_log_files_in_group sont automatiquement configur\u00e9s en fonction de la taille du buffer pool implicite calcul\u00e9e sur la quantit\u00e9 de m\u00e9moire d\u00e9tect\u00e9e sur le serveur. Les param\u00e8tres configur\u00e9s automatiquement sont \u00e9valu\u00e9s et reconfigur\u00e9s si n\u00e9cessaire \u00e0 chaque fois que le serveur MySQL est d\u00e9marr\u00e9.<\/p>\n Voici un exemple montrant les changements des param\u00e8tres en utilisant la fonctionnalit\u00e9.<\/p>\n On constate qu’en configuration par d\u00e9faut, quand la variable innodb_dedicated_server est d\u00e9sactiv\u00e9e, la variable innodb_buffer_pool_size est positionn\u00e9 \u00e0 128M, la variable innodb_log_file_size \u00e0 48M et innodb_log_files_in_group (ajout\u00e9 \u00e0 partir de MySQL 8.0.14) \u00e0 2.<\/p>\n Cependant, la variable innodb_log_file_size a \u00e9t\u00e9 positionn\u00e9 explicitement par d\u00e9faut \u00e0 48M dans le fichier de conf my.ini, ce qui signifie que cette variable ne sera pas modifi\u00e9 automatiquement apr\u00e8s l’activation de innodb_dedicated_server.<\/p>\n J’ai un serveur MySQL non d\u00e9di\u00e9 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\u00e9moire disponible sur mon PC.<\/p>\n Nous allons donc activer la variable innodb_dedicated et voir quelles sont les valeurs des variables positionn\u00e9es automatiquement en fonction de la m\u00e9moire sur mon PC.<\/p>\n La variable innodb_dedicated_server \u00e9tant en lecture seule, nous devons l’ajouter dans le fichier de configuration my.ini et red\u00e9marrer le serveur ou service MySQL : Une fois l’activation de innodb_dedicated_server, on constate que les variables ont chang\u00e9 en fonction de la m\u00e9moire d\u00e9tect\u00e9 (8G dont 7,7G utilisable) sur mon ordinateur.<\/p>\n En effet, la variable innodb_buffer_pool_size est pass\u00e9e de 128M \u00e0 6144M et la variable innodb_log_files_in_group est pass\u00e9e de 2 \u00e0 6 fichiers \u00e0 cause du changement de innodb_buffer_pool_size.<\/p>\n Pour autant, la variable innodb_log_file_size n’a pas \u00e9t\u00e9 modifi\u00e9e car elle avait \u00e9t\u00e9 mise explicitement par d\u00e9faut dans le fichier de configuration my.ini :<\/p>\n 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 :<\/p>\n Quant \u00e0 la variable innodb_flush_method, celle-ci a gard\u00e9 sa valeur par d\u00e9faut 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’\u00e9tait pas recommand\u00e9 \u00e0 cause de soucis rencontr\u00e9s sur le syst\u00e8me.<\/p>\n On va maintenant commenter la variable innodb_log_file_size positionn\u00e9e explicitement \u00e0 48M dans my.ini<\/p>\n Une fois cela fait, red\u00e9marrer le serveur ou service MySQL et v\u00e9rifier la nouvelle valeur de innodb_log_file_size.<\/p>\n Quand la variable innodb_log_file_size est comment\u00e9e dans my.ini, on constate que sa valeur de 512M est celle d\u00e9finie en fonction de la taille du buffer pool InnoDB. Avant la version 8.0.14, la valeur de la variable innodb_log_file_size \u00e9tait configur\u00e9e diff\u00e9rement avec plus de niveau li\u00e9e \u00e0 la m\u00e9moire d\u00e9tect\u00e9e sur le serveur.<\/p>\n On constate \u00e9galement une augmentation logique m\u00e9moire du process mysqld apr\u00e8s l’activation :<\/p>\n Puisque le test de cette nouveaut\u00e9 s’est faite sur un ordinateur Windows non d\u00e9di\u00e9, il serait int\u00e9ressant de voir la gestion de la m\u00e9moire sur un serveur Linux d\u00e9di\u00e9 dans le Cloud avec innodb_dedicated_server et \u00e9galement pour voir les diff\u00e9rents choix possible de innodb_flush_method sur Linux (par exemple des droits restraits sur certains fichiers).<\/p>\n Pour autant, m\u00eame si cette option est tr\u00e8s int\u00e9ressante sur le papier, certains environnements pr\u00e9cis auront besoin de configuration plus pr\u00e9cise et ajust\u00e9e \u00e0 la charge du travail effectu\u00e9e sur le serveur MySQL en sp\u00e9cifiant explicitement certaines variables au lieu d’utiliser le calcul automatique de innodb_dedicated_server.<\/p>\n En effet, les valeurs des variables calcul\u00e9es automatiquement par rapport \u00e0 la m\u00e9moire du serveur sont quand m\u00eame bien hautes et ne seront pas forc\u00e9ment n\u00e9cessaire pour certains serveurs.<\/p>\n Par exemple, un serveur qui est beaucoup charg\u00e9 en \u00e9criture sur les tables InnoDB pourrait \u00eatre int\u00e9ress\u00e9 car si la charge augmente au fil du temps sur le serveur MySQL, les valeurs s’ajusteront en fonction de la m\u00e9moire ajout\u00e9e 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\u00e8s utiles au vue de son activit\u00e9.<\/p>\n Le fait d’avoir une priorit\u00e9 sur une valeur mise explicitement dans le fichier de configuration my.ini par rapport \u00e0 innodb_dedicated_server, permet aux DBAs de garder et d’avoir la main pour sp\u00e9cifier des valeurs plus pr\u00e9cises pour les variables vis-\u00e0-vis de l’activit\u00e9.<\/p>\n Donc, il ne faut pas utiliser innodb_dedicated_server si ce n’est pas un serveur d\u00e9di\u00e9 et \u00e9viter d’avoir les variables d\u00e9pendantes configur\u00e9es explicitement si innodb_dedicated_server est activ\u00e9.<\/p>\n Vous en savez donc plus sur cette nouveaut\u00e9 innodb_dedicated_server de MySQL 8.0 qui fait beaucoup penser \u00e0 la gestion automatique de la m\u00e9moire (sga et pga) sur une base Oracle avec memory_target.<\/p>\n A bient\u00f4t pour de nouveaux articles sur MySQL !<\/p>\n Erwan Ollitrault<\/p>\n Ce second article sera sur la configuration automatique de variables gr\u00e2ce \u00e0 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\u00e9di\u00e9. Par d\u00e9faut, cette variable est positionn\u00e9e \u00e0 OFF. Cette… Continuer la lecture innodb_buffer_pool_size<\/h3>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/innodb_dedicated_server_buffer_pool_size.png\")
<\/p>\ninnodb_log_file_size<\/h3>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/innodb_dedicated_server_log_file_size.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/innodb_dedicated_server_log_file_size_before_8.0.14.png\")
<\/p>\ninnodb_log_files_in_group<\/h3>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/innodb_dedicated_server_log_files_in_group.png\")
<\/p>\ninnodb_flush_method<\/h3>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/innodb_flush_method.png\")
<\/p>\n\r\n[Avertissement] [000000] InnoDB: L'option innodb_dedicated_server est \r\nignor\u00e9e pour innodb_buffer_pool_size car innodb_buffer_pool_size = 134217728 \r\nest sp\u00e9cifi\u00e9e explicitement.\r\n<\/pre>\n
Avant modification :<\/h3>\n
mysql> select @@version;\r\n+-----------+\r\n| @@version |\r\n+-----------+\r\n| 8.0.14 |\r\n+-----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.innodb_dedicated_server;\r\n+----------------------------------+\r\n| @@global.innodb_dedicated_server |\r\n+----------------------------------+\r\n| 0 |\r\n+----------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'innodb_dedicated%';\r\n+-------------------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+-------------------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n| innodb_dedicated_server | COMPILED | | 0 | 0 | NULL | NULL | NULL |\r\n+-------------------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> 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;\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n| @@global.innodb_buffer_pool_size\/1024\/1024 | @@global.innodb_log_file_size\/1024\/1024 | @@global.innodb_log_files_in_group | @@global.innodb_flush_method |\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n| 128.00000000 | 48.00000000 | 2 | unbuffered |\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@innodb_dedicated_server `Innodb Dedicated Server`, concat(round((@@innodb_buffer_pool_size \/ 1024 \/ 1024 ),2),' GB') `Innodb Buffer Pool Size`,concat\r\n\r\n(round((@@innodb_log_file_size \/ 1024 \/ 1024),2),' MB') `Innodb Log file size`,@@innodb_flush_method `Innodb Flush Method`\\G\r\n*************************** 1. row ***************************\r\nInnodb Dedicated Server: 0\r\nInnodb Buffer Pool Size: 128.00 GB\r\nInnodb Log file size: 48.00 MB\r\nInnodb Flush Method: unbuffered\r\n1 row in set (0.11 sec) <\/pre>\n
mysql> set persist innodb_dedicated_server=1; \r\nERROR 1238 (HY000): Variable 'innodb_dedicated_server' is a read only variable \r\nmysql> set global innodb_dedicated_server=1; \r\nERROR 1238 (HY000): Variable 'innodb_dedicated_server' is a read only variable<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/activation_innodb_dedicated_server.png\")
<\/p>\nmysql> restart; \r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
Apr\u00e8s modification :<\/h3>\n
\r\nmysql> select @@global.innodb_dedicated_server;\r\nNo connection. Trying to reconnect...\r\nConnection id: 7\r\nCurrent database: *** NONE ***\r\n\r\n+----------------------------------+\r\n| @@global.innodb_dedicated_server |\r\n+----------------------------------+\r\n| 1 |\r\n+----------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'innodb_dedicated%';\r\n+-------------------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+-------------------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| innodb_dedicated_server | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n+-------------------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> 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;\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n| @@global.innodb_buffer_pool_size\/1024\/1024 | @@global.innodb_log_file_size\/1024\/1024 | @@global.innodb_log_files_in_group | @@global.innodb_flush_method |\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n| 6144.00000000 | 48.00000000 | 6 | unbuffered |\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'innodb_log_file%';\r\n+---------------------------+-----------------+----------------------------------------------+-----------+----------------------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+---------------------------+-----------------+----------------------------------------------+-----------+----------------------+----------+----------+----------+\r\n| innodb_log_file_size | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 4194304 | 18446744073709551615 | NULL | NULL | NULL |\r\n| innodb_log_files_in_group | COMPILED | | 2 | 100 | NULL | NULL | NULL |\r\n+---------------------------+-----------------+----------------------------------------------+-----------+----------------------+----------+----------+----------+\r\n2 rows in set (0.01 sec)<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/total_memory.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/innodb_dedicated_server_6_log_files_in_group.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/conf_my.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/non_prise_en_compte_innodb_log_file_size_explicit-1024x295.png\")
<\/a><\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/commenter_innodb_log_file_size.png\")
<\/p>\nmysql> restart;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> 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;\r\nERROR 2006 (HY000): MySQL server has gone away\r\nNo connection. Trying to reconnect...\r\nConnection id: 8\r\nCurrent database: *** NONE ***\r\n\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n| @@global.innodb_buffer_pool_size\/1024\/1024 | @@global.innodb_log_file_size\/1024\/1024 | @@global.innodb_log_files_in_group | @@global.innodb_flush_method |\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n| 6144.00000000 | 512.00000000 | 6 | unbuffered |\r\n+--------------------------------------------+-----------------------------------------+------------------------------------+------------------------------+\r\n1 row in set (0.16 sec)\r\n\r\nmysql> select @@global.innodb_dedicated_server;\r\n+----------------------------------+\r\n| @@global.innodb_dedicated_server |\r\n+----------------------------------+\r\n| 1 |\r\n+----------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'innodb_log_file%';\r\n+---------------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+---------------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+\r\n| innodb_log_file_size | COMPILED | | 4194304 | 18446744073709551615 | NULL | NULL | NULL |\r\n| innodb_log_files_in_group | COMPILED | | 2 | 100 | NULL | NULL | NULL |\r\n+---------------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+\r\n2 rows in set (0.10 sec)<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/conso_process_mysqld.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/02\/conso_process_mysqld_apres_activation.png\")
<\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"