In MySQL 8.0, we will be introducing a new configuration parameter called innodb_dedicated_server=bool. When ON, this option will look at the system memory, and then automatically set the these configuration parameters using the following rules:
innodb_buffer_pool_size

server_memory < 1G ? 128M (same as current default)
server_memory <= 4G ? server_memory * 0.5
server_memory > 4G ? server_memory * 0.75

innodb_log_file_size

server_memory < 1G ? 48M (same as current default)
server_memory <= 4G ? 128M
server_memory <= 8G ? 512M
server_memory <= 16G ? 1024M
server_memory > 16G ? 2048M

innodb_flush_method = O_DIRECT_NO_FSYNC

When innodb_dedicated_server is OFF, the configuration will be as it is today. That is to say that it is not specifically a minimal configuration, but also not optimal for larger memory systems.

Background

The goal of innodb dedicated server is to improve the out-of-the-box experience when a user does not make any changes to configuration. As per our documentation, the current behavior is that:

The default configuration is designed to permit a MySQL server to start on a virtual machine that has approximately 512MB of RAM.

The 512M number is chosen to match the memory seen on entry-level cloud instances, which has not seen any increases over the years.

If (for example) a user starts MySQL on an instance with 64GB of RAM, the same small footprint will be used. This will result in a very tiny innodb-buffer-pool-size of 128M, when the recommended advice is to use 50-75% of system memory.

Which leads us to the question, could we target the resource consumption to match the host?

Problem #1: Deciding when to Target

The first issue to mention with targeting resource consumption to match the host, is that this is not always desired. Consider the use cases of running MySQL on your desktop, or running it on a server alongside a webserver.

In these cases it is not a safe assumption to set the buffer pool to 50-75% of system memory.

Problem #2: Deciding how to Target

If we have established cases where it is not safe to use 50-75% of system memory, maybe you could then set a percentage of memory that MySQL can safely use?

We thought about this, and it has two problems:

1. If you say “MySQL can use 60%”, then there would have to be some math to determine the buffer pool.  For example:
   16GB RAM Machine
   At 100% Dedicated for MySQL the Buffer Pool is set to 16GB * 0.75 =  12GB
   Adjust for 60% usage = 7.2GB
2. Our concern is that taking 60% of 75% might get confusing (KISS principle).
3. By the time you are asking a user to chose a number between 1% and 100%, you are already asking for configuration options. This quickly starts to defeat the purpose of this feature, which is to decide what to do in the case a user has not specified any configuration.

4. So the most important decision is only whether or not this server is intended to be a dedicated MySQL Server. For the other cases, it is reasonable to expect a user to adjust the configuration settings for their specific circumstances.

   Problem #3: Asking an Installer Question

   So we’ve arrived at this option being a boolean (is it dedicated or not?), but we did’t really answer the question of whether or not it is appropriate (i.e. do other programs have to run on this instance). On Mac OS X and Windows, we can now ask if this is a dedicated MySQL Server or not in the installers:

   Would you like MySQL to use all available memory on this system? [ Yes / No ]
   Selecting ‘Yes’ will improve overall MySQL performance. You may change this decision at any point by editing the innodb_dedicated_server option in your MySQL configuration file.

   The problem is that on Linux systems, we can not guarantee that our RPM and DEB packages are able to ask questions. Which gets us to the next problem.

   Problem #4: Selecting a Default

   Because we can not reliably ask questions, we need to have a default to fallback to when the user has not specified anything. Since this default will be selected when the user has not specified any configuration, it quickly puts you in a chicken-and-egg situation:

   i.e. if the user had to set that the server should be dedicated, they’ve made changes to configuration. At this point couldn’t they configure the buffer pool, log file and flush method anyway?

   That is to say that the true value delivered by this feature requires it to default ON. When OFF, it only achieves a level of indirection in making the question slightly easier to answer. The out-of-the-box experience is not actually improved.

   Side note: Another way this problem could be addressed is by having different defaults for different operating systems, or by having a ‘mysql-production’ (innodb-dedicated-server=ON) and a ‘mysql-development’ (innodb-dedicated-server=OFF) package.  This creates a new set of problems long enough for its own blog post :-).  The short answer is that we would prefer not to do this.

   Status Report on MySQL 8.0.3

   In MySQL 8.0.3, the feature innodb-dedicated-server will ship as described, but the default value will be set to OFF (for now). This is quite a big change, and there are some corner cases which we need to figure out the best way of moving forward. For example:

5. • If an installer (which can’t ask questions) auto-starts MySQL after install, it could result in an OOM situation which prevents MySQL from starting. For the same ease-of-use motivation, we don’t want to expect a user to tail a log file and have to resolve this issue themselves.
   • Defaulting to 2048MB for innodb-log-file-size is a >20 times increase over MySQL 5.7. We do not believe we can reliably detect free space on a partition in a cross platform way, and incorporate this into our calculation.
   • The dedicated-server value for innodb-log-file-size is slightly arbitrary. It does not not specifically relate to the amount of memory on the system, or the size of the buffer pool. For example: a workload that keeps updating the same pages may need a larger redo log than a workload with a bigger buffer pool. Maybe we can reduce the risk of out of space issues by changing the max value to 512M?
   • Maybe we can find a middle ground for innodb-buffer-pool-size too, where instead of taking up to 75% of system memory, it takes up to 50%? This makes it somewhat more cooperative with other processes, while still measurably better than the default. This feature was never intended as a complete replacement for configuration, and so being conservative with values is fine.

6. Seeking Feedback

   Our intention is to change the default to innodb-dedicated-server=ON before the GA release of MySQL 8.0. We’ve not gotten there yet; as we believe there is an opportunity to collect feedback, and improve upon how we resolve some of the known corner cases.

   While for many of our advanced users, this feature may not have much impact (configuration settings can be changed!), I remind you to please consider the goal is to narrowly focused on out of the box experience.

   Please let us know your thoughts, and thank you for helping improve MySQL!