DBMS parameters

To fine-tune the database service, you can specify parameter values that will be passed to DBMS during the service launch and will become part of the configuration. Keep in mind that DBMS settings can significantly affect service performance. If the values were not set at the DB service creation, then Auto values will be applied.

There are plenty of parameters you can set in the web interface, so you may have to scroll up and down to find a desired parameter. If you don’t find the necessary parameters, you can add those parameters and values in the database service wizard.

These parameters can be also passed via PaaS API in the parameters field of the service creation request.

MySQL

The subsection describes DBMS parameters and lists its “valid”, “default” and Auto values. The Auto values for each MySQL service configuration parameter are selected so as to optimize resource utilization in K2 Cloud.

max_connections

The maximum permitted number of simultaneous client connections that a MySQL host can handle.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–100000	151	MiB RAM/32, min 151

max_allowed_packet

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the`mysql_stmt_send_long_data() <https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-send-long-data.html>`_.

Required	Datatype	Valid values	Defaults	Auto
Yes	Integer	1024–1073741824	16 MiB (MariaDB); 64 MiB (Percona)	N/A

max_connect_errors

Maximum number of connection errors, when the server blocks further connections to the host.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1– 18446744073709551615	100	N/A

innodb_buffer_pool_size

InnoDB buffer size (in bytes) used to cache table data and indexes. A larger buffer allows you to reduce the number of I/O operations when repeatedly accessing the same data.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	5242880– 9223372036854775807	128 MiB	50% RAM, if ≤ 4 GiB; 75% RAM, if ≥ 4 GiB

innodb_buffer_pool_instances

The number of regions that innodb_buffer_pool_size is divided into when innodb_buffer_pool_size > 1 GiB. Dividing the buffer pool allows you to use it more efficiently. This parameter is relevant for Percona 5.7, 8.0 и MariaDB 10.2, 10.3, 10.4.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–64	8 or 1, if innodb_buffer_pool_size < 1GiB	The number of full GiB in innodb_buffer_pool_size, if the parameter value is greater than 1 GiB

innodb_log_file_size

The size of a single file in bytes in the redo system log. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	4 MiB–512 GiB	96 MiB (for MariaDB v.10.5 and higher) 48 MiB (for MariaDB v.10.4 and lower) 48 MiB (for Percona 8)	(innodb_buffer_pool_size × 25%)/2, but no more than 4 GiB

innodb_log_files_in_group

The number of system log files in a log group.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	2–100	2	N/A

innodb_change_buffering

This parameter controls the cached data to optimize delays for write operations to secondary indexes so that the I/O operations can be performed sequentially.

Required	Datatype	Valid values	Defaults	Auto
No	String	inserts none deletes purges changes all none	none: ≥ MariaDB 10.5.15, 10.6.7, 10.7.3, 10.8.2; all: ≤ MariaDB 10.5.14, 10.6.6, 10.7.2, 10.8.1; Percona 8	N/A

innodb_io_capacity

The number of I/O operations per second (IOPS) available to InnoDB background tasks.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	100– 18446744073709551615	200	N/A

innodb_io_capacity_max

The maximum number of IOPS that InnoDB background tasks can perform.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	100– 18446744073709551615	max(innodb_io_capacity × 2, 2000)	N/A

innodb_purge_threads

The number of background threads allocated for the InnoDB purge operation. Increasing the value improve the efficiency systems where data operations are performed on multiple tables.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–32	4	N/A

innodb_thread_concurrency

The maximum number of threads permitted inside of InnoDB. This parameter is relevant for Percona 5.7, 8.0 and MariaDB 10.2, 10.3, 10.4.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–1000	0	N/A

innodb_sync_array_size

The size of the mutex/lock wait array. Increasing the value increases the level of parallelism when the number of waiting threads is large. This parameter is relevant for Percona 5.7, 8.0 and MariaDB 10.2, 10.3, 10.4.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–1024	1	N/A

innodb_flush_log_at_trx_commit

Controls the MySQL behaviour for transaction commit operations.

• 0 — Logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

• 1 (default) — This value provides for the highest availability level. InnoDB redo logs are written and flushed to disk at each transaction commit.

• 2 — InnoDB redo logs are written after each transaction commit and flushed to disk once per second. An OS or power outage can cause the loss of transactions in the last second.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0 1 2	1	N/A

thread_cache_size

The number of threads that the server caches to establish new network connections.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–16384	-1 (autosizing)	N/A

tmp_table_size

The maximum size of internal in-memory temporary tables (bytes). This parameter does not apply to user-created MEMORY tables.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1024–4294967295	16 MiB	N/A

max_heap_table_size

The maximum size in bytes to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	16384–4294966272	16 MiB	N/A

table_open_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–1048576	MariaDB: 2000 Percona 5.7: 2000 Percona 8: 4000	N/A

transaction_isolation

The transaction isolation level. See the MySQL documentation.

Required	Datatype	Valid values	Defaults	Auto
No	String	REPEATABLE-READ READ-UNCOMMITTED READ-COMMITTED SERIALIZABLE	REPEATABLE-READ	N/A

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it.

Required	Datatype	Valid values	Defaults	Auto
No	Numeric	1–31536000	28800	N/A

connect_timeout

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.

Required	Datatype	Valid values	Defaults	Auto
No	Numeric	2–31536000	10	N/A

pxc_strict_mode

PXC mode. The supported modes are detailed in the Percona documentation. It is applicable only in a high-availability configuration and only to percona.

Required	Datatype	Valid values	Defaults	Auto
Yes, if the High-availability service mode and percona vendor are selected.	String	DISABLED PERMISSIVE ENFORCING MASTER	ENFORCING	DISABLED

innodb_strict_mode

MySQL operation modes. The supported modes are detailed in the MySQL documentation.

Required	Datatype	Valid values	Defaults	Auto
Yes	String	ON OFF	ON	OFF

gcache.size

Gcache circular buffer storage size (the space the node uses for caching writesets), preallocated on startup.

Galera parameter. It is applicable only to a high-availability service.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	134217728–RAM/10	N/A	N/A

gcs.fc_limit

If the recv queue length exceeds the number of writesets, replication is suspended. There can be much more writesets in master-slave configurations. Replication will resume according to the gcs.fc_factor setting.

Galera parameter. It is applicable only to a high-availability service.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–2147483647	N/A	N/A

gcs.fc_factor

The fraction of gcs.fc_limit at which replication is resumed when the recv queue length falls below this value.

Galera parameter. It is applicable only to a high-availability service.

Required	Datatype	Valid values	Defaults	Auto
No	Float	0.0–1.0	N/A	N/A

gcs.fc_master_slave

This variable indicates whether the cluster has only one source node. This determines whether the gcs.fc_limit parameter is dynamically recalculated (if false) or not (if true).

Deprecated parameter. It is applicable only to Percona 5.7, MySQL 5.7, and MariaDB 10.2 and 10.3.

Galera parameter. It is applicable only to a high-availability service.

Required	Datatype	Valid values	Defaults	Auto
No	Boolean	true false	N/A	N/A

gcs.fc_single_primary

Indicates that there is more than one replication source. As the number of nodes in the cluster grows, the value of gcs.fc_limit dynamically increases. At the same time, the number of writes from nodes also increases. When this parameter is false (multi-primary), the gcs.fc_limit parameter is dynamically modified. This gives each node extra margin to be a bit further behind applying writes. The gcs.fc_limit parameter is modified as the square root of the cluster size, i.e., in a four-node cluster, it is twice the base value. This is done to compensate for the increasing replication rate noise.

This parameter replaces the deprecated gcs.fc_master_slave parameter and is applicable to Percona 8.0, MySQL 8.0, and MariaDB 10.4, 10.5, 10.6 and 10.7.

Galera parameter. It is applicable only to a high-availability service.

Required	Datatype	Valid values	Defaults	Auto
No	Boolean	true false	N/A	N/A

Redis

The subsection describes Redis service configuration parameters and lists its valid values.

databases

Number of databases (not relevant for high availability services).

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–2147483647	16	N/A

maxmemory

Maximum RAM size that will be allocated for user data on cluster nodes.

Note

RAM_LIMIT value is specified in MiB and equals to RAM size minus 3072 MiB (3 GiB).

Required	Datatype	Valid values, MiB	Defaults	Auto, MiB
No	String	0–RAM_LIMIT	N/A	RAM_LIMIT*80% – for single-node configurations, Sentinel, and a traditional 6-node Kubernetes cluster RAM_LIMIT*40% – for a traditional 3-node Kubernetes cluster

maxmemory-policy

Memory management mode when there is not enough memory.

Required	Datatype	Valid values	Defaults	Auto
No	String	noeviction allkeys-lru allkeys-lfu volatile-lru allkeys-random volatile-ttl volatile-lfu volatile-random	noeviction	N/A

The mode parameters are described here.

timeout

Time in seconds, for which connection to an inactive client is maintained.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–2147483647	0	N/A

tcp-backlog

The size of a connection queue.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–4096	511	N/A

tcp-keepalive

This option is used in order to detect dead peers (clients that cannot be reached even if they are identified as connected).

Required	Datatype	Valid values	Defaults	Auto
No	Integer	Non-negative	300	N/A

This parameter is described in the Redis documentation.

PostgreSQL

The subsection describes the database parameters and lists their valid values as well as default and Auto values. The Auto values for each PostgreSQL service configuration parameter are chosen so that resource utilization in the K2 Cloud is optimal.

A detailed description of the parameters can be found in the PostgreSQL documentation.

autovacuum

Controls whether the server should run the autovacuum launcher daemon.

Required	Datatype	Valid values	Defaults	Auto
No	String	ON/OFF	ON	N/A

autovacuum_max_workers

Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–262143	3	= vCPU, min 3

autovacuum_vacuum_cost_delay

Specifies the delay in milliseconds that will be used in automatic VACUUM operations when the cost limit is exceeded.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	-1 1–100	20 (ver 10-11) 2 (ver 12+)	N/A

autovacuum_vacuum_cost_limit

Specifies the cost limit value that will be used in automatic VACUUM operations.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	-1 1–10000	-1	N/A

autovacuum_analyze_scale_factor

Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE.

Required	Datatype	Valid values	Defaults	Auto
No	Float	0–100	0.1	N/A

autovacuum_vacuum_scale_factor

Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM.

Required	Datatype	Valid values	Defaults	Auto
No	Float	0–100	0.2	N/A

default_pool_size

The size of the pool of open connections to databases (PgBouncer).

Required	Datatype	Valid values	Defaults
No	Integer	1–200	20

max_connections

The maximum number of simultaneous connections to the database server.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–262143	100	200 × vCPU

shared_buffers

Sets the amount of memory the database server uses for shared memory buffers. See for details.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	16–1073741823	1024	25% RAM

wal_keep_segments

The minimum number of log files segments that should be kept in the pg_xlog directory, in case a standby server needs to fetch them for streaming replication. This parameter is relevant only for PostgreSQL: 10, 11, 12 version.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–2147483647	0	4

min_wal_size

As long as WAL disk usage stays below this setting (in bytes), old WAL files are always recycled for future use at a checkpoint, rather than removed.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	32–2147483647 MiB	80 MiB	5% of the volume, 1 GiB max

max_wal_size

The max size in bytes that WAL can reach at automatic checkpoints.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	2–2147483647 MiB	1 GiB	10% of the volume, 8 GiB max

wal_buffers

Shared memory to buffer WAL data not yet written to a volume. It is an integer number specifying the quantity of 8 KB units.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	8–262143	1/32 shared buffers, no less than 8 (64 KiB), but no more that the size of 1 WAL segment	N/A

effective_cache_size

Sets the planner’s assumption about the effective size of the disk cache that is available to a single query. See details.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1–2147483647	524288	N/A

work_mem

Sets the base maximum amount of memory in bytes to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	64–2147483647 KiB	4 MiB	N/A

maintenance_work_mem

Specifies the maximum amount of memory in bytes to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	1 MiB–2 GiB	64 MiB	N/A

effective_io_concurrency

Sets the number of concurrent disk I/O operations.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–1000	1	N/A

max_worker_processes

Sets the maximum number of background processes that the system can support.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–262143	8	= vCPU

max_parallel_workers_per_gather

Sets the maximum number of workers that can be started by a single Gather node. Parallel workers are taken from the pool of processes established by max_worker_processes.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–1024	2	vCPU/2

max_parallel_maintenance_workers

Sets the maximum number of parallel workers that can be started by a single utility command. This parameter is relevant only for PostgreSQL versions 11 and higher.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–1024	2	vCPU/2

max_parallel_workers

Sets the maximum number of workers that the system can support for parallel operations.

Required	Datatype	Valid values	Defaults	Auto
No	Integer	0–1024	8	= vCPU

max_client_conn

The maximum permitted number of simultaneous client connections to PgBouncer.

Required	Datatype	Valid values	Defaults
No	Integer	1–1000	100

pool_mode

PgBouncer operating mode.

Required	Datatype	Valid values	Defaults
No	String	session transaction statement	session

MongoDB

The subsection includes descriptions and valid values of DBMS parameters.

net.maxIncomingConnections

The maximum number of concurrent connections allowed for mongos or mongod.

Required	Datatype	Valid values	Defaults
No	Integer	10–51200	51200

operationProfiling.mode

Indicates which operations to profile.

DBMS profiler operating mode:

• off — profiling disabled;

• slowOp (by default) — information is collected only about slow operations the duration of which exceeds the threshold as per operationProfiling.slowOpThresholdMs;

• all — information is collected about all operations.

Required	Datatype	Valid values	Defaults
No	String	off slowOp all	slowOp

operationProfiling.slowOpThresholdMs

The operation time threshold in milliseconds, above which the operation is considered slow.

Required	Datatype	Valid values	Defaults
No	Integer	0–36000000	100

systemLog.quiet

The quiet mode of mongos or mongod, which is used to limit the output volume.

Required	Datatype	Valid values	Defaults
No	Boolean	true false	false

systemLog.verbosity

The level of message detail in the message log. To learn more, see MongoDB documentation.

Required	Datatype	Valid values	Defaults
No	String	v vv vvv vvvv vvvvv	0

storage.journal.commitIntervalMs

The maximum interval between saving log data, in milliseconds.

Required	Datatype	Valid values	Defaults
No	Integer	1–500	100

storage.wiredTiger.engineConfig.cacheSizeGB

The maximum size of internal cache in gibibytes, which will be used to store all data. To learn more about the parameter, follow this link.

Required	Datatype	Valid values	Defaults
No	Float	0,25–100000 GiB	(RAM — 1 GiB)/2 or 0,25 GiB