SRS – Threads, Modules, and Daemons and high level Optimization options

SRS – Threads, Modules, and Daemons

SRS – SAP/Sybase Replication Server uses several Replication Server threads during replication process to perform data operations. Replication Server also stores data in queues and relies on the Replication Server System Database (RSSD) for critical system information like Routes, Connections, Replication Definitions and Subscriptions . These internal operations support various processes within the primary and replicate Replication Servers.

Replication Server runs multiple threads concurrently. The total number of threads depends on the number of databases that a Replication Server manages and the number of Replication Servers to which it has direct routes. Each thread performs a specific function such as managing a user session, receiving messages from a RepAgent, receiving messages from another Replication Server, or applying transactions to databases.

Some threads call specific portions (or “modules”) of Replication Server to determine the destination of messages and transactions, and to determine what operations to replicate and how to replicate them.

Daemon threads, which run in the background and perform specified operations at predefined times or in response to certain events, run during such Replication Server activities as subscription materialization.

When you troubleshoot the replication system, verify the status of Replication Server threads, modules, and daemons.

To optimize the Replication System, you can change the values of the configuration parameters to improve SAP Replication Server performance. Rs_init sets the default values for these configuration parameters. Most of our DBAs implement the parallel DSI but we can also do the same for RepAgent using Multipath and parallel DIST configuration to improve the performance besides other configuration paramters cited below.

For an Example:

dist_direct_cache_read  - This parameter 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_cmd_batch_size - This parameter sets the maximum number of bytes that Replication Server places into a command batch. Default: 8192 bytes

SRS – SAP/ Sybase Replication Server threads.

There are 6 important threads running in the Replication Server. To optimize the SRS process and reduce the latency/lag time we need to fine tune these parameters based on the actual resource contention.

Threads

RepAgent

Returns information about Replication Agent threads. These threads scan/read transactions from the Source database logs to inbound queue of the Replication Server.

Optimization  à         Multipath Replication

dist

Returns information about Distributor threads. These threads distribute transactions in the inbound queue to replicate databases and Replication Servers.

Optimization  à         Parallel Processing in DIST

dsi

Returns information about DSI threads. These threads apply replicated transactions to databases.

Optimization  à         Parallel Processing in DSI and DSI Bulk Copy-In

rsi

Returns information about RSI threads. These threads send messages to other Replication Servers.

Optimization  à         Cache System Tables using sts_cache_size and sts_full_cache_<TBL>

sqm

Returns information about SQM threads. These threads manage Replication Server stable queues.

Optimization  à         SQM Command Cache

sqt

Returns information about SQT threads. These threads read queues and group functions into transactions.

Optimization  à         SQT Cache and Direct Replication for Inbound Commands

More info about the threads:

1.    REP AGENT: – Replication agent Thread

ü  Reads the primary database transaction lo to find transactions (SQL statements or procs) that have occurred against tables that are marked for replication

ü  Forwards transactions to the replication server using a proprietary language called Log Transfer Language (LTL)

ü  Function as a connection manager for the repagents and passes the changes to SQM

ü  Maintains a secondary truncation point in transaction log, which prevents transactions from being truncated until they are safely stored in the replication server stable device.

ü  Coordinate recovery between the transaction log and replication server

ü  Each database may only have one Repagent thread (excluding multipath replication)

2.    SQM: – Stable Queue Manager thread

ü  is the only thread that interacts with the stable queue it performs all logical I/O to the stable queue (physical i/o performed by the dAIO daemon)

ü  writes the logged changes to disk via os i/o routine, notify that async i/o deamon (dAIO)

ü  The SQM is responsible for “Queue I/O”. All reads, writes, deletes and queue dumps from the stable queue “Duplicate Detection”. Compares OQID’s from LTL to determine if LTL log row is a duplicate of one already received.

3.    SQT: – The Stable Queue Transaction thread

ü  Responsible for sorting the transactions into commit order. The repagent starts scanning the transaction as soon as they have started/began in the Source, and SQL will sort the transactions based on the commit time.

ü  Request the next disk block from the SQM and sort the transaction into commit order, again another read request is done via SQM->dAIO. Once the commit record for transaction has been seen the SQT alerts distribution thread (DIST) that transaction is available.

4.    DIST: – Distribution thread

ü  Read transaction that was notified by SQT and determine the subscription for the table. Once all of the subscribers identified the DIST thread forward the transaction to the SQM for the outbound queue for destination connection.

5.    RSI :- Replication server interface

ü  If the SRS setup uses multiple Replication Servers, then RSI sends the transactions from Primary RepServer to Secondary RepServer.

6.    DSI and DSI EXEC: – data server interface and data server interface execution threads

ü  Translates the replication transactions functions into destination command language (TSQL) and applies the transaction to replicate database.

Other important threads:

·         Connection:-

ü  Connection exists between replication server and the database they manage

ü  A Replication Server has a connection to reach replicate database it manages.

ü  A Replication thread DSI uses this connections to send updates to the replicate database

ü  The DSI logs into the RDS as a regular client connection using the maintenance user login.

ü  A maintenance user login is a special userid  used by replication servers to make changes in replicate database and RSSD.

·         dAIO:- async i/o deamon thread

ü  Polls the o/s for completion and notify the SQM that i/o completed.

Some Interesting Scenarios and Resolutions

Scheduling Replication Tasks or Delaying the Replication

Sybase Replication Server provides support for Scheduling a specific task (It could be Suspending a connection or Resuming a Connection) or Delaying replication by a fixed period of time (Not more than 24 Hours)

Why do we need to Suspend/Resume or Delay Replication?

If you look into Creditcard online statement or Mobile call History, you would notice the Data is visible/available till last night, I mean the data found till the date prior to current-day only.
Due to business requirements and performance issues, during peak hours or day-time we might need to stop the Replication or suspend the Replication and Resume in the Evening i.e. off business hours.

We can do this using schedules : create a schedule to execute shell command/script at a time we specify:

Syntax -->  create schedule schedule_name as 'TIME' [set { on | off } for exec 'command'

For an Example: 

Create “schedule1” to execute the suspend_conn.sh script in UNIX that suspends the connection to the pubs2 database every day at 6AM:

create schedule schedule1 as ‘0 6 * * *’ for exec ‘suspend_conn.sh’

Create “schedule2” to execute the resume_conn.sh script that resumes the connection to the pubs2 database every day at 10 PM:

create schedule schedule2 as ‘0 22 * * *’ for exec ‘suspend_conn.sh’

Delay Replication

We can configure Replication Server to delay replication by a fixed period of time. We can use the replicate database as a failback system by keeping the replicate database a certain amount of time behind the primary database to provide a window of time during which to recover any human error committed on the primary database, such as a table or records dropped or truncated by mistake. 

We can specify a maximum delay of 24 hours. The default delay value is 00:00 which means there is no delay.

To delay by a couple of hours of the time (Lag/Latency will be more than 2 Hours now) transactions commit at the pubs2 replicate database in the SECOND_DS data server, enter:

alter connection to SECOND_DS.pubs2  set dsi_timer to ‘02:00’

All about kernel mode introduced in ASE 15.7

What is the Kernel Mode? 

First things fist :) Adaptive Server supports only threaded mode on the Windows platform.

kernel mode determines the mode the Adaptive Server kernel uses, either threaded or process. 

In threaded mode, Adaptive Server uses operating system threads to support Adaptive Server engines and specialized tasks. In this mode, Adaptive Server is a single process running on the operating system, which is the new mode introduced in ASE 15.7. 

In process mode, Adaptive Server uses individual processes to support Adaptive Server engines. In this mode, each engine is a distinct process running on the operating system. The process mode is the same kernel used by Adaptive Server versions earlier than 15.7.

The values for kernel mode are character data, so you must use 0 as a placeholder for the second sp_configure parameter, which must be numeric, and specify threaded or processas the third parameter.

• Sybase assumes you use threaded mode in your production server, and includes process mode for backward compatibility. Process mode may not support features for Adaptive Server 15.7 and later.
• BTW, this configuration parameter is STATIC.
• global variable @@kernelmode Returns the mode (threaded or process) for which Adaptive Server is configured.

more info in the blog of  Mr. Andrew Melkonyan (Thanks much sir for the excellent explanation indeed)

https://blogs.sap.com/2013/02/11/ase-157-threaded-vs-process-kernel-mode/

Limit the number of rows in SQL query results

How to get only the first n rows from the subquery or child table's results?

Sometimes we need to fetch first n rows from the table(s). Different RDBMSs offer different functions to ease this task but unfortunately Sybase has no direct answer/solution.

BTW, If we need to get first 10 rows/records from a table

In Oracle 11g/12c

SELECT * FROM Inventory_tbl WHERE ROWNUM < 11

In IBM DB2, Oracle (12c), PostgreSQL (v8.3 and later)

SELECT * FROM Inventory_tbl FETCH FIRST 10 ROWS ONLY

In Microsoft SQL Server 2000+, Sybase ASE 12.5.x+

SELECT TOP 10 * FROM Inventory_tbl

or

set rowcount 10

SELECT * FROM Inventory_tbl

In MySQL, PostgreSQL

SELECT * FROM Inventory_tbl LIMIT 10

This is pretty simple.. But when we need to limit the number of rows of sub-query or secondary table, Sybase ASE has no support. 

For our understanding:

I need to write a Query to return only top 10 records from the child table.. You can take the Tables sysobjects and syscolumns to try this: I want All the user tables from sysobjects and only first 5 matching columns from syscolumns (Here we need to restrict 2nd table i.e. syscolumns rows to 5) I have implemented using cursors and # tables but I need a single Query. (in MySQL we have LIMIT option and ORACLE ROWNUM but set rowcount or top n of Sybase is not helpful for subQuery)
select so.id, so.uid, so.name, sc.colid, sc.name from sysobjects so, syscolumns sc where so.id=sc.id and so.id in (select top 10 id from syscolumns sc where sc.id=so.id) order by so.id

A query similar to above would satisfy our requirements but top n functionality was not supported in subqueries.

The same in MySQL is very simple, as limit function restricts the rowcount

select so.id, so.uid, so.name, sc.colid, sc.name from sysobjects so, syscolumns sc where so.id=sc.id and so.id in (select id from syscolumns sc where sc.id=so.id limit 10) order by so.id

If you have any ideas please comment or share with me @ sybase.professional@gmail.com
Thanks and Regards, Kasi Dogga