DBMS parameters
In this article:
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, |
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); |
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– |
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– |
128 MiB |
50% 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) |
(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: ≥ MariaDB 10.5.15, 10.6.7, 10.7.3, 10.8.2; |
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– |
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– |
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 |
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 |
N/A |
transaction_isolation#
The transaction isolation level. See the MySQL documentation.
Required |
Datatype |
Valid values |
Defaults |
Auto |
---|---|---|---|---|
No |
String |
REPEATABLE-READ |
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 |
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 |
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 |
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 |
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 |
maxmemory-policy#
Memory management mode when there is not enough memory.
Required |
Datatype |
Valid values |
Defaults |
Auto |
---|---|---|---|---|
No |
String |
noeviction |
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 |
20 (ver 10-11) |
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 |
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 |
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, |
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, |
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 |
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 |
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 |
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 |