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 options:
Option 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 ( sqlorha)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_serverssection. 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_clientorsql_serverssectionssql_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_portspecified 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:portpairs defined as "<IP/hostname>:<port>". If data compression is used for communications between server and clients, with this format the globalcompression_levelmust be specified for all clients in parametersql_compression_level.Or a JSON object containing vector
shardscan be used to specify each shard'saddressandcompression_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