Cluster Settings

Warning:
As of November 18, 2022, CockroachDB v21.1 is no longer supported. For more details, refer to the Release Support Policy.

Cluster settings apply to all nodes of a CockroachDB cluster and control, for example, whether or not to share diagnostic details with Cockroach Labs as well as advanced options for debugging and cluster tuning.

They can be updated anytime after a cluster has been started, but only by a member of the admin role, to which the root user belongs by default.

Note:

In contrast to cluster-wide settings, node-level settings apply to a single node. They are defined by flags passed to the cockroach start command when starting a node and cannot be changed without stopping and restarting the node. For more details, see Start a Node.

Settings

Warning:

Many cluster settings are intended for tuning CockroachDB internals. Before changing these settings, we strongly encourage you to discuss your goals with Cockroach Labs; otherwise, you use them at your own risk.

SettingTypeDefaultDescription
bulkio.stream_ingestion.minimum_flush_intervalduration5sthe minimum timestamp between flushes; flushes may still occur if internal buffers fill up
changefeed.node_throttle_configstringspecifies node level throttling configuration for all changefeeeds
cloudstorage.gs.default.keystring[deprecated] if set, JSON key to use during Google Cloud Storage operations. This setting will be removed in 21.2, as we will no longer support the `default` AUTH mode for GCS operations.
cloudstorage.http.custom_castringcustom root CA (appended to system's default CAs) for verifying certificates when interacting with HTTPS storage
cloudstorage.timeoutduration10m0sthe timeout for import/export storage operations
cluster.organizationstringorganization name
cluster.preserve_downgrade_optionstringdisable (automatic or manual) cluster version upgrade from the specified version until reset
diagnostics.forced_sql_stat_reset.intervalduration2h0m0sinterval after which SQL statement statistics are refreshed even if not collected (should be more than diagnostics.sql_stat_reset.interval). It has a max value of 24H.
diagnostics.reporting.enabledbooleantrueenable reporting diagnostic metrics to cockroach labs
diagnostics.reporting.intervalduration1h0m0sinterval at which diagnostics data should be reported
diagnostics.sql_stat_reset.intervalduration1h0m0sinterval controlling how often SQL statement statistics should be reset (should be less than diagnostics.forced_sql_stat_reset.interval). It has a max value of 24H.
enterprise.licensestringthe encoded cluster license
external.graphite.endpointstringif nonempty, push server metrics to the Graphite or Carbon server at the specified host:port
external.graphite.intervalduration10sthe interval at which metrics are pushed to Graphite (if enabled)
feature.backup.enabledbooleantrueset to true to enable backups, false to disable; default is true
feature.changefeed.enabledbooleantrueset to true to enable changefeeds, false to disable; default is true
feature.export.enabledbooleantrueset to true to enable exports, false to disable; default is true
feature.import.enabledbooleantrueset to true to enable imports, false to disable; default is true
feature.restore.enabledbooleantrueset to true to enable restore, false to disable; default is true
feature.schema_change.enabledbooleantrueset to true to enable schema changes, false to disable; default is true
feature.stats.enabledbooleantrueset to true to enable CREATE STATISTICS/ANALYZE, false to disable; default is true
jobs.retention_timeduration336h0m0sthe amount of time to retain records for completed jobs before
kv.allocator.load_based_lease_rebalancing.enabledbooleantrueset to enable rebalancing of range leases based on load and latency
kv.allocator.load_based_rebalancingenumerationleases and replicaswhether to rebalance based on the distribution of QPS across stores [off = 0, leases = 1, leases and replicas = 2]
kv.allocator.qps_rebalance_thresholdfloat0.25minimum fraction away from the mean a store's QPS (such as queries per second) can be before it is considered overfull or underfull
kv.allocator.range_rebalance_thresholdfloat0.05minimum fraction away from the mean a store's range count can be before it is considered overfull or underfull
kv.bulk_io_write.max_ratebyte size1.0 TiBthe rate limit (bytes/sec) to use for writes to disk on behalf of bulk io ops
kv.closed_timestamp.follower_reads_enabledbooleantrueallow (all) replicas to serve consistent historical reads based on closed timestamp information
kv.protectedts.reconciliation.intervalduration5m0sthe frequency for reconciling jobs with protected timestamp records
kv.range_split.by_load_enabledbooleantrueallow automatic splits of ranges based on where load is concentrated
kv.range_split.load_qps_thresholdinteger2500the QPS over which, the range becomes a candidate for load based splitting
kv.rangefeed.enabledbooleanfalseif set, rangefeed registration is enabled
kv.replication_reports.intervalduration1m0sthe frequency for generating the replication_constraint_stats, replication_stats_report and replication_critical_localities reports (set to 0 to disable)
kv.snapshot_rebalance.max_ratebyte size8.0 MiBthe rate limit (bytes/sec) to use for rebalance and upreplication snapshots
kv.snapshot_recovery.max_ratebyte size8.0 MiBthe rate limit (bytes/sec) to use for recovery snapshots
kv.transaction.max_intents_bytesinteger262144maximum number of bytes used to track locks in transactions
kv.transaction.max_refresh_spans_bytesinteger256000maximum number of bytes used to track refresh spans in serializable transactions
kv.transaction.reject_over_max_intents_budget.enabledbooleanfalseif set, transactions that exceed their lock tracking budget (kv.transaction.max_intents_bytes) are rejected instead of having their lock spans imprecisely compressed
security.ocsp.modeenumerationoffuse OCSP to check whether TLS certificates are revoked. If the OCSP
server is unreachable, in strict mode all certificates will be rejected
and in lax mode all certificates will be accepted. [off = 0, lax = 1, strict = 2]
security.ocsp.timeoutduration3stimeout before considering the OCSP server unreachable
server.auth_log.sql_connections.enabledbooleanfalseif set, log SQL client connect and disconnect events (note: may hinder performance on loaded nodes)
server.auth_log.sql_sessions.enabledbooleanfalseif set, log SQL session login/disconnection events (note: may hinder performance on loaded nodes)
server.clock.forward_jump_check_enabledbooleanfalseif enabled, forward clock jumps > max_offset/2 will cause a panic
server.clock.persist_upper_bound_intervalduration0sthe interval between persisting the wall time upper bound of the clock. The clock does not generate a wall time greater than the persisted timestamp and will panic if it sees a wall time greater than this value. When cockroach starts, it waits for the wall time to catch-up till this persisted timestamp. This guarantees monotonic wall time across server restarts. Not setting this or setting a value of 0 disables this feature.
server.consistency_check.max_ratebyte size8.0 MiBthe rate limit (bytes/sec) to use for consistency checks; used in conjunction with server.consistency_check.interval to control the frequency of consistency checks. Note that setting this too high can negatively impact performance.
server.eventlog.enabledbooleantrueif set, logged notable events are also stored in the table system.eventlog
server.eventlog.ttlduration2160h0m0sif nonzero, entries in system.eventlog older than this duration are deleted every 10m0s. Should not be lowered below 24 hours.
server.host_based_authentication.configurationstringhost-based authentication configuration to use during connection authentication
server.hsts.enabledbooleanfalseif true, HSTS headers will be sent along with all HTTP requests. The headers will contain a max-age setting of one year. Browsers honoring the header will always use HTTPS to access the DB Console. Ensure that TLS is correctly configured prior to enabling.
server.oidc_authentication.autologinbooleanfalseif true, logged-out visitors to the DB Console will be automatically redirected to the OIDC login endpoint (this feature is experimental)
server.oidc_authentication.button_textstringLogin with your OIDC providertext to show on button on DB Console login page to login with your OIDC provider (only shown if OIDC is enabled) (this feature is experimental)
server.oidc_authentication.claim_json_keystringsets JSON key of principal to extract from payload after OIDC authentication completes (usually email or sid) (this feature is experimental)
server.oidc_authentication.client_idstringsets OIDC client id (this feature is experimental)
server.oidc_authentication.client_secretstringsets OIDC client secret (this feature is experimental)
server.oidc_authentication.enabledbooleanfalseenables or disabled OIDC login for the DB Console (this feature is experimental)
server.oidc_authentication.principal_regexstring(.+)regular expression to apply to extracted principal (see claim_json_key setting) to translate to SQL user (golang regex format, must include 1 grouping to extract) (this feature is experimental)
server.oidc_authentication.provider_urlstringsets OIDC provider URL ({provider_url}/.well-known/openid-configuration must resolve) (this feature is experimental)
server.oidc_authentication.redirect_urlstringhttps://localhost:8080/oidc/v1/callbacksets OIDC redirect URL via a URL string or a JSON string containing a required `redirect_urls` key with an object that maps from region keys to URL strings (URLs should point to your load balancer and must route to the path /oidc/v1/callback) (this feature is experimental)
server.oidc_authentication.scopesstringopenidsets OIDC scopes to include with authentication request (space delimited list of strings, required to start with `openid`) (this feature is experimental)
server.rangelog.ttlduration720h0m0sif nonzero, range log entries older than this duration are deleted every 10m0s. Should not be lowered below 24 hours.
server.remote_debugging.modestringlocalset to enable remote debugging, localhost-only or disable (any, local, off)
server.shutdown.drain_waitduration0sthe amount of time a server waits in an unready state before proceeding with the rest of the shutdown process
server.shutdown.lease_transfer_waitduration5sthe amount of time a server waits to transfer range leases before proceeding with the rest of the shutdown process
server.shutdown.query_waitduration10sthe server will wait for at least this amount of time for active queries to finish
server.time_until_store_deadduration5m0sthe time after which if there is no new gossiped information about a store, it is considered dead
server.user_login.timeoutduration10stimeout after which client authentication times out if some system range is unavailable (0 = no timeout)
server.web_session_timeoutduration168h0m0sthe duration that a newly created web session will be valid
sql.auth.resolve_membership_single_scan.enabledbooleanfalsedetermines whether to populate the role membership cache with a single scan
sql.cross_db_fks.enabledbooleanfalseif true, creating foreign key references across databases is allowed
sql.cross_db_sequence_owners.enabledbooleanfalseif true, creating sequences owned by tables from other databases is allowed
sql.cross_db_views.enabledbooleanfalseif true, creating views that refer to other databases is allowed
sql.defaults.copy_partitioning_when_deinterleaving_table.enabledbooleanfalsedefault value for enable_copying_partitioning_when_deinterleaving_table session variable
sql.defaults.default_int_sizeinteger8the size, in bytes, of an INT type
sql.defaults.disallow_full_table_scans.enabledbooleanfalsesetting to true rejects queries that have planned a full table scan
sql.defaults.idle_in_session_timeoutduration0sdefault value for the idle_in_session_timeout; default value for the idle_in_session_timeout session setting; controls the duration a session is permitted to idle before the session is terminated; if set to 0, there is no timeout
sql.defaults.idle_in_transaction_session_timeoutduration0sdefault value for the idle_in_transaction_session_timeout; controls the duration a session is permitted to idle in a transaction before the session is terminated; if set to 0, there is no timeout
sql.defaults.large_full_scan_rowsfloat0default value for large_full_scan_rows session setting which determines the maximum table size allowed for a full scan when disallow_full_table_scans is set to true
sql.defaults.reorder_joins_limitinteger8default number of joins to reorder
sql.defaults.results_buffer.sizebyte size16 KiBdefault size of the buffer that accumulates results for a statement or a batch of statements before they are sent to the client. This can be overridden on an individual connection with the 'results_buffer_size' parameter. Note that auto-retries generally only happen while no results have been delivered to the client, so reducing this size can increase the number of retriable errors a client receives. On the other hand, increasing the buffer size can increase the delay until the client receives the first result row. Updating the setting only affects new connections. Setting to 0 disables any buffering.
sql.defaults.serial_normalizationenumerationrowiddefault handling of SERIAL in table definitions [rowid = 0, virtual_sequence = 1, sql_sequence = 2, sql_sequence_cached = 3]
sql.defaults.statement_timeoutduration0sdefault value for the statement_timeout; default value for the statement_timeout session setting; controls the duration a query is permitted to run before it is canceled; if set to 0, there is no timeout
sql.defaults.transaction_rows_read_errinteger0the limit for the number of rows read by a SQL transaction which - once exceeded - will fail the transaction (or will trigger a logging event to SQL_INTERNAL_PERF for internal transactions); use 0 to disable
sql.defaults.transaction_rows_read_loginteger0the threshold for the number of rows read by a SQL transaction which - once exceeded - will trigger a logging event to SQL_PERF (or SQL_INTERNAL_PERF for internal transactions); use 0 to disable
sql.defaults.transaction_rows_written_errinteger0the limit for the number of rows written by a SQL transaction which - once exceeded - will fail the transaction (or will trigger a logging event to SQL_INTERNAL_PERF for internal transactions); use 0 to disable
sql.defaults.transaction_rows_written_loginteger0the threshold for the number of rows written by a SQL transaction which - once exceeded - will trigger a logging event to SQL_PERF (or SQL_INTERNAL_PERF for internal transactions); use 0 to disable
sql.distsql.max_running_flowsinteger500maximum number of concurrent flows that can be run on a node
sql.distsql.temp_storage.workmembyte size64 MiBmaximum amount of memory in bytes a processor can use before falling back to temp storage
sql.guardrails.max_row_size_errbyte size0 Bmaximum size of row (or column family if multiple column families are in use) that SQL can write to the database, above which an error is returned; use 0 to disable
sql.guardrails.max_row_size_logbyte size0 Bmaximum size of row (or column family if multiple column families are in use) that SQL can write to the database, above which an event is logged to SQL_PERF (or SQL_INTERNAL_PERF if the mutating statement was internal); use 0 to disable
sql.log.slow_query.experimental_full_table_scans.enabledbooleanfalsewhen set to true, statements that perform a full table/index scan will be logged to the slow query log even if they do not meet the latency threshold. Must have the slow query log enabled for this setting to have any effect.
sql.log.slow_query.internal_queries.enabledbooleanfalsewhen set to true, internal queries which exceed the slow query log threshold are logged to a separate log. Must have the slow query log enabled for this setting to have any effect.
sql.log.slow_query.latency_thresholdduration0swhen set to non-zero, log statements whose service latency exceeds the threshold to a secondary logger on each node
sql.metrics.statement_details.dump_to_logsbooleanfalsedump collected statement statistics to node logs when periodically cleared
sql.metrics.statement_details.enabledbooleantruecollect per-statement query statistics
sql.metrics.statement_details.plan_collection.enabledbooleantrueperiodically save a logical plan for each fingerprint
sql.metrics.statement_details.plan_collection.periodduration5m0sthe time until a new logical plan is collected
sql.metrics.statement_details.thresholdduration0sminimum execution time to cause statement statistics to be collected. If configured, no transaction stats are collected.
sql.metrics.transaction_details.enabledbooleantruecollect per-application transaction statistics
sql.multiple_modifications_of_table.enabledbooleanfalseif true, allow statements containing multiple INSERT ON CONFLICT, UPSERT, UPDATE, or DELETE subqueries modifying the same table, at the risk of data corruption if the same row is modified multiple times by a single statement (multiple INSERT subqueries without ON CONFLICT cannot cause corruption and are always allowed)
sql.notices.enabledbooleantrueenable notices in the server/client protocol being sent
sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabledbooleanfalseif enabled, uniqueness checks may be planned for mutations of UUID columns updated with gen_random_uuid(); otherwise, uniqueness is assumed due to near-zero collision probability
sql.spatial.experimental_box2d_comparison_operators.enabledbooleanfalseenables the use of certain experimental box2d comparison operators
sql.stats.automatic_collection.enabledbooleantrueautomatic statistics collection mode
sql.stats.automatic_collection.fraction_stale_rowsfloat0.2target fraction of stale rows per table that will trigger a statistics refresh
sql.stats.automatic_collection.min_stale_rowsinteger500target minimum number of stale rows per table that will trigger a statistics refresh
sql.stats.histogram_collection.enabledbooleantruehistogram collection mode
sql.stats.multi_column_collection.enabledbooleantruemulti-column statistics collection mode
sql.stats.post_events.enabledbooleanfalseif set, an event is logged for every CREATE STATISTICS job
sql.temp_object_cleaner.cleanup_intervalduration30m0show often to clean up orphaned temporary objects
sql.trace.log_statement_executebooleanfalseset to true to enable logging of executed statements
sql.trace.session_eventlog.enabledbooleanfalseset to true to enable session tracing. Note that enabling this may have a non-trivial negative performance impact.
sql.trace.stmt.enable_thresholdduration0sduration beyond which all statements are traced (set to 0 to disable). This applies to individual statements within a transaction and is therefore finer-grained than sql.trace.txn.enable_threshold.
sql.trace.txn.enable_thresholdduration0sduration beyond which all transactions are traced (set to 0 to disable). This setting is coarser grained thansql.trace.stmt.enable_threshold because it applies to all statements within a transaction as well as client communication (e.g. retries).
timeseries.storage.enabledbooleantrueif set, periodic timeseries data is stored within the cluster; disabling is not recommended unless you are storing the data elsewhere
timeseries.storage.resolution_10s.ttlduration240h0m0sthe maximum age of time series data stored at the 10 second resolution. Data older than this is subject to rollup and deletion.
timeseries.storage.resolution_30m.ttlduration2160h0m0sthe maximum age of time series data stored at the 30 minute resolution. Data older than this is subject to deletion.
trace.debug.enablebooleanfalseif set, traces for recent requests can be seen at https:///debug/requests
trace.lightstep.tokenstringif set, traces go to Lightstep using this token
trace.zipkin.collectorstringif set, traces go to the given Zipkin instance (example: '127.0.0.1:9411'); ignored if trace.lightstep.token is set
versionversion21.1set the active cluster version in the format '.'

View current cluster settings

Use the SHOW CLUSTER SETTING statement.

Change a cluster setting

Use the SET CLUSTER SETTING statement.

Before changing a cluster setting, please note the following:

  • Changing a cluster setting is not instantaneous, as the change must be propagated to other nodes in the cluster.

  • Do not change cluster settings while upgrading to a new version of CockroachDB. Wait until all nodes have been upgraded before you make the change.

See also


Yes No