Skip to main content

MySQL sync settings

Last update:

The MySQL sync settings affect the health and performance of the database cluster.When the cluster is created, default values are set in the settings.The default values are chosen to ensure high cluster performance and vary depending on the cluster configuration and the version of MySQL sync.

You can:

  • see the default values for the settings and change them when creating the cluster;
  • or see the settings and change them in an already created cluster.

We recommend that you change the settings only when necessary — incorrect values may render the cluster inoperable or degrade its performance.

When scaling the cluster, some settings are automatically changed to default values.For a list of such settings, see Automatically changing settings when scaling a cluster.

View settings

You can view a list of settings available for modification when creating a cluster or changing settings.

If you have changed settings, you can separately view a list of settings with values different from the default values.

A detailed description of all settings can be found in The MySQL Server section of the MySQL documentation.

  1. In the Dashboard, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS Settings block, click Edit.
  5. Optional: to see only settings where you have changed the default value, check the Show changed settings checkbox.
  6. Take a look at the settings.

Change settings

carefully

We recommend that you change the settings only when necessary — incorrect values may render the cluster inoperable or degrade its performance.

After changing some settings, the cluster will automatically reboot and will be unavailable during the reboot.The reboot will start immediately after saving the changes.The list of such settings can be found in the subsection List of settings that require a reboot.

  1. In the Dashboard, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS Settings block, click Edit.
  5. Indicate the new values.
  6. Click Save.

List of settings that require a reboot

  • default_time_zone;
  • innodb_adaptive_hash_index_parts;
  • innodb_autoinc_lock_mode;
  • innodb_buffer_pool_instances;
  • innodb_buffer_pool_pool_load_at_startup;
  • innodb_commit_concurrency;
  • innodb_flush_method;
  • innodb_ft_cache_size;
  • innodb_ft_max_token_size;
  • innodb_ft_min_token_size;
  • innodb_ft_total_cache_size;
  • innodb_ft_sort_pll_degree;
  • innodb_log_buffer_size (only for MySQL semi-sync 5.7);
  • innodb_log_file_size;
  • innodb_page_cleaners;
  • innodb_purge_threads;
  • innodb_read_io_threads;
  • innodb_rollback_on_timeout;
  • innodb_sort_buffer_size;
  • innodb_sync_array_size;
  • innodb_write_io_threads;
  • max_digest_length;
  • table_open_cache_instances;
  • thread_pool_size;
  • thread_stack.

Automatic change of settings when scaling the cluster

DBMS settings have valid value boundaries.If during cluster scaling the settings values go beyond the valid value boundaries, such values are automatically replaced with default values so that the cluster can work.

When the cluster is scaled and goes to ACTIVE status, you can change the settings and set new values.

A list of settings whose values change to default values when the cluster is scaled:

  • innodb_buffer_pool_instances;
  • innodb_buffer_pool_size;
  • innodb_log_file_size (only for MySQL semi-sync 5.7);
  • max_connections;
  • max_heap_table_size;
  • temptable_max_ram;
  • thread_pool_size;
  • tmp_table_size.

Encoding and sorting algorithm settings

The encoding (CHARACTER SET or CHARSET) and the sorting algorithm (COLLATION) for the DBMS are defined in the loose_character_set_server and collation_server settings.The default values differ depending on the DBMS version.

MySQL sync 5.7MySQL sync 8
loose_character_set_serverlatin1utf8mb4
collation_serverlatin1_swedish_ciutf8mb4_0900_ai_ci

You can change the settings when you create a cluster or change the settings in a cluster that has already been created.The encoding must be compatible with the sorting algorithm.You can view compatible values by using the mysql command > SHOW COLLATION WHERE Charset = '<charset>'; where <charset> is the encoding for which you are looking at compatible sorting algorithms.

For more information about the settings and their compatibility, see the Character Sets and Collations in MySQL article in the MySQL documentation.