Replication Server Performance and Tuning
Q&A Log from October 22 Techcast

Questions and Answers:

Asked: Can RepServer replicate sp_addlogins
Answered: Currently there is no "native support" however, it can be done with a bit of work. If interested in the technique, please send me you e-mail.

Asked: what version repserver has parallel DSI
Answered: Parallel-DSI was introduced in version 11.0

Asked: How can you maximize throughput for unpartitioned tables by parallel dsi?
Answered: In version 12.5 we offer a technique to partition transactions based on a number of options including PDB User, transaction begin/commit time and transaction ID. By using this partitioning technique we expect customers to be able to gain more benefit from Parallel-DSI since this offers customers a way to configure the various DSI threads to distribute transactions in such a way that the contention between them is reduced.

Asked: if sqt_max_cache_size = 10 and with parallel DSI . dsi_num_threads to 5 and dsi_sqt_max_cache_size =0. Will the sqt_max_cache_size will be equally be distributed among parallel threads or each thread will get the max. of sqt_max_cache_size.
Answered: dsi_sqt_max_cache_size=0 tells each SQT to use the value of sqt_max_cache_size.

Asked: what is an atomic insert?
Answered: From database point of view, a transaction is an atomic event.

Asked: can we use parallel dsi for warm standby system
Answered: Yes, you can.

Asked: At what component the Rolled back transactiona are rejected
Answered: SQT, which checks the whole transaction and through out transactions that are rollbacked.

Asked: Is there anyway to find out how many transactions were processed by the replication server? (except the read column in admin who,sqm)
Answered: As of 12.1 RepServer offers a number of monitors and counters which can be used to monitor various aspects of RepServer processing, including what you ask for here. For more information refer to the Admin Guide for 12.1.

Asked: Can we use historical server to capture repserver metrics?
Answered: Historical server will not work with RepServer. However, RepServer engineering is actively engaged in developing tools and techniques for capturing and analyzing the output for the Monitor and Counter feature introduced in 12.1 Please ask your Sybase representative to get a copy of these tools to try out. It is not part of the product set yet and we are still soliciting feedback for the needed features.

Asked: Is there anyway to calculate/estimate the size of each cmd in a single transaction?
Answered: Assume you can estimate transaction size in your data server. Repserver tranaction size is approximately 2 to 3 times larger than the transation size in data server. It should never be bigger than 4 times of that size.

Asked: Can you quantify the % increase of no of transactions between parallel_dsi and single dsi thread?
Answered Privately: It is hard to say. It is depends on your transaction profile.

Asked: Is there a way to institute bi-directional replication without using stored procedures to send the data to the primary and then to the secondary??
Answered: Yes there is although you should keep in mind these two considerations: 1. RepServer design assumes exactly one primary copy of data. this is why by design RepServer expects you to use the technique you describe - "remote request for primary update" 2. To circumvent the design to enable update anywhere, database and application changes are required to prevent transaction "collissions" from multiple locations.

Asked: what would happen if transaction from one DSIs in parallel DSI is becoming victim opf deadlock? All the Parallel DSIs are rolled back? What will happend if again deadlocking on retry
Answered: With RepServer version 12.5 and earlier, when one DSI/E thread is deadlocked RepServer will rollback all open transactions and re-submit them serially. With the upcoming EBF to 12.5 there will be a new feature called Commit Control. When enabled, RepServer handles deadlock a bit more intelligently in that only the transaction in deadlock will be rolled back and re-submitted. We do, however, have safegards in place to force all transactions to be rolled back in the event that there is a danger of committing them out of sequence.

Asked: Large tran begin to be applied by DSI thread thread before DSI see the commit, I issued a large tran which contain over 100k cmds in a tran, why I don't see them on replicate site until I commit on primary site? How do I know those cmds start to be applied without waiting for a commit?
Answered: Assume you are not talking about warm standby. Transactions have to go to outbound queues. Without seeing commit, DIST would write transactions to outbound queue. Therefore, DSI won't see it.

Asked: Under what circumstances do you get mini-abort
Answered: From user level, you could set save point and rollback to the save point. From data server point, sometime it will do CLR, compansation log record, i.e., do things first and then rollback. Those are two major mini-rollback sources.

Asked: Is there any white paper on P&T for warm standy configuration.
Answered: Probably the best source of information is the Performance and Tuning whitepaper at http://my.sybase.com/detail?id=1015811

Asked: How relaible it is to use rs_subcmp for comparing data in large table on primary and standby. What if primary table is modified a lot ?
Answered: rs_subcmp is reliable product. But it is slow for large tables. When comparing active and standby, I am not sure it is a good tool to use when data is still replicating. All those data in queues won't be counted. You may not know it is really missing to still in replicating. Normally you need suspend applications to use rs_subcmp.

Asked: What is the BEST text source to learn Rep Server
Answered: I know that there are one or two RepServer texts published independent of Sybase. However, I've never reviewed them. For me, the best source is the documentation set. You can also check http://www.sybase.com/support/techdocs with RepServer "white papers".

Asked: I didn't see the records in the table on standby database.
Answered: Which isolation level you were using? If a transaction is not committed, you won't see the record if you are not in isolation level 0

Asked: Can parallel DSI be used for heterogeneous replication?
Answered: With 12.5 and earlier releases, it takes a bit of custom Function String work but can be done. With the upcoming 12.5 EBF RepServer introduces a feature called Commit Control which eliminates the need for rs_threads, thus opening up parallel-DSI to non-ASE replicates more easily.

Asked: I am currently running RS 12.0 and converting to RS 12.5. How do I best mimic 12.0 behavior in using 12.5?
Answered: Which behavior do you want to mimic?

Asked: I am not using paralell DSI. I believe I have a lot of contention.
Answered: You will get additional database connections if you are doing (de)materialization.

Asked: Eventhough Parallel Dsi is great but still we are single threaded on the repagent. Any words?
Answered: Of course we typically consider this issue with each new effort towards performance. However, all of our testing in-house and to my knowledge at customer sites has indicated that the performance bottle-neck is in the DSI. In that light, most performance efforts are concentrated in that area. We will more likely address the single-threaded RepAgent issue once that part becomes the bottle-neck.

Asked: when is this next EBF coming?
Answered: Due 1Q03.

Asked: Do you have special recommendation for users of peoplesoft applications trying to use replication server?
Answered: There are no "special" recommendations. We expect that typical performance tuning suggestions will work equally well in this environment.

Asked: When you do intend to make the rep sever use an SMP architecture instead of just an OPen server ?
Answered: In the upcoming 12.5 EBF, RepServer will be SMP-enabled.

Asked: how can the Rep12.5 handle the error/exception?
Answered: What type of error/exception you are talking about? For Repserver error and exception, it will be the same.

Asked: Is it more efficient for warm standby to still have repdefs, and if so, why?
Answered: Yes. Majorly because primary key information. Repserver does not need to update every columns.

Asked: can we use multiple dsi for single transaction?
Answered: Note: There is a difference between "multiple DSI" and "parallel DSI". However, neither case can a transaction span these DSI threads.

Asked: Do you have an updated version of GenRepDefs and Subs script for 12.5?
Answered: Those scripts are not part of RS release. There could be updated ones. But we don't have control over those scripts.

Asked: Are the DSIs dynamically spawned within the configured range ?
Answered: DSI/E threads are all spawned when RepServer boots up. We do this to confirm up frant that there are sufficient resources to handle the configure max. However, if a thread remains unused (for a configurable amount of time) that thread will be put to sleep and the connection freed.

Asked: if sqt_max_cach_size = 20MB. dsi_num_threads = 4 and dsi_sqt_max_cache_size =0. What will be thesqt available to each dsi thread? Is it 20MB or 5MB.
Answered: It will be 20MB. But that's 20MB to the DSI/S thread, not the parallel DSI/E threads.

Asked: To setup a unicode enabled repserver what should I do
Answered Privately: It is hard for a short answer. You need to have your data server to use utf8. Once you have utf8 in place, rest of things should be easy. There should be a section in Repserver manual to describe this.

Asked: what is the url for the white paper?
Answered: The URL for the P&T white paper is http://my.sybase.com/detail?id=1015811

Asked: How much to the DSI/E thread? Is it 20MB or 5MB.
Answered: No SQT cache is allotted to the DSI/E threads. They use DSI/S's SQT cache.

Asked: We have high volume transactions involving more than 100,000 rows per transaction. How does parallel DSI help us here?
Answered: To the extent that the transactions are independent, parallel DSI will help. If the transactions reference the same replicate database resources (at the row or page level for example) parallel-DSI will revert to serial application.

Asked: Any bench mark results on encription of data over the network Vs non-encription
Answered: I assume you are talking about SSL feature. The connection time could be multiple times slower. Atfer that, there should not be much performance impact. Unfortunately, we don't have numbers.

Asked: What is the different DSI/S's SQT and SQT+SQM?
Answered: Perhaps you mean, "What's the difference between DSI/S's SQT and DIST's SQT?" If so, the Distibutor's SQT runs on its on thread. DSI/S's SQT runs on the DSI/S's thread. Other than that, there is no difference.

Asked: Does it mean, 4 DSI/E can use the entire 20MB, if it can.
Answered: That's correct.

Asked: Is the out of box 12.5 version of replication server is DSI parallel?
Answered: Parallel DSI is not configured automatically.

Asked: Are there any plans to have the ASE RepAgent seperate from the database, so it can continue to read transactions from the logfiles when the database is down? Or does the use of the ltm prevent this? (mharrold@cas.org)
Answered: No plans currently but we will consider it.

Asked: If that is the case, why would anyone ever turn on paralell unless they could be sure that insert and updates on the same row would never occur consecutively? Our applications do insert and updates consecutively on the same row. What mode should I be running in assurecomplete data consistency?
For high volume transactions involving more than 100,000 rows per transaction. How does parallel DSI help us here?
Answered: To the extent that the transactions are independent, parallel DSI will help. If the transactions reference the same replicate database resources (at the row or page level for example) parallel-DSI will revert to serial application.