MySQL settings
For Managed Service for MySQL clusters, you can configure MySQL settings. Some settings are configured at the cluster level, while others, at the user level.
The label next to the setting name shows which interface can be used to set the value of this setting: the management console, CLI, API, or Terraform. The All interfaces
label means that all of the above interfaces are supported.
Depending on the selected interface, the same setting may be represented in a different way. For example, Innodb buffer pool size in the management console matches:
innodb_buffer_pool_size
in gRPC, CLI, or Terraform.innodbBufferPoolSize
in the REST API.
How settings depend on the host class
The values of some MySQL settings can be automatically adjusted when you change the host class:
- If the values were not specified or are not suitable for the new class, the default settings for this class will be applied.
- If the settings you specified manually are suitable for the new class, they will not change.
The settings that depend on the host class are:
Cluster-level DBMS settings
-
Audit log
All interfaces
Manages writing the MySQL audit log.
By default, this setting is disabled.
For more information, see the MySQL documentation
. -
Auto increment
All interfaces
Sets the interval between the values of
AUTO_INCREMENT
columns.The minimum value is
1
and the maximum value is65535
. The default value is1
.For more information, see the MySQL documentation
. -
Auto increment offset
All interfaces
Sets the initial value for
AUTO_INCREMENT
columns. This setting is ignored if its value is greater than Auto increment.The minimum value is
1
and the maximum value is65535
. The default value is1
.For more information, see the MySQL documentation
. -
Binlog cache size
All interfaces
Size of the cache (in bytes) for storing changes to the binary log
during the transaction.The minimum value is
4096
(4 KB) and the maximum value is67108864
(64 MB). The default value is32768
(32 KB).For more information, see the MySQL documentation
. -
Binlog group commit sync delay
All interfaces
Sets the delay before synchronizing the binary log to disk when performing a
COMMIT
for the binary log. To synchronize more transactions to disk at a time, set the delay to a value greater than zero. This will reduce the total time perCOMMIT
for a group of transactions.The minimum value is
0
(no delay) and the maximum value is1000000
(one second). The default value is0
.For more information, see the MySQL documentation
. -
Binlog row image
All interfaces
Determines how row images are written to the binary log for row-based replication:
FULL
(default): Log all columns in both the "before" and "after" images.MINIMAL
: Log only those columns in the "before" image that are required to identify the rows to be changed; log only those columns in the "after" image where a value was specified by the SQL statement, or generated by auto-increment.NOBLOB
: Log all columns in the "before" and "after" images (same asFULL
), except forBLOB
andTEXT
columns that either have not changed, or are not required to identify rows.
For more information, see the MySQL documentation
. -
Binlog rows query log events
All interfaces
Controls logging information events (for example, query log events) in the binary log.
By default, this setting is disabled.
For more information, see the MySQL documentation
. -
Binlog transaction dependency tracking
Management console
CLI
API
The schema to calculate logical temporary labels that help replicas determine which transactions can be executed concurrently. It is used for generating dependency information, which is written to a binary log when multithreaded replication (Slave parallel workers) with the Slave parallel type
LOGICAL_CLOCK
policy is enabled.Possible values:
-
COMMIT_ORDER
(default): Two transactions are considered independent if the period of committing the first transaction overlaps with the period of committing the second one. -
WRITESET
: Based onCOMMIT_ORDER
. Additionally, two transactions are considered conflicting if a value (hash) appears in the record sets of both transactions. -
WRITESET_SESSION
: Two transactions are considered dependent if at least one criterion is met:- The transactions are dependent according to the
WRITESET
schema. - The transactions were committed during one user session.
- The transactions are dependent according to the
For more information, see the MySQL documentation
. -
-
Character set server
All interfaces
The character set
used by the MySQL cluster when working with data and exchanging information with MySQL clients. This choice affects the performance of SQL functions for manipulating strings and other features.The default value is
utf8mb4
.For more information, see the MySQL documentation
. -
Collation server
All interfaces
The algorithm for collating characters
used by the MySQL cluster when working with data and exchanging information with MySQL clients. This choice affects the performance of SQL functions for sorting data, manipulating strings, and other features.The default value is
utf8mb4_0900_ai_ci
.For more information, see the MySQL documentation
. -
Default authentication plugin
All interfaces
The authentication plugin used in Managed Service for MySQL clusters:
mysql_native_password
: The authentication method that was used in MySQL prior to the introduction of authentication plugins.sha256_password
: The authentication method using the SHA-256 hashing algorithm for passwords.caching_sha2_password
(default): Similar tosha256_password
, uses server-side caching for better performance and provides some additional features.
For more information, see the MySQL documentation
. -
Default time zone
All interfaces
The server time zone.
The default value is
Europe/Moscow
.For more information, see the MySQL documentation
. -
Explicit defaults for timestamp
All interfaces
Controls non-standard default values and
NULL
value handling inTIMESTAMP
columns.By default, the setting is enabled, which disables any nonstandard behaviors.
For more information, see the MySQL documentation
. -
General log
All interfaces
Controls writing the MySQL general query log.
By default, this setting is disabled.
For more information, see the MySQL documentation
. -
Group concat max len
All interfaces
The maximum result length (in bytes) for the GROUP_CONCAT()
function.The minimum value is
4
and the maximum value is33554432
(32 MB). The default value is1024
(1 KB).For more information, see the MySQL documentation
. -
Innodb adaptive hash index
All interfaces
Manages the InnoDB adaptive hash index
. It may be desirable to disable this index for some types of database workloads. The MySQL documentation recommends performing load testing on real data to determine whether to enable the adaptive hash index.By default, the adaptive hash index is enabled.
For more information, see the MySQL documentation
. -
Innodb buffer pool size
All interfaces
The size of the InnoDB buffer pool (in bytes) used for caching table and index data. A larger buffer pool requires fewer I/O operations to access the same table data more than once.
The minimum value is
134217728
(128 MB). The maximum and default values depend on the selected host class and are set according to the table:Amount of GB RAM on the host Default value Maximum value 2 268435456
(0.25 GB)536870912
(0.5 GB)4 1610612736
(1.5 GB)2684354560
(2.5 GB)≥ 8 0.5 × RAM
0.8 × RAM
For more information, see the configuration recommendations and MySQL documentation
. -
Innodb compression level
Management console
CLI
API
The compression level
zlib
used for InnoDB compressed tables and indexes.The minimum value is
0
and the maximum value is9
. The default value is6
.For more information, see the MySQL documentation
. -
Innodb flush log at trx commit
All interfaces
Determines the behavior of MySQL for a transaction's
COMMIT
:1
(default): Required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.2
: Logs are written after each transaction commit, but flushed to disk once a second. Transactions for which logs have not been flushed can be lost in a crash.
For more information, see the MySQL documentation
. -
Innodb ft max token size
All interfaces
The maximum length of words stored in the InnoDB
FULLTEXT
index.The minimum value is
10
and the maximum value is84
. The default value is84
.For more information, see the MySQL documentation
. -
Innodb ft min token size
All interfaces
The minimum length of words stored in the InnoDB
FULLTEXT
index.The minimum value is
0
and the maximum value is16
. The default value is3
.For more information, see the MySQL documentation
. -
Innodb io capacity
All interfaces
The number of I/O operations per second (IOPS) available to all InnoDB background tasks. This setting affects processes that require I/O, such as flushing data to disk, and can be used to limit the number of I/O operations.
The minimum value is
100
and the maximum value is100000
. The default value is200
.For more information, see the MySQL documentation
. -
Innodb io capacity max
All interfaces
The maximum number of I/O operations per second (IOPS) available to all InnoDB background tasks. If a host's flushing activity falls behind, InnoDB may start flushing data to disk at a higher rate of IOPS than defined by the Innodb io capacity setting but within the specified maximum number of IOPS.
The minimum value is
100
and the maximum value is100000
. The default value is2000
.For more information, see the MySQL documentation
. -
Innodb lock wait timeout
All interfaces
The maximum time (in seconds) an InnoDB transaction waits for a row lock. When a lock wait timeout occurs, an error is returned and the current SQL statement (not the entire transaction) is rolled back.
You can decrease this value for OLTP applications and interactive user applications. You can increase this value if your application has lengthy operations, e.g., large
INSERT
orUPDATE
queries awaiting completion when transforming large arrays of data from the data storage.The minimum value is
1
and the maximum value is28800
(480 minutes, or 8 hours). The default value is50
.For more information, see the MySQL documentation
. -
Innodb log buffer size
All interfaces
The size of the buffer (in bytes) that InnoDB uses to write the log files on disk. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. This saves disk I/O.
The minimum value is
1048576
(1 MB) and the maximum value is268435456
(256 MB). The default value is16777216
(16 MB).For more information, see the MySQL documentation
. -
Innodb log file size
All interfaces
The size of a single InnoDB redo log file (in bytes). The larger the value, the less checkpoint
flush activity is required in the buffer pool, saving disk I/O. At the same time, larger log files make crash recovery slower.The minimum value is
268435456
(256 MB) and the maximum value is4294967296
(4 GB). The default value is268435456
(256 MB).For more information, see the MySQL documentation
. -
Innodb numa interleave
All interfaces
Controls the NUMA Interleave
policy for allocation of the InnoDB buffer pool.This policy is disabled by default.
For more information, see the MySQL documentation
. -
Innodb online alter log max size
All interfaces
The size of temporary InnoDB log files (in bytes) received during online DDL queries
. Larger value allows to use more DML operations during DDL queries; however, it also increases the period after running a DDL query during which a table is locked while the log data is being applied.The minimum value is
65536
(64 KB) and the maximum value is107374182400
(100 GB). The default value is134217728
(128 MB).For more information, see the MySQL documentation
. -
Innodb page size
All interfaces
The page size for InnoDB tablespace (in bytes). You cannot edit this setting after you create a cluster.
Possible values:
4096
(4 KB),8192
(8 KB),16384
(16 KB),32768
(32 KB), and65536
(64 KB). The default value is16384
(16 KB).For more information, see the MySQL documentation
. -
Innodb print all deadlocks
All interfaces
Manages writing information about all deadlocks
to the error log. If disabled, you see information about only the last deadlock, using theSHOW ENGINE INNODB STATUS
command.By default, the option for showing information about all deadlocks is off.
For more information, see the MySQL documentation
. -
Innodb purge threads
All interfaces
The number of InnoDB I/O threads used for purge operations
. Increasing the number of these threads can improve efficiency on systems where data manipulation operations (INSERT
,UPDATE
, orDELETE
) are performed on multiple tables.The minimum value is
1
and the maximum value is16
. The default value is4
.For more information, see the MySQL documentation
. -
Innodb read io threads
All interfaces
The number of InnoDB I/O threads used for read operations.
The minimum value is
1
and the maximum value is16
. The default value is4
.For more information, see the MySQL documentation
. -
Innodb status output
Management console
Manages the output of a standard InnoDB monitor
.By default, the standard monitor is disabled.
For more information, see the MySQL documentation
. -
Innodb strict mode
Management console
Manages the strict mode
in Innodb. When enabled, specific conditions that are normally treated as warnings are considered errors.By default, InnoDB strict mode is enabled.
For more information, see the MySQL documentation
. -
Innodb temp data file max size
All interfaces
The maximum size of an InnoDB temporary tablespace
(in bytes).The minimum value is
0
(no temporary tablespace is used). The maximum value is107374182400
(100 GB). The default value is0
.For more information, see the MySQL documentation
. -
Innodb thread concurrency
All interfaces
The maximum number of concurrently executed InnoDB threads.
The minimum value is
0
(no limit) and the maximum value is1000
. The default value is0
.For more information, see the MySQL documentation
. -
Innodb write io threads
All interfaces
The number of InnoDB I/O threads used for write operations.
The minimum value is
1
and the maximum value is16
. The default value is4
.For more information, see the MySQL documentation
. -
Interactive timeout
All interfaces
The period of time (in seconds) for which the server waits for activity during an interactive connection before terminating it.
The minimum value is
600
(10 minutes) and the maximum value is86400
(24 hours). The default value is28800
(8 hours).For more information, see the MySQL documentation
. -
Join buffer size
All interfaces
The minimum size of the buffer (in bytes) that is used for:
- Plain index scans.
- Range index scans.
- Full table scans (for
JOIN
operations where no index is used).
One join buffer of the specified size is allocated for each full join between two tables. Increase the value of this setting to get a faster full join when adding indexes is not possible.
The minimum value is
1024
(1 KB) and the maximum value is16777216
(16 MB). The default value is262144
(256 KB).For more information, see the MySQL documentation
. -
Lock wait timeout
Management console
CLI
API
The maximum wait time (in seconds) when attempting to get a metadata lock
.The minimum value is
1
, the maximum one is31536000
(one year). The default value is also31536000
.For more information, see the MySQL documentation
. -
Log error verbosity
Management console
Defines the type of events written to the error log:
2
: Errors and warnings.3
(by default): Errors, warnings, and information messages.
For more information, see the MySQL documentation
. -
Log slow filter
Management console
CLI
API
Filter for a slow query log by query type. It is a string with any combination of the following comma-separated values:
full_scan
: Queries that perform a full table scan will be included in the log.full_join
: Queries that perform full combination (FULL JOIN, without indexes) will be included in the log.tmp_table
: Queries that create an implicit internal temporary table will be included in the table.tmp_table_on_disk
: Queries that save a temporary table on a disk will be included in the log.filesort
: Queries that perform file sorting will be included in the log.filesort_on_disk
: Queries that perform file sorting on the disk will be included in the log.
If the filter value is set, only query types included in the filter will appear in the slow query log.
The default value is
""
(empty string, query filter is disabled).For more information, see the Percona documentation
. -
Log slow rate limit
All interfaces
The setting shows which part of queries will be included in the slow query log. Depending on the Log slow rate type setting value, the setting is applied to individual queries (
QUERY
) or sessions (SESSION
).If the value is
1
, the log will include every query or session that is considered slow. With other values, every Nth query or cluster will be logged, which will reduce cluster load.If query processing time exceeds the value of Slow query log always write time, the query is written to the slow query log regardless of the value of Log slow rate limit.
The minimum value is
1
and the maximum value is1000
. The default value is1
.For more information, see the Percona documentation
. -
Log slow rate type
All interfaces
Sets the entry type for the slow query log to configure Log slow rate limit:
query
: At the query level.session
: At the session level.
The default value is
query
.For more information, see the Percona documentation
. -
Log slow sp statements
All interfaces
Controls writing of expressions run by stored procedures to the slow query log.
By default, the writing of expressions is enabled.
For more information, see the Percona documentation
. -
Long query time
All interfaces
If a query takes longer than this number of seconds, it is considered slow. The lower the value, the more queries will be considered slow.
The minimum value is
0
and the maximum value is3600
(1 hour). The default value is10
.For more information, see the MySQL documentation
. -
Lower case table names
All interfaces
Determines the type of storage and comparison of table names:
0
(by default): Table names are saved with no changes. Comparisons are case-sensitive.1
: Table names are saved in lowercase. Comparisons are case-insensitive.
You cannot edit this setting after you create a cluster.
For more information, see the MySQL documentation
. -
Max allowed packet
All interfaces
The maximum size (in bytes) of one packet, string, or parameter sent by the mysql_stmt_send_long_data()
function.The default value is small in order to discard incorrect packages, which are usually larger. Increase this value if you are using large BLOB columns or long strings.
The minimum value is
1024
(1 KB) and the maximum value is134217728
(128 MB). The default value is16777216
(16 MB).For more information, see the MySQL documentation
. -
Max connections
All interfaces
The maximum number of simultaneous connections permitted for MySQL cluster hosts.
The minimum value is
10
. The maximum and default values depend on the selected host class and are determined by the formula:- The maximum value is
<MB_of_RAM_per_host> / 8
. - The default value is
<MB_of_RAM_per_host> / 32
. The minimum value is100
.
For more information, see the MySQL documentation
. - The maximum value is
-
Max digest length
Management console
The amount of memory (in bytes) allocated for the calculation of normalized statement digests
.The minimum value is
1024
(1 KB) and the maximum value is8192
(8 KB). The default value is1024
(1 KB).For more information, see the MySQL documentation
. -
Max heap table size
All interfaces
The maximum size of user-created [MEMORY tables]https://dev.mysql.com/doc(/refman/8.0/en/memory-storage-engine.html) (in bytes). Changing this setting has no effect on any existing MEMORY table. It is also used in conjunction with the Tmp table size setting to limit the size of internal in-memory tables.
The minimum value is
16384
(16 KB) and the maximum value is134217728
(128 MB). The default value is16777216
(16 MB).For more information, see the MySQL documentation
. -
Max prepared stmt count
Management console
CLI
API
Maximum number of prepared requests on the server.
This option can be used for protecting from DoS attacks that overflow the server memory by a large number of prepared requests.
The minimum value is
0
, the maximum one is4194304
. The default value is16382
.For more information, see the MySQL documentation
. -
Max sp recursion depth
Management console
CLI
API
The maximum number of recursive invocations of a stored procedure.
The minimum value is
0
(recursion is disabled) and the maximum value is255
. The default value is0
.For more information, see the MySQL documentation
. -
Mdb offline mode disable lag
All interfaces
The replication lag (in seconds) before switching MySQL to
offline_mode = OFF
. The value must be less than the Mdb offline mode enable lag value.The minimum value is
60
(1 minute) and the maximum value is86400
(24 hours). The default value is300
(5 minutes). -
Mdb offline mode enable lag
All interfaces
The replication lag (in seconds) before switching MySQL to
offline_mode = ON
so that users get up-to-date data.The minimum value is
600
(10 minutes) and the maximum value is432000
(5 days). The default value is86400
(24 hours). -
Mdb preserve binlog bytes
All interfaces
The size of the binary logs (in bytes) to store on cluster hosts.
The minimum value is
1073741824
(1 GB) and the maximum value is107374182400
(100 GB). The default value is1073741824
(1 GB). -
Mdb priority choice max lag
All interfaces
The replication lag (in seconds) before switching the cluster host role from replica to master. The value must be less than the Mdb offline mode enable lag value.
The minimum value is
0
and the maximum value is86400
(24 hours). The default value is60
(1 minute). -
Net read timeout
All interfaces
The maximum number of seconds to wait for more data from a network connection before aborting the read.
The minimum value is
1
and the maximum value is1200
(20 minutes). The default value is30
.For more information, see the MySQL documentation
. -
Net write timeout
All interfaces
The maximum number of seconds to wait for data to be written to a network connection before aborting the write.
The minimum value is
1
and the maximum value is1200
(20 minutes). The default value is60
.For more information, see the MySQL documentation
. -
Optimizer search depth
Management console
CLI
API
Maximum depth of search performed by the query optimizer.
If the setting value is higher than the number of relationships in the query, a better query execution plan will be created; it will, however, require more time to create such a plan. If the value is lower, the query execution plan will be generated faster, although it may not be perfect.
The minimum value is
0
(the system will select the search depth automatically), while the maximum one is62
. The default value is62
.For more information, see the MySQL documentation
. -
Optimizer switch
Management console
CLI
API
String with a set of flags
, each of which enables or disables certain optimization behavior.For more information, see the MySQL documentation
. -
Range optimizer max mem size
All interfaces
The maximum memory consumption (in bytes) by the range optimizer.
The minimum value is
1048576
(1 MB) and the maximum value is268435456
(256 MB). The default value is8388608
(8 MB).For more information, see the MySQL documentation
. -
Regexp time limit
All interfaces
The limit on the number of steps for matching operations performed by REGEXP_LIKE()
and similar functions when using regular expressions . This setting indirectly affects execution time.The minimum value is
0
(no limit) and the maximum value is1048576
. The default value is0
.For more information, see the MySQL documentation
. -
Rpl semi sync master wait for slave count
All interfaces
The number of replica acknowledgments that the master must receive during semi-synchronous replication before a transaction's
COMMIT
.The minimum value is
1
and the maximum value is2
. The default value is1
.For more information, see the MySQL documentation
. -
Slave parallel type
All interfaces
Specifies the policy used to decide which transactions are allowed to execute in parallel on the replica when multithreaded replication is enabled (set by Slave parallel workers):
LOGICAL_CLOCK
: Transactions that are part of a group commit for the same binary log on the source are executed in parallel on the replica.DATABASE
(default): Transactions that update different databases are applied in parallel.
For more information, see the MySQL documentation
. -
Slave parallel workers
All interfaces
Sets the number of threads for executing replication transactions in parallel on the replica.
The minimum value is
0
(multithreading on the replica is disabled) and the maximum value is64
. The default value is0
.For more information, see the MySQL documentation
. -
Slow query log
All interfaces
Permits logging slow queries. A query is considered slow if its execution time exceeds the Long query time setting value.
Acceptable values include:
0
orOFF
.1
orON
.
The default value is
OFF
.For more information, see the MySQL documentation
. -
Slow query log always write time
All interfaces
Query execution time (in seconds) after which a query is unconditionally written to the slow query log ignoring the Log slow rate limit setting.
The minimum value is
0
and the maximum value is3600
(1 hour). The default value is10
.For more information, see the Percona documentation
. -
Sort buffer size
All interfaces
The size of the buffer (in bytes) used for performing in-memory sorts.
The minimum value is
32768
(32 KB) and the maximum value is16777216
(16 MB). The default value is262144
(256 KB).For more information, see the MySQL documentation
. -
Sql mode
All interfaces
MySQL cluster SQL modes:
-
ALLOW_INVALID_DATES
: Do not perform full check of dates. In this mode, the server checks that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. Invalid dates like2004-04-31
are converted to0000-00-00
and a warning is returned. -
ANSI_QUOTES
: Treat"
as an identifier quote character rather than a string quote character. With this mode enabled, use single quotes ('
) rather than double quotes ("
) for strings. -
ERROR_FOR_DIVISION_BY_ZERO
: Division by zero returnsNULL
and a warning. This SQL mode is deprecated. -
HIGH_NOT_PRECEDENCE
: Priority of theNOT
operator is higher when parsing Boolean expressions. With this mode enabled, an expression likeNOT a BETWEEN b AND c
is parsed as(NOT a) BETWEEN b AND c
instead ofNOT (a BETWEEN b AND c)
. -
IGNORE_SPACE
: Allows spaces between a function name and the opening bracket(
. As a result, built-in function names are treated as keywords. Identifiers that are the same as function names must be quoted. -
NO_AUTO_VALUE_ON_ZERO
: OnlyNULL
inserted into a column with theAUTO_INCREMENT
attribute generates the next sequence number for the column. Normally, new sequence numbers are generated when inserting either0
orNULL
into it. So this mode can be useful if you need to explicitly store0
in this column. -
NO_BACKSLASH_ESCAPES
: Disables the use of backslash (\
) as an escape character. With this mode enabled, backslash is treated as an ordinary character. -
NO_DIR_IN_CREATE
: When creating a table, ignore allINDEX DIRECTORY
andDATA DIRECTORY
directives. -
NO_ENGINE_SUBSTITUTION
: Do not use the default storage engine automatically and return an error if theCREATE TABLE
orALTER TABLE
statement specifies an engine that is unavailable. -
NO_UNSIGNED_SUBTRACTION
: Negative result is allowed when using subtraction between integer values, one of which is unsigned. -
NO_ZERO_DATE
: Determines the use of the0000-00-00
date:- If strict SQL mode is disabled,
0000-00-00
is permitted and inserting it produces a warning. - If strict SQL mode is enabled,
0000-00-00
is not permitted. If you try to insert it, an error occurs.
This SQL mode is deprecated.
- If strict SQL mode is disabled,
-
NO_ZERO_IN_DATE
: Determines the use of dates in which the month or day is zero:- If strict SQL mode is disabled, dates with zero parts are inserted as
0000-00-00
with a warning returned. - If strict SQL mode is enabled, dates with zero parts are not permitted. If you try to insert them, an error occurs.
This SQL mode is deprecated.
See also: NO_ZERO_DATE.
- If strict SQL mode is disabled, dates with zero parts are inserted as
-
ONLY_FULL_GROUP_BY
: Reject queries in whichSELECT
,HAVING
, orORDER BY
refer to non-aggregated columns that are not named in theGROUP BY
clause (SQL-92 style behavior). -
PAD_CHAR_TO_FULL_LENGTH
:CHAR
column values are padded with spaces to their full length. This does not apply toVARCHAR
columns. -
PIPES_AS_CONCAT
: Treat||
as a string concatenation operator (same as CONCAT() rather than a synonym for OR . -
REAL_AS_FLOAT
: TreatREAL
as a synonym forFLOAT
(by default, MySQL treatsREAL
as a synonym forDOUBLE
). -
STRICT_ALL_TABLES
: Enable strict SQL mode for all storage engines. -
STRICT_TRANS_TABLES
: Enable strict SQL mode for all transactional storage engines and, when possible, for non-transactional storage engines. -
TIME_TRUNCATE_FRACTIONAL
: Enables truncation of a fractional part when inserting aTIME
,DATE
, orTIMESTAMP
value into a column that has fewer fractional digits (by default, MySQL uses rounding rather than truncation). -
ANSI
: Combination of the following modes:REAL_AS_FLOAT
.PIPES_AS_CONCAT
.ANSI_QUOTES
.IGNORE_SPACE
.ONLY_FULL_GROUP_BY
.
-
TRADITIONAL
: Combination of the following modes:STRICT_ALL_TABLES
.STRICT_TRANS_TABLES
.NO_ZERO_DATE
.NO_ZERO_IN_DATE
.ERROR_FOR_DIVISION_BY_ZERO
.NO_ENGINE_SUBSTITUTION
.
You can select multiple modes from the list or completely disable all SQL Mode settings.
By default, the following set of SQL modes is used:
ERROR_FOR_DIVISION_BY_ZERO
.NO_ENGINE_SUBSTITUTION
.NO_ZERO_DATE
.NO_ZERO_IN_DATE
.ONLY_FULL_GROUP_BY
;STRICT_TRANS_TABLES
.
For more information, see the MySQL documentation
. -
-
Sync binlog
All interfaces
Determines how often the binary log is synchronized to disk:
0
: Synchronization is disabled. The MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This method provides the best performance. However, data might be lost in case of power failure or operating system crash, as transactions might be committed before they are synced to the binary log.1
: The binary log is synced to disk before transactions are committed. This is the safest method, but it may have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are in a prepared state. This lets you perform automatic recovery and roll back the transactions, which guarantees that no transaction is lost from the binary log.N
: The binary log is synchronized to disk after collectingN
commit groups for the binary log. In case power fails or the operating system crashes, transactions might be committed before they are synced to the binary log. This method may have a negative impact on performance due to the increased number of disk writes. The higher theN
value, the better the performance and the higher the risk of data loss.
The minimum value is
0
and the maximum value is4096
. The default value is1
.For more information, see the MySQL documentation
. -
Table definition cache
All interfaces
The number of table definitions that can be stored in the definition cache. If your database has a large number of tables, increase this value to speed up opening of tables.
The minimum value is
400
and the maximum value is524288
. The default value is2000
.For more information, see the MySQL documentation
. -
Table open cache
All interfaces
The size of the open tables cache for all threads. If the value of Opened tables
is large and you do not use FLUSH_TABLES often, increase the value of this setting.Increasing it requires increasing the number of file descriptors for the MySQL cluster hosts.
The minimum value is
400
and the maximum value is524288
. The default value is4000
.For more information, see the MySQL documentation
. -
Table open cache instances
All interfaces
To improve scalability, the open tables cache can be partitioned into smaller segments. This value sets the number of such cache instances.
The minimum value is
1
and the maximum value is32
. The default value is16
.For more information, see the MySQL documentation
. -
Thread cache size
All interfaces
The number of threads that are cached to handle new connections. When establishing a new connection, threads from the cache are reused first and only then new threads are created. Increase this value to improve performance if you have a lot of new connections.
The minimum value is
10
and the maximum value is10000
. The default value depends on the selected host class and is determined by the formula:max_connections / 10
Where
wmax_connections
is the default value of the Max connections setting for the selected host class.For more information, see the MySQL documentation
. -
Thread stack
All interfaces
The stack size (in bytes) for each thread. The default value is large enough for normal operation of the MySQL server. If the thread stack size is too small, it limits the complexity of SQL statements, the recursion depth of stored procedures, and other memory-consuming actions.
The minimum value is
131072
(128 KB) and the maximum value is16777216
(16 MB). The default value is196608
(192 KB).For more information, see the MySQL documentation
. -
Tmp table size
All interfaces
The maximum size of in-memory temporary tables (in bytes). If a table exceeds this limit, it is converted to an on-disk temporary table. This setting does not affect user-created MEMORY tables. Increase this value if you run many advanced
GROUP BY
queries and your cluster hosts have enough RAM.The minimum value is
1024
(1 KB) and the maximum value is134217728
(128 MB). The default value is16777216
(16 MB).For more information, see the MySQL documentation
. -
Transaction isolation
All interfaces
The default transaction isolation level:
READ-COMMITTED
: A query only sees the strings that were committed before it is run.REPEATABLE-READ
: All queries in the current transaction only see the strings that were committed before the first query to select and update data that was executed in this transaction.SERIALIZABLE
: Same asREPEATABLE-READ
, except that InnoDB implicitly convertsSELECT
statements toSELECT ... FOR SHARE
, if autocommit is off. If autocommit is on, aSELECT
is executed in its own transaction inread only
mode and can be serialized.
For more information, see the MySQL documentation
. -
Wait timeout
All interfaces
The period of time (in seconds) for which the server waits for activity during a non-interactive connection before terminating it.
The minimum value is
600
(10 minutes) and the maximum value is86400
(24 hours). The default value is28800
(8 hours).For more information, see the MySQL documentation
.
Custom settings
These settings apply at the individual user level.
-
Authentication plugin
Management console
CLI
Terraform
Authentication plugin that defines user authentication policy.
By default, it is not specified, and the
Default authentication plugin
option you select when creating a cluster applies.To learn more about authentication plugins, see the MySQL documentation
. -
Administrative privileges
Management console
CLI
Terraform
Administrative privileges are user permissions that apply at the level of the entire DB cluster.
You can grant the following administrative privileges to users:
-
REPLICATION CLIENT
: Enables use of statements:- SHOW MASTER STATUS
: Provides information about the status of the master's binary logs. - SHOW REPLICA | SLAVE STATUS
: Provides information about the status of the the replication thread basic parameters. - SHOW BINARY LOGS
: Displays a list of binary logs on the cluster host.
- SHOW MASTER STATUS
-
REPLICATION SLAVE
: Enables connections to the replication thread from outside Yandex Cloud. Enables use of statements:- SHOW REPLICAS | SHOW SLAVE HOSTS
: Displays a list of currently registered replicas. - SHOW RELAYLOG EVENTS
: Shows events in a replica relay log. - SHOW BINLOG EVENTS
: Shows events in a binary log.
- SHOW REPLICAS | SHOW SLAVE HOSTS
-
PROCESS
: Allows to use theSHOW PROCESSLIST
operator and view the status of data storage systems, e.g.,SHOW ENGINE INNODB STATUS
. In Managed Service for MySQL, this privilege also grants permission to read mysql , performance_schema , and sys system database tables.
By default, this setting is not specified, which means the user has no administrative privileges. You can also grant multiple privileges to users.
-
-
Connection limits
Management console
CLI
Terraform
This section configures user restrictions.
-
Max connections per hour
The maximum number of connections per hour allowed for the user.
The minimum value is 0 (no limit).
By default, this setting is not specified, which means there is no limit. -
Max questions per hour
The maximum number of requests per hour allowed for the user (except for queries executed within stored procedures).
The minimum value is 0 (no limit).
By default, this setting is not specified, which means there is no limit. -
Max updates per hour
The maximum number of
UPDATE
requests per hour allowed for the user.The minimum value is 0 (no limit).
By default, this setting is not specified, which means there is no limit. -
Max user connections
The maximum number of simultaneous connections allowed for the user.
The minimum value is 0 (no limit).
By default, this setting is not specified and the max_user_connections global cluster setting applies.
For more information, see the MySQL documentation
. -
For information about managing user permissions to access DB objects, see User permissions.