Case study 38: Replication Server (in Warm Standby setup) inbound queue is growing
Platforms: Solaris 10, et all
SAP ASE version: 15.0, et all.
SAP RS version: 15.2
Background story: We had a situation where, in some cases of above average load, inbound queue started to grow, and replication is lagging.
In order to check this phenomenon, we have been using following query, executed over RS rssd database: use rssd
go
select dsname, dbname, q_type, count(*) from rs_segments, rs_databases
where used_flag > 0 and q_number=dbid
group by dsname, dbname, q_type
go
Periodically, we got this warning, that one of the queues, on the most busy database, is growing. And from that script, we saw that we have had growing queue type 1, which is inbound queue. Queue type 0 is outbound queue.
Solution: By reading lots of documentation, and trying to understand how Replication Server is working, I have found solution for this particular issue. The issue was on Standby side of replication process. ASE database on standby side was not correctlly setup for replication. In one great article I have found an asnwer, which was that I need to setup statement cache size (which was 0 on standby database), and to enable literal autoparam. By doing this, replication queue steadilly started to empty, to its normal size.
Conclusion: Why this change solved this issue? Because, even on primary side we did not have AdHoc queries, but only stored procedures are executing, once replication is done, by my understanding, it is executed on stanby side as a series of Inserts, Updates and Deletes. So, by turning on these two parameters, we have enabled query plan reuse, and Sybase on stanby side did not need to create query plan for every single, AdHoc query (I, U, D), in every execution, but it was able to reuse already created plan. What I would need to investiga is some interesting thing, which I found during documentation reading, is that outbound queue is not used in Warm Standby installation, and that data are replicated from inbound queue!! This is very interesting thing to be reseached.
