Configuring database flags

This page describes how to configure database flags for Cloud SQL, and lists the flags that you can set for your instance. You use database flags for many operations, including adjusting MySQL parameters, adjusting options, and configuring and tuning an instance.

In some cases, setting one flag may require that you set another flag to fully enable the desired functionality. For example, to enable slow query logging, you must set both the slow_query_log flag to on and the log_output flag to FILE to make your logs available using the Google Cloud Console Logs Viewer.

When you set, remove, or modify a flag for a database instance, the database might be restarted. The flag value is then persisted for the instance until you remove it. If the instance is the source of a replica, and the instance is restarted, the replica is also restarted to align with the current configuration of the instance.

Configuring database flags

Setting a database flag

Console

  1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Scroll down to the Flags section.
  4. To set a flag that has not been set on the instance before, click Add item, choose the flag from the drop-down menu, and set its value.
  5. Click Save to save your changes.
  6. Confirm your changes under Flags on the Overview page.

gcloud

Edit the instance:

gcloud sql instances patch [INSTANCE_NAME] --database-flags [FLAG1=VALUE1,FLAG2=VALUE2]

This command will overwrite all database flags previously set. To keep those and add new ones, include the values for all flags you want set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").

For example, to set the general_log, skip_show_database, and wait_timeout flags, you can use the following command:

gcloud sql instances patch [INSTANCE_NAME] --database-flags \
    general_log=on,skip_show_database=on,wait_timeout=200000

REST v1

To set a flag for an existing database:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": "flag_name",
        "value": "flag_value"
      }
    ]
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

For example, to set the general_log flag for an existing database use:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": "general_log",
        "value": "on"
      }
    ]
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

If there are existing flags configured for the database, modify the previous command to include them. The PATCH command overwrites the existing flags with the ones specified in the request.

REST v1beta4

To set a flag for an existing database:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": "flag_name",
        "value": "flag_value"
      }
    ]
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

For example, to set the general_log flag for an existing database use:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": "general_log",
        "value": "on"
      }
    ]
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

If there are existing flags configured for the database, modify the previous command to include them. The PATCH command overwrites the existing flags with the ones specified in the request.

Clearing all flags to their default value

Console

  1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Open the Database flags section.
  4. Click the X next to all of the flags shown.
  5. Click Save to save your changes.

gcloud

Clear all flags to their default values on an instance:

gcloud sql instances patch INSTANCE_NAME \
--clear-database-flags

You are prompted to confirm that the instance will be restarted.

REST v1

To clear all flags for an existing instance:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "databaseFlags": []
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

To clear all flags for an existing instance:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "databaseFlags": []
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Viewing current values of database flags

To view all current values of the MySQL system variables, log into your instance with the mysql client and enter the following statement:

 SHOW VARIABLES;

Note that you can change the value only for supported flags (as listed below).

Determining what database flags have been set for an instance

To see what flags have been set for a Cloud SQL instance:

Console

  1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
  2. Select the instance to open its Instance Overview page.

    The database flags that have been set are listed under the Database flags section.

gcloud

Get the instance state:

gcloud sql instances describe INSTANCE_NAME

In the output, database flags are listed under the settings as the collection databaseFlags. For more information about the representation of the flags in the output, see Instances Resource Representation.

REST v1

To list flags configured for an instance:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

In the output, look for the databaseFlags field.

REST v1beta4

To list flags configured for an instance:

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

In the output, look for the databaseFlags field.

Flags managed by Cloud SQL

Cloud SQL adjusts certain system flags depending on the instance machine type. You cannot adjust these values.

innodb_buffer_pool_instances
  • 1 for db-f1-micro and db-g1-small.
  • 1 if RAM < 7.5 GB.
  • 2 if 7.5 GB <= RAM < 13 GB.
  • 4 if 13 GB <= RAM < 26 GB.
  • 8 if RAM >= 26 GB.

Supported flags

The flags supported in Cloud SQL are the most commonly requested flags for MySQL. Flags not mentioned below are not supported.

For a given flag, Cloud SQL might support a different range from the corresponding MySQL parameter or option.

The flags apply to all versions of MySQL supported by Cloud SQL except where noted.

A | B | C | D | E | F | G | I | L | M | N | O | P | Q | R | S | T | U | W

Cloud SQL Flag Type
Acceptable Values and Notes		 Restart
Required?
activate_all_roles_on_login boolean
on | off
default: off
No
auto_increment_increment integer
1 ... 65535		 No
auto_increment_offset integer
1 ... 65535		 No
binlog_cache_size integer
4096 ... 9223372036854775807		 No
binlog_expire_log_seconds integer
0 or 86400 (1 day) ... 4294967295 (max value)
Default is 2592000, which equals 30 days.

See the Tips section for more information about this flag.

 No
binlog_group_commit_sync_delay 0 ... 1000000

Supported in MySQL 5.7, 8.0

Default is 0.

 No
binlog_group_commit_sync_no_delay_count 0 ... 1000000

Supported in MySQL 5.7, 8.0

Default is 0.

 No
binlog_row_image enumeration
full (default), minimal, or noblob		 No
binlog_stmt_cache_size 4096 ... 9223372036854775807 No
binlog_transaction_dependency_history_size integer

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
binlog_transaction_dependency_tracking enumeration

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
character_set_server string
utf8 or utf8mb4 (recommended)		 Yes
default_password_lifetime integer 0...65535
default: 0
No
default_time_zone string
Specify values as time zone offsets, from -12:59 to +13:00. Leading zeros required.

For example, London is in the UTC time zone, which is +00:00 in the supported values for this flag. Named areas, such as 'Europe/London', are not supported by Cloud SQL.

 Yes
disconnect_on_expired_password boolean on | off
default: on
Yes
eq_range_index_dive_limit integer
0 ... 2147483647		 No
event_scheduler boolean
on | off

If you are using the Event Scheduler, configure your instance with an activation policy of ALWAYS to ensure that scheduled events run.

 No
expire_log_days integer
0 ... 99
Default is 0, which means no automatic removal.

See the Tips section for more information about this flag.

 No
explicit_defaults_for_timestamp boolean
on | off		 Yes
ft_max_word_len integer
10 ... 252		 Yes
ft_min_word_len integer
1 ... 16		 Yes
ft_query_expansion_limit integer
0 ... 1000		 Yes
general_log boolean
on | off

See the Tips section for more information about general logs.

 No
generated_random_password_length integer 5-255
default: 20
No
group_concat_max_len integer
4 ... 17179869184		 No
init_connect string No
innodb_adaptive_hash_index boolean
on | off		 No
innodb_adaptive_hash_index_parts integer
1 ... 512		 Yes
innodb_adaptive_max_sleep_delay integer
0 ... 1000000		 No
innodb_autoinc_lock_mode integer
0 ... 2		 Yes
innodb_buffer_pool_size integer

Setting this flag for MySQL 5.6 requires a restart. See the Tips section for more information about this flag.

No
innodb_change_buffer_max_size integer
0 ... 50		 No
innodb_concurrency_tickets integer
1 ... 4294967295		 No
innodb_file_per_table boolean
on | off

See the Tips section for more information about this flag.

 No
innodb_fill_factor integer
10 ... 100		 No
innodb_flush_log_at_trx_commit integer
1, 2
Default: 1.

See the Tips section for more information about this flag.

 No
innodb_ft_cache_size integer
1600000 ... 80000000		 Yes
innodb_ft_enable_diag_print boolean
on | off		 No
innodb_ft_enable_stopword boolean
on | off		 No
innodb_ft_max_token_size integer
10 ... 252

Supported only in MySQL 5.6 and later versions.

 Yes
innodb_ft_min_token_size integer
0 ... 16

Supported only in MySQL 5.6 and later versions.

 Yes
innodb_ft_num_word_optimize integer
1000 ... 10000		 No
innodb_ft_result_cache_limit integer
1000000 ... 4294967295		 No
innodb_ft_server_stopword_table string

Supported only in MySQL 5.6 and later versions.

 No
innodb_ft_sort_pll_degree integer
1 ... 32		 Yes
innodb_ft_total_cache_size integer
32000000 ... 1600000000		 Yes
innodb_ft_user_stopword_table string No
innodb_large_prefix boolean
on | off

Supported only in MySQL 5.6.

 No
innodb_lock_wait_timeout integer
1 ... 1073741824		 No
innodb_log_buffer_size integer
262144 ... 4294967295		 Yes
innodb_log_file_size integer
MySQL 5.6: 1048576 ... 274877906944
MySQL 5.7: 4194304 ... 274877906944 		Yes
innodb_lru_scan_depth integer
100 ... 9223372036854775807		 No
innodb_old_blocks_pct integer
5 ... 95		 No
innodb_old_blocks_time integer
0 ... 4294967295		 No
innodb_online_alter_log_max_size integer
65536 ... 9223372036854775807		 No
innodb_optimize_fulltext_only boolean
on | off		 No
innodb_print_all_deadlocks boolean
on | off		 No
innodb_purge_threads 1 ... 32

Supported in MySQL 5.6, 5.7, 8.0

Default is 1.

 Yes
innodb_random_read_ahead boolean
on | off		 No
innodb_read_ahead_threshold integer
0 ... 64		 No
innodb_read_io_threads integer
1 ... 64 		Yes
innodb_replication_delay integer
0 ... 4294967295		 No
innodb_rollback_on_timeout boolean
on | off		 Yes
innodb_rollback_segments integer
1 ... 128		 No
innodb_sort_buffer_size integer
65536 ... 67108864		 Yes
innodb_stats_auto_recalc boolean
on | off		 No
innodb_stats_method enumeration
nulls_equal | nulls_unequal | nulls_ignored		 No
innodb_stats_on_metadata boolean
on | off		 No
innodb_stats_persistent boolean
on | off		 No
innodb_stats_persistent_sample_pages integer
1 ... 9223372036854775807		 No
innodb_stats_sample_pages integer
1 ... 9223372036854775807		 No
innodb_stats_transient_sample_pages integer
1 ... 9223372036854775807		 No
innodb_status_output boolean
on | off		 No
innodb_status_output_locks boolean
on | off		 No
innodb_strict_mode boolean
on | off		 No
innodb_sync_array_size 1 ... 1024

Supported in MySQL 5.6, 5.7, 8.0

Default is 1.

 Yes
innodb_thread_concurrency integer
0 ... 1000		 No
innodb_thread_sleep_delay integer
0 ... 1000000		 No
innodb_write_io_threads integer
1 ... 64		 Yes
interactive_timeout integer
1 ... 31536000		 No
internal_tmp_mem_storage_engine enumeration
MEMORY, TempTable
This flag is used for MySQL 8.0 only.		 No
join_buffer_size integer
128 ... 9223372036854775807		 No
local_infile boolean
on | off		 No
lock_wait_timeout integer
1 ... 31536000		 No
log_bin_trust_function_creators boolean
on | off		 No
log_output set
FILE | TABLE | NONE		 No
log_queries_not_using_indexes boolean
on | off		 No
log_throttle_queries_not_using_indexes integer
0 ... 9223372036854775807		 No
long_query_time float
0 ... 30000000

Cloud SQL provides the ability to set this flag to less than 1 if needed.

If the log_queries_not_using_indexes flag is also enabled, you may see queries with less than the time specified here.

 No
lower_case_table_names integer
0 or 1

In most cases, use the default value of 0, which makes table and database names case sensitive. If you need to change the value of this flag, make sure that you understand how the change will affect your existing tables and databases.

Yes
mandatory_roles string role name
default: empty string
No
max_allowed_packet integer
16384 ... 1073741824

This value must be a multiple of 1024, if sql_mode=TRADITIONAL or sql_mode=STRICT_ALL_TABLES.

 No
max_binlog_size integer
4096 ... 1073741824		 No
max_connections integer
14 ... 262143 		Yes
max_digest_length integer
0 ... 1048576		 Yes
max_execution_time integer
0 ... 9223372036854775807		 No
max_heap_table_size integer
16384 ... 67108864

See the Tips section for more information about this flag.

 No
max_join_size integer
16 ... 9223372036854775807		 No
max_length_for_sort_data integer
4 ... 8388608		 No
max_points_in_geometry integer
3 ... 1048576		 No
max_prepared_stmt_count integer
0 ... 1048576		 No
max_seeks_for_key integer
1 ... 9223372036854775807		 No
max_sort_length integer
4 ... 8388608		 No
max_sp_recursion_depth integer
0 ... 255		 No
max_user_connections integer
0 ... 4294967295		 No
max_write_lock_count integer
1 ... 9223372036854775807		 No
net_read_timeout integer
30 ... 4294967295		 No
net_retry_count integer
10 ... 4294967295		 No
net_write_timeout integer
60 ... 4294967295		 No
optimizer_prune_level integer
0 ... 1		 No
optimizer_search_depth integer
0 ... 62		 No
optimizer_trace_max_mem_size integer
0 ... 9223372036854775807		 No
optimizer_trace_offset integer
-9223372036854775808 ... 9223372036854775807		 No
parser_max_mem_size integer
10000000 ... 9223372036854775807		 No
password_history integer 0-4294967295
default: 0
No
password_require_current boolean on | off
default: off
No
password_reuse_interval integer 0-4294967295
default: 0
No
performance_schema boolean
on | off (default)

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_accounts_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_digests_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_stages_history_long_size integer
-1 ... 1048576		 Yes
performance_schema_events_stages_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_statements_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_statements_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_transactions_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_transactions_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_waits_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_events_waits_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_hosts_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_cond_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_cond_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_digest_length integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_file_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_file_handles integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_file_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_index_stat integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_memory_classes integer
0 ... 1024

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_metadata_locks integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_mutex_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_mutex_instances integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_prepared_statements_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_program_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_rwlock_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_rwlock_instances integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_socket_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_socket_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_sql_text_length integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_stage_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_statement_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_statement_stack integer
1 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_table_handles integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_table_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_table_lock_stat integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_thread_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_max_thread_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_session_connect_attrs_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_setup_actors_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_setup_objects_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
performance_schema_users_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

 Yes
query_alloc_block_size integer
1024 ... 4294967295		 No
query_cache_limit integer
0 ... 223338299392

This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

 No
query_cache_min_res_unit integer
0 ... 9223372036854775807

This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

 No
query_cache_size integer
0 ... 223338299392

This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. See the Tips section for more information about this flag.

 No
query_cache_type enumeration
0 ... 2

This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. See the Tips section for more information about this flag.

 Yes
query_cache_wlock_invalidate boolean
on | off

This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

 No
query_prealloc_size integer
8192 ... 9223372036854775807		 No
range_alloc_block_size integer
4096 ... 4294967295		 No
range_optimizer_max_mem_size integer
0 ... 9223372036854775807		 No
read_buffer_size integer
8192 ... 2147483647		 No
read_only boolean
on | off

Has no effect for replicas.

 No
read_rnd_buffer_size integer
1 ... 2147483647		 No
show_compatibility_56 boolean
on | off

Supported only in MySQL 5.7.

 No
skip_show_database flag
on | off		 Yes
slave_parallel_type enumeration

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
slave_parallel_workers integer

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
slave_preserve_commit_order boolean

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
slave_pending_jobs_size_max integer

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
slow_query_log boolean
on | off

See the Tips section for more information on slow query logs.

 No
sort_buffer_size integer
32768 ... 9223372036854775807		 No
sql_mode string

See the Server SQL Modes in the MySQL documentation for allowed values, including combined modes, such as ANSI. NO_DIR_IN_CREATE is not supported.

Cloud SQL for MySQL doesn't support empty values for the sql_mode flag. Instead of using an empty value, set this flag to the NO_ENGINE_SUBSTITUTION mode.

 No
stored_program_cache integer
16 ... 524288		 No
sync_binlog integer
0 ... 4294967295

Supported only on read replicas and external server replicas in MySQL 5.7 and 8.0.

The default setting of 1 enables the synchronization of the binary log to disk before transactions are committed.

This flag is not supported on primary instances or legacy HA failover replicas. If a replica using this flag is promoted, the flag is removed.

 No
table_definition_cache integer
400 ... 524288		 No
table_open_cache integer
1 ... 524288		 No
table_open_cache_instances integer
1 ... 64		 Yes
thread_cache_size integer
0 ... 16384		 No
thread_stack integer
131072 ... 9223372036854775807		 Yes
tmp_table_size integer
1024 ... 67108864

See the Tips section for more information about this flag.

 No
transaction_alloc_block_size integer
1024 ... 131072		 No
transaction_isolation enumeration
READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE		 Yes
transaction_prealloc_size integer
1024 ... 131072		 No
transaction_write_set_extraction enumeration

For information about how to use this flag and its acceptable values, see Configuring parallel replication.

 No
updatable_views_with_limit integer
0 ... 1		 No
wait_timeout integer
1 ... 31536000		 No

Tips for working with flags

general_log, slow_query_log

To make your general or slow query logs available, enable the corresponding flag and set the log_output flag to FILE. This makes the log output available using the Logs Viewer in the Google Cloud Console. Note that Google Cloud's operations suite logging charges apply. To minimize instance storage cost, general and slow query logs on the instance disk will be rotated when the log file is older than 24 hours (and no changes have been made within that duration) or greater than 100MB in size. Old log files will be automatically deleted after the rotation.

If log_output is set to NONE, you will be unable to access the logs. If you set log_output to TABLE, the log output is placed in a table in your database. If this table becomes large, it can affect instance restart time or cause the instance to lose its SLA coverage; for this reason, the TABLE option is not recommended. If needed, you can truncate your log tables by using the API. For more information, see the instances.truncateLog reference page.

expire_logs_days, binlog_expire_logs_seconds
If you enable point-in-time recovery, the expiration period of your binary logs will be determined by the lesser of your transaction log retention period and the value of this flag. For more information, see the transaction log retention page.
innodb_buffer_pool_size

The value of the innodb_buffer_pool_size is the size in bytes of the buffer pool. You cannot enable this flag on instances that have fewer than 3,840 MiB of RAM.

This flag is not configurable for shared-core machine types (f1_micro and g1_small). Changing this flag on MySQL 5.6 requires a restart.

In Cloud SQL, the default, minimum allowable, and maximum allowable values of the innodb_buffer_pool_size flag depend on the instance's memory. These values can be roughly calculated as a percentage of the instance's RAM. By default, the value of this flag is typically set close to the maximum allowable value. The maximum allowable allocation percentage increases with instance size. The minimum allowable value is usually about 20% of the instance's RAM.

Approximate values for this flag:

Instance RAM RangeMin %Default %Max %
0 - 4.0GB of RAM~34%
4.0GB - 7.5GB~20%~34%~34%
7.5GB - 12GB~20%~52%~52%
12GB - 24GB~20%~67%~67%
24GB and above~20%~72%~72%

Your exact values may vary. To calculate the current value for your instance, you can run the query:

  show global variables like 'innodb_buffer_pool_size'

For reference, the minimum allowable, default, and maximum allowable values are provided for the predefined machine types below.

Predefined machine type Instance RAM (GB) Min (GB)
(% of total)		 Default (GB)
(% of total)		 Max (GB)
(% of total)
f1-micro 0.6 - 0.053 -
g1-small 1.7 - 0.625 -
db-custom-1-3840 3.75 0.875
(23%)		 1.375
(37%)		 1.375
(37%)
db-custom-2-7680 7.5 1.5
(20%)		 4
(53%)		 4
(53%)
db-custom-4-15360 15 3
(20%)		 10.5
(70%)		 10.5
(70%)
db-custom-8-30720 30 6
(20%)		 22
(73%)		 22
(73%)
db-custom-16-61440 60 12
(20%)		 44
(73%)		 44
(73%)
db-custom-32-122880 120 24
(20%)		 87
(73%)		 87
(73%)
db-custom-64-245760 240 48
(20%)		 173
(72%)		 173
(72%)
db-custom-96-368640 360 72
(20%)		 260
(72%)		 260
(72%)
db-custom-2-13312 13 3
(23%)		 9
(69%)		 9
(69%)
db-custom-4-26624 26 6
(23%)		 19
(73%)		 19
(73%)
db-custom-8-53248 52 11
(21%)		 38
(73%)		 38
(73%)
db-custom-16-106496 104 21
(20%)		 75
(72%)		 75
(72%)
db-custom-32-212992 208 42
(20%)		 150
(72%)		 150
(72%)
db-custom-64-425984 416 84
(20%)		 300
(72%)		 300
(72%)
db-custom-96-638976 624 125
(20%)		 450
(72%)		 450
(72%)

innodb_file_per_table

For all MySQL versions 5.6 and higher, the default value is ON.

innodb_flush_log_at_trx_commit
If you need higher performance, we recommend setting the value to 2. Cloud SQL does not support setting the value for this flag to 0. If you set the flag value to 2, you must either disable the binary log on the replica, or set sync_binlog to a value other than 1.

You can only enable this flag for read replicas and external server replicas. For external server replicas, this flag must be set to the default value of 1 for full ACID compliance. You cannot enable this flag on primary instances or failover replicas.

max_heap_table_size, tmp_table_size

Exhausting the available instance memory can occur when you set tmp_table_size and max_heap_table_size too high for the number of concurrent queries the instance processes. Exhausting the memory will result in an instance crash and restart.

For more information about working with these flags, see How MySQL Uses Internal Temporary Tables and The MEMORY Storage Engine.

performance_schema

You cannot enable this flag on instances that have fewer than 26,624 MiB of RAM. If you enable this flag, you cannot change your machine type to a size that does not support the flag; you must first disable this flag.

System flags changed in Cloud SQL

All other database system flags that are not listed in the supported flags section are called managed flags. For certain managed flags, Cloud SQL sets the flag to a value other than the default setting to ensure Cloud SQL instances run reliably. You can't change the values on these system flags.

Managed flags with a non-default setting are listed below.

Variable Name Setting in Cloud SQL. Notes
binlog_format ROW Differs in MySQL 5.6 only
binlog_error_action ABORT_SERVER Differs in MySQL 5.6 only
innodb_file_format Barracuda Differs in MySQL 5.6 only
innodb_flush_method O_DIRECT
innodb_flush_neighbors 0 (in MySQL 5.6) and 2 (in MySQL 5.7 and 8.0)
innodb_io_capacity 5000
innodb_buffer_pool_instances
Size of Instance
(first match) 		innodb_buffer_pool_instances
< 1024 1
< 2048 1
< 7680 1
< 13312 2
< 26624 4
default 8
memlock true
skip_name_resolve ON
relay_log_info_repository TABLE
relay_log_recovery ON
slave_net_timeout 30
master_info_repository TABLE
rpl_semi_sync_master_enabled 1
rpl_semi_sync_master_timeout 3000
innodb_page_cleaners 8 Differs in MySQL 5.7, 8.0 only
admin_address 127.0.0.1 Differs in MySQL 8.0 only
create_admin_listener_thread ON
port-open-timeout 120 Differs in MySQL 8.0 only
innodb_io_capacity_max 10000

Troubleshooting

Issue Troubleshooting
After enabling a flag the instance loops between panicking and crashing. Contact customer support to request flag removal followed by a hard drain. This forces the instance t restart on a different host with a fresh configuration without the undesired flag or setting.
You see the error message Bad syntax for dict arg when trying to set a flag. Complex parameter values, such as comma-separated lists, require special treatment when used with gcloud commands.
The time zone doesn't automatically change. Automated time zone changes aren't supported in Cloud SQL for MySQL and must be done manually, and not by string but by time zone offset value.

Edit the instance to change the default_time_zone flag. Name areas are not supported. For example:

Europe/LondonLondon is in the UTC time zone, which would be a supported value of +00:00 for the default_time_zone flag.

What's next