Skip to main content

MySQL semi-sync settings

The MySQL semi-sync settings affect the stability and performance of the database cluster. When creating a cluster, default values are applied to the settings. These defaults are optimized for high performance and vary depending on the cluster configuration and the version of MySQL semi-sync.

You can:

  • view default settings and change them when creating a cluster;
  • or view settings and change them in an already created cluster.

We recommend changing setting values only when necessary—incorrect values can make the cluster inoperable or reduce its performance.

When scaling a cluster, the values of some settings are automatically replaced with defaults. You can view a list of such settings in the Automatic settings changes when scaling a cluster subsection.

View settings

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

If you have changed the settings, you can separately view a list of settings whose values differ from the defaults.

Detailed descriptions of all settings can be found in the The MySQL Server section of the MySQL documentation.

  1. In the Dashboard, on the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS settings block, click Change.
  5. Optional: to see only the settings for which you have changed the default value, select the Show modified parameters checkbox.
  6. View settings.

Change settings

carefully

We recommend changing setting values only when necessary—incorrect values can make the cluster inoperable or reduce its performance.

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

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

List of settings that require a restart

  • default_time_zone;
  • innodb_adaptive_hash_index_parts;
  • innodb_autoinc_lock_mode;
  • innodb_buffer_pool_instances;
  • innodb_buffer_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 (for MySQL semi-sync 5.7 only);
  • innodb_log_file_size (for MySQL semi-sync 5.7 only);
  • 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 setting changes when scaling a cluster

DBMS settings have valid value ranges. If, during cluster scaling, setting values go beyond the allowed range, these values are automatically replaced with default values to keep the cluster operational.

After the cluster has been scaled and its status becomes ACTIVE, you will be able to change settings and set new values.

List of settings that are reset to their default values when scaling a cluster:

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

Character set and collation settings

The character set (CHARACTER SET) and collation (COLLATION) for the DBMS are defined in the loose_character_set_server and collation_server settings. Default values vary depending on the DBMS version.

MySQL semi-sync 5.7MySQL semi-sync 8.0
loose_character_set_serverlatin1utf8mb4
collation_serverlatin1_swedish_ciutf8mb4_0900_ai_ci

You can change the settings when creating a cluster or change the settings in an existing cluster. The character set must be compatible with the collation. To view compatible values, run the command mysql> SHOW COLLATION WHERE Charset = '<charset>';, where <charset> is the character set for which you are checking compatible collations.

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