Difference between revisions of "Replication Server Performance Tuning"

Revision as of 13:49, 17 March 2006

Performance Tuning

Here are some rules of thumb to tune a replication server. They may not always be suitable for your environment, but it can be used as a starting point for further improvements.

RepAgent

Some basic settings need to be changed to get a better througput from the RepAgent to the RepServer. Change these setting with the sp_config_rep_agent stored procedure and do a restart of the RepAgent afterwards.

• "scan batch size", set to "10000"
• "scan timeout", set to "5" (seconds) (not really a performance booster)
• "send buffer size", set to "16k"
• Check if "batch ltl" is set to "true".

With the sp_help_rep_agent stored procedure you can see if the RepAgent can keep up with the activity in the transaction log. Output of this procedure (with the "scan" option") shows the current and the end marker. The end marker is the last page/row number of the transaction log, and the current marker is the page/row number currently being scanned by the RepAgent. In the output of sp_help_rep_agent the markers are shown like this (example): (2954283,4). The first number is the page number and second one the record number on the page.

SQM

Do not spend a lot of time on this. Just make sure that the stable devices are on raw disk when your operating system supports it.

SQT

The most important configuration parameter for the SQT is "sqt_max_cache_size". To check if this parameter has been set too low, run an "admin who,sqt" at regular intervals and check if the columns "removed" or "full" contain a non-zero value. Be aware that the output of "admin who,sqt" also shows the DSI threads. So when "removed" or "full" are non-zero for a DSI thread, change the memory setting at the connection-level using "alter connection to <dataserver.database> set dsi_sqt_max_cache_size to '<new-value>'". Otherwise change the "sqt_max_cache_size" configuration parameter using "rs_configure".

Replicating Stored Procedures

Using stored procedure replication can greatly improve the performance of a replicated environment. Before using this feature check if the tables that are modified through the stored procedure are also marked for replication. For warm standby environments this is usually done with the sp_reptostandby stored procedure. Since data at the replicate site is now modified through a stored proc, some side effects can occur.

• Tables using identity columns will have a different value in their identity column on the primary and replicate site.
• Inserted/Updated values from global variables (like @@spid), various system functions like getdate() will also differ between primary and replicate, causing a difference in data between sites.

Check if this applies to your situation before using stored procedure replication.

Usually a stored procedure is marked for replication like this: sp_setrepproc "<procedure name>","function" There is no need to create anything else (like subscribtions or other stuff).

Replicating System Procedures like sp_addlogin / sp_password / etc.

This seems to be a classic problem at warm-standby environments. Mark A. Parsons found a solution for this, but here is a much easier one. The description given below is a kind of template based on the requirement to replicate the execution of sp_addlogin. You can easily modify it for other Sybase supplied system stored procedures.

• Setup a database (e.g. myDatabase) for warm standby as you normally do.
• Grant the neccesary roles to the maintenance user to succesfully execute system stored procedures (like sso_role for sp_addlogin)
• create a wrapper stored procedure like this template

use myDatabase
go
create procedure sp_addlogin @loginame varchar(30),
                             @passwd   varchar(31)
as
-- trick the RepAgent
commit tran
 
exec sybsystemprocs..sp_addlogin @loginame, @passwd
 
-- Start a new transaction (only at the standby side)
if  proc_role("replication_role") > 0
    begin tran
go

• mark the new procedure for replication: sp_setrepproc sp_addlogin,"function"

That's all. Each execution of sp_addlogin in the myDatabase will create a login at the primary and the standby server.

Just a couple of remarks:

• The template given above is not complete with regard to the parameters that need to be passed to sybsystemprocs..sp_addlogin. It's just a template.
• even when a transaction fails on the primary side (for instance when the login already exists) it will be replicated to the standby side failing there as well. This will bring down the DSI connection. You can add some extra checks in the template to prevent common errors. To exit the procedure prematurely in those cases, do not give a "commit", but a "rollback".
• When you do not need to replicate table modifications in the myDatabase, do not mark the database with sp_reptostandby. This is to prevent unnecessary transactions flowing through the RepAgent to the Replication Server.
• This method has not been tested thorougly.
• Do not mark the sp_addlogin stored procedure in sybsystemprocs for replication. When you do this, RepAgent adds an implicit "begin transaction" before the execution of the stored procedure, causing it to fail.
• There is no need to change the Sybase supplied system stored procedures so this method is save with regard to upgrades.