📣
TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →
​
Sign InStart for Free
AI
Developer
Best Practices
API
​
Sign InStart for Free

SHOW SETTINGS

TiDB Cloud Lake provides a variety of system settings that enable you to control how TiDB Cloud Lake works. This command displays the current and default values, as well as the Setting Levels, of available system settings. To update a setting, use the SET or UNSET command.

  • Some TiDB Cloud Lake behaviors cannot be changed through the system settings; you must take them into consideration while working with TiDB Cloud Lake. For example,
    • TiDB Cloud Lake encodes strings to the UTF-8 charset.
    • TiDB Cloud Lake uses a 1-based numbering convention for arrays.
  • TiDB Cloud Lake stores the system settings in the system table system.settings.

Syntax

SHOW SETTINGS [LIKE '<pattern>' | WHERE <expr>] | [LIMIT <limit>]

Setting Levels

Each TiDB Cloud Lake setting comes with a level that can be Global, Default, or Session. This table illustrates the distinctions between each level:

LevelDescription
GlobalSettings with this level are written to the meta service and affect all clusters in the same tenant. Changes at this level have a global impact and apply to the entire database environment shared by multiple clusters.
DefaultSettings with this level are service defaults for individual query instances. Changes at this level only affect the query instance where the default is applied.
SessionSettings with this level are restricted to a single request or session. They have the narrowest scope and apply only to the specific session or request in progress, providing a way to customize settings on a per-session basis.

Examples

SHOW SETTINGS LIMIT 5;

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                     name                    │  value │ default │   range  │  level  │                                                                     description                                                                    │  type  │
├─────────────────────────────────────────────┼────────┼─────────┼──────────┼─────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────┤
│ acquire_lock_timeout                        │ 15     │ 15      │ None     │ DEFAULT │ Sets the maximum timeout in seconds for acquire a lock.                                                                                            │ UInt64 │
│ aggregate_spilling_bytes_threshold_per_proc │ 0      │ 0       │ None     │ DEFAULT │ Sets the maximum amount of memory in bytes that an aggregator can use before spilling data to storage during query execution.                      │ UInt64 │
│ aggregate_spilling_memory_ratio             │ 0      │ 0       │ [0, 100] │ DEFAULT │ Sets the maximum memory ratio in bytes that an aggregator can use before spilling data to storage during query execution.                          │ UInt64 │
│ auto_compaction_imperfect_blocks_threshold  │ 50     │ 50      │ None     │ DEFAULT │ Threshold for triggering auto compaction. This occurs when the number of imperfect blocks in a snapshot exceeds this value after write operations. │ UInt64 │
│ collation                                   │ utf8   │ utf8    │ ["utf8"] │ DEFAULT │ Sets the character collation. Available values include "utf8".                                                                                     │ String │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Was this page helpful?