HVAR Performance Tuning

Replication Server automatically sets the recommended default values of several parameters. You can change the values of these parameters to tune replication performance.

You must execute a separate alter connection command for each parameter you want to change. Do not enter more than one parameter after entering alter connection.

See Replication Server Reference Manual > Replication Server Commands > alter connection for full descriptions of the parameters.

dsi_bulk_threshold

dsi_bulk_threshold specifies the number of net row change commands after compilation has occurred on a table for a command type, that when reached, triggers Replication Server to use bulk copy-in on that table for the same command type.

Default is 20 net row change commands.

Example:

alter connection to SYDNEY_DS.pubs2
set dsi_bulk_threshold to ‘15’
go

dsi_cdb_max_size

dsi_cdb_max_size specifies, in megabytes, the maximum size of a transaction that HVAR can compile if the transaction does not exceed the DSI SQT cache or if the number of commands in the transaction does not exceed dsi_compile_max_cmds.

When the size of transactions in the current group that HVAR is compiling reaches dsi_compile_max_cmds, HVAR starts a new group. If there is no more data to read, and even if the group does not reach the maximum size set in dsi_cdb_max_size, HVAR completes grouping the current set of transactions into the current group.

Default is 1024MB.

Example:

alter connection to SYDNEY_DS.pubs2
set dsi_cdb_max_size to ‘2048’
go

dsi_compile_max_cmds

dsi_compile_max_cmds specifies, in number of commands, the maximum size of a transaction that HVAR can compile if the transaction does not exceed the DSI SQT cache or if the transaction size does not exceed dsi_cdb_max_size. Replication Server replicates noncompilable transactions through the continuous replication mode.

When the number of commands in the current group that HVAR is compiling reaches dsi_compile_max_cmds, HVAR starts a new group. If there is no more data to read, and even if the group does not reach the maximum number of commands set in dsi_compile_max_cmds, HVAR completes grouping the current set of transactions into the current group.

Default is 10,000 commands.

Example:

alter connection to SYDNEY_DS.pubs2
set dsi_compile_max_cmds to ‘50000’
go

dsi_compile_retry_threshold

dsi_compile_retry_threshold specifies a threshold value for the number of commands in a group. If the number of commands in a group containing failed transactions is smaller than the value of dsi_compile_retry_threshold, Replication Server does not retry processing the group in HVAR mode, and saves processing time, thus improving performance. Instead, Replication Server switches to continuous replication mode for the group. Continuous replication mode sends each logged change to the replicate database according to the primary database log order.

Default is 100 commands.

Example:

alter connection to SYDNEY_DS.pubs2
set dsi_compile_retry_threshold to '200'
go

dsi_command_convert

dsi_command_convert – specifies how to convert a replicate command.

Note: This parameter is available for use apart from HVAR.

A combination of these operations specifies the type of conversion:

d – delete
i – insert
u – update
t – truncate
none – no operation

Combinations of operations for dsi_command_convert include i2none, u2none, d2none, i2di, t2none, and u2di. The operation before conversion precedes the “2” and the operations after conversion are after the “2”. For example:

d2none – do not replicate the delete command. With this option, you need not customize the rs_delete function string if you do not want to replicate delete operations.
i2di,u2di – convert both insert and update to delete followed by insert, which is equivalent to an autocorrection. If you disable row count validation by setting dsi_row_count _validation off, set dsi_command_convert to i2di,u2di to avoid duplicate key errors and allow autosynchronization of databases during replication.
t2none – do not replicate truncate table.

Default for dsi_command_convert is none which means there is no command conversion.

Example:

alter connection to SYDNEY_DS.pubs2
set dsi_command_convert to ‘i2di,u2di’
go