Server Performance and Tuning
Q&A Log from October 24, Techcast
Questions and Answers
Asked: Where can I find detailed
information on the internal repserver counters?
Privately: THe best source of counter data is in the RSSD in table rs_statcounters.
Asked: Where can I find information explaining the repserver counters and how to best tune them?
Answered: The best source of information is available in the form of a M&C analysis package available from your Sybase Rep. Have them contact RepServer engineering for a copy. This package is in development, so we are looking for feedback.
Asked: which versions of rep server allow for using parallel DSI
Answered: Parallel-DSI was available as of version 11.0
Asked: What is the biggest
bottleneck in Rep server's performance
Answered: The DSI is the biggest bottleneck.
Asked: Can I use Parallel DSI for a system which replicates from Sybase 12.5 to a DB2 server on OS/390 ?
Answered: You can implement parallel-DSI with some effort. You will have to create a number of function strings to manage the rs_threads table in DB2. WIth the upcoming EBF to release 12.5, this effort goes away since we eliminate the need for rs_threads.
Asked: In warmstandby repserver
envs, can still only dbo do store-procedure exec replications in all repserver
versions ? if yes, and why ?
Answered: Do you mean not replicating maint user transaction?
Asked: in ASE12.0 at least
Answered: Repserver replicates sproc owned by any user. This includes dbo.
Asked: U mean warmstandby rep-server ?
Answered: Yes. Warm standby replication as well.
Asked: ASE12.0 or ASE12.0+ ?
Answered: This has been the feature since Repserver released.
Asked: what effect does parallelism
have on memory?
Answered: Each DSI/Executor thread will require some amount of memory for it's own structures. However, it is not significant. More significant is the memory required for the transactions each thread will handle. With more threads, depending on the size of your transactions, noticably more and more memory will be required. However, it is governed by the sqt_max_cache_size configuration.
Asked: Why does warmstandby repserver can NOT automatically replicating the transactions based on the existing user table indexes without manually creating the rep-defs ?
Answered: I assume you are talking about primary keys. You don't need to create replication definitions if it is not for primary key for performance.
Asked: can I have isolation 3 with wait fot commit
Answered: Currently this can not be configured without developing a custom function string to turn on the isolation level you want. However, we are considering allowing isolation level to be set independently of serialization method in a future release.
Asked: how to avoid deadlocking with parallel DSI in Rep server 11.0.3
Answered: In general, less DSIE threads and smaller group size help reducing parallel DSI deadlock
Asked: what is the impact of parallel DSI on system resources in terms CPU and memory?
Answered: In terms of memory, as more DSI/E threads are configured, more transactions will be held in memory at a time and so more memory required. You can govern the amount of memory used for this purpose with the sqt_max_cache_size configuration. In terms of CPU, currently RepServer runs on a single CPU, so more DSI/E threads will introduce more contention for CPU time. With the upcoming EBF to 12.5, RepServer will be SMP-enabled allowing it to make use of all available CPU's.
Asked: Can you comment on usefulness of Parallel DSI in warm standby setup?
Answered: I would say depends on your transaction profile. I would start with Parallel DSI in warm standby. If you get lots of deadlock even with very few DSIe, then I would turn off parallel DSI.
Asked: what is isolation level 3 ?
Answered: See http://manuals.sybase.com:80/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/53739;pt=53739 for an explanation of isolation levels.
Asked: So we still have to
create rep-defs for getting better warmstandby rep performance even though the
primary_key exists already? thanks alot.
Answered: Repserver knows primary key through replication definition. Without replication definitions, Repserve takes every column as part of primary key.
Asked: One question asked was
that they used parallel DSi because the queue was filling up. You suggested
that if they used the wait_for_commit would have helped the queue to be applied
faster? Is that correct?
Answered: Typically wait_for_commit is the slowest serialization method. If you are not experiencing a large number of contention issues with isolation_level_3, use that one to clear the queue faster (and safer than "none")
Asked: can you configure parallel dsi for one of many target databases?
Answered: Parallel DSI is configured for each DSI connection.