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.

[ChrisBaker]

Along with Ben's and Matthias' comments, you should also confirm that the AIX LPAR has been correctly configured for the resources required by the SRS.

Ensure that there is a 1:1 mapping of OS threads to processor threads by setting AIXTHREAD_SCOPE=S in the shell environment that starts the SRS and that sufficient CPU and memory resources are available to the LPAR running SRS by confirming the number of VCPs vs. entitlement by running 'lparstat -i' and checking the output.

If data is accumulating, perhaps there are insufficient CPU resources available to process the queue.

Chris

[DilipVoora]

Hi Chris,

Thanks for the hint.

Regards,
Dilip Voora

[DilipVoora]

rs-ticket-history.txtHi Mark,

Thanks for looking into the details provided.

At this point of time we resintalled RS with 15.7.1 SP306 ROLLUP and started it with the configuration we have had used for SP135. The main reason behind doing this is to avoid unnecessary issues (which we assume are not being reported/getting printed in the errorlog). Even after running the job with RS (SP306 ROLLUP) we are noticing slowness in pdb-exec and mostly in exec_dist. Attached the rs_ticket_history output.

However, I am very eager to understand more with respect to your previous comment and then to implement them along with the configuration we are tweaking with.

From the previous comment:

1.repagent is running with default scan batch size (1000) and default ltl batch size (16384); net result: repagent requesting trunc pt movements at a rate of ~5/sec
<<Dilip>> Can you please explain this calculation and the section (if anything) referred from sp_sysmon?

2. only half of repagent packets sent to RS are full; some % of the non-full packets are likely related to the trunc pt movement requests
<<Dilip>> Are you telling this based on the output in rep agent section of sp_sysmon by (packets sent-full packets sent)? Please clarify.

3. repagent is spending an excessive amount of time requesting trunc pt movements (~5 requests per second)
<<Dilip>> How to check this?

4. phoenix_log cache fills up too quickly which pushes log pages to disk; repagent becomes disk bound because all (older) pages have to be pulled from disk; don't know at this point if cache is too small and/or repagent-related configs are (partially) to blame and/or there is excessive/unnecessary DML activity in the database
<<Dilip>> With this assumption only you are suggesting to increase the size of the cache dedicated for logsegment and then configure 64K buffer pool followed by changing the sp_logiosize at database level from 8K to 64K. If the results are good, can we leave it running with 64K? During normal business hours (the client is a bank) will there be any effect to the normal transactions on the database?

Regards,
Dilip Voora

rs-ticket-history.txt

[Mark_A_Parsons]

re: #1/#3:

• sp_sysmon session is 10 minutes (600 seconds)
• sp_sysmon Replication Agent section for phoenix db shows Truncation Point Movement total of 3139/3141
• 3139/600 = 5.2 trunc pt movements per second

re: #2:

• correct, sp_sysmon/repagent section, Network Packet Information > Packets Sent vs Full Packets Sent

re: #4:

• yes, increase the logio size and cache/pool to 64K (8x 8K); primary objective is to pull pages off disk at a quicker rate
• whether you leave it like this all day is up to you; I'd suggest you leave it at 64K (during 'normal' biz hours) and see if your (performance) monitoring metrics show any related degradation and then decide if this change should be implemented only during (nightly) batch processing
• whether you continue to use 64K will also depend on whether or not other changes are effective at decreasing/eliminating the need for the repagent to pull 'old' log pages from disk

[Mark_A_Parsons]

Observations from the sp_config_rep_agent and sp_sysmon(10min) output:

• NOTE: focusing on phoenix db/log
• NOTE: focusing on repagent related items
• phoenix database is running with logiosize = 16KB (dataserver page size = 8KB)
• phoenix log has it's own cache (phoenix_log)
• repagent activity is disk bound at a (read) rate of ~200 i/o's per second; 100% APF usage
• repagent is running with default scan batch size (1000) and default ltl batch size (16384); net result: repagent requesting trunc pt movements at a rate of ~5/sec
• only half of repagent packets sent to RS are full; some % of the non-full packets are likely related to the trunc pt movement requests
• no noticeable issues with hitting up against limits on the number of oustanding disk i/o requests

Operational issues:

• phoenix_log cache fills up too quickly which pushes log pages to disk; repagent becomes disk bound because all (older) pages have to be pulled from disk; don't know at this point if cache is too small and/or repagent-related configs are (partially) to blame and/or there is excessive/unnecessary DML activity in the database
• number of disk i/o's (reading from log device) appear to be a bottleneck; if we can't increase the number of reads, can we increase the size of the reads?
• repagent is spending an excessive amount of time requesting trunc pt movements (~5 requests per second)

Suggestions:

• decrease the number of times repagent requests a trunc pt movement; (repagent) scan batch size = 10000, ltl batch size = 32768; increasing these values further (especially scan batch size) is possible but will want to see if performance metrics show there's room for improvement
• increase the logio size for the phoenix database; this will require a pool to be created in the phoenix_log cache of the same size (eg, sp_logiosize = 64KB, sp_pool_config = 64KB); NOTE: repagent config changes require the repagent to be stopped/started for config changes to take effect; NOTE: may need to add memory to the cache; objective at this point is to use larger (disk) io requests to get log pages off of disk at a faster rate; maintaining this larger logio size will depend on whether or not we can eliminate the flushing of log pages to disk
• consider adding memory to the phoenix_log cache; will need to add some for the (temporary?) bump up of logio size to 64KB; once repagent gets caught up it will be desirable if repagent can keep up with log activity (ie, no need to read log pages from disk) and part of this may require increasing the size the log cache (with the majority of memory allocated to the pool associated with logio size)
• review the DML activity that's generating the log activity; review batch processing as well as database design; objective is to look for opportunities to either reduce the amount of log activity and/or move some unnecessary log activity to a different database; common issue with batch processing is the use of temporary/staging tables in the main database (moving said tables to a different database, and thus different log, may help reduce log activity in the main database); another common practice is the rebuilding of entire data sets (delete all rows, insert all rows, perform multiple updates against all rows) when only a small percentage of the data set has changed since the previous rebuild
• review replication requirements; are there any tables that do not need to be replicated (eg, temporary/staging tables) and if so then umark those tables for replication (sp_setreptable/never)? are there some groupings of DML/log activity that could be replaced with stored proc and/or DML statement replication (objective being to reduce the volume of log activity that has to be processed by the repagent and forwarded to the repserver)? are there some groups of DML activity that do not need to be replicated (set replication on/off), though this would need to be carefully managed (disabling replication at the wrong time will leave the RDB out of sync)

This is going to be a repetitive process; make changes, monitor performance, make additional changes:

• continue running sp_sysmon (10-15 min) sessions
• consider running the occasional sp_sysmon / cache wizard session
• become familiar with the MDA tables related to repagent activity

[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

[MattthiasWild]

Hello Dilip.

The end of mainstream maintenance for SAP ASE 15.7 was in 2020. There are a lot of performance improvements in more recent versions of SAP ASE 16.0 and SRS 16.0.