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, the replica will also restart 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

To set a flag for an existing database:

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

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

HTTP method and URL:

PATCH https://www.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 below, make the following replacements:

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

HTTP method and URL:

PATCH https://www.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

To clear all flags for an existing instance:

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

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

HTTP method and URL:

PATCH https://www.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

To list flags configured for an instance:

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

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

HTTP method and URL:

GET https://www.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.

Supported flags

The flags supported in Cloud SQL are the most commonly requested flags for MySQL.

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?
In
Beta?
auto_increment_increment integer
1 ... 65535
No No
auto_increment_offset integer
1 ... 65535
No No
binlog_cache_size integer
4096 ... 9223372036854775807
No Yes
binlog_row_image enumeration
full (default), minimal, or noblob
No No
binlog_stmt_cache_size 4096 ... 9223372036854775807 No Yes
character_set_server string
utf8 or utf8mb4 (recommended)
Yes 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 No
eq_range_index_dive_limit integer
0 ... 2147483647
No No
event_scheduler boolean
on | off

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

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

See the Tips section for more information about general logs.

No No
group_concat_max_len integer
4 ... 17179869184
No No
init_connect string No Yes
innodb_adaptive_hash_index boolean
on | off
No Yes
innodb_adaptive_hash_index_parts integer
1 ... 512
Yes Yes
innodb_adaptive_max_sleep_delay integer
0 ... 1000000
No No
innodb_autoinc_lock_mode integer
0 ... 2
Yes No
innodb_change_buffer_max_size integer
0 ... 50
No Yes
innodb_concurrency_tickets integer
1 ... 4294967295
No No
innodb_file_per_table boolean
on | off

See the Tips section for more information about this flag.

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

Supported only in MySQL 5.6 and later versions.

Yes No
innodb_ft_min_token_size integer
0 ... 16

Supported only in MySQL 5.6 and later versions.

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

Supported only in MySQL 5.6 and later versions.

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

Supported only in MySQL 5.5 and 5.6.

No No
innodb_lock_wait_timeout integer
1 ... 1073741824
No No
innodb_log_buffer_size integer
262144 ... 4294967295
Yes Yes
innodb_log_file_size integer
MySQL 5.6: 1048576 ... 274877906944
MySQL 5.7: 4194304 ... 274877906944
Yes Yes
innodb_lru_scan_depth integer
100 ... 9223372036854775807
No No
innodb_old_blocks_pct integer
5 ... 95
No No
innodb_old_blocks_time integer
0 ... 4294967295
No No
innodb_online_alter_log_max_size integer
65536 ... 9223372036854775807
No No
innodb_optimize_fulltext_only boolean
on | off
No No
innodb_print_all_deadlocks boolean
on | off
No No
innodb_random_read_ahead boolean
on | off
No No
innodb_read_ahead_threshold integer
0 ... 64
No No
innodb_read_io_threads integer
1 ... 64
Yes No
innodb_replication_delay integer
0 ... 4294967295
No Yes
innodb_rollback_on_timeout boolean
on | off
Yes No
innodb_rollback_segments integer
1 ... 128
No No
innodb_sort_buffer_size integer
65536 ... 67108864
Yes Yes
innodb_stats_auto_recalc boolean
on | off
No No
innodb_stats_method enumeration
nulls_equal | nulls_unequal | nulls_ignored
No No
innodb_stats_on_metadata boolean
on | off
No No
innodb_stats_persistent boolean
on | off
No No
innodb_stats_persistent_sample_pages integer
1 ... 9223372036854775807
No No
innodb_stats_sample_pages integer
1 ... 9223372036854775807
No No
innodb_stats_transient_sample_pages integer
1 ... 9223372036854775807
No No
innodb_status_output boolean
on | off
No No
innodb_status_output_locks boolean
on | off
No No
innodb_strict_mode boolean
on | off
No No
innodb_thread_concurrency integer
0 ... 1000
No No
innodb_thread_sleep_delay integer
0 ... 1000000
No No
innodb_write_io_threads integer
1 ... 64
Yes No
interactive_timeout integer
1 ... 31536000
No No
join_buffer_size integer
128 ... 9223372036854775807
No Yes
local_infile boolean
on | off
No No
lock_wait_timeout integer
1 ... 31536000
No No
log_bin_trust_function_creators boolean
on | off
No No
log_output set
FILE | TABLE | NONE
No No
log_queries_not_using_indexes boolean
on | off
No No
log_throttle_queries_not_using_indexes integer
0 ... 9223372036854775807
No 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 No
lower_case_table_names integer
0 or 1

In most cases, the default value of 0 should be used, 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 No
max_allowed_packet integer
16384 ... 1073741824

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

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

See the Tips section for more information about this flag.

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

See Tips section for more information about performance_schema flags.

Yes No
performance_schema_accounts_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_digests_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

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

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_statements_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_statements_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_transactions_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_transactions_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_waits_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_waits_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_hosts_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_cond_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_cond_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_digest_length integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_file_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_file_handles integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_file_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_index_stat integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_memory_classes integer
0 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_metadata_locks integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_mutex_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_mutex_instances integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_prepared_statements_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_program_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_rwlock_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_rwlock_instances integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_socket_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_socket_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_sql_text_length integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_stage_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_statement_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_statement_stack integer
1 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_table_handles integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_table_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_table_lock_stat integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_thread_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_thread_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_session_connect_attrs_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_setup_actors_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_setup_objects_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_users_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
query_alloc_block_size integer
1024 ... 4294967295
No Yes
query_cache_limit integer
0 ... 223338299392
No No
query_cache_min_res_unit integer
0 ... 9223372036854775807
No Yes
query_cache_size integer
0 ... 223338299392

See the Tips section for more information about this flag.

No No
query_cache_type enumeration
0 ... 2

See the Tips section for more information about this flag.

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

Has no effect for replicas.

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

Supported only in MySQL 5.7.

No No
skip_show_database flag
on | off
Yes No
slow_query_log boolean
on | off

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

No No
sort_buffer_size integer
32768 ... 9223372036854775807
No Yes
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.

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

See the Tips section for more information about this flag.

No No
transaction_alloc_block_size integer
1024 ... 131072
No Yes
transaction_isolation enumeration
READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE
Yes Yes
transaction_prealloc_size integer
1024 ... 131072
No Yes
updatable_views_with_limit integer
0 ... 1
No No
wait_timeout integer
1 ... 31536000
No 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 or greater than 100MB in size, and 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.

innodb_file_per_table

This flag is set to OFF by default for MySQL 5.5; for all later MySQL versions, the default value is ON.

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.

Some values for these flags can cause your instance to lose its SLA coverage. Learn more. For more information about working with these flags, see How MySQL Uses Internal Temporary Tables and The MEMORY Storage Engine.

performance_schema*

You must use the API to change the value of this flag. It cannot be enabled on instances with a tier size smaller than db-n1-standard-8 or db-n1-highmem-4. If this flag is enabled, you cannot change your tier to a size that does not support this flag; you must first disable this flag.

query_cache_size, query_cache_type

Some values for these flags can cause your instance to lose its SLA coverage. Learn more.

Flags managed by Cloud SQL

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

Cloud SQL Flag Values and Notes
innodb_buffer_pool_instances See the buffer pool instances section below for more information about this flag.
innodb_buffer_pool_size See the buffer pool size section below for more information about this flag.
innodb_buffer_pool_instances
  • 1 for f1-micro, g1-small and n1-standard-1.
  • 2 for n1-standard-2.
  • 4 for n1-standard-4 and n1-highmem-2.
  • 8 for all other tiers.

innodb_buffer_pool_size

approximate sizes.

  • ~ 1.5 GB for f1-micro, g1-small and n1-standard-1.
  • ~ 4 GB for n1-standard-2.
  • ~ 11 GB for n1-standard-4.
  • ~ 22 GB for n1-standard-8.
  • ~ 46 GB for n1-standard-16.
  • ~ 92 GB for n1-standard-32.
  • ~ 186 GB for n1-standard-64.
  • ~ 10 GB for n1-highmem-2.
  • ~ 20 GB for n1-highmem-4.
  • ~ 40 GB for n1-highmem-8.
  • ~ 80 GB for n1-highmem-16.
  • ~ 160 GB for n1-highmem-32.
  • ~ 320 GB for n1-highmem-64.

Troubleshooting

Click the links in the table for details:

For this problem... The issue might be... Try this...
Data with character set utf8mb4. This character set is not supported. Filter utf8mb4 strings out of your data.
Enabling a flag crashes the instance. The max_connections flag value may be set too high. Contact customer support to request a flag removal.
Can't add the performance_schema flag. Instance size is too small. Update to a larger instance.
Time zone doesn't automatically change. Automated time zone change is not supported. Time must be changed manually. Learn more.

Data with character set utf8mb4

Failed to import data with character set utf8mb4.

The issue might be

The character set utf8mb4 is not supported even though the documentation has previously indicated it is.

Things to try

Filter utf8mb4 strings out of your data.


Enabling a flag crashes the instance

After enabling a flag the instances loop between panicking and crashing.

The issue might be

Setting the max_connections flag value too high causes this error.

Things to try

Contact customer support to request a flag removal followed by a hard drain. This forces the instance to restart on a different host with a fresh configuration without the undesired flag or setting.


Can't add the performance_schema flag

You cannot add the performance_schema flag because it isn't in the dropdown menu of supported flags.

The issue might be

The performance_schema and its variants (performance_schema_accounts_size, performance_schema_accounts_size, etc.) cannot be enabled on instances smaller than db-n1-standard-8 or db-n1-highmem-4.

Things to try

Edit the instance to upgrade to a larger size if you need to use this flag.


Time zone doesn't automatically change

The time zone didn't automatically change for daylight savings time.

The issue might be

Automated time zone changes are not supported in Cloud SQL and must be done manually, and not by string but by time zone offset value.

Things to try

Edit the instance to change the default_time_zone flag. Named 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