Parallel DSI parameters

You can customize the parallel DSI thread environment using the configuration parameters shown in Table 4-6. Use these configuration parameters with alter connection to tune parallel DSI threads for individual connections.

Table 4-6: Parallel DSI configuration parameters

Configuration parameter

Description

dsi_commit_check_locks_intrvl

The number of milliseconds (ms) the DSI executor thread waits between executions of the rs_dsi_check_thread_lock function string.

Default: 1000 ms (1 second)

Minimum: 0

Maximum: 86,400,000 ms (24 hours)

dsi_commit_check_locks_log

The number of times the DSI executor thread executes the rs_dsi_check_thread_lock function string before logging a warning message.

Default: 200

Minimum: 1

Maximum: 1,000,000

dsi_commit_check_locks_max

The maximum number of times the DSI executor thread executes the rs_dsi_check_thread_lock function string before rolling back and retrying a transaction.

Default: 400

Minimum: 1

Maximum: 1,000,000

dsi_commit_control

Specifies whether commit control processing is handled internally by Replication Server using internal tables (on) or externally using the rs_threads system table (off).

Default: on

dsi_ignore_underscore_names

When the dsi_partitioning_rule is set to “name,” specifies whether or not Replication Server ignores transaction names that begin with an underscore. Values are “on” and “off.”

Default: on

dsi_isolation_level

Specifies the isolation level for transactions. ANSI standard and Adaptive Server supported values are:

  • 0 – ensures that data written by one transaction represents the actual data.

  • 1 – prevents dirty reads and ensures that data written by one transaction represents the actual data.

  • 2 – prevents nonrepeatable reads and dirty reads, and ensures that data written by one transaction represents the actual data.

  • 3 – prevents phantom rows, nonrepeatable reads, and dirty reads, and ensures that data written by one transaction represents the actual data.

Through the use of custom function strings, Replication Server can support any isolation level the replicate data server may use. Support is not limited to the ANSI standard only.

Default: the current transaction isolation level for the target data server

dsi_large_xact_size

The number of statements allowed in a transaction before it is considered to be a large transaction.

Default: 100

Minimum: 4

Maximum: 2,147,483,647 (in bytes)

dsi_max_xacts_in_group

Specifies the maximum number of transactions in a group. Larger numbersmay improve data latency at the replicate database.

Range of values: 1 – 1000.Default: 20

dsi_max_cmds_in_batch

Defines maximum number of source commands for which output commands can be batched.

Range: 1 – 1000

Default: 100

dsi_num_large_xact_threads

The number of parallel DSI threads to be reserved for use with large transactions. The maximum value is one less than the value of dsi_num_threads.

Default: 0

dsi_num_threads

The number of parallel DSI threads to be used for a connection. A value of 1 disables the parallel DSI feature.

Default: 1

Minimum: 1

Maximum: 255

dsi_partitioning_rule

Specifies the partitioning rules (one or more) the DSI uses to partition transactions among available parallel DSI threads. Values are origin, origin_sessid, time, user, name, none, and ignore_origin. See “Partitioning rules: reducing contention and increasing parallelism” for detailed information.

Default: none

dsi_serialization_method

Specifies the method used to determine when a transaction can start, while still maintaining consistency. In all cases, commit order is preserved.

These option methods are ordered from most to least amount of parallelism. Greater parallelism can lead to more contention between parallel transactions as they are applied to the replicate database. To reduce contention, use the dsi_partition_rule option.

  • no_wait – specifies that a transaction can start as soon as it is ready, without regard to the state of other transactions.

    NoteYou can only set dsi_serialization_method to no_wait if dsi_commit_control is set to “on”.

  • wait_for_start – specifies that a transaction can start as soon as the transaction scheduled to commit immediately before it has started.

  • wait_for_commit (default) – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it is ready to commit.

  • wait_after_commit – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it has committed completely.

These options are retained only for backward compatibility with earlier versions of Replication Server:

  • none – same as wait_for_start.

  • single_transaction_per_origin – same as wait_for_start with dsi_partitioning_rule set to origin.

  • isolation_level_3 – same as wait_for_start with dsi_isolation_level set to 3.

dsi_sqt_max_cache_size

The maximum SQT cache size for the outbound queue in bytes. The default, 0, means the current setting of the sqt_max_cache_size parameter is used as the maximum cache size for the connection.

See “Sizing the SQT cache” for more information about setting the SQT cache size.

Default: 0

For 32-bit Replication Server:

  • Minimum – 0

  • Maximum – 2,147,483,647 (in bytes)

For 64-bit Replication Server:

  • Minimum – 0

  • Maximum – 2,251,799,813,685,247 (in bytes)

parallel_dsi

A shorthand method for configuring parallel DSI threads. A value of “on” sets dsi_num_threads to 5, dsi_num_large_xact_threads to 2, dsi_serialization_method to wait_for_commit, and dsi_sqt_max_cache_size to 1 million bytes (on 32-bit platform) and 20 million bytes (64-bit platform). A value of “off” sets the parallel DSI values to their defaults. You can set this parameter to “on” and then set individual parallel DSI configuration parameters to fine-tune your configuration.

Default: off

To configure a connection for parallel DSI, set the parallel_dsi parameter to on and then set individual parallel DSI configuration parameters to fine-tune your environment.

For example, to enable parallel DSI for the connection to the pubs2 database on the SYDNEY_DS data server, enter:

alter connection to SYDNEY_DS.pubs2
 set parallel_dsi to 'on'

NoteYou can also set individual parallel DSI configuration parameters using the configure replication server command.

See “Configuring parallel DSI for optimal performance” for guidelines on configuring the parameters.