15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server

Table des matières

When innodb_dedicated_server is enabled, InnoDB automatically configures the following options according to the amount of memory detected on the server:

innodb_buffer_pool_size

Table 15.7 Automatically Configured Buffer Pool Size

Detected Server Memory	Buffer Pool Size
< 1G	128MiB (the `innodb_buffer_pool_size` default)
<= 4G	Detected server memory * 0.5
> 4G	Detected server memory * 0.75

innodb_log_file_size

Table 15.8 Automatically Configured Log File Size

Detected Server Memory	Log File Size
< 1GB	48MiB (the `innodb_log_file_size` default)
<= 4GB	128MiB
<= 8GB	512MiB
<= 16GB	1024MiB
> 16GB	2048MiB

innodb_flush_method
The flush method is set to O_DIRECT_NO_FSYNC when innodb_dedicated_server is enabled. If the O_DIRECT_NO_FSYNC setting is not available, the default innodb_flush_method setting is used.

Warning

Prior to MySQL 8.0.14, the O_DIRECT_NO_FSYNC setting is not recommended for use on Linux systems. It may cause the operating system to hang due to file system metadata becoming unsynchronized. As of MySQL 8.0.14, InnoDB calls fsync() after creating a new file, after increasing file size, and after closing a file, which permits O_DIRECT_NO_FSYNC mode to be safely used on XFS and EXT4 file systems. The fsync() system call is still skipped after each write operation.

Only consider enabling this option if your MySQL instance runs on a dedicated server where the MySQL server is able to consume all available system resources. Enabling this option is not recommended if your MySQL instance shares system resources with other applications.

If an automatically configured option is configured explicitly in an option file or elsewhere, the explicitly specified setting is used and a startup warning similar to this is printed to stderr:

[Warning] [000000] InnoDB: Option innodb_dedicated_server is ignored for innodb_buffer_pool_size because innodb_buffer_pool_size=134217728 is specified explicitly.

Explicit configuration of one option does not prevent the automatic configuration of other options. For example, if innodb_dedicated_server is enabled and innodb_buffer_pool_size is configured explicitly in an option file, innodb_log_file_size and innodb_flush_method are still subject to automatic configuration.

Automatically configured settings are reevaluated according to the amount of detected server memory each time the MySQL server is started. If the amount of detected server memory changes, the automatically configured settings are adjusted accordingly.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-dedicated-server.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut