Skip to main content
SereneDBSereneDB
897Contact Us

SET / RESET

The SET statement modifies the provided SereneDB configuration option at the specified scope.

Examples​

Update the memory_limit configuration value:

SQL
SET memory_limit = '10GB';

Run the queries of the current session on 1 thread:

SQL
SET threads = 1;

Or use the TO keyword:

SQL
SET threads TO 1;

Change configuration option to default value:

SQL
RESET threads;

Retrieve configuration value:

SQL
SELECT current_setting('threads');
 current_setting
-----------------
 (varies)

Set the default collation for the session:

SQL
SET SESSION default_collation = 'nocase';

Set a Global Variable​

Set the default sort order globally:

SQL
SET GLOBAL default_order = 'desc';

Size the thread pool of the whole instance to 4 threads:

SQL
SET GLOBAL threads = 4;

GLOBAL settings persist across the whole instance and outlive the session that set them, so reset them with RESET GLOBAL once the override is no longer needed:

SQL
RESET GLOBAL default_order;
SQL
RESET GLOBAL threads;

Threads​

What threads changes depends on the scope it is set in. SET GLOBAL threads sizes the pool of worker threads the whole instance runs queries on. A session value (SET threads, SET SESSION threads) or a transaction value (SET LOCAL threads) leaves the pool alone and caps how many of its threads each query of that session or transaction runs on; a cap larger than the pool has no effect until the pool grows. SHOW threads returns the cap when one is set and the pool size otherwise. RESET threads drops the session's cap, RESET GLOBAL threads sizes the pool back to its default.

Run the queries of one transaction on at most 2 threads:

SQL
BEGIN TRANSACTION;
SET LOCAL threads = 2;
SHOW threads;
COMMIT;
 threads
---------
       2

Async threads​

async_threads takes the same three scopes as threads. SET GLOBAL async_threads sizes the pool of async threads the whole instance runs its I/O on. A session value (SET async_threads, SET SESSION async_threads) or a transaction value (SET LOCAL async_threads) leaves the pool alone and caps how many async threads the queries of that session or transaction use; 0 runs their I/O without async threads, as a pool of none does. SHOW async_threads returns the cap when one is set and the pool size otherwise. RESET async_threads drops the session's cap, RESET GLOBAL async_threads sizes the pool back to its default.

Run the I/O of one transaction's queries without async threads:

SQL
BEGIN TRANSACTION;
SET LOCAL async_threads = 0;
SHOW async_threads;
COMMIT;
 async_threads
---------------
             0

Syntax​

Syntax

SET updates a SereneDB configuration option to the provided value.

RESET​

Syntax

The RESET statement changes the given SereneDB configuration option to the default value.

Scopes​

Configuration options can have different scopes:

  • GLOBAL: Configuration value is used (or reset) across the entire SereneDB instance.
  • SESSION: Configuration value is used (or reset) only for the current session attached to a SereneDB instance.
  • LOCAL: Configuration value is used only until the current transaction ends, then the session value returns. SET LOCAL outside a transaction block is an error.

When not specified, the default scope for the configuration option is used. The sdb_ search settings and integer_division default to the session. Engine options such as default_null_order, preserve_insertion_order and enable_progress_bar default to GLOBAL, so a plain SET changes them for every session on the server; write SET SESSION to keep the change to your own. A few options, such as memory_limit, exist only globally: SET SESSION and SET LOCAL refuse them with option "<name>" cannot be set locally. threads and async_threads take all three scopes, see Threads and Async threads.

Configuration​

See the Configuration page for the full list of configuration options.

Edit this page ↗