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

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

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