Advanced parameters for Public Cloud Databases for MySQL

View as Markdown

This guide lists all the supported advanced parameters that allow you to configure your Public Cloud Databases for MySQL according to your use cases

Objective

This guide lists all the supported advanced parameters that allow you to configure your Public Cloud Databases for MySQL according to your use cases.

Requirements

A Public Cloud project in your OVHcloud account.
A database running on your OVHcloud Public Cloud Databases (this guide can help you to meet this requirement).

Instructions

Info

Advanced parameters can also be managed with Terraform, through the advanced_configuration argument (key/value pairs) of the ovh_cloud_project_database resource.

Using the OVHcloud Control Panel

Please refer to this guide to find out how to change your advanced parameters from the OVHcloud Control Panel.

Using API

Please refer to this guide to find out how to change your advanced parameters from the OVHcloud API.

Advanced parameters for MySQL

Below you can find the advanced parameters available for a MySQL service:

Info

The table below lists the advanced parameters available for a MySQL service. Your service shows the exact list, with the allowed values, in the Configuration tab of the Control Panel and through the API.

mysql.lower_case_table_names can only be set when the service is created.

Parameters
binlog_retention_period `long` min: `600` max: `9007199254740991` Warning: reducing this value can make a large batch of binary logs eligible for purge at once. Depending on the volume, this can sometimes stall the MySQL commit path and block writes until the purge completes. To stay on the safe side, prefer lowering the value gradually in small decrements during a low-traffic window rather than dropping it drastically in one step.
mysql.automatic_sp_privileges `boolean` When enabled, the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine and revokes them when the routine is dropped.
mysql.connect_timeout `long` min: `2` max: `3600` The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
mysql.default_time_zone `string` Default server time zone as an offset from UTC (from -12:00 to +12:00), a time zone name, or 'SYSTEM' to use the MySQL server default.
mysql.div_precision_increment `long` min: `0` max: `30` Number of digits by which to increase the scale of the result of division operations performed with the `/` operator. Default is 4.
mysql.end_markers_in_json `boolean` Whether optimizer JSON output such as EXPLAIN FORMAT=JSON adds end markers that repeat a structure's key near its closing bracket, making large JSON structures easier to read.
mysql.eq_range_index_dive_limit `long` min: `0` max: `4294967295` The number of equality ranges in a query at or above which the optimizer switches from index dives to index statistics when estimating the number of qualifying rows. 0 means always use index dives. Default is 200.
mysql.group_concat_max_len `long` min: `4` max: `18446744073709552000` The maximum permitted result length in bytes for the GROUP_CONCAT() function.
mysql.information_schema_stats_expiry `long` min: `900` max: `31536000` The time, in seconds, before cached statistics expire.
mysql.innodb_adaptive_hash_index `boolean` Whether InnoDB adaptive hash indexing is enabled. The optimal setting is workload-dependent: the adaptive hash index speeds up lookups for some workloads but its internal latch can become a contention point under high concurrency, in which case disabling it can improve throughput.
mysql.innodb_change_buffer_max_size `long` min: `0` max: `50` Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. Default is 25.
mysql.innodb_flush_neighbors `long` min: `0` max: `2` Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent (default is 1): 0 - dirty pages in the same extent are not flushed, 1 - flush contiguous dirty pages in the same extent, 2 - flush dirty pages in the same extent.
mysql.innodb_ft_enable_stopword `boolean` Whether stopword processing is applied when creating or rebuilding an InnoDB FULLTEXT index. Enabled by default.
mysql.innodb_ft_max_token_size `long` min: `10` max: `84` Maximum length of words that are stored in an InnoDB FULLTEXT index.
mysql.innodb_ft_min_token_size `long` min: `0` max: `16` Minimum length of words that are stored in an InnoDB FULLTEXT index. Changing this parameter will lead to a restart of the MySQL service.
mysql.innodb_ft_num_word_optimize `long` min: `1000` max: `10000` Number of words processed during each OPTIMIZE TABLE operation on an InnoDB FULLTEXT index. Default is 2000.
mysql.innodb_ft_result_cache_limit `long` min: `1000000` max: `4294967295` Maximum memory in bytes used per query for the InnoDB FULLTEXT search query result cache. Sized automatically based on the service plan's memory; setting a value overrides the calculated default.
mysql.innodb_ft_server_stopword_table `string` This option is used to specify your own InnoDB FULLTEXT index stopword list for all InnoDB tables.
mysql.innodb_ft_user_stopword_table `string` This option is used to specify your own InnoDB FULLTEXT index stopword list for specific InnoDB tables.
mysql.innodb_io_capacity `long` min: `100` max: `4294967295` The number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. Set this to a value appropriate for the underlying storage; it must not exceed innodb_io_capacity_max.
mysql.innodb_io_capacity_max `long` min: `100` max: `4294967295` The maximum number of I/O operations per second (IOPS) that InnoDB background tasks may perform when flushing falls behind. Defaults to twice innodb_io_capacity (minimum 2000). This must be greater than or equal to innodb_io_capacity.
mysql.innodb_lock_wait_timeout `long` min: `1` max: `3600` The length of time in seconds an InnoDB transaction waits for a row lock before giving up. Default is 120.
mysql.innodb_log_buffer_size `long` min: `1048576` max: `4294967296` The size in bytes of the buffer that InnoDB uses to write to the log files on disk. Requests above 15% of the RAM provided by your service plan are rejected, because a larger buffer leaves less memory for the buffer pool and client connections.
mysql.innodb_max_dirty_pages_pct `double` min: `5` max: `99` Percentage of dirty pages in the InnoDB buffer pool at which InnoDB starts flushing aggressively. Lowering it spreads write I/O more evenly and shortens crash recovery, at the cost of flushing pages that might have been modified again. Must stay at or above innodb_max_dirty_pages_pct_lwm. Default is 90.
mysql.innodb_max_dirty_pages_pct_lwm `double` min: `0` max: `99` Percentage of dirty pages in the InnoDB buffer pool at which InnoDB starts pre-flushing, to keep the dirty page ratio from reaching innodb_max_dirty_pages_pct. 0 turns pre-flushing off. Cannot be set above innodb_max_dirty_pages_pct. Default is 10.
mysql.innodb_max_purge_lag `long` min: `0` max: `4294967295` Number of outstanding transactions by which the InnoDB purge operation may fall behind before INSERT, UPDATE and DELETE are delayed to let it catch up. The delay applies per row and grows with the lag, and it is capped by innodb_max_purge_lag_delay, which has to be set as well for this option to have any effect. Default is 0, which never delays anything.
mysql.innodb_max_purge_lag_delay `long` min: `0` max: `10000000` Cap in microseconds on the per-row delay innodb_max_purge_lag applies to INSERT, UPDATE and DELETE. MySQL clamps the delay to this value unconditionally, so while this is 0, no delay is ever applied and innodb_max_purge_lag on its own does nothing. Both options must be non-zero for either to take effect. Default is 0.
mysql.innodb_online_alter_log_max_size `long` min: `65536` max: `1099511627776` The upper limit in bytes on the size of the temporary log files used during online DDL operations for InnoDB tables.
mysql.innodb_optimize_fulltext_only `boolean` When enabled, OPTIMIZE TABLE on InnoDB tables only updates the FULLTEXT index instead of rebuilding the table. Intended to be enabled temporarily during FULLTEXT index maintenance and disabled afterwards; while enabled, OPTIMIZE TABLE does not reclaim table space.
mysql.innodb_print_all_deadlocks `boolean` When enabled, information about all deadlocks in InnoDB user transactions is recorded in the error log. Disabled by default.
mysql.innodb_read_io_threads `long` min: `1` max: `64` The number of I/O threads for read operations in InnoDB. Default is 4. Changing this parameter will lead to a restart of the MySQL service.
mysql.innodb_rollback_on_timeout `boolean` When enabled a transaction timeout causes InnoDB to abort and roll back the entire transaction. Changing this parameter will lead to a restart of the MySQL service.
mysql.innodb_thread_concurrency `long` min: `0` max: `1000` Defines the maximum number of threads permitted inside of InnoDB. Default is 0 (infinite concurrency - no limit).
mysql.innodb_write_io_threads `long` min: `1` max: `64` The number of I/O threads for write operations in InnoDB. Default is 4. Changing this parameter will lead to a restart of the MySQL service.
mysql.interactive_timeout `long` min: `30` max: `604800` The number of seconds the server waits for activity on an interactive connection before closing it.
mysql.internal_tmp_mem_storage_engine `string` values: `MEMORY`, `TempTable` The storage engine for in-memory internal temporary tables.
mysql.log_output `string` values: `NONE`, `TABLE` The slow query log output destination when slow_query_log is ON. To store slow queries in the mysql.slow_log table, choose `TABLE`. To silence slow logs, choose `NONE`.
mysql.long_query_time `double` min: `0` max: `3600` The slow_query_logs work as SQL statements that take more than long_query_time seconds to execute.
mysql.lower_case_table_names `long` values: `0`, `1` Sets how table and database names are stored and compared. 0 = case-sensitive (default), 1 = names stored lowercase, comparisons are case-insensitive. This option can only be set when creating the service and cannot be changed later. See the MySQL documentation on identifier case sensitivity for details.
mysql.max_allowed_packet `long` min: `102400` max: `1073741824` Size of the largest message in bytes that can be received by the server. Default is 67108864 (64M). Statements and rows larger than this are rejected with a "packet too large" error.
mysql.max_connections `long` min: `30` max: `100000` The maximum permitted number of simultaneous client connections. Lower this to reserve memory for other work. The value cannot exceed the limit set by your service plan. Upgrading the plan does not raise a value you have set explicitly, so increase it yourself after an upgrade.
mysql.max_execution_time `long` min: `0` max: `4294967295` Execution timeout in milliseconds for read-only top-level SELECT statements. 0 (the default) means no timeout.
mysql.max_heap_table_size `long` min: `1048576` max: `1073741824` Limits the size of internal in-memory tables. Also set tmp_table_size. Default is 16777216 (16M).
mysql.max_seeks_for_key `long` min: `1` max: `18446744073709552000` Limit on the assumed maximum number of index seeks when looking up rows based on a key. Lowering this value causes the optimizer to prefer index lookups over table scans.
mysql.max_user_connections `long` min: `0` max: `99990` The maximum number of simultaneous connections permitted to any single user account. 0, the default, means no per-account limit. Any other value must be at least 10 below max_connections, so that monitoring and your own admin sessions can still connect when an application saturates its own limit. The service's own replication and management connections are not affected, however low you set it.
mysql.net_buffer_length `long` min: `1024` max: `1048576` Start sizes of connection buffer and result buffer. Default is 16384 (16K). Changing this parameter will lead to a restart of the MySQL service.
mysql.net_read_timeout `long` min: `1` max: `3600` The number of seconds to wait for more data from a connection before aborting the read.
mysql.net_write_timeout `long` min: `1` max: `3600` The number of seconds to wait for a block to be written to a connection before aborting the write.
mysql.optimizer_prune_level `long` min: `0` max: `1` Controls the heuristics applied during query optimisation to prune less-promising partial plans from the optimizer search space. 0 disables heuristics (exhaustive search); 1 prunes plans based on the number of rows retrieved.
mysql.optimizer_search_depth `long` min: `0` max: `62` Maximum depth of search performed by the query optimizer when choosing a join order. Larger values produce better plans for joins over many tables but take longer to compile; 0 lets the optimizer choose the depth automatically.
mysql.optimizer_switch `string` Comma-separated list of optimizer flag assignments in the form flag=on\|off\|default, or the single value `default` to reset all flags. Flags not listed keep their current values. Controls query optimizer behaviours such as index merge, hash join and semijoin strategies.
mysql.performance_schema_events_statements_history_size `long` min: `0` max: `1024` The number of rows per thread in the events_statements_history table.
mysql.relay_log_space_limit `long` min: `134217728` max: `18446744073709552000` The maximum amount of space in bytes to use for all relay logs while replicating from an external migration source. When the limit is reached, the replication I/O thread stops fetching relay log events until the SQL thread has caught up. Raise this to give a large migration a bigger relay-log budget; ensure the service disk is sized accordingly. The setting applies only on the node replicating from the external source; standby nodes always use the default (the smaller of 5 GiB and 30% of the service disk), which is also used when this option is left unset.
mysql.slow_query_log `boolean` Slow query log enables capturing of slow queries. Setting slow_query_log to false also truncates the mysql.slow_log table.
mysql.sort_buffer_size `long` min: `32768` max: `1073741824` Sort buffer size in bytes for ORDER BY optimisation. Default is 262144 (256K). Requests above 2% of the RAM provided by your service plan are rejected, because the buffer is allocated per session and its cost multiplies with the connection count.
mysql.sql_mode `string` Global SQL mode. Set it to an empty value to use the MySQL server defaults. If you do not set this field when creating a new service, a default SQL mode (strict, SQL standard compliant) is assigned.
mysql.sql_require_primary_key `boolean` Requires a primary key to be defined for new tables or old tables modified with ALTER TABLE, and fails if it is missing. It is recommended to always have primary keys because various features may break if any large table is missing them.
mysql.tmp_table_size `long` min: `1048576` max: `1073741824` Limits the size of internal in-memory tables. Also set max_heap_table_size. Default is 16777216 (16M).
mysql.wait_timeout `long` min: `1` max: `2147483` The number of seconds the server waits for activity on a non-interactive connection before closing it. Requests to set this below 30 are rejected, because a shorter timeout would close your own idle connections between statements.
mysql.windowing_use_high_precision `boolean` Whether window functions are computed to high precision. Disabling this trades exactness for speed in window function evaluation.

Go further

Visit our dedicated Discord channel: https://discord.gg/ovhcloud. Ask questions, provide feedback and interact directly with the team that builds our databases services.

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

Join our community of users.

Was this page helpful?