Configuring s-Server

<< Click to Display Table of Contents >>

Navigation:  Administering SQLstream Blaze >

Configuring s-Server

Previous pageReturn to chapter overviewNext page

System parameter configuration

You can customize the operation of the SQLstream s-Server in a number of ways, through SQLstream s-Studio (the user interface) and other components such as plugins and drivers. Note that a Plugin JAR file added to the standard $SQLSTREAM_HOME/plugin directory is not automatically added to the SQLstream s-Server CLASSPATH unless a symlink to that JAR is put into the $SQLSTREAM_HOME/plugin/autocp directory (see the Readme file in the $SQLSTREAM_HOME/plugin/autocp directory).

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.1.0.XXX.

This chapter examines each of these mechanisms and the standard parameters they control.

You can set and read parameters in several ways. It is important to distinguish how and where you do so because, for example, you need to know which parameters override others. In general, changes to properties should be put in $SQLSTREAM_HOME/aspen.custom.properties: see Overriding Java Parameters.

In broad terms, the parameters you can configure are these:

Repository parameters
Java parameters
Tracing parameters
Adapter and SQL/MED parameters

Data and Wallclock time

SQLstream strongly recommends that administrators use NTP (Network Time Protocol) on all computers to ensure that system clocks are closely synchronized. However, the system will still operate correctly if there is an offset between clocks.

Time Zone

By default, s-Server and s-Studio are set to operate in UTC/GMT. SQLstream strongly recommends leaving this setting intact unless you are absolutely sure that all SQLstream components (including all agents) are running in the same time zone. Time zone is set by a JVM parameter called user.timezone defined in th e defintAspenRuntime.sh script.

Repository parameters

The SQLstream repository contains all the definitions of objects (schemas, streams, views, etc.) defined in your system. In addition to these objects, the repository holds various parameters regarding the internal operation of the repository itself, called system parameters.

You set repository parameters using SQL:

ALTER SYSTEM SET <name> = <value>;

ALTER SESSION SET <name> = <value>;

 

Note that parameter names are mixed-case identifiers, so you will need to enclose them in double quotes. For example, to set the databaseMaxSize parameter to 100000, you would type

ALTER SYSTEM SET "databaseMaxSize" = 100000;

 

The SYSTEM and SESSION designators determine the scope of the parameter being set. There are three levels of scope for a repository parameter: SYSTEM, DEFAULT, and SESSION, as explained in the following table.

Scope level

Meaning

SYSTEM

Value applies to the whole system (all nodes, all sessions).

DEFAULT

Value applies to all SESSIONs that do not specifically override the value.

SESSION

Value applies only to the current session.

SYSTEM and DEFAULT scope persist across restarts of the server. SESSION scope applies only to a running session. For details, see the topics ALTER SESSION and ALTER SYSTEM in the SQLstream SQL Reference Guide.

System parameters

System parameters determine the extent and behavior of the SQLstream s-Server. You can set the values for the entire system or for a particular session, using the ALTER SYSTEM and ALTER SESSION commands. Different parameters may take effect at different times, as indicated by the "Update Policy" entries in the following table.

Most parameter changes take place either immediately or the next time the system is started. A few are immutable, but are included in the table for reference; treat them as read-only parameters.

Note, however, that configuring SQLstream s-Server to accept JDBC driver connections from other hosts, even if the server is behind a NAT router, requires additional preparation as described in the subtopic Configuring JDBC Driver Connections from Other Hosts below.

Parameter

Type and Usage

Default Value

Update Policy

cachePagesInit

32-bit integer (number of pages)

Sets number of memory buffers to allocate from the OS for Fennel's data cache (also used as a pool for volatile scratch memory allocated by ExecStreams). Increasing it after startup causes new free buffers to be allocated; decreasing it causes buffers to be released (flushing them first if they contain dirty data). Note that this memory is NOT part of the JVM heap, although it shares the same virtual memory address space.

5000

Immediate.

cachePageSize

32-bit integer (number of bytes; must be a power of 2). Determines contiguous size of memory buffers allocated by cache. Also determines contiguous size of pages stored on disk; these are the elemental allocation units for all disk-based data structures such as indexes.

65536

Immutable after database creation.

cachePagesMax

32-bit integer (number of pages). Determines upper bound for number of pages cache can allocate. On startup, actual upper bound is set to the larger of this parameter and cachePagesInit. Subsequently, changes to cachePagesInit are limited by upper bound calculated at startup.

70000

Takes effect on startup.

cacheReservePercentage

32-bit integer (percentage between 1 and 99, inclusive). Usage: The percentage of the Fennel data cache that should be set aside for global resource usage. The remainder will be available to allocate to individual statements.

5

Immediate.

calcVirtualMachine

String, one of CALCVM_FENNEL, CALCVM_JAVA, or CALCVM_AUTO. Usage: Controls which calculator virtual machine is used for evaluating SQL row expressions. CALCVM_FENNEL uses a virtual machine implemented inside Fennel. CALCVM_JAVA generates and compiles Java code on the fly. CALCVM_AUTO allows the optimizer to choose a blend of the two calculators based on cost or other factors. Changing this parameter causes all entries to be discarded from the code cache.

CALCVM_AUTO

Immediate.

checkpointInterval

32-bit integer (seconds, 0 to disable timer-based checkpoints). Sets the interval between automatic timed checkpoints. Regardless of this setting, checkpoints may occur at other times as well (e.g. when the system detects that free log space is low, or when an administrator issues the explicit CHECKPOINT command).

300

Takes effect on startup.

codeCacheMaxBytes

64-bit integer (number of bytes, 0 for disabled, -1 for unlimited). Usage: Sets the size of the Farrago code cache, which is used for optimizer plan caching and SQL/MED data wrapper pooling. Setting to MAX causes the cache to grow without bound except for the JVM heap size. Setting to MIN disables the code cache, discarding any currently cached entries. Setting to a finite value causes LRU victimization according to rough estimates on per-object memory usage.

2000000

Immediate.

databaseIncrementSize

32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the db.dat file when all existing pages have been allocated.

1000

Takes effect on startup.

databaseInitSize

32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of Fennel's db.dat file.

1000

Only used at time of database creation; immutable after that.

databaseMaxSize

32-bit integer (number of pages, 0 for unlimited). Determines maximum size to which db.dat file can grow. Further allocations will fail even if space remains in the file system.

0

Takes effect on startup.

databaseShadowLogIncrementSize

32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the shadowlog.dat file when all existing pages have been allocated.

1000

Takes effect on startup.

databaseShadowLogInitSize

32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the shadowlog.dat file. This file is recreated every time Fennel is loaded, and is used as part of physical recovery.

2000

Takes effect on startup.

databaseTxnLogIncrementSize

32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the txnlog.dat file when all existing pages have been allocated.

1000

Takes effect on startup.

databaseTxnLogInitSize

32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the txnlog.dat file. This file is recreated every time Fennel is loaded, and is used as part of logical recovery.

2000

Takes effect on startup.

deviceSchedulerType

String (name of scheduler implementation). Controls the scheduler used for disk I/O. threadPool means to fake asynchronous I/O via a thread pool, and use buffered file access. On Linux, aioLinux means to use libaio for asynchronous I/O, with O_DIRECT for unbuffered file access. The default is to to use libaio if available, otherwise to fallback to threadPool. On Windows, ioCompletionPort (the default) means to use asynchronous I/O via completion ports and unbuffered file access. In all cases, if an unrecognized or unsupported value is set, the system silently reverts to default behavior to avoid causing startup failures.

null (let system decide)

Takes effect on startup.

expectedConcurrentStatements

32-bit integer (number of statements). Indicates to the resource governor what is expected to be the maximum number of concurrent statements executing at any given time. It is used by the resource governor to determine the maximum resource allocation for each statement.

4

Immediate.

fennelDisabled

boolean. Determines whether Fennel native code components are loaded when the system starts up.

false

Takes effect on startup.

groupCommitInterval

32-bit integer (milliseconds). Determines the amount of time to wait during commit in the hope that the commit's disk write can be combined with that of other concurrent transactions.

30

Takes effect on startup.

jniHandleFile

String (file-system path). See Fennel Jni Handle Tracing

{empty string}

Takes effect on startup.

resourceDir

String (file-system path). Sets the location of FennelResource.properties (or localized variant).

$SQLSTREAM_HOME/s-Server/

catalog/fennel

Takes effect on startup.

sdpListenerPort

32-bit integer, -1 for personality-based defaults. Sets the port number on which to listen for SDP connections (This is the port number specified in client JDBC URL's). However, for connections from other hosts, see also the Network Configuration subsection below entitled Configuring JDBC Driver Connections from Other Hosts.

-1 (implies 5570 for SQLstream personality)

Takes effect on startup.

tempIncrementSize

32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the temp.dat file when all existing pages have been allocated.

1000

Takes effect on startup.

tempInitSize

32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the temp.dat file. This file is recreated every time Fennel is loaded.

1000

Takes effect on startup.

Reading system parameters

To read the values of the system parameters, you can execute the following two queries, either using sqllineClient or SQLstream s-Studio:

SELECT * FROM SYS_FEM."Config"."FennelConfig";

SELECT * FROM SYS_FEM."Config"."FarragoConfig";

Each system parameter has its own column, named for the parameter. The value in the column is the current value of the parameter.

Java parameters

Most of the SQLstream system is written in Java, so some of its behavior can be controlled using Java properties. Some of those parameters are passed on the command-line to the Java virtual machine by the startup script, s-Server and the auxiliary script defineAspenRuntime.sh. You can also set parameter values for the server by using the file $SQLSTREAM_HOME/aspen.properties.

Java properties are only used to control bootstrap behavior (startup activities before the server is up and running) such as setting initial tracing and network configuration.

The currently-implemented Java parameters all deal with Network Configuration. Fore more details, see the topic Network Configuration in this guide.

Overriding Java parameters

You should not edit the file aspen.properties itself. Rather, if you want to override or add a value, put that parameter into a file called aspen.custom.properties in the $SQLSTREAM_HOME/s-Server directory. This enables you to make your own set of changes without having to modify the standard parameters.

When the SQLstream s-Server starts up, it reads its Java parameters in the following order, with each successive source potentially overriding the preceding ones:

1.Parameters from aspen.properties (read only)
2.Parameters from aspen.config (read only)
3.Parameters from aspen.custom.properties
4.Java system parameters with names beginning with "aspen."

As you can see, your entries in aspen.custom.properties can override the standard settings distributed in aspen.properties or aspen.config, so there is no need to modify those files.

If you need to change the parameters passed to the Java runtime, such as the class path or memory sizes, you will need to make those changes to the script $SQLSTREAM_HOME/bin/defineAspenRuntime.sh.

Parameter substitution

Java parameters can include other variables in their definitions. The syntax for these references is much like that used for variables in a shell script, a dollar sign followed by the name of the referenced variable inside curly braces, such as ${aspen.home.dir}. Undefined parameters are treated as empty strings.

Thus, you can define file paths relative to the SQLstream installation directory, for example:

my.file.name=${aspen.home.dir}/temp/myfile.name

 

The same syntax can also be used within SQL/MED and adapter parameters.

Configuring JDBC Driver Connections from Other Hosts

The instructions in this section enable you to configure the SQLstream s-Server to accept JDBC driver connections from other hosts, even if the server is behind a NAT router.

SDP Requirements

The SQLstream JDBC driver connects to SQLstream s-Server using SDP. SDP requires that the hostnames match at both ends of a remote connection. That means that the server must have

An IP address reachable from client systems
A host name that resolves to that address for the client (either through DNS or an explicit host name mapping such as an entry in the client's /etc/hosts file)
Configuration files that use the resolvable host name or the explicit IP address

Here are the configuration requirements:

/etc/hosts

Many Linux systems will, by default, assign a system's host name to the loopback interface (IP address 127.0.0.1). For a server installation that other systems will connect to, you need to ensure that the host name is explicitly assigned to the external IP address:

127.0.0.1 localhost

a.b.c.d hostName.domain hostName

 

$SQLSTREAM_HOME/aspen.properties

The aspen.properties file needs to specify the host name of the server in a way that can be resolved by client systems or else use the IP address:

aspen.sdp.host=<hostName or a.b.c.d>

JDBC URI

The client system connects to the server via a URI that uses the host name (or IP address) just as specified in aspen.properties:

jdbc:sqlstream:sdp://<hostName>:<port>, autoCommit=false

jdbc:sqlstream:sdp://<a.b.c.d>:<port>, autoCommit=false

 

The port specified in aspen.controlnode.url must match aspen.sdp.port. The hostName or IP address specified in aspen.controlnode.sdp.host and aspen.controlnode.url must