Replication Server Parameters that Affect Performance

block_size to 'value' with shutdown

Specifies the queue block size which is the number of bytes in a contiguous block of memory used by stable queue structures.

Valid values: 16KB, 32KB, 64KB, 128KB, or 256KB

Default: 16KB

License: Separately licensed under the Advanced Services Option.

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. You may change this value if you have Adaptive Server that has been reconfigured.

Maximum: 16,384 bytes

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

deferred_queue_size

The maximum size of an Open Server deferred queue. If Open Server limits are exceeded, increase the maximum size. The value must be greater than 0.

Default: 2,048 on Linux and HPIA32,       1024 on other platforms

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

dist_direct_cache_read

Enables the distributor (DIST) thread to read SQL statements directly from the Stable Queue Thread (SQT) cache. This reduces the workload from SQT and the dependency between the two, and improves the efficiency of both SQT and DIST.

Default: off

dsi_bulk_copy

Turns the bulk copy-in feature on or off for a connection. If dynamic_sql and dsi_bulk_copy are both on, DSI applies bulk copy-in. Dynamic SQL is used if bulk copy-in is not used. Sybase recommends that you turn dsi_bulk_copy on to improve performance if you have large batches of inserts.

Default: off.

dsi_bulk_threshold

The number of consecutive insert commands in a transaction that, when reached, triggers Replication Server to use bulk copy-in. When Stable Queue Transaction (SQT) encounters a large batch of insert commands, it retains in memory the number of insert commands specified to decide whether to apply bulk copy-in. Because these commands are held in memory, Sybase suggests that you do not configure this value much higher than the configuration value for dsi_large_xact_size.

Replication Server uses dsi_bulk_threshold for Real-time loading (RTL) replication to Sybase IQ and High volume adaptive replication (HVAR) to Adaptive Server. If the number of commands for an insert, delete, or update operation on one table is less than the number you specify after compilation, RTL and HVAR use language instead of bulk interface.

Minimum: 1

Default: 20

dsi_cmd_batch_size

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

Default: 8192 bytes

dsi_cmd_prefetch

Allows DSI to build the next batch of commands while waiting for the response from data server, and therefore improves DSI efficiency. If you also tune your data server to enhance performance, you are likely to gain an additional performance increase when you use this feature.

Default: off

When you set dsi_compile_enable to ‘on’, Replication Server ignores what you set for dsi_cmd_prefetch.

License: Separately licensed under the Advanced Services Option.

dsi_max_xacts_in_group

Specifies the maximum number of transactions in a group. Larger numbers may improve data latency at the replicate database. Range of values: 1 – 1000.

Default: 20

This parameter is ignored when dsi_compile_enable is turned on.

dsi_non_blocking_commit

Specifies the number of minutes to extend the period of time Replication Server saves messages after a commit. Range of values: 0– 60 minutes.

Default: 0 – non-blocking commit is disabled.

Enable this parameter to improve replication performance when you use the the delayed_commit option of Adaptive Server 15.0 and later, or the equivalent feature in Oracle 10g v2 and later.

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. A value of –1 means no grouping.

Maximum: 2,147,483,647

Default: 65,536 bytes

This parameter is ignored when dsi_compile_enable is turned on.

dynamic_sql

Turns dynamic SQL feature on or off for a connection. Other dynamic SQL related configuration parameters will take effect only if this parameter is set to on.

Default: off

dynamic_sql_cache_size

Specifies to Replication Server how many database objects may use the dynamic SQL for a connection.

Default: 100

Minimum: 1

Maximum: 65,536

dynamic_sql_cache_management

Manages the dynamic SQL cache for a DSI executor thread. Values: mru - keeps the most recently used statements and deallocates the rest to allocate new dynamic statements when dynamic_sql_cache_size is reached. fixed (default) - Replication Server stops allocating the new dynamic statements once dynamic_sql_cache_size is reached.

exec_cmds_per_timeslice

Control the number of commands the RepAgent executor can process by using exec_cmds_per_timeslice to specify the number of LTL commands an LTI or RepAgent executor thread can process before yielding the CPU. By increasing this value, you allow the RepAgent executor thread to control CPU resources for longer periods of time, which may improve throughput from RepAgent to Replication Server.

Set this parameter at the connection level using alter connection.

Default: 2,147,483,647

Minimum: 1

Maximum: 2,147,483,647

exec_nrm_request_limit

Specifies the amount of memory available for messages from a primary database waiting to be normalized.

Set nrm_thread to ‘on’ with configure replication server before you use exec_nrm_request_limit.

Default: 1,048,576 bytes (1MB)

Minimum: 16,384 bytes (16KB)

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

License: Separately licensed under the Advanced Services Option.

exec_sqm_write_request_limit

Specifies the amount of memory available for messages waiting to be written to an inbound queue.

Default: 1MB Minimum: 16KB Maximum: 2GB

init_sqm_write_delay

The initial amount of time an SQM Writer should wait for more messages before writing a partially full block of messages to the queue. The SQM Writer always tries to write full blocks to the queue. If it has partially filled a block, and cannot fill it, SQM Writer waits the amount of time specified by init_sqm_write_delay before rechecking whether messages are waiting to be added to the block. If no messages exist, SQM Writer doubles the init_sqm_write_delay time. The SQM Writer continues to double the delay time until it reaches the value of init_sqm_write_max_delay. At this point, SQM Writer writes the partially full block.

Default: 100 milliseconds

init_sqm_write_max_delay

The maximum amount of time an SQM Writer thread should wait for more messages before writing a partially full block of messages to the queue. See the description of init_sqm_write_delay for more information.

Default: 1,000 milliseconds

mem_reduce_malloc

Enable to allocate memory in larger chunks, which reduces the number of memory allocations and leads to improved Replication Server performance.

Default: off

License: Separately licensed under the Advanced Services Option.

mem_thr_dsi

Specifies the percentage of the total memory used to force the DSI thread to stop populating the SQT cache.

Default: 80% of memory_limit value.

Range: 1 – 100

mem_thr_exec

Specifies the percentage of the total memory used to force the EXEC thread to stop receiving commands from RepAgent.

Default: 90% of memory_limit value.

Range: 1 – 100

mem_thr_sqt

Specifies the percentage of the total memory used to force the SQT thread to flush the largest transaction from its cache if possible.

Default: 85% of memory_limit value.

Range: 1 – 100

mem_warning_thr1

Specifies the threshold percentage of the total memory used before the first warning message is generated. See memory_limit.

Default: 80% of memory_limit value.

Range: 1 – 100

mem_warning_thr2

Specifies the threshold percentage of the total memory used before the second warning message is generated. See memory_limit.

Default: 90% of memory_limit value.

Range: 1 – 100

memory_control

Manages the memory control behavior of threads that require significant amount of memory. See memory_limit.

Default: on

memory_limit

The maximum total memory the Replication Server can use, in megabytes.

Values for several other configuration parameters are directly related to the amount of memory available from the memory pool indicated by memory_limit. These include exec_nrm_request_limit, exec_sqm_write_request_limit, md_sqm_write_request_limit, queue_dump_buffer_size, sqt_max_cache_size, sre_reserve, and sts_cachesize.

Default: 2,047

If the value you set is larger than 2,047, downgrading resets the value to 2,047 to protect against overflow.

md_sqm_write_request_limit

Specifies the amount of memory available to the Distributor for messages waiting to be written to the outbound queue.

Default: 1MB

Minimum: 16K

Maximum: 2GB

nrm_thread

Enables the NRM thread which Replication Server can use to normalize and pack Log Transfer Language (LTL) commands in parallel with parsing by the RepAgent Executor thread. Parallel processing by the NRM thread reduces the response time of the RepAgent executor thread. The NRM thread is a thread split from RepAgent executor thread.

Use the configure replication server command to set nrm_thread to on before you use exec_nrm_request_limit.

Default: off

License: Separately licensed under the Advanced Services Option.

rec_daemon_sleep_time

Set wake up intervals by specifying the sleep time for the recovery daemon, which handles “strict” save interval messages in warm standby applications and certain other operations.

Default: 2 minutes

smp_enable

Enables symmetric multiprocessing (SMP). Specifies whether Replication Server threads should be scheduled internally by Replication Server or externally by the operating system. When Replication Server threads are scheduled internally, Replication Server is restricted to one machine processor, regardless of how many may be available. Values are “on” and “off.”

Default: on

Upgrading or downgrading does not change the value you set.

sqm_async_seg_delete

Set sqm_async_seg_delete to on to enable a dedicated daemon for deleting segments and improve performance for inbound and outbound queue processing.

Default: on

You must restart Replication Server for any change to the parameter setting to take effect.

sqm_cache_enable

Indicates whether to enable SQM caching and large I/O in a stable device.

Default: on

sqm_cache_size

Indicates the number of pages in cache where size of a page is specified by sqm_page_size. Range is 1 to 4096.

Default: 16

sqm_page_size

Indicates the number of blocks in a page.

Sets server-wide stable queue page size in blocks per page. Enclose page sizes in single quotes or double quotes. For example, setting page size to 4 instructs Replication Server to write to the stable queue in 64K chunks.

Configuring the page size also sets the I/O size of Replication Server. The range is 1 to 64.

Default: 4

sqm_recover_segs

Specifies the number of stable queue segments Replication Server allocates before updating the RSSD with recovery QID information.

Sybase recommends that you increase the value of sqm_recover_segs to improve performance.

Default: 1

Minimum: 1

Maximum: 2,147,483,648

sqm_write_flush

For stable device considerations, sqm_write_flush specifies whether or not writes to memory buffers are flushed to the disk before the write operation completes. Values are “on,” “off,” and “dio”.

Default: on

sqt_init_read_delay

The length of time an SQT thread sleeps while waiting for an SQM read before checking to see if it has been given new instructions in its command queue. With each expiration, if the command queue is empty, SQT doubles its sleep time up to the value set for sqt_max_read_delay.

Default: 1 milliseconds (ms)

Minimum: 0 ms

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

sqt_max_cache_size

Use sqt_max_cache_size for sizing the SQT cache to the maximum SQT cache memory, in bytes.

If the value you set is larger than 2,147,483,647 bytes, downgrading resets the value to 2,147,483,647 bytes to protect against overflow.

sqt_max_prs_size

Maximum memory consumed by commands unpacked by the transaction profiling processes for HVAR and RTL. in bytes.

Use configure replication server to set the parameter at the server level for all connectionsor or use alter connection at the database level for individual connections. The default value at the database level is 0. If you retain the database-level default or reset to the default, Replication Server uses the value set at the server level.

sqt_max_read_delay

The maximum length of time an SQT thread sleeps while waiting for an SQM read before checking to see if it has been given new instructions in its command queue.

Default: 1 ms

Minimum: 0 ms

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

sts_cachesize

For caching system tables, use sts_cachesize to specify the total number of rows that are cached for each cached RSSD system table. Increasing this number to the number of active replication definitions prevents Replication Server from executing expensive table lookups.

Monitor whether the STS cache is too small by reviewing counter 11008 – STSCacheExceed or examining the Replication Server log for warnings that rows have been removed from the STS cache.

Default: 1000

sts_full_cache_system_table_name

For caching system tables, use sts_full_cache_system_table_name to specify an RSSD system table that is to be fully cached. Fully cached tables do not require access to the RSSD for simple select statements. Only some RSSD tables can be fully cached.

sub_daemon_sleep_time

For setting wake up intervals, usesub_daemon_sleep_time to set the number of seconds the subscription daemon sleeps before waking up to recover subscriptions. The range is 1 to 31,536,000.

Default: 120 seconds

sub_sqm_write_request_limit

Specifies the memory available to the subscription materialization or dematerialization thread for messages waiting to be written to the outbound queue.

Default: 1MB

Minimum: 16K

Maximum: 2GB