The current default configuration targets a minimal configuration of around 512M of memory.

As many deployments are in virtual machines or isolated containers, and user expectations are changing towards ease-of-use, we need an easier way to expand the target configuration to consume all resources on the machine.

Thus; a new option innodb_dedicated_server is introduced which could be set OFF/ON. If ON, settings for following InnoDB variables (if not specified explicitly) would be scaled accordingly

• innodb_buffer_pool_size (to consume appropriate amount of memory).
• innodb_log_file_size
• innodb_flush_method

Upgrade Story

This default changes MySQL's approach to resource allocation from trying to fit within an approximately 512M memory foot print, to assuming it can consume all system resources. This has the benefit that most users will no longer need to make any configuration changes to MySQL in order for it to perform well out of the box.

In cases where MySQL can not consume all system resources (for example a Developer's workstation) it is recommended to revert to the previous configuration (where approx 512M of RAM is used). This can be done by setting innodb_dedicated_server=OFF.

FR-1: A new variable innodb_dedicated_server is added:

• Type : boolean
• Values : ON/OFF
• Default : OFF
• Scope : global
• Dynamic : no

FR-2: When innodb_dedicated_server=ON the following settings will change value based on the amount of installed memory detected on the server ('server_memory'):

• 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

FR-3: Explicit setting values will always take precedence over innodb_dedicated_server option i.e. when there is an explicit value specified for innodb_buffer_pool_size/innodb_log_file_size/innodb_flush_method this explicit value will be used and there would be warning printed

"[Warning] InnoDB: Option innodb_dedicated_server is ignored for <variable name> because <variable name>=<variable explicit value> is specified explicitly."

Here 'variable name' could be innodb_buffer_pool_size/innodb_log_file_size/inndob_flush_method

NOTE: Variable value, specified at anyplace (for eg: configuration file, command line or persisted conf file etc.) will be considered explicitly specified.

FR-4: Explicit setting of any variable doesn't affect autoscaling of other variables i.e. if innodb_buffer_pool_size is set explicitly, then it will not be autoscaled based on innodb_dedicated_server value. But innodb_log_file_size and innodb_flush_method will be set based on innodb_dedicated_server value.

FR-5: Autoscaling is re-evaluated on each subsequent mysqld restart (i.e. at no point are values saved in persistent config). This is required for the use case of cloud VMs that change size.

FR-6: If O_DIRECT_NO_FSYNC is not supported, the previous default should apply. (MySQL must still start on all platforms with no out-of-box changes.)

FR-7: If auto-scaling the redo log file results in an out of space issue, the following semantics should be respected:

• The new (incomplete) log files should be deleted
• An error should be written to the log file as follows:

 "[ERROR] InnoDB: Error number 28 means 'No space left on device'
  [ERROR] InnoDB: Cannot set log file <file name> to size <size> MB".

• The server should refuse to start i.e. a back-off algorithm on redo-log size is not in scope. Refusing to start is an acceptable limitation, but ideally the failed log file should be deleted first as writing to the error log because of the assumption they are the same device.

NFR-1: Users who use innodb_dedicated_server=ON do not expect configuration to be fine-tuned. We do not cater for MyISAM/InnoDB mix, and if we want to change the auto-scale rules we should wait for next GA.

A new variable

• name : innodb_dedicated_server
• type : boolean
• scope : global
• persistent : NO
• default : ON
• purpose : indicate if auto-scaling is to be done or not.

The innodb_dedicated_server option can not be dynamic, because the log file option is currently not dynamic.

A new plugin service

In plugin, there is no way to know the source of a variable setting (for eg: COMPILED, COMMAND_LINE etc.). But server has this information. So a new service is added using which a plugin can get SOURCE information of a variable. This information is needed to make sure auto-scaling is not done if variable is set explicitly.

enum enum_variable_source source
get_sysvar_source (const char *variable_name, 
                   unsigned int variable_name_length);

NOTES

This WL is designed to make MySQL "work better outside of the box", and adjust to changing user expectations about simplicity.

Survey

• Microsoft SQL Server "By default, SQL Server can change its memory requirements dynamically based on available system resources."

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options

• MongoDB (prior to WiredTiger) uses mmap files, and thus has some ability to

use free system resources without needing such a configuration option.

• MongoDB (with WiredTiger) uses 60% of RAM less 1GB. Explained here:

https://docs.mongodb.com/manual/reference/configuration- options/#storage.wiredTiger.engineConfig.cacheSizeGB

• TokuDB uses 50% of physical memory by default:

https://www.percona.com/doc/percona- server/5.7/tokudb/tokudb_variables.html#tokudb_cache_size

• Amazon RDS uses DBInstanceClassMemory*3/4 (i.e. 75% of memory). And beats

MySQL at naive benchmarks because of this. RDS does not enable pfs by default, which is why it can use 75%.

• MySQL Cloud Service on OPC currently scales the memory on initial instance

creation, but not having a good default means that resizing a virtual machine instance does not work correctly (requires manual config change).

Installers

Our installers can be modified to ask the following question, which is phrased to be as frictionless as possible:

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.

(Note that the installers should not run a script to determine sizes, as this does not work with virtual machine resizing etc. We also know that users copy config files around.)

Behavior

Resolving conflicts between option precedence: - Actual setting names always take precedence over innodb_dedicated_server option - Warning should be printed to error log:

"Option dedicated_server will not take effect because 

innodb_buffer_pool_size=128M was specified".

For determining the memory size on different operating systems, relevant article: http://nadeausoftware.com/articles/2012/09/c_c_tip_how_get_physical_memory_size_system

Need something similar for disks.