Connection parameters that affect performance

Table 4-3 describes the database connection parameters that can affect performance. See Chapter 7, “Managing Database Connections,” in the Replication Server Administration Guide Volume 1 for a complete list of connection parameters.

**Table 4-3: Connection parameters that affect performance**
Configuration parameter	Description
batch	The default, “on,” allows command batches to a replicate database. Default: on
db_packet_size	The maximum size of a network packet. During database communication, the network packet value must be within the range accepted by the database. Maximum: 16384 bytes Default: 512-byte network packet for all Adaptive Server databases
disk_affinity	Specifies an allocation hint for assigning the next partition. Enter the logical name of the partition to which the next segment should be allocated when the current partition is full. Values are “partition_name” and “off.” Default: off
dsi_cmd_batch_size	The maximum number of bytes that Replication Server places into a command batch. Default: 8192 bytes
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. Used with parallel DSI. See “Using parallel DSI threads”. Default: 1000 ms (1 second) Minimum: 0 Maximum: 86,400,000 ms (24 hours)
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. Used with parallel DSI. See “Using parallel DSI threads”. 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). Used with parallel DSI. See “Using parallel DSI threads”. 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 commands allowed in a transaction before the transaction is considered to be large. Minimum: 4 Maximum: 2147483647 Default: 100
dsi_max_xacts_in_group	Specifies the maximum number of transactions in a group. Larger numbers may reduce commit processing at the replicate database, and thereby improve throughput. Range of values: 1 – 100. See “Specifying the number of transactions in a group”. Default: 20
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. The maximum value is 255. Default: 1
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, none, time, user, and name. See also “Partitioning rules: reducing contention and increasing parallelism”. 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. 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 – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it is ready to commit. These options are retained only for backward compatibility with older 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. Default: wait_for_commit
dsi_sqt_max_cache_size	Maximum SQT (Stable Queue Transaction) interface cache memory for the database connection, 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. Default: 0
dsi_xact_group_size	The maximum number of bytes, including stable queue overhead, to place into one grouped transaction. A grouped transaction is a set of transactions that the DSI applies as a single transaction. –1 means no grouping. Sybase recommends that you set dsi_xact_group_size to the maximum value and use dsi_max_xacts_in_group to control the number of transactions in a group. Maximum: 2,147,483,647 Default: 65,536 bytes
exec_cmds_per_timeslice	Specifies the number of LTL commands an LTI or RepAgent Executor thread can process before it must yield the CPU to other threads. The range is 1 to 2,147,483,648. Default: 5
exec_sqm_write_request_limit	Specifies the amount of memory available to the LTI or RepAgent Executor thread for messages waiting to be written to the inbound queue. Default: 16,384 bytes Minimum: 16,384 bytes Maximum: 983,040 bytes
md_sqm_write_request_limit	Specifies the amount of memory available to the DIST thread for messages waiting to be written to the outbound queue. The range is 65,536 to 983,040 bytes. Default: 100,000 bytes
parallel_dsi	A shorthand method for configuring parallel DSI to default values. 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. 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
use_batch_markers	If use_batch_markers is set to on, the function strings rs_batch_start and rs_batch_end will be executed. This parameter will only need to be set to on for replicate data servers that require additional SQL translation to be sent at the beginning and end of a batch of commands that are not contained in the rs_begin and rs_commit function strings. Default: off

Table 4-3: Connection parameters that affect performance

Configuration parameter

Description

batch

The default, “on,” allows command batches to a replicate database.

Default: on

db_packet_size

The maximum size of a network packet. During database communication, the network packet value must be within the range accepted by the database.

Maximum: 16384 bytes

Default: 512-byte network packet for all Adaptive Server databases

disk_affinity

Specifies an allocation hint for assigning the next partition. Enter the logical name of the partition to which the next segment should be allocated when the current partition is full. Values are “partition_name” and “off.”

Default: off

dsi_cmd_batch_size

The maximum number of bytes that Replication Server places into a command batch.

Default: 8192 bytes

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. Used with parallel DSI. See “Using parallel DSI threads”.

Default: 1000 ms (1 second)

Minimum: 0

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

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. Used with parallel DSI. See “Using parallel DSI threads”.

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). Used with parallel DSI. See “Using parallel DSI threads”.

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 commands allowed in a transaction before the transaction is considered to be large.

Minimum: 4

Maximum: 2147483647 Default: 100

dsi_max_xacts_in_group

Specifies the maximum number of transactions in a group. Larger numbers may reduce commit processing at the replicate database, and thereby improve throughput. Range of values: 1 – 100.

See “Specifying the number of transactions in a group”.

Default: 20

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. The maximum value is 255.

Default: 1

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, none, time, user, and name. See also “Partitioning rules: reducing contention and increasing parallelism”.

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.
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 – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it is ready to commit.

These options are retained only for backward compatibility with older 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.

Default: wait_for_commit

dsi_sqt_max_cache_size

Maximum SQT (Stable Queue Transaction) interface cache memory for the database connection, 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.

Default: 0

dsi_xact_group_size

The maximum number of bytes, including stable queue overhead, to place into one grouped transaction. A grouped transaction is a set of transactions that the DSI applies as a single transaction. –1 means no grouping.

Sybase recommends that you set dsi_xact_group_size to the maximum value and use dsi_max_xacts_in_group to control the number of transactions in a group.

Maximum: 2,147,483,647 Default: 65,536 bytes

exec_cmds_per_timeslice

Specifies the number of LTL commands an LTI or RepAgent Executor thread can process before it must yield the CPU to other threads. The range is 1 to 2,147,483,648.

Default: 5

exec_sqm_write_request_limit

Specifies the amount of memory available to the LTI or RepAgent Executor thread for messages waiting to be written to the inbound queue.

Default: 16,384 bytes Minimum: 16,384 bytes Maximum: 983,040 bytes

md_sqm_write_request_limit

Specifies the amount of memory available to the DIST thread for messages waiting to be written to the outbound queue. The range is 65,536 to 983,040 bytes.

Default: 100,000 bytes

parallel_dsi

A shorthand method for configuring parallel DSI to default values. 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. 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

use_batch_markers

If use_batch_markers is set to on, the function strings rs_batch_start and rs_batch_end will be executed.

This parameter will only need to be set to on for replicate data servers that require additional SQL translation to be sent at the beginning and end of a batch of commands that are not contained in the rs_begin and rs_commit function strings.

Default: off