The automatic settings must include the following:

• innodb_buffer_pool_size: something like 80% of the buffer pool size has been suggested in the past
• innodb_log_file_size: for write-heavy workloads, this should be innodb_buffer_pool_size or even larger.

80% of memory for buffer pool is way too much!! We (RDBA) set that to 60% because large queries can quickly use a lot of memory and they trigger OOM otherwise.

serg,

First POC of implementing this MDEV.

While the original concept of AUTO_SET was to set a good value at startup, the enhancement concept here is to include the auto_set boolean as part of the sys_var. This way any area of code that uses the variable can examine if auto_set for that variable is true, and if it make sense to change it, the code effectively has permission to do so. This applies to plug-ins also. Like what Monty requested in MDEV-19839, the plugin can preserve the rations of all AUTO_SET variables.

So a innodb_log_file_size can increase based on reasonable emergency write pressure, or innodb_buffer_pool_size can increase. based on a reasonable innodb_buffer_pool_reads vs innodb_buffer_pool_read_requests. There's an reasonable expectation that commiserate to the increase sizes there's also decrease in size if there is a reduction in need (perhaps a little slower that increasing to avoid setting flip-flopping). If its not obvious what reasonable is, some new system variable e.g. innodb_buffer_pool_ideal_read_hit_ratio can guide the increase/decrease.

Further those that consume extra memory because of need, yes innodb_buffer_pool_size I'm looking at you, will take advantage of MDEV-24670 operating system memory pressure as a signal to hold off further allocation and to reduce usage reasonably (again define a sysvar for that threshold (e.g. unused_cache_time) for reasonableness if needed).

Setting a system variable to a value, either by command line/config or SET GLOBAL var=value will remove the auto setting. The variable can be set back to auto with SET GLOBAL xxx=AUTOSET if it had an AUTO_SET flag.

The problem with available memory measures is that the point at which MariaDB starts up may not be the most, or least memory congested time. So lets behave like a good citizen of the computer and take what's needed and bypass some low defaults, release what's not needed, and if the OS cries help, give within the reasonable bounds of variables that are autoset.

The goal of autoset is to approach a low configuration application to give all users the best possible performance wrt to their hardware without configuration based on their workload.

This task should preferably done together with MDEV-19839.

However, before starting this task, we need a full specification of what should be done, how it should be done, all variables that should support autoset, including full syntax.
Daniel, can you please provide a specification that we can review?
The specification should also include a full mtr test for all syntax's, which we can then use to check against the implementation.

Autoset was originally designed to only be used at server startup. This is because setting one variable may cause other variables to also be automatically changed (for example storage engine caches).
It should also be made clear that using any autoset variable implies that the server is totally dedicated for MariaDB and MariaDB is free to allocate all resources available for itself!

mtr specification in updated commit covering all syntax changes. (unless you think a non-information_schema exposure of autoset is needed).

An AUTO_SET option on variable by default gain an autoset property in which they can adjust over time. Once assigned a fixed value, configuration or SET GLOBAL var=x they loose their autoset property giving experienced administrators maximum control.

Undecided, should setting to AUTOSET propagate to a sys_var::update to enable to auto-balancing of the variable? The argument against this propagation is that to be workload driven, you can examine Sys_XXX.auto_set at any time, particularly the time when its decided its not big enough, which probably saves some between thread coordination.

The autoset is valid only on global variables. Setting a global variable to a fixed value and having the session variable have the option of choosing a different value doesn't make sense. Though a session variable could depend on the global autoset status, like sort_buffer_size (could be), that on excessive sort_merge_passes or an estimated workload, this could increase the sort buffer if the global sort_buffer_size has autoset property (aka hasn't been assigned a fixed value).

Ratios and interdependence between variables is expected, so if a workload pushes the value of one cache variable size, the code is free to change the other system variables (if they too have a autoset property).

Also undecided is with workload based adjustments going on, should these variables be saved as the starting point for the next server restart?

Regardless if its a dedicated server, different workloads will require memory in different practices. rpizzi's comment above proves this!

To allocate all memory/resources to MariaDB deprives the user from actually being able to measure how much their workload needs. I don't want our users to need to over-provision hardware because of lack of ability to measure workload resources using standard OS tools.

The downside is obviously it does mean during the autosizing there is a small amount of CPU usage in the workload to account for the resizing and a careful implementation of each individual resizing mechanisms to ensure the overhead is low. To allocate all resources even across NUMA boundaries can also cause a performance hit.

The existing autoset balancing on startup around rlimits on nofiles is a good first estimate to try to stay out of trouble.

It should be made clear that autoset is about auto-sizing based on workload, not allocation for the sake of existence of resources!

MDEV-19839 For the plugin side like:

--- a/include/mysql/plugin.h

+++ b/include/mysql/plugin.h

@@ -235,6 +235,7 @@ typedef int (*mysql_show_var_func)(MYSQL_THD, struct st_mysql_show_var*, void *,

 #define PLUGIN_VAR_OPCMDARG     0x2000 /* Argument optional for cmd line */

 #define PLUGIN_VAR_DEPRECATED   0x4000 /* Server variable is deprecated */

 #define PLUGIN_VAR_MEMALLOC     0x8000 /* String needs memory allocated */

+#define PLUGIN_VAR_AUTOSET     0x10000 /* Autoset */

 struct st_mysql_sys_var;

 struct st_mysql_value;

@@ -326,6 +327,7 @@ typedef void (*mysql_var_update_func)(MYSQL_THD thd,

   type *value; type def_val;    \

   type min_val; type max_val;   \

   type blk_sz;                  \

+ my_bool autoset;                 \

 } MYSQL_SYSVAR_NAME(name)

And a similar implementation in sys_var_pluginvar::. Likewise functionality that a plugin can auto-adjust based on workload and implement rations of variables if needed.

Also the convoluted logic of setting innodb_open_files (whose initial default value is 0) should be revised here. It currently depends on open_files_limit and table_open_cache (tc_size):

	if (innobase_open_files < 10) {

		innobase_open_files = 300;

		if (srv_file_per_table && tc_size > 300 && tc_size < open_files_limit) {

			innobase_open_files = tc_size;

		}

	}

	if (innobase_open_files > open_files_limit) {

		ib::warn() << "innodb_open_files " << innobase_open_files

			   << " should not be greater"

			   << " than the open_files_limit " << open_files_limit;

		if (innobase_open_files > tc_size) {

			innobase_open_files = tc_size;

		}

	}

	srv_max_n_open_files = innobase_open_files;