SAP Replication Server with two (correlated?) problems

[former_member350489]

We're replicating from one Primary ASE to three Replicate ASE's, using a MSA setup with two RS (PRS and RRS) on two sites. Every RS is "serving" two ASE's, and the setup is fully switchable, including a reverse of the replication flow. RS version is 15.7.1 SP102 and ASE version is 15.7.0 ESD#4.2.

I have noticed a slow increased amount of Memory Usage on both RS's, from a normal 3-5 % usage up to 30-40 % during a time of 2-3 weeks.

At the same time, I can see an increase on all three ASE's in the "number of open objects", displayed with sp_monitorconfig 'all'.

If I restart my connections or RS's, the Memory Usage drops down to a "normal" state on both RS, and the "number of open objects" also drops down to a "normal" state on all ASE's ! After this, it all starts ticking up again, slowly...

Did a bit of googlin' and found at least one known problem (the memory leak) https://launchpad.support.sap.com/#/notes/2221960/E that could be my type of memory problem, but I cannot find any reports having both of them or the "number of open objects" caused by RS...

The solution in the note I found is to upgrade RS (of course) and the workaround is to deactivate the RS option "dist_cmd_direct_replicate" at primary connection side or server-wide.

This parameter is however not described very well in the documentation, so I am curious about how it would affect any performance.

So my questions are;

• Have anyone out there any experience from one or both of these problem in a similar MSA setup ?
• If so, how did you fix the problem(s) ?
• Have anyone any experience from changing the parameter "dist_cmd_direct_replicate", and the outcome from changing it ?

Please Advice

Thanks

/Mike

[former_member350489]

Mark, I have totally forgotten to update this thread with the findings and my result after the changes in our RS's !

By turning off the "dynamic_sql" globally in our Replication Servers, both our problems has now disappeared;

- Replication Server memory usage is back to normal

- Number of open objects is no longer increasing up to the limits anymore

Problems solved

Thanks

Cheers /Mike

[former_member350489]

These are the types of objects that are increasing according to the output from dbcc des;

objname=*sq2136322818_0055194197ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2136334493_0101857914ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2136477912_0610685807ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2136776425_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2136894352_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2136895423_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137011208_1500200008ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137062192_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137376664_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137494591_0679312931ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137506266_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137546646_0169304417ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137806921_0608524408ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137869580_0590626820ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2137870651_1617653249ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138170235_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138210615_0273550946ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138233965_1225473354ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138430510_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138431581_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138534620_1969750368ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138651476_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138690785_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138808712_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)
objname=*sq2138951060_1820207394ss* dol_rowno=0 dol_vattcnt=0 dol_status: 0x00: (0x0000)

Currently there are about >15.000 of them and still increasing...

I do not recall why we turned on the "dynamic_sql", since we have had it that way more or less for years, but from my setup script there is a note saying that is should be related to the "dsi_bulk_copy" parameters ?

And for your information, the "literal autoparam" is set to 1 already and the "statement cache" = 4096 !

I will go ahead and try the change in our test environment

Thanks for your suggestions

Cheers

Mike

[former_member350489]

Hi Mark,

I actually do not know if we have had this problem before, as it is now, but I do know that we had one occasion a month ago on our Primary ASE when "Number of open objects" did ran out, then I could clearly see in the process list that a LOT of processes got into an "EXEC" state. But then it was a new application that did not reconnect properly.

This time it is happening on all the three Replicate ASE's, and they have all got Standby licenses (SF), so they do not have any active connections at all.

We have changed two things in January; I added a second license to both RS's, so they now run on two engines each and we moved the script that runs rs_ticket away from our Primary ASE now to be run from en external server instead of locally.

Yes, the RS reboot and/or connection restart makes the number of open objects to drop !

We are seeing the problem on all three Replicate ASE's.

I have not tracked down the objects yet, is it "dbcc des" I should use and write the output to a file for analyzing it ?

Both my RS's are configured equally, and the config is;

1> admin config, 'dynamic_sql'
2> go
Configuration Config Value Run Value Default Value Legal Values Datatype Status

----------------------------------------------------------------------------------------------------------------------------------------------
dynamic_sql on on off list: on,off string Connection/route restart required
dynamic_sql_cache_management mru mru fixed list: mru,fixed string Connection/route restart required
dynamic_sql_cache_size 2048 2048 100 range: 1,65535 integer Connection/route restart required

(3 rows affected)
===============================================================================

Cheers

Mike