Skip to main content

MySQL sync settings

Last update:

The MySQL sync settings affect the performance of the database cluster. When you create a MySQL sync cloud database cluster, the values for all settings are set by default. You can review and change them. We recommend that you change the values of the settings only when necessary — improperly selected values can degrade the performance of the cluster. The default values are chosen to ensure high cluster performance and vary depending on the cluster configuration and version of MySQL sync.

In an existing cluster, you can view or change settings.

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

View settings

You can view cluster settings when you create a cluster or change settings in an already created cluster.

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

  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. Look through the settings. Description of settings can be found in The MySQL Server subsection of MySQL documentation.

Change settings

For your information

We recommend that you change the settings only when necessary — incorrect values can degrade cluster performance.

Changing some parameters in the settings entails rebooting the databases in the cluster — the cluster may be unavailable during this time. You can see a list of them.

  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. Specify new values. Description of settings can be found in The MySQL Server subsection of MySQL documentation.
  6. Click Save.

List of settings that require a reboot

  • 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 (except MySQL sync 8);
  • 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

Any DBMS parameter has limits of acceptable values. If when scaling the cluster (changing the configuration), the setting values exceed the limits, such values are automatically replaced with the default values so that the cluster can work.

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

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

  • innodb_buffer_pool_size;
  • innodb_log_file_file_size (except MySQL sync 8);
  • innodb_buffer_pool_instances;
  • thread_pool_size;
  • max_heap_table_size;
  • tmp_table_size;
  • temptable_max_ram.

Encoding and sorting algorithm settings

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

props.DatabaseName 5 .7props.DatabaseName 8
loose_character_set_serverlatin1utf8mb4
collation_serverlatin1_swedish_ciutf8mb4_0900_ai_ci

You can change the settings when you create a cluster or change a setting in an already created cluster. The charset must be compatible with the sorting algorithm. You can view compatible values 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.