Related to log cache at sybase

[suznCB]

We have a warm standby replication between two sybase ase databases, to tune performance of both sybase ase and sybase replication, we added a log cache for active database, on the side of this database, we noted it became more agile when we execute storedprocedure that deal with huge tables, i.e this procedure was taken about 2 min to finish, after adding the log cache it takes now about 50 sec, and in the sysmon report , in replication agent section, we found that time needed to scan log was decreaeed comparing to the time needed when we were exec the same procedure before adding the log cache, but we also noted the latency between two databases increased , is there any related issue between them ?

We get latency by calculate the difference between origin time and commited time in the rs_lastcommit table at standby database

Any hint will be useful

Regards

[suznCB]

Dears

would you help in our issue, Please?

[Mark_A_Parsons · Accepted solution]

You haven't provided any additional details about the original issue (latency) so I take it this topic is now on the back burner.

As for the change in topic (should probably be a new/separate question) ...

------------------

If you disable replication of some tables then those tables will be out of sync so if/when you perform a switch active you'll find the 'new' active database is 'missing' data from those non-replicated tables. While there's nothing wrong with this kind of setup (eg, you don't want to replicate what are effectively 'scratch' tables) you need to be 100% certain this is the correct approach for said tables.

As for disabling replication of some tables ... you can still use sp_reptostandby but then you'll want to follow that up with sp_setreptable <tablename>, 'never'. [See Example 6 in the documentation for sp_setreptable.]

------------------

As for the last item, this is merely highlighting the fact that if a table's primary key (used by Repserver to build a where clause for replicated delete/update statements) includes a column based on an approximate datatype (eg, real/float), then the where clause may not function properly due to the approximate nature of the values in said column (ie, if the where clause does not function properly then the delete/update statement may not be replicated). This has nothing to do with replication but rather the general issue of how a where clause may not function as expected when applied to a column based on an approximate datatype.

Your (internet search) reference is suggesting ... if you have a table with a primary key (or unique index) that references a real/float column ... that you explicitly define a primary key (in a table's repdef) to insure that it does not reference the real/float column; 'course this assumes you can define a primary key for the table sans the real/float column (though generally speaking you probably shouldn't have a primary key (or unique index) based on a real/float column to start with).

If you your table's primary key (or unique index) does not reference a column that uses an approximate datatype then you should be able to ignore this issue.

[suznCB · Accepted solution]

Thank you very much

[Mark_A_Parsons]

I don't see enough details (yet) to say one way or the other what the issue may be.

Consider:

• how big is the transaction(s) performed by the stored proc? is it one single transaction or a bunch of smaller transactions?
• has the stored proc been processing larger volumes of data lately?
• how are you measuring latency ... are you running a query directly against rs_lastcommit or are you using a (3rd party) GUI/process to 'monitor' replication latency?
• you say latency has increased but what is this increase in relation to? what was the latency prior to adding the log cache? what are the actual latency times? are you seeing this increase for every run of the proc?
• are there any differences between the primary and replicate dataservers? any glaring differences in the sp_sysmon data (for the same period of activity) between primary and replicate dataserver? does the replicate database have a comparably configured log cache?
• what kind of latency are you seeing for rs_ticket invocations (before, during and after the proc has executed; latency == difference between rs_ticket_history's pdb_t and rdb_t columns)? what kind of (rs_ticket) latency do you see when there's little/no activity in the primary db? (NOTE: depending on how the repagent is configured it may be necessary, when the primary database has been quiet for a period of time, to submit a few rs_ticket invocations so as to 'wake up' the repagent, and in this case you would want to look at the latency of the latter ticket)
• have you by any chance made config changes to the repserver and/or the replicate dataserver/database and if so what were they?

[suznCB]

Thanks for replying

I checked latency by rs_lastcommit table I calculated difference between origin time and destination time.

the execution time on the active database became better, for example a stored procedure which executed on active database wase taken 2 minutes but after adding log cache it takes now just seconds, BUT after a lot of monitoring I found that there are some tables excluded from warm standby replication

for example, that procedure deals with two tables which are excluded by using alter connection set u2none, i2none,d2none, but I found the operations on those two tables arrive to the rep server some times around 5000 transactions, while at the same time transactions on a dummy table transfer smoothly with no latency by checking count(*) on both active and standby databases

So now I am thinking about rebuild a warm standby replication but without using sp_reptostandby

Maybe I will get all objects in the database and set replicate table for each one individually or maybe add replication definition!

Would you advice please especially that our data is so sensitive we should be sure no data loss in replicated database and be sure we are able to switch active database when a disaster happend in the old active database

On the internet, I read some thing like the following which worried me:

In addition, create replication definitions on tables that contain approximate numeric datatypes so that the where clause used to construct the SQL statement applied to the standby database does not include these columns. The range and storage precision of approximate numeric datatypes (real, float) is machine-dependent, and may result in the wrong rows being modified, or the correct row not being found.

Would you h3lp and advice with thanks