Replication for the database is slow.

[sladebe]

nightly-monitoring-1.txtHi Experts,

I recently setup MSA database replication for a database of size 3.5 TB and started a process called EOD/nightly (almost 7000 jobs) and when we observe it segments of the primary database from admin who,sqm we see the data is accumulating in IBQ and not getting transferred to OBQ. Attached the details for you to advise.

ASE and SRS : 15.7 SP135 on AIX 7.2 with logical page size as 8K.
sqt_max_cache_size configured is 200 GB.
stable device of 362 G

In the output of sqt of target databases we see negative values. Are they expected and what does they indicate? Also please let me know the ways to monitor the problematic areas.

Regards,
Dilip Voora

nightly-monitoring-1.txt

[sladebe]

Just a quick check, do you have "smp_enable" turned on? (use actual OS threading which lets threads actually run in parallel)

But other than that, repserver performance tuning is a complicated thing to do. See:

• Replication Server Administration Guide Volume 2 -> Performance Tuning -> Configuration Parameters that Affect Performance (15.7.1)
• Replication Server Administration Guide Volume 2 -> Performance Tuning (15.7.1)
• Replication Server Administration Guide Volume 2 -> Monitor Performance Using Counters (15.7.1)

If you have a SAP support contact, it would probably be best to open an "incident" with them asking for help.

[DilipVoora]

Hi All,

After implementing this setting with MALLOCOPTIONS we see an improvment in terms of performance from replication server to targets. However, we are still facing challenges with the inbound as the replication agent is reading the data very very slow.

Parameters we have in place for replication agent:

scan batch size : 1000
send buffer size : 16K (8K logical page size in ASE)
number of send buffers : 50

When rs_ticket is issued in PDB, RA read it after 5 hours. Is there any parameter that we are missing here to configure? Please advise.

In Taregt 1:

17 M6_T - - - phoenix - ZEN_REPSERVER phoenix Mar 21 2023 5:56PM Mar 21 2023 9:20PM Mar 21 2023 9:21PM Jan 1 1900 12:00AM Mar 21 2023 9:26PM Mar 21 2023 9:26PM 168062304404 0 87849897 278219435 V=2;H1=M6_T;PDB(phoenix)=03/21/23 17:56:49.793;RA(phoenix)=03/21/23 21:20:34:266;EXEC(119)=03/21/23 21:20:34.271;B(119)=168062304404;DIST(55)=03/21/23 21:21:53.336;DSI(118)=03/21/23 21:26:27.087;DSI_T=87849897;DSI_C=278219435;RRS=ZEN_REPSERVER

In Target 2:

17 M6_T - - - phoenix - ZEN_REPSERVER phoenix Mar 21 2023 5:56PM Mar 21 2023 9:20PM Mar 21 2023 9:21PM Jan 1 1900 12:00AM Mar 22 2023 12:24AM Mar 22 2023 12:24AM 168062304404 0 87849897 278219435 V=2;H1=M6_T;PDB(phoenix)=03/21/23 17:56:49.793;RA(phoenix)=03/21/23 21:20:34:266;EXEC(119)=03/21/23 21:20:34.271;B(119)=168062304404;DIST(55)=03/21/23 21:21:53.336;DSI(125)=03/22/23 00:24:33.206;DSI_T=87849897;DSI_C=278219435;RRS=ZEN_REPSERVER

Regards,
Dilip Voora

[Mark_A_Parsons]

rs_ticket items of interest:

• ~3.3 hrs from PDB to RA is high; repagent issue? PDS(ASE) issue? PRS(SRS) issue? network (ASE<-->SRS) issue?
• DIST to DSI is ~5 mins for Target1 but ~3 hrs for Target2; assuming same workload being replicated to both RDBs, what's the explanation for the (nearly) 3hr difference? (ASE configs? network issues to Target2? different DSI configs?)

Several questions come to mind ...

• Is the repagent reading log pages from disk (eg, log activity is greater than the amount of cashe reserved for the log so older pages have to be read from disk)? If reading from disk, what are the service times on the disk IO requests?
• What's the roundtrip (network) timings from repagent to PRS? If high, does OS/ping show the same timings or is PRS taking too long to respond? If PRS is taking too long to respond, is the PRS starved of cpu cycles?
• Are any servers (ASE, SRS) cpu bound (ie, running at 100% cpu utilization)?
• Any chance (either) ASE server has a high volume of stalls due to caches incorrectly configured to use a relaxed LRU replacement policy?

Without a collection of performance metrics (sp_sysmon, MDA tables, OS/network monitoring) it's hard to say if there might be some configuration issues (and if so, where?).

[DilipVoora]

Hi Mark,

Yes the implementation is to replciate data to two targets. ASE configuration across PDB and replciated databases(targets) are same and even the DSI configuration at RS level is also same for the targets.

Details:

moncachepool-output.txtsysmon-from-pdb-22032023.txt
PDB on host A: Phoenix
RDB1 on host B: Phoenix
RDB2 on host C: Phoenix
RS on host D
RSSD on host D as a seperate ASE.

Several questions come to mind ...

• Is the repagent reading log pages from disk (eg, log activity is greater than the amount of cashe reserved for the log so older pages have to be read from disk)? If reading from disk, what are the service times on the disk IO requests?
  <<Dilip>> We have 2 GB named cache for the PDB's log and we are planning to increase it to 20 GB (8 GB for 128K buffer pool).
• What's the roundtrip (network) timings from repagent to PRS? If high, does OS/ping show the same timings or is PRS taking too long to respond? If PRS is taking too long to respond, is the PRS starved of cpu cycles?
  <<Dilip>> Can we check this from our side? If this has to be checked with network administartor we just need to ask them "how is the network speed between PDB-RS and then from RS-Target1 followed by RS-Target2"?
• Are any servers (ASE, SRS) cpu bound (ie, running at 100% cpu utilization)?
  <<Dilip>> No, as far as we checked.
• Any chance (either) ASE server has a high volume of stalls due to caches incorrectly configured to use a relaxed LRU replacement policy?
  <<Dilip>>No stalls as far as we see. Attached the output of monCachePool and sp_sysmon from primary database for your advise again on this.
  

Please check them and let me know if anything has to be reconfigured at PDB level.

Regards,
Dilip Voora

sysmon-from-pdb-22032023.txt

moncachepool-output.txt

[DilipVoora]

Round trip from PDB-PRS is 0/0/0 and from PRS-RDB1 is 0/2/64 and from PRS-RDB2 is 0/0/0. So, we don't see anything abnormal.

I request you to hep me with the below if possible.

If the slowness between PDB to RS is noticed(from rs_ticket) which parameters other than the below need to be checked at replication server level for inbound queue and in sp_config_rep_agent of pdb?

1. scan batch size
2. send buffer size
3. ltl batch size
4. dist_sqt_max_cache_size
5. dist_direct_cache_read to on
6. nrm_thread to on
7. exec_sqm_write_request limit

B) If the slowness between RS components is noticed(from rs_ticket) which parameters need to be checked in replication server level?

C) If the slowness between RS to RDB is noticed(from rs_ticket) which parameters other than the below need to be checked at replication server level for outbound queuessqt_max_cache_size

1. db_packet_size
2. sts_cachesize
3. dsi_compile_max_cmds
4. block_size
5. dsi_cdb_max_size