Octopus and Partitioned Tables (MPP)
SAND CDBMS Octopus can start any database defined in the nucleus.ini configuration file. This functionality is called "Octopus On-Demand Database", or ODDB. When a client connects to Octopus in ODDB mode and references a database name other than the one defined for the Octopus instance, Octopus will look for the appropriate [DATABASE database-name] section in the nucleus.ini file. If found, an instance of that database will be started in either a read-only or updatable mode.
Parameters relating to ODDB functionality are configured in the nucleus.ini file. To enable ODDB operation for a particular Octopus instance, make the following entry in the appropriate [OCTOPUS] section:
Most of the parameters can be set in either the [DATABASE] or [OCTOPUS] sections. A setting in the [DATABASE] section affects only that Octopus On-Demand Database, while a setting in the [OCTOPUS] section affects all On-Demand Databases. If the same parameter is set in both sections, Octopus will use the one from the [DATABASE] section.
In a simple configuration with only one defined Subclass, that Subclass must have the NumberOfEngines parameter set to 1 or greater, and have no ODDBSubClass. The same Subclass will be used to start the required OctoEng and the additional ODDB instances. In a complex configuration with more than one Subclass, at least one Subclass must have the ODDBSubClass parameter set to TRUE with a NumberOfEngines value of 0, and at least one other Subclass with ODDBSubClass=FALSE (or omitted) with the NumberOfEngines parameter set to 0 or greater.
For a list of the sections of the nucleus.ini file in which each parameter can be included, see the table below.
Optionally, certain parameters (Cache, DeltaPath, MaxDeltaFileRatio, MaxDeltaFileSize, MaxVMem) can be prefixed by "ODDB". These parameters have the same effect whether the prefix is present or not. If both the "ODDB"-prefixed parameter and the standard parameter are present in the same section, the "ODDB"-prefixed one is used.
The full order in which Octopus looks for an ODDB-related parameter (apart from ODDBSubClass) is as follows:
- [DATABASE] section, "ODDB" prefix
- [OCTOPUS] section, "ODDB" prefix
- [DATABASE] section, standard parameter
- [OCTOPUS] section, standard parameter
- If it has one, the default value for the parameter is used.
For example, when initializing the Cache parameter for an Octopus On-Demand Database, Octopus will first look for "ODDBCache" in the ODDB [DATABASE] section, then it will look for "ODDBCache" in the [OCTOPUS] section; if not found, Octopus will next look for "Cache", first in the [DATABASE] section, then in the [OCTOPUS] section. Finally, if neither "ODDBCache" nor "Cache" are found, the default Cache value (64 MB) will be used.
The following are the ODDB-related parameters:
Name Description DatabasePath
The path to the ODDB primary database file (database-name.n00). Any DatabasePath parameters set in [NODE] sections are ignored for ODDB operations, as are DatabasePath parameters set in remote nucleus.ini [DATABASE] sections. Octopus uses only the DatabasePath parameter set in either the [OCTOPUS] or [DATABASE] sections of the local nucleus.ini file.
Default: (the DatabasePath value in the OCTOPUS section)
ODDBCache (or Cache)
The maximum amount of virtual memory (in megabytes) that will be allocated for database operations. If set in a DATABASE section, the setting applies to that database only. If set in the OCTOPUS section, the cache setting applies to all databases started as ODDB instances.
The comma-separated list of databases that can be started as ODDB instances. If a user requests a database not in this list, an "access denied" error message will be returned.
Note that each database in the list should have a corresponding [DATABASE database-name] section in the nucleus.ini file.
Default: (all databases)
ODDBDeltaPath (or DeltaPath)
The location of the Delta Files to which changes to the database will be written.
Default: . (current directory)
Setting this parameter to TRUE keeps the ODDB instance running after the last client has disconnected from it. The amount of time that the ODDB instance will keep running without any active client connections is specified using the ODDBKeepAliveTimeOut parameter (which is 43,200 seconds12 hoursby default).
The amount of time (in seconds) that an ODDB instance will keep running after the last client has disconnected from it, when the ODDBKeepAlive parameter is set to TRUE. Setting this to 0 or a negative number causes the ODDB instance to time out right away, which is the same as setting ODDBKeepAlive to FALSE.
Default: 43200 (equivalent to 12 hours)
Sets the amount of detail that will be written to the log file for each new ODDB instance. By default, the amount of detail is at the highest level, so this parameter can only be used to reduce ODDB logging.
- 0 (minimal information about the ODDB instance)
- 1 (ODDB instance information; parameter logging)
- 2 (full verbosity)
The maximum amount of cache (in megabytes) that can be used by all ODDB instances running on this Octopus instance.
- 0 (no limit on cache)
- n (cache limited to n megabytes, where n is a positive integer)
ODDBMaxDeltaFileRatio (or MaxDeltaFileRatio)
The maximum size of the ODDB instance's temporary Delta File, in which database changes are recorded, specified as a percentage of the database size. The default setting for ODDBMaxDeltaFileRatio is 0 (disabled), meaning the ODDBMaxDeltaFileSize parameter is used instead to specify the maximum Delta File size. If values for both ODDBMaxDeltaFileRatio and ODDBMaxDeltaFileSize are specified, and the ODDBMaxDeltaFileRatio value is not 0, the ODDBMaxDeltaFileRatio value is used.
ODDBMaxDeltaFileSize (or MaxDeltaFileSize)
The maximum size (in megabytes) of the ODDB instance's temporary Delta File, in which database changes are recorded, specified in megabytes. If values for both ODDBMaxDeltaFileSize and ODDBMaxDeltaFileRatio are specified, and the ODDBMaxDeltaFileRatio value is not 0, the ODDBMaxDeltaFileRatio value is used.
The maximum number of ODDB instances that can run on this Octopus instance.
- 0 (no limit on number of instances)
- n (the maximum number of instances, where n is a positive integer)
(UNIX only) Sets the maximum amount of virtual memory (in megabytes) that the OctoEng's will use. If ODDBMaxVMem is not set, a server will use the environments hard ulimit setting to determine its virtual memory limit, based on the hard ulimits relation to the minimum memory requirements for the server (Cache value + 1024 MB).
If the ODDBMaxVMem setting is less than the Cache value + 1024 MB (1 GB), it is automatically set to Cache + 1 GB. If the ODDBMaxVMem setting is greater than the hard ulimit setting, it is automatically set to the hard ulimit setting. If ODDBMaxVMem is not set and the hard ulimit setting is unlimited, ODDBMaxVMem defaults to Cache + 1 GB.
The maximum virtual memory settings are summarized in the table below:
ODDBMaxVMem is not set ODDBMaxVMem is set ulimit < (Cache + 1 GB) Maximum virtual memory is set to the hard ulimit value, which is below OctoEng s minimum memory requirements (Cache value + 1 GB), so the server will fail to start. ODDBMaxVMem < (Cache + 1 GB) Maximum virtual memory is set to (Cache value + 1 GB). ulimit > (Cache + 1 GB) Maximum virtual memory is set to the hard ulimit value. ODDBMaxVMem > (Cache + 1 GB) Maximum virtual memory is set to the ODDBMaxVMem value, up to a maximum of the hard ulimit value. ulimit is unlimited Maximum virtual memory is set to (Cache value + 1 GB). ulimit is unlimited Maximum virtual memory is set to the ODDBMaxVMem value.
A setting of 0 removes any limits on virtual memory usage by the server, although it is still bound by the hard ulimit. However, if the hard ulimit is unlimited, a setting of 0 means that the OctoEng will attempt to use as much memory as possible for its operations, irrespective of other, non-SAND processes. This situation is not recommended unless the machine is dedicated to running SAND operations. Otherwise, it is best to ensure that a certain amount of system memory is reserved for non-SAND processes.
Minimum: Cache value + 1024 MB
Maximum: the hard ulimit setting
Default: Cache value + 1024 MB (set only if ODDBMaxVMem is undefined and the hard ulimit is unlimited)
The prioritized, comma-separated list of nodes where the ODDB instance should start. Octopus will attempt to start the database as an ODDB instance on the first node in the ODDBPreferredNode list. If that attempt fails, another attempt will be made to start the instance on the next node in the list. And so on down the list until the ODDB instance successfully starts on a node, or the list of nodes is exhausted (resulting in an error condition).
The asterisk wildcard character (*) can be used to indicate "any available node". If included in the ODDBPreferredNode list, it should appear last so that "any available node" will only be used if the specific nodes listed before the '*' are not accessible.
Each of the following configurations will produce the default behavior, which is to use any available node to start ODDB instances:
- (omit ODDBPreferredNode parameter)
This parameter is set in DATABASE sections only.
This parameter determines if the Octopus Agent will prioritize queued requests to start ODDB instances based on group membership, or else serve the instances on a first-come, first-serve basis.
Priority is given to the groups in the comma-separated ODDBPriority list, reading from left to right. When ODDB instance requests are queued, groups that are earlier in the ODDBPriority list are served before ones later in the list, and all groups have priority over instances that do not belong to any group.
A group name is specified using the ODDBPriorityGroup parameter in the [Octopus instance-name] section.
- FIFO (no prioritizing)
- group-name1[,group-nameN]... (the comma-separated list of groups in order of priority)
The priority group to which this Octopus instance belongs. All ODDB instances requested on this configuration will belong to this group. Queued ODDB instance requests will be served by Octopus Agents according to the group priority order, specified by the Agent ODDBPriority parameter.
Note that the priority group name can be arbitrary.
Default: (unnamed or default group)
Sets the public ODDB flag. If a database is designated as a public On-Demand Database, it will be included in a comma-delimited ODDB list returned by Octopus to programs (for instance, OctoInfo or "nisqlm -l") that request this information. This flag does not actually affect ODDB functionality; a non-public On-Demand Database can be started by Octopus, as long as the client provides the appropriate database name and it is not implicitly restricted by the ODDBDatabases parameter.
Specifies the ODDB operation mode, which is either ReadOnly or Update. ODDB ReadOnly is the same as the standard Octopus ReadOnly mode: SQL statements that attempt to change the database will have no effect. In contrast, Update mode allows changes, and will also accept SHUTDOWN and KEEP CHANGE commands.
Specifies whether an ODDB instance can be shared among multiple sessions of the same or different users. Setting this parameter to TRUE (the default) enables sharing. Setting this parameter to FALSE causes Octopus to start a separate ODDB instance for each session.
Specifies whether the Subclass can be used to start ODDB instances. Only Subclasses that have this parameter set to TRUE will be used for ODDB operations.
Defaults: TRUE (if there is only one Subclass)
FALSE (if there are multiple Subclasses)
If ODDBMaxCache is enabled, the ODDBWaitForCacheFit parameter determines whether the Agent will service ODDB instance requests strictly in the queued order (waiting for cache space to become available, if necessary), or whether requests will be filled according to the available cache space. In the latter case, if the next queued ODDB instance request cannot fit into the available cache, the Agent will skip that entire Octopus request queue and look for a request in another queue, assuming there are multiple Octopus instances with requests.
- TRUE (serve the next queued ODDB instance request, waiting for cache space to free up if necessary)
- FALSE (serve the next queued ODDB instance request that fits into the currently available cache)
Defaults: FALSE (if ODDBPRIORITY is set to FIFO)
TRUE (if ODDBPRIORITY is set to a list of groups)
Optionally, PassEnv can be used to specify custom parameter(s) that will be added to the end of the NUCLEUS environment variable for the current Octopus session. Note that settings made in the NUCLEUS variable override those made in the nucleus.ini file.
Parameters are defined in the following manner:
PassEnv=<parameter name>:<parameter value>
There are two ways that multiple parameters can be specified through PassEnv (and both methods can be combined).
- Define multiple parameters in the same PassEnv parameter definition, with each specified parameter/value combination separated by a space.
- Define multiple PassEnv parameters: there can be one in the [OCTOPUS] section and one in the [SUBCLASS] section. If there are PassEnv parameters in both sections, the user-specified parameters from the [SUBCLASS] section will be appended to the NUCLEUS environment variable first, followed by the parameters from the [OCTOPUS] section.
The amount of time (in seconds) before an SQL query executed against the Octopus On-Demand Database is cancelled. Specify 0 (or omit this parameter altogether) to remove all limits on the length of time a query may execute.
Note that setting this parameter has the same effect as issuing the SET TRANSACTION TIMEOUT SQL command. If both RescheduleTimeout and SET TRANSACTION TIMEOUT are set with differing values, the effective timeout period is the lower of the two.
ODDB Parameters by nucleus.ini Section
The following is a list of the ODDB parameters, listed under the sections where they can be used:
[OCTOPUS...] [DATABASE...] [OCTOPUS AGENT] [SUBCLASS...] OctoRunMode=ODDB DatabasePath ODDBMaxCache ODDBSubclass ODDBDatabases ODDBPreferredNode ODDBMaxServer PassEnv ODDBKeepAlive ODDBKeepAlive ODDBPriority ODDBKeepAliveTimeout ODDBKeepAliveTimeout ODDBWaitForCacheFit ODDBLogLevel ODDBPublic ODDBMaxCache ODDBRunMode=ODDB ODDBMaxServer ODDBShareDB ODDBPriorityGroup RescheduleTimeout ODDBPublic [ODDB]Cache ODDBShareDB [ODDB]DeltaPath PassEnv [ODDB]MaxDeltaFileRatio RescheduleTimeout [ODDB]MaxDeltaFileSize [ODDB]Cache [ODDB]MaxVMem [ODDB]DeltaPath [ODDB]MaxDeltaFileRatio [ODDB]MaxDeltaFileSize [ODDB]MaxVMem
By default, any database with its own section in the nucleus.ini file can be started as an ODDB instance. After an Octopus instance has been started with OctoRunMode=ODDB, a client can start an ODDB instance by connecting to the Octopus, but specifying another database name. If the specified database is defined in the nucleus.ini file, and the ODDBDatabases parameter does not implicitly exclude the database, it will be started by Octopus.
Clients can view a list of "public" ODDB databases (databases in the nucleus.ini file that have their ODDBPublic parameter set to "TRUE"), for example, through the Nucleus ODBC Driver connection dialog or via the nisqlm -l command. However, a database does not have to be public in order to be started as an ODDB instance.
Each ODDB instance can be started in two modes: ReadOnly and Update. ReadOnly is the same as the Octopus ReadOnly mode; SQL statements that change the database have no effect. Update is equivalent to Persistent Mode; it allows any update, and will also accept "SHUTDOWN" and "KEEP CHANGE" commands.
ODDB instances are started only on Subclasses that have the property ODDBSubClass set to TRUE. In more complex configurations where there are multiple Nodes with Subclasses, ODDB instances are normally started on Nodes in a reverse "round robin" manner. Assuming each Node has the ODDBSubClass parameter enabled, the first ODDB instance request is directed to the last Node in the [OCTOPUS x] Nodes list; the next request is directed to the second last Node in the list; and so on, up to the first Node in the list; the very next request is sent to the last Node again, repeating the order in which Nodes are assigned new ODDB instances. However, the order in which requests are handled can change, depending on the configuration options. View the Distribution of ODDB Requests Across Nodes section below for further information.
There is no rescheduling the starting of ODDB instances. If a request is made for an ODDB instance and that request times out, an error message will be returned to the client making the request, who will then have to repeat the request.
Note that the NumberOfEngines parameter defined for the Subclass has no bearing on the number of ODDB instances that can be started on a Node. The maximum number of ODDB instances is limited by the ODDBMaxServer and/or ODDBMaxCache parameters, or else if those parameters are set to unlimited, by the available system resources.
Once an ODDB instance is running, the user that made the request can execute queries against the database. If the ODDBShareDB parameter has been set to TRUE (the default), another user that requests the same database will connect to the same instance; otherwise, Octopus will start a separate ODDB instance for the second user.
An ODDB instance can be manually shut down in two ways (both require DBA privileges):
- Run the OctoShutdown program against the ODDB instance
- Issue an SQL SHUTDOWN command via nisqlm or another client program connected to the ODDB instance.
An ODDB instance will automatically shut down if there are no longer any clients connected to that instance and the ODDBKeepAlive parameter is set to FALSE (the default).
Distribution of ODDB Requests Across Nodes
If all Octopus Agents/Nodes either allow unlimited ODDB instances (ODDBMaxServer=0) or place a strict limit on ODDB instances (ODDBMaxServer > 0), the reverse round robin scheme for distributing instance requests will be used.
In a mixed environment, where some Nodes are unlimited and others are limited, each unlimited Node will run only a single ODDB instance until the limited Nodes have all reached their maximum number of ODDB instances. At that point, the unlimited Nodes will accept more ODDB instance requests. If the ODDB instance count for any limited Node falls below the maximum, that Node will have priority over unlimited Nodes for the next instance request.
Specifying Exact Nodes for ODDB Instances
The ODDBPreferredNode parameter, set in DATABASE sections of the nucleus.ini file, can be used to specify a comma-separated list of nodes on which to start ODDB instances of the database. For example:
Using the configuration above, Octopus will first attempt to start an ODDB instance on the first node in the list (alpha). If that fails, another startup attempt will be made on the next node in the list (beta). If none of the nodes (alpha, beta, and gamma) are accessible, an error will be generated, as the instance cannot be started on the specified hosts.
Note that there is no internal checking to verify that all of the nodes in the ODDBPreferredNode list actually exist. If a node is not found or is otherwise unavailable, Octopus will simply move on to the next node in the list, or else return an error if there is no next node.
The asterisk wildcard character (*) can be used in the list to indicate "any available node". If included, the wildcard character should appear last in the list so that "any available node" will only be used if the specific nodes listed before it are not accessible. For example:
The setting above instructs Octopus to first use node delta to start the ODDB instance. If that fails, startup should be attempted on node epsilon next. If the ODDB instance could not be started on either delta or epsilon, the '*' that follows in the list tells Octopus to then use any available node to start the instance.
On the other hand, a user might want to limit ODDB instances of a database to one particular node, in which case only that node would be specified for the ODDBPreferredNode parameter. In the following example, ODDB instance will either start on node zeta or not at all:
By default, any available node is used to start an ODDB instance. This default behavior is produced by all of the following configurations:
- (omit ODDBPreferredNode parameter)
Prioritizing ODDB Requests
In environments where all Nodes limit the ODDB instance count, multiple prioritization options exist:
All Nodes have ODDBPriority=FIFO, ODDBWaitForCacheFit=FALSE, and ODDBMaxCache > 0
Nodes serve ODDB instance requests in the expected order, but if there are multiple Octopus request queues and the next queued ODDB instance request requires more cache than is currently available for the Node, the Node will skip that queue entirely and jump to the next ODDB instance request in another queue.
Note that, depending on the workload, there is the possibility that a particular Octopus request queue might never get served.
All Nodes have ODDBPriority=FIFO, ODDBWaitForCacheFit=True, and ODDBMaxCache > 0
Nodes serve ODDB instance requests in the expected order. If an ODDB instance request requires more cache than is currently available for the Node, the Node will wait for the required cache space to become available from older ODDB instances expiring before starting the new ODDB instance.
In this scenario, multiple Nodes might be running well below their ODDBMaxServer setting, while waiting for cache space to free up.
Some Nodes have ODDBPriority=FIFO, ODDBWaitForCacheFit=True, and ODDBMaxCache > 0
With some Nodes defining a priority group order, it is best to "reserve" those Nodes for specific conditions, for example, large cache Octopus requests. Consider the following configuration file fragment:
And a particular Node has the following configuration:
In this example, the system will prioritize in a FIFO manner, up to the point of ODDB request contention. At that point, the Agent/Node listed above will prioritize queued ODDB requests in the following order:
- ODDB instance requests on "Brobdingnag" (priority group "bigcache")
- ODDB instance requests on "Gulliver" (priority group "midcache")
- ODDB instance requests on "Lilliput" (priority group "smallcache")
Thus the Node in this example will serve the large cache requests before the medium-sized cache requests, and the medium-sized cache requests before the small cache requests.
Logs and Monitoring
ODDB-related information is written to the Octopus log file. The amount of detail recorded can be configured using the ODDBLogLevel parameter. By default, ODDB logging is verbose.
OctoInfo has additional sections to accommodate ODDB functionality:
- An On Demand Database (ODDB) Server section, which contains information about all running ODDB Servers.
- An ODDB PublicDatabases section, which lists the public databases started on an ODDB Server.
- An ODDB Node Information section, which displays priority groups, maximum server, and maximum cache information for each ODDB-enabled Node.
For information, consult the Using the OctoInfo Program section.
Octopus and Partitioned Tables (MPP)