# xSQL SQL Parameters

> The xSQL configuration file can be used to define parameters that affect SQL operation.

> ### For an overview see page Configuration File Parameters

> These parameters consist of "name : value" pairs that can appear anywhere in the configuration file outside of other sections. For example:

>         db_params : {
>             mem_page_size : 256,
>             disk_page_size : 4096,
>             db_max_connections : 100,
>             ...
>         }

>         sql_scripts : [ script_1.sql, script_2.sql ],

>         sql_statements: "create table A (i integer);"
>         ...

> The following table lists the possible parameters:

> Parameter name : example value | Comment
> --- | ---
> sql_trace: false, | Enable / Disable SQL engine trace, boolean
> sql_port : 5001, | Listen port for SQL server, unsigned. As explained on the Network Communications page, valid IPv6 and IPv4 SQL port specifications can be of the following forms:
>         sql_port : 5001,
>         sql_port : "127.0.0.1:5001",
>         sql_port : "[::1]:5001",
> sql_local_sockets : true | Use Unix domain sockets for client-server interaction
> sql_n_replicas : 1, | Redundancy level, unsigned
> sql_connect_attempts : 10, | Maximum number of attempts to connect to the server, unsigned. Default value 10
> sql_connect_timeout : 2000, | Timeout for each connect attempt in milliseconds, unsigned. Default value 2000 (2 seconds)
> sql_read_timeout : 120000, | Timeout for read operations in milliseconds, unsigned. Default value 1200000 (20 minutes)
> sql_replication_type : sql, | (DEPRECATED)Replication method (`sql` or `ha`)
> sql_comm_buffer: 256k, | Size of SQL server transfer buffer in bytes, unsigned. Default 64k sql_comm_buffer: 256k,
> sql_server_threads : 8, | Number of SQL server threads
> sql_server_gc_threads: | The number of dedicated GC (garbage collection) threads that cleanup unused MVCC versions. Default value is 0
> sql_use_connection_pool : false | Enable / Disable the use of a thread pool for parallelism with sequences, boolean. Default value true
> sql_interrupt_timeout : 1000, | Interrupt timeout period in milliseconds to poll the interrupts from the client(s). If 0, disable interruption. Alternatively the Interrupt timeout can be specified in the `sql_servers` section. For example:
>         sql_servers : [
>             { port : 5000, interrupt_timeout : 1000 },
>             { port : 5001, interrupt_timeout : 1000 },
>         ]
> sql_n_shards :2 , | Number of database shards
> sql_compression_level : 1, | The global compression level for all remote clients. This value can be overridden in the `remote_client` or `sql_servers` sections
> sql_workspace_limit : 1g, | Maximum amount of memory SQL can use for internal allocators. Default 0 (no limit)
> sql_authentication_required : true | Require authentication for clients for remote SQL connections, boolean. Alias 'sql_auth_required' is supported for backward compatibility
> sql_login : 'user1', | Login username when authentication is required
> sql_password : 'mypass', | Login password when authentication is required
> sql_optimizer_params : {...} | Tuning parameters for SQL optimizer:
>         sql_optimizer_params : {
>             # Size of buffer used for batch insert, unsigned,
>             # default 1024
>             batch_insert_buffer_size : 1024,

>             # Preload existing UDFs, the default is false
>             preload_udf : true
>             # If set to true, the optimizer chooses the optimal join order for each table, the default is false
>             optimize_joins : true
>         },
> sql_servers : [ {...} ] | Array of SQL server descriptions. xSQL will start the specified number of servers (care must be taken to avoid conflicts with `sql_port` specified elsewhere.)
>         sql_servers : [
>          {
>             # Listen port for SQL server instance, unsigned.
>             port :5000,

>             # If true, enable "RSQL backwards compatibility" mode so that applications linked with old (pre-8.2) eXtremeDB packages will be able to connect.
>             protocol_compatibility : false,

>             # Size of SQL server transfer buffer in bytes, unsigned.
>             # Default is 'sql_comm_buffer'
>             comm_buffer    : 64k,

>             # Number of SQL server threads.
>             # Default is 'sql_server_threads'
>             server_threads : 8,

>             # Use Unix domain sockets for client-server interaction.
>             # Default is sql_local_sockets'
>             local_socket   : true,

>             # Require authentication for clients, boolean. Default value 'false'
>             auth_required : false,

>             # Period in milliseconds to poll the interrupts from the client(s).
>             # If 0, disable interruption. Default value 100
>             interrupt_timeout : 500,

>             # Number of SQL server threads. Default is 'sql_server_gc_threads'
>             gc_threads : 2,

>             # Period of activating GC threads in milliseconds.
>             # Default value is 100 msec
>             gc_period : 100,

>             # The level of compression: 0 = no compression; 1 = best speed,
>             # 9 - best compression. Default value is 0
>             compression_level : 1,

>             # SSL settings specific for this server. See the global
>             # ssl_params section for the description of the parameters
>             # and their default values. These parameters override the
>             # global settings. To inherit the global ssl_params settings,
>             # leave the ssl_params section empty.
>             ssl_params : {
>                 cipher_list : "HIGH:!aNULL:!kRSA:!PSK:!SRP:!MD5:!RC4",
>                 max_cert_list : 102400,
>                 verify_mode : [verify_peer, verify_fail_if_no_cert],
>                 verify_depth : 100,
>                 cert_file_pem : "sqlservercert.pem",
>                 pkey_file_pem : "sqlserverkey.pem"
>             }
>          },
>         ]
> remote_client :[ "192.168.1.100:5001", "192.168.1.102:5001" ], | An array of remote nodes ("shards") to connect in from can be specified as a JSON vector of `host:port` pairs defined as "<IP/hostname>:<port>". If data compression is used for communications between server and clients, with this format the global `compression_level` must be specified for all clients in parameter `sql_compression_level`. Or a JSON object containing vector `shards` can be used to specify each shard's `address` and `compression_level`. For example:
>         remote_client : {
>             shards: [
>                {
>             	address : "192.168.1.100:5001",
>                    compression_level : 1,
>                },
>                {
>             	address : "192.168.1.102:5002",
>                    compression_level : 9,
>                },
>            ],
>         }
> sql_scripts : [ script_1.sql, script_2.sql ], | Array of scripts (filenames) to execute
> sql_statements: "create table A (i integer);" | In-place SQL script
> free_memory_threshold : 4m, | Threshold to abort the current transaction if amount of free memory becomes lower. Unsigned, default 1Mb