ALTER SERVER PARAMETER (TSQL Statement)
alters the server parameters.
Syntax
ALTER SERVER PARAMETER SET <param_spec>
<param_spec> ::=
{
SERVER_SERVERNAME = 'server_name'
| SERVER_WORKERS_MAX = workers_max
| SERVER_GLOBAL_PAGE_CACHE_MEMORY = global_cache_memory_size
| SERVER_DEFAULT_DATABASE = default_database
| SERVER_DEFAULT_LANGUAGE = default_language
| SERVER_QUOTED_IDENTIFIER = { ON|OFF }
| SERVER_BULK_DIR = 'bulk_dir'
| SERVER_DUMP_DIR = 'dump_dir'
| SERVER_READ_TIMEOUT = read_timeout
| SERVER_LOCK_TICKER_INTERVAL = lock_ticker_interval
| SERVER_LOCK_TIMEOUT_TICKS_COUNT = lock_timeout_ticks_count
| SERVER_LOGGING_MAX_SIZE = logging_max_size
| SERVER_LOGGING_MAX_COUNT = logging_max_count
| SERVER_LOGGING_LOCALTIME = logging_localtime
| SERVER_WCACHE_MEMORY_MAX = wcache_mem_size
| SERVER_WCACHE_MODIF_MAX = wcache_modif_size
| SERVER_BATCH_TEXT_MAX_SIZE = batch_text_size
| SERVER_BATCH_INSERTS_MAX_COUNT = inserts_count
}
Arguments
SERVER_SERVERNAME =
‘server_name’server_name is a name of your choice, e.g. ‘jupiter/Accounting’.
- It is the combination of a machine name and a service name, separated by a slash
/
. - The machine name can be any name. By default, it is the name of the machine on which RSQL is running.
- The service name is any name describing the instance. By default, it is the name of the base directory of the instance.
- See
@@SERVERNAME
and@@SERVICENAME
.
This parameter takes effect after server restart.
- It is the combination of a machine name and a service name, separated by a slash
SERVER_WORKERS_MAX =
workers_maxworkers_max is the maximum number of concurrent workers, in the range 1 to 64 (64 is the default at installation).
This parameter takes effect after server restart.
SERVER_GLOBAL_PAGE_CACHE_MEMORY =
global_cache_memory_sizeglobal_cache_memory_size is the size in
MB
of the global page cache.- RSQL can only work with pages that are present in the global page cache or in the session’s private write cache.
- The size of this cache should be a small portion of the available RAM (10% is good. If your RAM is huge, you can even try with a smaller size).
- When a page is ejected from this global page cache, it remains for a while in the Linux system cache.
So, if global_cache_memory_size is defined too large, a page will be kept in memory twice (in the global page cache and in the Linux system cache), which is bad for performance.
This parameter takes effect after server restart.
SERVER_DEFAULT_DATABASE =
default_databasedefault_database is the default database for
CREATE LOGIN
.This parameter takes effect immediately.
SERVER_DEFAULT_LANGUAGE =
default_languagedefault_language is the default language for
CREATE LOGIN
.This parameter takes effect immediately.
SERVER_QUOTED_IDENTIFIER =
{ ON|OFF }specifies if identifiers can be delimited by double-quotes.
- To change this setting only for the session, see
SET QUOTED_IDENTIFIER
.
This parameter takes effect for the next script.
- To change this setting only for the session, see
SERVER_BULK_DIR =
‘bulk_dir’is the default directory for files imported by
BULK INSERT
or exported byBULK EXPORT
.- The directory can be an absolute path or a relative path. A relative path is relative to the base directory of the RSQL instance.
- By default, it is the directory
bulkdir
in the instance base directory.
This parameter takes effect immediately.
SERVER_DUMP_DIR =
‘dump_dir’is the default directory for files files generated by
BACKUP
or restored byRESTORE
.- The directory can be an absolute path or a relative path. A relative path is relative to the base directory of the RSQL instance.
- By default, it is the directory
dumpdir
in the instance base directory.
This parameter takes effect immediately.
SERVER_READ_TIMEOUT
= read_timeoutis the timeout in seconds that the server waits for client’s requests and keepalive messages.
- If the client doesn’t send a message before this duration has elapsed, the connection is closed.
- The value of read_timeout should be a little greater than the interval at which the client sends the keepalive messages.
- A client sends keepalive messages every 20 seconds to indicate that it is still alive. So, allowing for some delay, read_timeout should be 30 seconds or more.
- During long-running queries, if no keepalive is received from the client, the connection is closed. This way, locks and resources held by lost connections are freed.
- If read_timeout is too large, the server could wait too long for the client’s login message.
This parameter takes effect after server restart.
SERVER_LOCK_TICKER_INTERVAL
= lock_ticker_intervalspecifies the interval in milliseconds of the lock manager timeout ticker.
- The default value is 1000 ms (1 second). There is no reason to change this value.
This parameter takes effect after server restart.
SERVER_LOCK_TIMEOUT_TICKS_COUNT
= lock_timeout_ticks_countis the count of intervals of the lock manager timeout ticker, before a connection waiting for locks receives a timeout error.
This parameter takes effect after server restart.
SERVER_LOGGING_MAX_SIZE
= logging_max_sizewhen the size of the logging file reaches this value in
MB
, the rotation occurs.- If 0, the logging file grows indefinitely.
- By default, it is 5 (MB).
The logging file is specified by the
-logpath
option passed to the server executable (see Installation).This parameter takes effect after server restart.
SERVER_LOGGING_MAX_COUNT
= logging_max_countspecifies the number of old logging files to keep.
- The logging files in excess are automatically deleted.
- If 0, old logging files are never deleted.
- By default, it is 10.
The logging file is specified by the
-logpath
option passed to the server executable (see Installation).This parameter takes effect after server restart.
SERVER_LOGGING_LOCALTIME
= logging_localtimespecifies if the timestamp appended to the logging file name when rotated is in local time or UTC.
- By default, it is in local time.
The logging file is specified by the
-logpath
option passed to the server executable (see Installation).This parameter takes effect after server restart.
SERVER_WCACHE_MEMORY_MAX =
wcache_mem_sizeEach session has its own private write cache, used to store the pages it is modifying but not committed yet.
- The private cache size increases automatically as more pages are modified.
- The private cache stops growing when it reaches wcache_mem_size, which is the maximum size in
MB
. - If more pages are being modified than the private cache keep store in RAM, older pages are spilled to the logfile.
- The default value is good for most usage, in my opinion.
This parameter takes effect after server restart.
SERVER_WCACHE_MODIF_MAX =
wcache_modif_sizewcache_modif_size is the limit in
MB
of all the modifications a transaction can perform before committing.- This limit includes the modifications on tables as well as their indexes.
- If this limit is exceeded, an error is returned.
This parameter takes effect after server restart.
SERVER_BATCH_TEXT_MAX_SIZE
= batch_text_sizebatch_text_size is the max size of a batch that the server can receive, in bytes.
- If a batch contains too many tokens, the SQL compiler will use too much memory for the AST tree.
This parameter helps to mitigate this problem. - If this limit is exceeded, an error is returned.
- If a batch contains too many tokens, the SQL compiler will use too much memory for the AST tree.
SERVER_BATCH_INSERTS_MAX_COUNT
= inserts_countinserts_count is the max number of
INSERT
statements that are allowed in a batch.- If a batch contains too many tokens, the SQL compiler will use too much memory for the AST tree.
This parameter helps to mitigate this problem, because long series of INSERTs are the main cause of too large batches. - If this limit is exceeded, an error is returned.
- If a batch contains too many tokens, the SQL compiler will use too much memory for the AST tree.
Remarks
This statement doesn’t exist in MS SQL Server.
Only sa is allowed to execute this statement.
Most parameters take effect only after a server restart.
The command SHOW P
displays the server parameters.
The listening address cannot be specified with the ALTER SERVER PARAMETER
statement.
- The listening address is specified in the file
listener_address.cfg
located in the base directory (see Installation (Linux) page).
Examples
ALTER SERVER PARAMETER SET SERVER_SERVERNAME = 'jupiter/Accounting';
ALTER SERVER PARAMETER SET SERVER_QUOTED_IDENTIFIER = OFF;
ALTER SERVER PARAMETER SET SERVER_BULK_DIR = '/home/john/mybulkdir';
ALTER SERVER PARAMETER SET SERVER_DEFAULT_DATABASE = accounting;
ALTER SERVER PARAMETER SET SERVER_DEFAULT_LANGUAGE = fr_fr;